Python environment tests MySQLdb, Dbutil, sqlobject performance

First introduce the following mysqldb, Dbutil, Sqlobject:

(1) MySQLdb is the interface for Python to connect MySQL database, it implements the Python database API specification V2.0, based on the MySQL C API. In addition to MySQLdb, Python can also be oursql, pymysql, Myconnpy and other modules to achieve MySQL database operations;

(2) Several connection pools are provided in Dbutil to improve the access performance of the database, such as POOLEDDB,PESISTENTDB, etc.

(3) Sqlobject can implement the third-party module of database ORM Mapping, it can easily manipulate the records in database by object and instance.

To test the performance of these three, a simple example: 50 concurrent access to 4,000 records of a single table, the database records as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/5B/59/wKioL1UHAynA120PAACIghwJV_M537.jpg "title=" db. PNG "alt=" Wkiol1uhayna120paacighwjv_m537.jpg "/>

The test code is as follows:

1, MySQLdb code as follows, where the connection pool related code in CONNDB () temporarily made a comment, remove this comment can use connection pools to create a database connection:

(1) dboperator.py

import mysqldbfrom stockmining.stocks.setting import loggerfactoryimport  Connectionpoolclass dboperator (object):         def __init__ ( Self):         self.logger = loggerfactory.getlogger (' Dboperator ')         self.conn = None                   def conndb (self):         self.conn=mysqldb.connect (host= "127.0.0.1", user= "root", passwd= "Root", db= "Pystock", port=3307,charset= "UTF8")           # Open          #self When you need to use a connection pool. Conn=connectionpool.pool.connection ()          return self.conn    def closedb (self):       &Nbsp; if (Self.conn != none):             self.conn.close ()         def execute (self, sql):         try:             if (Self.conn != none):                 cursor = self.conn.cursor ()              else:                 raise mysqldb.error (' no connection ')                          n  = cursor.execute (SQL)             return  n        except mysqldb.error,e:             self.logger.error ("mysql error %d: %s"  %  (e.args[0],  E.ARGS[1])      def findbysql (self, sql):         try:            if (Self.conn  != none):                 cursor = self.conn.cursor ()              else:                 Raise mysqldb.error (' no connection ')                          cursor.execute (SQL)        &nbSp;     rows = cursor.fetchall ()               return rows        except  MySQLdb.Error,e:             Self.logger.error ("mysql error %d: %s"  %  (E.args[0], e.args[1]))

(2) test code testmysql.py, do 50 concurrency, to get to the database record made a simple traversal.

Import threading  import time  import dboperatordef run ():      operator = dboperator ()     operator.conndb ()      starttime = time.time ()     sql =  "select * from  Stock_cash_tencent "    peeps = operator.findbysql (SQL)      For r in peeps: pass      operator.closedb ()      endtime = time.time ()     diff =   (endtime -  starttime) *1000    print diff    def test ():       for i in range (:       )    threading. Thread (Target = run). Start ()          time.sleep (1)     if __name__ ==  ' __main__ ':        test () 

2. Connection Pool Related code:

(1) connectionpool.py

from dbutils import pooleddbimport mysqldbimport stringmaxconn = 30              #最大连接数mincached  = 10             #最小空闲连接maxcached  = 20            #最大空闲连接maxshared  = 30            #最大共享连接connstring = "Root#root#127.0.0.1#3307#pystock#utf8"   #数据库地址dbtype  =  " MySQL "  def createconnectionpool (connstring, dbtype):    db_conn  = connstring.split ("#");     if dbtype== ' MySQL ':         try:            pool =  POOLEDDB.POOLEDDB (Mysqldb, user=db_conn[0],passwd=db_conn[1],host=db_conn[2],port=string.atoi (db_ CONN[3]), Db=db_conn[4],charseT=db_conn[5], mincached=mincached,maxcached=maxcached,maxshared=maxshared,maxconnections=maxconn)              return pool         except Exception, e:             raise exception, ' conn datasource excepts,%s!!! (%s). '% ( Db_conn[2],str (e))             return nonepool  = createconnectionpool (Connstring, dbtype)

3, Sqlobject related code

(1) connection.py

From sqlobject.mysql Import builderconn = Builder () (user= ' root ', password= ' root ', host= ' 127.0.0.1 ', db= ' py Stock ', port=3307, charset= ' UTF8 ')

(2) stockcashtencent.py corresponds to the table in the database, 50 concurrency and made a simple traversal. (In fact, sqlobject performance is quite high if you do not iterate and only do count () calculations. ）

Import sqlobjectimport timefrom connection import connimport threadingclass  stockcashtencent (Sqlobject. Sqlobject):    _connection = conn         code = sqlobject. Stringcol ()     name = sqlobject. Stringcol ()     date = sqlobject. Stringcol ()     main_in_cash = sqlobject. Floatcol ()        main_out_cash = sqlobject. Floatcol ()       main_net_cash = sqlobject. Floatcol ()     main_net_rate= sqlobject. Floatcol ()     private_in_cash= sqlobject. Floatcol ()     private_out_cash= sqlobject. Floatcol ()     private_net_cash= sqlobject. Floatcol ()     private_net_rate= sqlobject. Floatcol ()    &Nbsp;total_cash= sqlobject. Floatcol () def test ():     starttime = time.time ()      Query  = stockcashtencent.select (True)     for result in  Query: pass    endtime = time.time ()     diff =    (endtime - starttime) *1000    print diff         if __name__ ==  ' __main__ ':     for  I in range ():           threading. Thread (Target = test). Start ()             Time.sleep (1)

The test results are as follows:

MySQLdb average (ms)	99.63999271
Dbutil average (ms)	97.07998276
Sqlobject average (ms)	343.2999897

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/5B/59/wKioL1UHAnXQKbJEAAD8JsxLQw8592.jpg "title=" Performace. PNG "alt=" Wkiol1uhanxqkbjeaad8jsxlqw8592.jpg "/>

Conclusion: In fact, in terms of test data, the performance of MySQLdb single connection and dbutil connection pool is not very different (100 concurrency is similar), contrary to sqlobject, although it has the convenience of programming, but it brings a huge performance problem, Which module to use in practice depends on the discretion.

This article from the "Cup of tea, wind light Cloud Light" blog, reproduced please contact the author!

Python environment tests MySQLdb, Dbutil, sqlobject performance