[Python] connects MySQL, as well as multi-threaded, multi-process connection MySQL continued

Before referring to the practice of others, using DBUTILS.POOLEDDB to establish a number of reusable MySQL connection, some of the article wrong, improper method, led me to go a lot of detours, after a few days, finally found the right way to use.

Online there are many use DBUTILS.POOLEDDB module to establish connection pool, plus threading multi-threaded connection MySQL example, not only did not tell the reader how to verify whether to run multithreaded, and mostly using the cursor () to establish multi-threaded connection, this is wrong. (Verify that it is a multithreaded method see article at the end)

Use the cursor () to establish a multi-threaded connection, error when executing SQL query: "Commands out of sync; You can ' t run the This command now "

The reasons are:

1, the query results are not released, and then execute the query

No storage results between 2, two queries should be reused connection () instead of cursor ()

MySQL Document: Commands out of sync

If you get Commands out of sync; You can ' t run this command now in your client code, calling client functions in the wrong order.

This can happen, for example, if you is using Mysql_use_result () and try to execute a new query before you have called my Sql_free_result ().

It can also happen if you try to execute both queries that return data without calling Mysql_use_result () or mysql_store_re Sult () in between.

The descriptions of the two functions in the DBUTILS.POOLEDDB module are as follows:

def connection (self, shareable=true):

"" "Get a steady, cached Db-api 2 connection from the pool.

If shareable is set and the underlying DB-API 2 allows it,

Then the connection is shared with other threads.

"""

def cursor (self, *args, **kwargs):

"" "Return a new Cursor Object using the connection." ""

Return Steadydbcursor (self, *args, **kwargs)

So to connect MySQL to execute SQL concurrently, you should use Connect () instead of using the cursor.

Multi-threaded Connection Mysql.demo

#!/usr/bin/env python# -*- coding:utf-8 -*-import mysqldbimport threadingimport  TIMEFROM DBUTILS.POOLEDDB IMPORT POOLEDDB ' Establish connection pool, return connection pool address ' ' Def dbpool (Ip,port, username,password,dbname,char_set= ' UTF8 '):     connkwargs = {' host ':  ip,   ' Port ': port,  ' user ': username,  ' passwd ': password,  ' db ':  dbname, ' CharSet ':  char_set}    pool = pooleddb (mysqldb, mincached=10,  Maxcached=10, maxshared=10, maxconnections=10, **connkwargs)     return  pool "Remove a connection from the connection pool, perform sqlnum: The total number of rows affected by the statistics ' Num=0def dbconnect (db_pool):     global  num    conn = db_pool.connection ()     cur =  Conn.cursor ()     try:        cur.execute ("Select  * from table_name")         lock.acquire ()          num += cur.rowcount        lock.release ()      except exception as e:        print e     finally:        cur.close ()          conn.close () if __name__ ==  ' __main__ ': ' Lock: Generates a global lock for executing statements, The statistical value of the number of rows affected is locked in use, allowing only one thread at a time to modify the locked variable '     lock = threading '. Lock ()     st = time.time ()     db_pool = dbpool (ip= ' 127.0.0.1 ', port = 1234,username= ' root ', password= ' 1234 ', dbname = ' try ', char_set= ' UTF8 ')       "     simultaneously connect MySQL to execute the number of threads is less than equal to the maxconnections set in the preceding POOLEDDB, if greater than this amount, Will report an exception: Toomanyconnections.      set to run only 10 threads at a time,Cycle after running.      '     thread_list = []    for  I in range (+):         t = threading. Thread (target=dbconnect,args= (Db_pool,))         thread_list.append (t)     while len (thread_list)!=0:        if  len (thread_list) >10:            thread_ list_length = 10        else:             thread_list_length = len (thread_list)          sub_thread_list = []         For n in range (thread_list_length):             sub_thread_list.aPpend (thread_list[0])             thread_ List.remove (thread_list[0])         for i in sub_thread_ List:            i.start ()          for j in sub_thread_list:             j.join ()     et = time.time ()      print et - st,num

Check if the program is multithreaded at run:

1. You can view the number of processes executed through "pstree-p PID" on the execution host

2. View the number of connections and SQL execution in MySQL: select * from Information_schema.processlist where host like ' 192.168.1.1% ' ORDER by 1

[Python] connects MySQL, as well as multi-threaded, multi-process connection MySQL continued