1.本人刚接触Python,环境用的是Python3,使用pymysql模块连接数据库。为了节省资源,我是打算在一个python脚本中的各个函数里共用一个mysql连接,
2.代码如下所示:
# coding = utf-8 import re import pymysql.cursors # connect to database connection = pymysql.connect( host='localhost', user='root', password='', db='accessory_db', charset='utf8', cursorclass=pymysql.cursors.DictCursor ) DB_PREFIX = 'oc_' with connection.cursor() as cursor: sql = 'SELECT * FROM `oc_attribute` WHERE `attribute_id` = 1' cursor.execute(sql) results = cursor.fetchone() print(results) def insert_opencart_catgory(data): ''' :param data: dict for category details :return: void ''' global connection global DB_PREFIX with connection.cursor() as cursor: data['top'] = str(data['top']) if 'top' in data else 0 sql = "INSERT INTO " + DB_PREFIX + "category SET parent_id = '" + str(data['parent_id']) + "', `top` = '" \ + data['top'] + "', `column` = '" + str(data['column']) + "', sort_order = '" + str(data['sort_order']) \ + "', status = '" + str(data['status']) + "', date_modified = NOW(), date_added = NOW()" print(sql) try: cursor.execute(sql) print(results) connection.commit() except: print('error create category') connection.rollback() connection.close() if __name__ == '__main__': data = dict() data.update(parent_id=0, top=1, column=1, sort_order=1, status=1) insert_opencart_catgory(data)
输出结果是:
{'sort_order': 1, 'attribute_id': 1, 'attribute_group_id': 6} Traceback (most recent call last): INSERT INTO oc_category SET parent_id = '0', `top` = '1', `column` = '1', sort_order = '1', status = '1', date_modified = NOW(), date_added = NOW() File "E:/algorithm/采集器/crawel_opencart.py", line 59, in insert_opencart_catgory cursor.execute(sql) File "E:\python3\lib\site-packages\pymysql\cursors.py", line 158, in execute result = self._query(query) File "E:\python3\lib\site-packages\pymysql\cursors.py", line 308, in _query conn.query(q) error create category File "E:\python3\lib\site-packages\pymysql\connections.py", line 819, in query self._execute_command(COMMAND.COM_QUERY, sql) File "E:\python3\lib\site-packages\pymysql\connections.py", line 1016, in _execute_command raise err.InterfaceError("(0, '')") pymysql.err.InterfaceError: (0, '') During handling of the above exception, another exception occurred: Traceback (most recent call last): File "E:/algorithm/采集器/crawel_opencart.py", line 71, ininsert_opencart_catgory(data) File "E:/algorithm/采集器/crawel_opencart.py", line 64, in insert_opencart_catgory connection.rollback() File "E:\python3\lib\site-packages\pymysql\connections.py", line 762, in rollback self._execute_command(COMMAND.COM_QUERY, "ROLLBACK") File "E:\python3\lib\site-packages\pymysql\connections.py", line 1016, in _execute_command raise err.InterfaceError("(0, '')") pymysql.err.InterfaceError: (0, '')
我发现在外面使用的数据库查询语句是可以正常使用的,但是使用global关键字,在函数里面调用外面定义的connection时,却会报错。
3.我觉得很奇怪的是,为什么同样使用外部定义的变量DB_PREFIX 可以正确获取得到,而connection却不行?
求各位指点一二,有什么办法可以提高mysql连接的效率?
Python跟C++ java的区别是它的执行流程跟main入口无关,而是按照调用的顺序来执行的。
connection.close() #这里优先于main的执行,导致操作之前,连接已经关闭了。
所以你应该把这一句放在main里最后一句执行