Python's Pymysql module

Pymysql is a library used in the python3.x version to connect to the MySQL server, and MySQLdb is used in Python2.

1. Basic syntax

 #   import pymysql module   Import   pymysql  #   connect database  conn = Pymysql.connect (host=" Your database Address ", user=" username ", password=" password ", database=" database name ", Charset= "UTF8")  #   cursor = Conn.cursor ()  #   Defines the SQL statement to execute  sql = #   Execute SQL statement  cursor.execute (SQL)  #   COMMIT transaction  conn.commit () 
  #查 
 Cursor.fetchone () 
 Cursor.fetchall () 
 Cursor.fetchmany () 

# close the Cursor object  #  Close Database connection conn.close ()

2. Increase

#Import Pymysql ModuleImportPymysql#Connect to Databaseconn = Pymysql.connect (host= "Your database Address", user= "user name", password= "password", database= "database name", charset="UTF8")#get a Cursor object that can execute SQL statementscursor =conn.cursor () SQL="INSERT into USER1 (name, age) VALUES (%s,%s);"username="Kebi" Age= 18Try:    #Execute SQL statements, and pass arguments to avoid SQL statement injectioncursor.execute (SQL, [username, age])#Commit a transactionConn.commit ()exceptException as E:#there is an exception, rolling back the transactionConn.rollback () cursor.close () Conn.close ( )

Gets the ID of the inserted data (used when associating the operation)

#Import Pymysql ModuleImportPymysql#Connect to Databaseconn = Pymysql.connect (host= "Your database Address", user= "user name", password= "password", database= "database name", charset="UTF8")#get a Cursor object that can execute SQL statementscursor =conn.cursor () SQL="INSERT into USER1 (name, age) VALUES (%s,%s);"username="Kebi" Age= 18Try:    #Execute SQL statementcursor.execute (SQL, [username, age])#Commit a transactionConn.commit ()#after you commit, get the ID of the data you just insertedlast_id =Cursor.lastrowidexceptException as E:#there is an exception, rolling back the transactionConn.rollback () cursor.close () Conn.close ( )

Batch Execution

#Import Pymysql ModuleImportPymysql#Connect to Databaseconn = Pymysql.connect (host= "Your database Address", user= "user name", password= "password", database= "database name", charset="UTF8")#get a Cursor object that can execute SQL statementscursor =conn.cursor () SQL="INSERT into USER1 (name, age) VALUES (%s,%s);"Data= [("Kebi", 18), ("Maoxian", 20), ("Xiaoniao"21st)]Try:    #batch execution of multiple insert SQL statementscursor.executemany (SQL, data)#Commit a transactionConn.commit ()exceptException as E:#there is an exception, rolling back the transactionConn.rollback () cursor.close () Conn.close ( )

3. By deleting

#Import Pymysql ModuleImportPymysql#Connect to Databaseconn = Pymysql.connect (host= "Your database Address", user= "user name", password= "password", database= "database name", charset="UTF8")#get a Cursor object that can execute SQL statementscursor =conn.cursor () SQL="DELETE from USER1 WHERE id=%s;"Try: Cursor.execute (SQL, [4])    #Commit a transactionConn.commit ()exceptException as E:#there is an exception, rolling back the transactionConn.rollback () cursor.close () Conn.close ( )

4. Change

#Import Pymysql ModuleImportPymysql#Connect to Databaseconn = Pymysql.connect (host= "Your database Address", user= "user name", password= "password", database= "database name", charset="UTF8")#get a Cursor object that can execute SQL statementscursor =conn.cursor ()#SQL statement to modify datasql ="UPDATE USER1 SET age=%s WHERE name=%s;"username="Kebi" Age= 80Try:    #Execute SQL statementcursor.execute (SQL, [age, username])#Commit a transactionConn.commit ()exceptException as E:#there is an exception, rolling back the transactionConn.rollback () cursor.close () Conn.close ( )

5. Check

#Import Pymysql ModuleImportPymysql#Connect to Databaseconn = Pymysql.connect (host= "Your database Address", user= "user name", password= "password", database= "database name", charset="UTF8")#get a Cursor object that can execute SQL statementscursor =conn.cursor ()#SQL statement for querying datasql ="SELECT id,name,age from USER1 WHERE id=1;"#Execute SQL statementcursor.execute (SQL)#get a single query dataRET =Cursor.fetchone ()#gets the specified number of dataRet2=cursor.fetctmany (3)#Get allret3=Cursor.fetchall () cursor.close () Conn.close ( )#Print the results of the queryPrint(ret)

6. Other

# cursor moves 1cursor.scroll (1, mode="absolute")by absolute position#  Cursor moves in relative position (current position) 1cursor.scroll (1, mode="relative")

Python's Pymysql module