Python connects to MySQL database using the Pymysql module

This article describes the basic use of the third-party library--pymysql that Python3 connects to MySQL.

Pymysql Introduction

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

You can also use Pymysql to connect to the MySQL database in Django.

Pymysql Installation

Install Pymysql

Connection Database Considerations

Be aware of the following before you proceed with this article:

• You have a MySQL database and have started.
• You have a user name and password that you can connect to the database
• You have a database that has permission to manipulate

Basic use

#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 ()#define the SQL statement to executesql ="""CREATE TABLE USER1 (id INT auto_increment PRIMARY KEY, name CHAR (TEN) NOT null unique,age TINYINT NOT NULL) Engine=innod b DEFAULT Charset=utf8;"""#Execute SQL statementcursor.execute (SQL)#close the Cursor objectcursor.close ()#To close a database connectionConn.close ()

Returns the dictionary format data:

#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 that can execute an SQL statement and return the result as a dictionarycursor = Conn.cursor (cursor=pymysql.cursors.DictCursor)#define the SQL statement to executesql ="""CREATE TABLE USER1 (id INT auto_increment PRIMARY KEY, name CHAR (TEN) NOT null unique,age TINYINT NOT NULL) Engine=innod b DEFAULT Charset=utf8;"""#Execute SQL statementcursor.execute (SQL)#close the Cursor objectcursor.close ()#To close a database connectionConn.close ()

Attention:

charset= "UTF8", encoding not written "Utf-8"

Add and delete to change the operation increased

#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="Alex" Age= 18#Execute SQL statementcursor.execute (SQL, [username, age])#Commit a transactionConn.commit () cursor.close () Conn.close ( )

Insert Data Failure rollback

#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="Alex" Age= 18Try:    #Execute SQL statementcursor.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="Alex" 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= [("Alex", 18), ("Egon", 20), ("Yuan"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 ( )

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 ( )

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="Alex" 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 ( )

Check

Querying single data

#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 () cursor.close () Conn.close ( )#Print the results of the queryPrint(ret)

Querying more than one piece of data

#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;"#Execute SQL statementcursor.execute (SQL)#get more than one query dataRET =Cursor.fetchall () cursor.close () Conn.close ( )#Print the results of the queryPrint(ret)

Advanced usage

# a specified number of data can be obtained Cursor.fetchmany (3)#  cursor is moved by absolute position 1cursor.scroll (1, mode=" Absolute " #  cursor moves in relative position (current position) 1cursor.scroll (1, mode="relative") )

Python connects to MySQL database using the Pymysql module