Python--Connect to MySQL Database

Due to the incompatibility of the python2.x with the 3.x, it is not possible to connect the MySQL database using a module mysqldb similar to those commonly used in 2.x in Python3.

In python3.x, the program to connect to MySQL is: Oursql, Pymysql, Myconnpy and so on.

What I used to do is pymysql.

1, Pymysql Installation:

Generally recommended to install directly with the PIP3 Package Manager

PIP3 Install Pymysql

can also have other installation methods, Baidu.

2, Pymysql Reference:

As with other modules, after installing Pymysql, import pymysql into the desired py file and then use Pymysql for MySQL database connection operation.

Import Pymysql

3, Pymysql application:

1 #!/usr/bin/env python2 #-*-coding:utf-8-*-3 4 ImportPymysql5 6 #Connect to MySQL7conn =Pymysql.connect (8host='127.0.0.1',9port=3306,TenUser='Root', OnePasswd='Root', Adb='Pystudy', -charset='UTF8' - ) the  - #use the cursor method to create a cursor object cur -Cur =conn.cursor () -  + #executes the SQL statement using the Excute method and returns the number of rows affected - #recount = Cur.execute (' SELECT * from students ') +  A #executing multiple INSERT statements using the Executemany method atLi = [ -('Txowner','male', 23,13281818181,'CN'), -('xtsec','male', 25,132834321,'USA'), -('Tom','male', 28,1328153431,'JP'), -('Gouzi','female', 22,132345181,'CN'), - ] inReCount1 = Cur.executemany ('INSERT into students (name,sex,age,tel,nal) VALUES (%s,%s,%s,%s,%s)', Li) -  to #Delete Data + #recount = cur.execute (' Delete from students where age = ") -  the #Modifying Data * #recount = Cur.execute (' update students set sex =%s where name =%s ', (' female ', ' admin ')) $     #Note: The%s placeholder is used here, and no% is required to write the parameters laterPanax Notoginseng  - #thing rollback operation (a thing rollback operation is only valid until the transaction is committed and cannot be rolled back once the thing is committed) theConn.rollback ()#only useful when Engine=innodb, MyISAM does not support things, so it cannot be rolled back +  A #commit the current thing to the database execution the Conn.commit () +  - #using the Cursor object's Fetchall method to get all the data $ #data = Cur.fetchall () $ #data = Cur.fetchone () - #data = Cur.fetchmany (3) -  the #print (data) - Wuyi #Close Connection the cur.close () -Conn.close ()

4, Pymysql module Description:

# !/usr/bin/env python # -*-coding:utf-8-*- " " pymysql module function: Python connection mysql simple usage: pymysql. Connect () parameter description Host (str):      MySQL server address port (int):      MySQL server port number (default 3306 not writable) User (str):      username passwd (str)    : Password db (str):        database name CharSet (str):   connection encoding (default not writable) method supported by Connection object cursor ()        Use the connection to create and return a cursor commit ()        Commits the current transaction rollback ()      rolls back the current transaction (MySQL needs to determine if the storage engine is Innodb,myisam is not able to roll back before using the transaction) close ()         closes the method supported by the Cursor Object Execute (OP)     Perform a database Operation command Executemany ()   Execute multiple Database Operations Command Fetchone () get the      next row of the result set Fetchmany (size) Gets the next few rows of the result set (returned as tuples) Fetchall ()      gets all the rows in the result sets (returned as tuples) rowcount ()      returns the number of data bars or affects the number of rows close ()         closes the cursor object ' "'

5, things Roll back

1 #!/usr/bin/env python2 #-*-coding:utf-8-*-3 4 ImportPymysql5 6 #Connect to MySQL7conn =Pymysql.connect (8host='127.0.0.1',9port=3306,TenUser='Root', OnePasswd='Root', Adb='Pystudy', -charset='UTF8' - ) the  - #use the cursor method to create a cursor object cur -Cur =conn.cursor () -  + Try: -Li = [ +('Txowner','male', 23,13281818181,'CN'), A('xtsec','male', 25,132834321,'USA'), at('Tom','male', 28,1328153431,'JP'), -('Gouzi','female', 22,132345181,'CN'), -     ] -ReCount1 = Cur.executemany ('INSERT into students (name,sex,age,tel,nal) VALUES (%s,%s,%s,%s,%s)', Li) -     assert1==2 -  in exceptException as E: - Conn.rollback () to  + Else: - Conn.commit () the  * #Close Connection $ cur.close ()Panax NotoginsengConn.close ()

Python--Connect to MySQL Database