python MySQLdb 一个连接connection多个cursor

使用MySQLdb时，如果创建一个连接connection，多个线程同时使用，会不会有问题？

在下文中，我们将模拟这种场景，看是否会出现问题。

1.示例

1.1 正常的情况

创建一个连接，两个线程同时使用这个连接，生成游标cursor，进行查询，并输出结果。

程序启动后，让线程1睡眠1s，保证让线程2线执行。

import MySQLdb
import threading
import time


def get_connection():
        host = "127.0.0.1"
        port = 3306
        user = "root"
        passwd = "Aa123456"

        conn = MySQLdb.connect(host=host, port=port, user=user,passwd=passwd, connect_timeout=2, charset="utf8")
        return conn


def thread1_runtime(conn, sql):
        time.sleep(1)
        cursor = conn.cursor()
        cursor.execute(sql)

        ret = cursor.fetchone()
        thread_name = threading.current_thread().name
        print("thread name:%s, ret:%s" %(thread_name, ret))

def thread2_runtime(conn, sql):
        cursor = conn.cursor()
        cursor.execute(sql)

        ret = cursor.fetchone()
        thread_name = threading.current_thread().name
        print("thread name:%s, ret:%s" %(thread_name, ret))
        
        
if __name__ == "__main__":

        thread1_sql = "select 1"
        thread2_sql = "select 2"

        conn = get_connection()

        thread1 = threading.Thread(target=thread1_runtime, name="thread 001", args=(conn, thread1_sql))
        thread2 = threading.Thread(target=thread2_runtime, name="thread 002", args=(conn, thread2_sql))


        thread1.start()
        thread2.start()


        thread1.join()
        thread2.join()

        print("...main exit....")

从结果可以看到，一切正常。

output:

thread name:thread 002, ret:(2,)

thread name:thread 001, ret:(1,)

…main exit…

1.2 异常的情况

以下例子中，程序启动后，线程1和线程2使用同一连接创建新的游标cursor，执行查询。

接着，线程1睡眠1秒，线程2睡眠2秒。
最后各自打印查询结果。

import MySQLdb
import threading
import time


def get_connection():
        host = "127.0.0.1"
        port = 3306
        user = "root"
        passwd = "Aa123456"

        conn = MySQLdb.connect(host=host, port=port, user=user,passwd=passwd, connect_timeout=2, charset="utf8")
        return conn


def thread1_runtime(conn, sql):
        cursor = conn.cursor()
        cursor.execute(sql)

        time.sleep(1)
        ret = cursor.fetchone()
        thread_name = threading.current_thread().name
        print("thread name:%s, ret:%s" %(thread_name, ret))

def thread2_runtime(conn, sql):
        cursor = conn.cursor()
        cursor.execute(sql)

        time.sleep(2)
        ret = cursor.fetchone()
        thread_name = threading.current_thread().name
        print("thread name:%s, ret:%s" %(thread_name, ret))

if __name__ == "__main__":

        thread1_sql = "select 1"
        thread2_sql = "select 2"

        conn = get_connection()

        thread1 = threading.Thread(target=thread1_runtime, name="thread 001", args=(conn, thread1_sql))
        thread2 = threading.Thread(target=thread2_runtime, name="thread 002", args=(conn, thread2_sql))

        thread1.start()
        thread2.start()

        thread1.join()
        thread2.join()

        print("...main exit....")

output:

Exception in thread thread 002:
Traceback (most recent call last):
File “/usr/local/Cellar/python/3.6.4_4/Frameworks/Python.framework/Versions/3.6/lib/python3.6/threading.py”, line 916, in _bootstrap_inner
self.run()
File “/usr/local/Cellar/python/3.6.4_4/Frameworks/Python.framework/Versions/3.6/lib/python3.6/threading.py”, line 864, in run
self._target(*self._args, **self._kwargs)
File “one_conn_mutiple_cursor.py”, line 28, in thread2_runtime
cursor.execute(sql)
File “/Users/lanyang/workspace/orange-service/.venv/lib/python3.6/site-packages/MySQLdb/cursors.py”, line 198, in execute
res = self._query(query)
File “/Users/lanyang/workspace/orange-service/.venv/lib/python3.6/site-packages/MySQLdb/cursors.py”, line 304, in _query
db.query(q)
File “/Users/lanyang/workspace/orange-service/.venv/lib/python3.6/site-packages/MySQLdb/connections.py”, line 217, in query
_mysql.connection.query(self, query)
MySQLdb._exceptions.ProgrammingError: (2014, “Commands out of sync; you can’t run this command now”)

thread name:thread 001, ret:(1,)
…main exit…

从打印结果可以看到，线程1可以正常打印结果，但线程2报错了。

线程1睡眠先结束，去获取查询结果，可以打印。
线程2睡眠后结束，再去获取查询结果，已经无法获取结果了。

2.总结

从上面例子中，我们可以得出结论：

一个连接connection同一个时间点只能有一个cursor，执行sql，并获取结果。

所以，不要在多个线程中同时使用一个连接connection，否则会出现不可预料的结果。