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连接的效率?
大家讲道理2017-04-17 17:48:08
Python과 C++ Java의 차이점은 실행 프로세스가 기본 항목과 관련이 없고 호출 순서대로 실행된다는 점입니다.
으아악그래서 이 문장을 메인에 넣고 마지막 문장으로 실행해야 합니다