[python] 串連MySQL操作

標籤：python mysqldb

環境：Linux CentOS6.7，python 2.7.13

說明：串連MySQL，進行增刪改查操作，並將執行的SQL和耗時記錄到日誌裡

demo:

#!/usr/bin/env python# -*- coding:utf-8 -*-import MySQLdbimport loggingimport time‘‘‘設定日誌輸出路徑和格式‘‘‘logging.basicConfig(level=logging.INFO,                    format=‘%(asctime)s %(levelname)s %(message)s‘,                    datefmt=‘%Y-%m-%d %H:%M:%S‘,                    filename=‘/home/sql.log‘,                    filemode=‘a+‘)‘‘‘執行的SQL語句可能有多個，可以在這邊進行組裝‘‘‘def sql_assemble(*args):    sql_list = [        ‘delete from table_a where id = %s‘ % args[0],        ‘delete from table_b where content in %s‘ % args[1],        ‘update table_c set aa = %s‘ % args[2],    ]    return sql_listclass mysqlopr(object):    def __init__(self,ip,port,username,password,dbname,char_set=‘utf8‘):‘‘‘建立對象的時候就要求將參數傳入進來‘‘‘        self.ip = ip        self.port = port        self.username = username        self.password = password        self.dbname = dbname        self.char_set = char_set    def connect(self):‘‘‘mysql如果開啟了安全模式，insert、delete和update無法執行，需要先設定SET SQL_SAFE_UPDATES = 0執行單條SQL，使用execute(),執行多條SQL，可以使用executemany()，但只有INSERT和REPLACE會執行的快點。說明如下executemany：This method improves performance on multiple-row INSERT and REPLACE. Otherwise it is equivalent to looping over args with execute().‘‘‘        self.db = MySQLdb.connect(host=self.ip,port = self.port,user=self.username,passwd=self.password,db =self.dbname,charset=self.char_set)        self.cursor = self.db.cursor()        #sql_s = ‘SET SQL_SAFE_UPDATES = 0‘        #self.cursor.execute(sql_s)        logging.info(‘link to mysqldb: ‘+self.dbname)    def select(self,sql): ‘‘‘sst :查詢開始時間set :查詢結束時間fetchone(): 只返回一行fetchall(): 返回所有行fetchmany(size): 返回size行rowcount: 這是一個long型唯讀屬性，返回執行execute()方法後影響的行數。‘‘‘        try:            logging.info(sql)            self.index_list = []            sst = time.time()            self.cursor.execute(sql)            set = time.time()#select_result = self.cursor.fetchone()#select_result = self.cursor.fetchmany(10)            select_result = self.cursor.fetchall()            #print self.cursor.rowcount,type(self.cursor.rowcount)            logging.info(‘select count: ‘ + self.cursor.rowcount + ‘, cost :‘ + str(sst - set))        except Exception as e:            print e            logging.error("Error: unable to fecth data" + str(e))    def insert(self):‘‘‘和其他方法類似，這邊省略‘‘‘        pass    def update(self):‘‘‘和其他方法類似，這邊省略‘‘‘        pass    def delete(self,sql):‘‘‘dst:刪除操作開始時間det:刪除操作結束時間刪除成功提交，失敗復原‘‘‘        try:            logging.info(sql)            dst = time.time()            self.cursor.execute(sql)            self.commit()            det = time.time()            logging.info(‘delete row: ‘ + str(self.cursor.rowcount) + ‘, cost :‘ + str(det - dst))        except Exception as e:            print e            self.rollback()            logging.error(str(sql) + ‘,‘ + str(e))    def commit(self):        self.db.commit()    def rollback(self):        self.db.rollback()    def close(self):       self.cursor.close()       self.db.close()if __name__ == ‘__main__‘:    st = time.time()    a = mysqlopr(ip=‘192.168.1.1‘,port = 8888,username=‘try‘,password=‘123456‘,dbname =‘trydb‘,char_set=‘utf8‘)    a.connect()‘‘‘如果參數相同，SQL語句不同，可以現在sql_assemble方法中配置好SQL，再把參數傳遞進去。或者這邊直接調用對應的方法執行這裡過程省略‘‘‘    a.close()    et = time.time()    logging.info("SQL executed over,cost: " + str(et -st))    print et - st

多線程、多進程串連MySQL說明：

1、多線程串連：

起初我使用threading.thread模組，先建立一個MySQL串連，然後由多個線程來執行具體的SQL。但發現在執行的時候，不是報MySQL串連被關閉，就是出現其他異常錯誤。上網查詢，是因為多個線程無法共用一個資料庫連接，會出現不可預測的情況，官方文檔說明如下：

The MySQL protocol can not handle multiple threads using the same connection at once. Some earlier versions of MySQLdb utilized locking to achieve a threadsafety of 2. While this is not terribly hard to accomplish using the standard Cursor class (which uses mysql_store_result()), it is complicated by SSCursor (which uses mysql_use_result(); with the latter you must ensure all the rows have been read before another query can be executed. It is further complicated by the addition of transactions, since transactions start when a cursor execute a query, but end when COMMIT or ROLLBACK is executed by the Connection object. Two threads simply cannot share a connection while a transaction is in progress, in addition to not being able to share it during query execution. This excessively complicated the code to the point where it just isn‘t worth it.

The general upshot of this is: Don‘t share connections between threads. It‘s really not worth your effort or mine, and in the end, will probably hurt performance, since the MySQL server runs a separate thread for each connection. You can certainly do things like cache connections in a pool, and give those connections to one thread at a time. If you let two threads use a connection simultaneously, the MySQL client library will probably upchuck and die. You have been warned.

For threaded applications, try using a connection pool. This can be done using the Pool module.

官方建議使用串連池模組，參照了他人的做法，使用DBUtils.PooledDB來建立線程串連池，一次性建立多個串連，但是查詢MySQL中 串連 執行SQL語句 情況，發現也只有一個串連有在執行SQL，其他串連都是sleep狀態，至今不明白為何是這情況。

2、多進程串連：

後面沒轍，改用多進程（multiprocessing模組），可以和MySQL建立多個串連並發執行SQL。對比執行耗時，整體效能比單個進程快，但其中單個SQL的執行效率，多進程沒有沒有單進程執行的快。

demo:

#!/usr/bin/env python# -*- coding:utf-8 -*-import MySQLdbfrom multiprocessing import Process,Poolclass mysqlopr():    ‘‘‘省略‘‘‘pool = Pool(5)####設定進程數for i in range(10):    pool.apply_async(func=run_sql_func, args=(arg,))####非同步執行    #pool.apply(func=run_sql_func, args=(arg,))                ####同步執行，官方不建議使用，python3.+版本已無該方法pool.close()pool.join()  # 進程池中進程執行完畢後再關閉，如果注釋，那麼程式直接關閉。

[python] 串連MySQL操作