DBUtil开发
问题
pymysql操作mysql,虽然简单,但每次都要链接数据库,获取游标,关闭游标,关闭链接。这些操作无技术含量,还要重复编写!!我们应该想法提高开发效率
解决方案
编写工具类,将公共的内容封装起来
获取链接,获取游标
关闭游标,关闭链接
合并DML方法
查询数据
- 单条查询
- 多条查询
import pymysql
class DBUtil:
config = {
'host':'localhost',
'port':3306,
'user':'root',
'passwd':'root',
'db':'test',
'charset':'utf8'
}
def __init__(self) -> None:
'''
获取连接
获取游标
'''
self.con = pymysql.connect(**DBUtil.config)
self.cursor = self.con.cursor()
def close(self) -> None:
'''
关闭连接和游标
:return:
'''
if self.cursor:
self.cursor.close()
if self.con:
self.con.close()
def excute_dml(self,sql,args):
try:
# 执行SQL
self.cursor.execute(sql,args)
# 提交事务
self.con.commit()
except Exception as e:
if self.con:
self.con.rollback()
finally:
self.close()
def query_one(self,sql,agrs):
'''
获取一条数据
'''
try:
# 执行SQL
self.cursor.execute(sql,agrs)
# 获取结果
rs = self.cursor.fetchone()
return rs
except Exception as e:
print(e)
finally:
self.close()
def query_all(self,sql):
'''
获取一条数据
'''
try:
# 执行SQL
self.cursor.execute(sql)
# 获取结果
rs = self.cursor.fetchall()
return rs
except Exception as e:
print(e)
finally:
self.close()
if __name__ == '__main__':
db = DBUtil()
sql = "select * from product where id = %s"
args = (1002)
# print(db.query_one(sql,args))
sql1 = "select * from product"
print(db.query_all(sql1))
((1001, '拯救者', Decimal('100.00'), 3, '好用实惠'),
(1002, '华为', Decimal('2200.00'), 4, '高端'),
(1003, '华为1', Decimal('2200.00'), 4, '高端1'),
(1004, '华为2', Decimal('2200.00'), 4, '高端2'),
(1005, '华为3', Decimal('2200.00'), 4, '高端3'),
(1006, '华为4', Decimal('2200.00'), 4, '高端4'),
(1008, '惠普', Decimal('2200.00'), 4, '高端7'),
(1009, '华为Mate', Decimal('2200.00'), 4, '高端9'),
(1010, '华为Mate', Decimal('2200.00'), 4, '高端9'))