在工作中,手写SQL语句基于execute执行,这种方式,性能是最好的!但是往往开发中,如果我们编写大量固定的SQL语句,也会导致程序的维护成本提升,同时将来如果应用程序要切换底层数据库的话,将会带来巨大的兼容问题。
import pymysql.cursors
class DB(object):
"""数据库工具类"""
connected = False # 数据库连接状态,False表示没有连接或连接失败
__conn = None # 数据库连接对象
class DBError(Exception):
"""数据库异常基类"""
pass
class DBConfigError(DBError):
"""数据库配置异常类"""
pass
class ConnectionError(DBError):
"""数据库连接异常类"""
pass
class ExecuteError(DBError):
"""数据库执行异常类"""
pass
def __init__(self, conf):
if type(conf) is not dict:
raise self.DBConfigError("错误: 数据库连接参数必须是字典类型!")
for key in ["host", "user", "password", "database"]:
if key not in conf.keys():
raise self.DBConfigError(f"错误: 数据库连接缺少'{key}'参数")
if not conf.get("port", None):
conf["port"] = 3306
if 'charset' not in conf.keys():
conf["charset"] = "utf8mb4"
try:
self.__conn = pymysql.connect(
host=conf['host'],
port=conf['port'],
user=conf['user'],
password=conf['password'],
database=conf['database'],
charset=conf['charset'],
cursorclass=pymysql.cursors.DictCursor
)
self.connected = True
except pymysql.Error as e:
raise self.ConnectionError(f"数据库连接失败: {e}")