pymysql - Python 3 多个函数共用一个mysql连接

Question

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

Answer 1

Python跟C++ java的区别是它的执行流程跟main入口无关，而是按照调用的顺序来执行的。

connection.close()  #这里优先于main的执行，导致操作之前，连接已经关闭了。

所以你应该把这一句放在main里最后一句执行