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, in <module>
insert_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连接的效率?
The difference between Python and C++ Java is that its execution process has nothing to do with the main entry, but is executed in the order of calls.
So you should put this sentence in main and execute it as the last sentence