1.新建一个项目，目录结构如下：
新建.py文件“opmysql.py”,输入以下代码：
1 import logging, os, pymysql 2 from public import config 3 class OperationDbInterface(object): 4 def __init__(self,host_db='##', user_db='##', passwd_db='##', name_db='##', port_db=3006, link_type=0): 5 """ 6 :param host_db: 数据库服务主机 7 :param user_db: 数据库用户名 8 :param passwd_db: 数据库密码 9 :param name_db: 数据库名称 10 :param port_db: 端口号，整型数据 11 :param link_type: 连接类型，用于设置输出数据是元组还是字典，默认是字典，link_type=0 12 :return:游标 13 """ 14 try: 15 if link_type == 0: 16 self.conn = pymysql.connect(host=host_db, user=user_db, passwd=passwd_db, db=name_db, port=port_db, charset='utf8') 17 self.cur = self.conn.cursor() 18 except pymysql.Error as e: 19 print("创建数据库连接失败|Mysql Error %d: %s" % (e.args[0], e.args[1])) 20 logging.basicConfig(filename=config.src_path + '/log/syserror.log', level=logging.DEBUG, format='%(asctime)s %(filename)s[line:%(lineno)d]%(levelname)%s %(message)s') 21 logger = logging.getLogger(__name__) 22 logger.exception(e) 23 24 # 定义单条数据操作，包含删除、更新操作 25 def op_sql(self, condition): 26 """ 27 28 :param condition: SQL语句，该通用方法可用来替代updateone, deleteone 29 :return: 字典形式 30 """ 31 try: 32 self.cur.execute(condition) # 执行SQL语句 33 self.conn.commit() # 提交游标数据 34 result = {'code':'0000', 'message':'执行通用操作成功', 'data':[]} 35 36 except pymysql.Error as e: 37 self.conn.rollback() # 执行回滚操作 38 result = {'code': '9999', 'message': '执行通用操作异常', 'data':[]} 39 print("数据库错误|op_sql %d: %s" % (e.args[0], e.args[1])) 40 logging.basicConfig(filename=config.src_path + '/log/syserror.log', level=logging.DEBUG, format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s') 41 logger = logging.getLogger(__name__) 42 logger.exception(e) 43 return result 44 45 # 查询表中单条数据 46 def select_one(self, condition): 47 """ 48 49 :param condition: SQL语句 50 :return: 字典形式的单条查询结果 51 """ 52 try: 53 rows_affect = self.cur.execute(condition) 54 if rows_affect > 0: # 查询结果返回数据大于0 55 results = self.cur.fetchone() # 获取一条结果 56 result = {'code': '0000', 'message': '执行单条查询操作成功', 'data': results} 57 else: 58 result = {'code': '0000', 'message': '执行单条查询操作成功', 'data': []} 59 60 except pymysql.Error as e: 61 self.conn.rollback() # 执行回滚操作 62 result = {'code': '9999', 'message': '执行单条查询操作异常', 'data': []} 63 print("数据库错误|select_one %d: %s" % (e.args[0], e.args[1])) 64 logging.basicConfig(filename=config.src_path + '/log/syserror.log', level=logging.DEBUG, format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s') 65 logger = logging.getLogger(__name__) 66 logger.exception(e) 67 return result 68 69 # 查询表中多条数据 70 def select_all(self, condition): 71 """ 72 73 :param condition: SQL语句 74 :return: 字典形式的批量查询结果 75 """ 76 try: 77 rows_affect = self.cur.execute(condition) 78 if rows_affect > 0: # 查询结果返回数据大于0 79 self.cur.scroll(0, mode='absolute') # 将鼠标光标放回到初始位置 80 results = self.cur.fetchall() # 返回游标中所有结果 81 result = {'code': '0000', 'message': '执行批量查询操作成功', 'data': results} 82 else: 83 result = {'code': '0000', 'message': '执行批量查询操作成功', 'data': []} 84 85 except pymysql.Error as e: 86 self.conn.rollback() # 执行回滚操作 87 result = {'code': '9999', 'message': '执行批量查询操作异常', 'data': []} 88 print("数据库错误|select_all %d: %s" % (e.args[0], e.args[1])) 89 logging.basicConfig(filename=config.src_path + '/log/syserror.log', level=logging.DEBUG, 90 format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s') 91 logger = logging.getLogger(__name__) 92 logger.exception(e) 93 return result 94 95 # 查询表中多条数据 96 def insert_data(self, condition, params): 97 """ 98 99 :param condition: insert语句 100 :param params: insert 数据，列表形式[('3','Tom','1 year)] 101 :return: 字典形式的批量插入数据结果 102 """ 103 try: 104 results = self.cur.executemany(condition, params) # 返回插入的数据条数 105 self.conn.commit() 106 result = {'code': '0000', 'message': '执行批量查询操作成功', 'data': results} 107 108 except pymysql.Error as e: 109 self.conn.rollback() # 执行回滚操作 110 result = {'code': '9999', 'message': '执行批量插入操作异常', 'data': []} 111 print("数据库错误|insert_more %d: %s" % (e.args[0], e.args[1])) 112 logging.basicConfig(filename=config.src_path + '/log/syserror.log', level=logging.DEBUG, 113 format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s') 114 logger = logging.getLogger(__name__) 115 logger.exception(e) 116 return result 117 118 # 关闭数据库 119 def __del__(self): 120 if self.cur != None: 121 self.cur.close() # 关闭数据库 122 if self.conn != None: 123 self.conn.close() # 释放数据库资源 124 125 126 if __name__ == "__main__": 127 test=OperationDbInterface() # 实例化类 128 result_select_all = test.select_all("SELECT * FROM purchaseinquiryitem") # 查询多条数据 129 result_select_one = test.select_one("SELECT * FROM purchaseinquiryitem WHERE id=61") # 查询单条数据 130 131 result_op_sql = test.op_sql("update purchaseinquiryitem set Brand='test' WHERE id=61") # 通用操作 132 result = test.insert_data("insert into purchaseinquiryitem(CreationTime, BillNo, PurchaseQty, Brand, GoodsName, Unit) values (%s,%s,%s,%s,%s,%s)", [('2020-05-18 10:20:45.210269', 'C11202005181020455731', 100, '测试', 'tessGoodsNmae', '组')]) # 插入操作 133 print(result_select_all['data'], result_select_all['message']) 134 print(result_select_one['data'], result_select_one['message']) 135 print(result['data'], result['message'])
其中导入模块的配置文件config.py文件如下：
1 # -*- coding:utf-8 -*- 2 import os 3 src_path = os.path.dirname(os.path.dirname(os.path.realpath(__file__))) 4 print(src_path)
运行opmysql.py文件返回如下：
E:Pythonpython.exe F:/interface_caiyuntest/common/opmysql.py
F:interface_caiyuntest
((1, datetime.datetime(2020, 5, 16, 11, 37, 6, 757850), 11927590, datetime.datetime(2020, 5, 16, 11, 37, 6, 758095), 11927590, 'C11202005161137011190', datetime.datetime(2020, 5, 20, 0, 0), 10, 'aaa', 'NANOSET COLOR', 'C11-20200515029', '华强方特（深圳）互联科技有限公司', '[{"Name": "规格", "Value": "4300*1590"}]', '桶', 2102020000006, '湖南省长沙市宁乡县夏铎铺镇凤桥社区龙凤山华强方特项目部', 0), (2, datetime.datetime(2020, 5, 16, 11, 37, 6, 758273), 11927590, datetime.datetime(2020, 5, 16, 11, 37, 6, 758292), 11927590, 'C11202005161137011190', datetime.datetime(2020, 5, 20, 0, 0), 10, 'bbb', 'FANTAWILD DREAMLAND横牌1', 'C11-20200515029', '华强方特（深圳）互联科技有限公司', '[{"Name": "规格", "Value": "1000*80"}]', '套', 2002020000001, '湖南省长沙市宁乡县夏铎铺镇凤桥社区龙凤山华强方特项目部', 0), (43, datetime.datetime(2020, 5, 17, 17, 4, 49, 10661), 11940703, datetime.datetime(2020, 5, 17, 17, 4, 49, 10675), 11940703, 'C11202005171704486894', datetime.datetime(2020, 8, 20, 0, 0), 100, '联想', '19寸电脑显示器', '', '华强方特（深圳）互联科技有限公司', '[]', '个', 2020003697, '甘肃省嘉峪关市南市区热源厂三楼热源厂华强项目部', 0), (44, datetime.datetime(2020, 5, 17, 17, 4, 49, 10707), 11940703, datetime.datetime(2020, 5, 17, 17, 4, 49, 10745), 11940703, 'C11202005171704486894', datetime.datetime(2020, 6, 6, 0, 0), 50, '坚果', '72小时精通 电脑故障诊断排除（全彩版)', '', '华强方特（深圳）互联科技有限公司', '[]', '个', 2020007011, '甘肃省嘉峪关市南市区热源厂三楼热源厂华强项目部', 0), (45, datetime.datetime(2020, 5, 17, 17, 4, 49, 10763), 11940703, datetime.datetime(2020, 5, 17, 17, 4, 49, 10764), 11940703, 'C11202005171704486894', datetime.datetime(2020, 5, 20, 0, 0), 10, '华为', '11.2声道网络影音控制器', '', '华强方特（深圳）互联科技有限公司', '[]', '台', 2020002186, '甘肃省嘉峪关市南市区热源厂三楼热源厂华强项目部', 0), (46, datetime.datetime(2020, 5, 17, 17, 4, 49, 10766), 11940703, datetime.datetime(2020, 5, 17, 17, 4, 49, 10767), 11940703, 'C11202005171704486894', datetime.datetime(2020, 6, 20, 0, 0), 10, '小米', '23W室外草地音箱', '', '华强方特（深圳）互联科技有限公司', '[]', '台', 2020004704, '广东省汕头市龙湖区泰星庄泰星路12号', 0), (47, datetime.datetime(2020, 5, 17, 17, 4, 49, 10775), 11940703, datetime.datetime(2020, 5, 17, 17, 4, 49, 10776), 11940703, 'C11202005171704486894', datetime.datetime(2020, 7, 28, 0, 0), 80, '爱普生', '4D影院偏振镜架', '', '华强方特（深圳）互联科技有限公司', '[]', '件', 2020007444, '甘肃省嘉峪关市南市区热源厂三楼热源厂华强项目部', 0), (48, datetime.datetime(2020, 5, 17, 17, 4, 49, 10778), 11940703, datetime.datetime(2020, 5, 17, 17, 4, 49, 10779), 11940703, 'C11202005171704486894', datetime.datetime(2020, 7, 28, 0, 0), 80, '爱普生', '4D影院偏振镜架', '', '华强方特（深圳）互联科技有限公司', '[]', '件', 2020007444, '宁波市杭州湾新区天宝路188号方特总仓', 0), (49, datetime.datetime(2020, 5, 17, 17, 4, 49, 10781), 11940703, datetime.datetime(2020, 5, 17, 17, 4, 49, 10782), 11940703, 'C11202005171704486894', datetime.datetime(2020, 7, 28, 0, 0), 80, '爱普生', '4D影院偏振镜架', '', '华强方特（深圳）互联科技有限公司', '[]', '件', 2020007444, '甘肃省嘉峪关市南市区热源厂三楼热源厂华强项目部', 0), (50, datetime.datetime(2020, 5, 17, 17, 4, 49, 10783), 11940703, datetime.datetime(2020, 5, 17, 17, 4, 49, 10785), 11940703, 'C11202005171704486894', datetime.datetime(2020, 7, 28, 0, 0), 80, '爱普生', '4D影院偏振镜架', '', '华强方特（深圳）互联科技有限公司', '[]', '件', 2020007444, '甘肃省嘉峪关市南市区热源厂三楼热源厂华强项目部', 0), (51, datetime.datetime(2020, 5, 17, 17, 4, 49, 10786), 11940703, datetime.datetime(2020, 5, 17, 17, 4, 49, 10787), 11940703, 'C11202005171704486894', datetime.datetime(2020, 7, 28, 0, 0), 80, '爱普生', '4D影院偏振镜架', '', '华强方特（深圳）互联科技有限公司', '[]', '件', 2020007444, '甘肃省嘉峪关市南市区热源厂三楼热源厂华强项目部', 0), (52, datetime.datetime(2020, 5, 17, 17, 4, 49, 10789), 11940703, datetime.datetime(2020, 5, 17, 17, 4, 49, 10790), 11940703, 'C11202005171704486894', datetime.datetime(2020, 7, 28, 0, 0), 80, '爱普生', '4D影院偏振镜架', '', '华强方特（深圳）互联科技有限公司', '[]', '件', 2020007444, '宁波市杭州湾新区天宝路188号方特总仓', 0), (59, datetime.datetime(2020, 5, 18, 10, 20, 45, 210264), 11940703, datetime.datetime(2020, 5, 18, 10, 20, 45, 210265), 11940703, 'C11202005181020455731', datetime.datetime(2020, 8, 20, 0, 0), 100, '联想', '19寸电脑显示器', '', '华强方特（深圳）互联科技有限公司', '[{"Name": "颜色", "Value": "黑色"}]', '个', 2020003697, '芜湖市鸠江区赤铸山东路华强文化科技产业园方特东方神画公园', 0), (60, datetime.datetime(2020, 5, 18, 10, 20, 45, 210267), 11940703, datetime.datetime(2020, 5, 18, 10, 20, 45, 210268), 11940703, 'C11202005181020455731', datetime.datetime(2020, 6, 6, 0, 0), 50, '坚果', '72小时精通 电脑故障诊断排除（全彩版)', '', '华强方特（深圳）互联科技有限公司', '[{"Name": "尺寸", "Value": "36寸"}]', '个', 2020007011, '甘肃省嘉峪关市南市区热源厂三楼热源厂华强项目部', 0), (61, datetime.datetime(2020, 5, 18, 10, 20, 45, 210269), 11940703, datetime.datetime(2020, 5, 18, 10, 20, 45, 210270), 11940703, 'C11202005181020455731', datetime.datetime(2020, 5, 20, 0, 0), 10, 'test', '11.2声道网络影音控制器', '', '华强方特（深圳）互联科技有限公司', '[{"Name": "重量", "Value": "5.6kg"}]', '台', 2020002186, '广东省汕头市龙湖区泰星庄泰星路12号', 0)) 执行批量查询操作成功
(61, datetime.datetime(2020, 5, 18, 10, 20, 45, 210269), 11940703, datetime.datetime(2020, 5, 18, 10, 20, 45, 210270), 11940703, 'C11202005181020455731', datetime.datetime(2020, 5, 20, 0, 0), 10, 'test', '11.2声道网络影音控制器', '', '华强方特（深圳）互联科技有限公司', '[{"Name": "重量", "Value": "5.6kg"}]', '台', 2020002186, '广东省汕头市龙湖区泰星庄泰星路12号', 0) 执行单条查询操作成功
1 执行批量查询操作成功
Process finished with exit code 0
数据库中可显示新插入成功的记录
