Python Connection MySQL

This article goes to Yuzi blog, the article Python connection MySQL, Welcome to visit yuchaoshui.com for more information!

One, common MySQL connection method

?? Use the module MySQLdb Normal mode connection.

#!/usr/bin/env python# _*_ coding:utf-8 _*_import MySQLdbconn = MySQLdb.connect(host=‘127.0.0.1‘, port=3306, user=‘root‘, passwd=‘123‘, db=‘test‘)cursor = conn.cursor()sql_1 = "select * from user where id = %s;" % (5,)sql_2 = "select * from user          where id = %s;" % (5,)sql_3 = """           insert into user(username, password)           values("yuchaoshui", "123");        """try:    print cursor.execute(sql_1)    print cursor.fetchall()    print cursor.execute(sql_2)    print cursor.fetchall()    print cursor.execute(sql_3)    conn.commit()except Exception as e:    print(e)    conn.rollback()cursor.close()conn.close()

?? execute()returns the number of rows affected by the result representation. cursor.fetchone()Retrieve a result. Sql_1 Straight line finish, sql_2 write, sql_3 more lines write. No action is required at commit() the time of the query, and must be submitted when inserting, updating, deleting commit() .

Second, connect to MySQL using connection pool

#!/usr/bin/env python# _*_ coding:utf-8 _*_import MySQLdbfrom DBUtils.PooledDB import PooledDBpool = PooledDB(MySQLdb, 5, host=‘127.0.0.1‘, port=3306, user=‘root‘, passwd=‘123‘, db=‘test‘)conn = pool.connection()cursor = conn.cursor()sql_1 = "select * from user where id = %s;" % (5,)sql_2 = "select * from user          where id = %s;" % (5,)sql_3 = """           insert into user(username, password)           values("yuchaoshui", "123");        """try:    print cursor.execute(sql_1)    print cursor.fetchall()    print cursor.execute(sql_2)    print cursor.fetchall()    print cursor.execute(sql_3)    conn.commit()except Exception as e:    print(e)    conn.rollback()cursor.close()conn.close()

?? 5 is the minimum number of connections in the connection pool, and each time a database connection is required to connection() get a connection using a function.

• Default value for POOLEDDB

  PooledDB(self, creator, mincached=0, maxcached=0, maxshared=0, maxconnections=0, blocking=False, maxusage=None, setsession=None, reset=True, failures=None, ping=1, *args, **kwargs)

• Parameters of the POOLEDDB:

1. mincached, minimum number of idle connections, if the number of idle connections is less than this, pool creates a new connection
2. Maxcached, the maximum number of idle connections, if the number of idle connections is greater than this, pool closes the idle connection
3. MaxConnections, maximum number of connections,
4. Blocking, when the number of connections reaches the maximum number of connections, when the connection is requested, if the value is true, the program that requests the connection waits until the current number of connections is less than the maximum number of connections, and if this value is False, the error is
5. Maxshared, when the number of connections reaches this number, the newly requested connection will share the connection that has been allocated
   
   

Third, module import connection MySQL

?? The module mysqlhelper.py is written in connection pooling and can be used by importing MySQL connection instances elsewhere in the project. Module point this download mysqlhelper.py

#!/usr/bin/env python# _*_ coding:utf-8 _*_from __future__ import print_functionfrom dbutils.pooleddb Import Pooleddbimport mysqldbimport sys__all__ = [' m '] + ["M" +str (i) for I in range (2, one)]class MH (object): Def __init__ (self ): Try:print ("Connecting MySQL Server {0}@{1}:{2} ...".            Format (self.__class__.db, Self.__class__.host, Self.__class__.port), end= '. ')        Self.conn = Self.__class__.pool.connection () Self.cursor = Self.conn.cursor () print (' ok! ')            Except Exception, E:print ("Pool.connection error: {0}". Format (e)) def select (Self, query= "): Try:            Self.effect = Self.cursor.execute (query) return self.cursor except Exception as E: Print ("Select error: {0}". Format (e)) def update (self, query= "): Try:self.effect = Self.cursor.execu Te (query) self.conn.commit () except Exception as E:print ("Update error: {0} ". Format (e)) self.conn.rollback () Self.effect = 0# M2 class inherits from M1, which represents a new MySQL connection pool. # If you need a new connection pool, you can add it in the following format.             Class MH2 (MH): Passdef init_pool (M, host= ' 127.0.0.1 ', port=3306, user= ' root ',  Password= ", database= ' test ', pool_size=5): M.host = host M.port = Int (port) m.user             = User M.password = password m.db = Database M.pool_size = pool_size Try:m.pool = POOLEDDB (MySQLdb, M.pool_size, Host=m.host, Port=m.port, User=m.user, Passwd=m.passwo Rd, Db=m.db) except Exception, E:print ("POOLEDDB init error: {0}". Format (e)) exit (1) # Initialize the connection pool, Can have more than one. The first parameter is a connection pool class that was manually defined earlier. Init_pool (MH, ' 127.0.0.1 ', 3306, ' root ', ' 123 ', ' Test ') init_pool (MH2, ' 12.55.5.61 ', 3306, ' root ', ' 123 ', ' test ') # definition will be exported instance of MySQL. A connection pool can provide multiple instance objects at the same time. m = MH () m2 = MH2 () if __name__ = = "__main__": Pass #print "\nm info:" #print M.selecT ("select * from User;"). Fetchone () #print m.effect #print m.select ("select * from User;").    Fetchall () #print m.effect #m. Update ("INSERT into User (Username,password) VALUES (' haha ', ' heihei ');")  #print m.effect ################################################## #print "\nm2 info:" #print m2.select ("SELECT * from user; "). Fetchone () #print m2.effect #print m2.select ("select * from User;").    Fetchall () #print m2.effect #m2. Update ("INSERT into User (Username,password) VALUES (' haha ', ' heihei ');") #print M2.effect

• How to use

#!/usr/bin/env python# _*_ coding:utf-8 _*_from mysqlhelper import m, m2import timedef test():    print "\nm info:"    print m.select("select * from user;").fetchone()    print m.effect    print m.select("select * from user;").fetchall()    print m.effect    m.update("insert into user(username,password) values(‘haha‘, ‘heihei‘);")    print m.effect        #################################################        print "\nm2 info:"    print m2.select("select * from user;").fetchone()    print m2.effect    print m2.select("select * from user;").fetchall()    print m2.effect    m2.update("insert into user(username,password) values(‘haha‘, ‘heihei‘);")    print m2.effectif __name__ == ‘__main__‘:    test()

This article goes to Yuzi blog, the article Python connection MySQL, Welcome to visit yuchaoshui.com for more information!

Python Connection MySQL