python跟php如何共用mysql_pymysql - Python 3 多个函数共用一个mysql连接

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

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连接的效率？