Python接続MYSQLデータベースクラス
10329 ワード
Python接続MYSQLデータベースクラス
DBHelper.py:
構成情報db_config.py
テストコード:
DBHelper.py:
import pymysql
import logging
import sys
from mySQL.db_config import db_config
#
# logger
logger = logging.getLogger("baseSpider")
#
formatter = logging.Formatter('%(asctime)s\
%(levelname)-8s:%(message)s')
#
file_handler = logging.FileHandler("operation_database.log")
file_handler.setFormatter(formatter)
#
console_handler = logging.StreamHandler(sys.stdout)
console_handler.setFormatter(formatter)
# logge
logger.addHandler(file_handler)
logger.addHandler(console_handler)
logger.setLevel(logging.INFO)
class DBHelper():
# ,
def __init__(self,sql,params=None):
self.sql = sql
self.params = params
self.conn = None
self.cur = None
def connectiondatabase(self):
print(db_config['host'],db_config['username'],db_config['password'],db_config['database'],db_config['charset'])
try:
self.conn = pymysql.connect(db_config['host'],db_config['username'],
db_config['password'],db_config['database'],charset=db_config['charset'])
except:
logger.error("connectDatabase failed")
return False
self.cur = self.conn.cursor()
return True
#
def closedatabase(self):
# , ;
if self.conn and self.cur:
self.cur.close()
self.conn.close()
return True
# sq ,
def execute(self):
self.connectiondatabase()
try:
if self.conn and self.cur:
# , sql,
self.cur.execute(self.sql,self.params)
self.conn.commit()
except:
logger.error("execute failed: " + self.sql)
logger.error("params: " + self.params)
self.closedatabase()
return False
return True
#
def select(self):
self.connectiondatabase()
self.cur.execute(self.sql,self.params)
result = self.cur.fetchall()
print(result)
return result
構成情報db_config.py
db_config = {
'host':'127.0.0.1',
'port':3306,
'username':'root',
'password':'admin',
'database':'DataBase',
'charset':'utf8'
}
テストコード:
from mySQL import DBHelper as mdb
def main():
# mysql,
SQL=r'SELECT * FROM TUsers'
db=mdb.DBHelper(SQL)
rows=db.select()
# , , ,
for row in rows:
print(row)
if __name__ == '__main__':
main()