• 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,否则会出现不可预料的结果。

  • 相关阅读:
    DLL相关注意
    给我个理由要用虚析构函数
    字符指针常量和字符数组
    给自己普及网络基本知识
    面向对象三大基本特性,五大基本原则
    C/C++内存区域划分
    C++多态性
    Java XML Schema详解
    Java SE 第一百零五讲 对象的克隆与浅拷贝
    Java SE 第九十七~一百零五讲 线程与进程
  • 原文地址:https://www.cnblogs.com/lanyangsh/p/10849533.html
Copyright © 2020-2023  润新知