Python connects to MySQL database using the Pymysql module

Python connect MySQL database pymysql module using Python3 to connect MySQL

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

Pip 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

= Pymysql.connect (host= "Your database Address", user= "user name", password= "password", database= "database name", charset==" " "" CREATE TABLE USER1 (id INT auto_increment PRIMARY KEY, name CHAR (TEN) NOT null unique,age TINYINT NOT NULL) Engine=innodb DE FAULT Charset=utf8;"" " # Execute SQL statement cursor.execute (SQL) # Close Cursor Object Cursor.close () # Close database connection conn.close ()

Returns the dictionary format data:

= Pymysql.connect (host= "Your database Address", user= "user name", password= "password", database= "database name", charset== conn.cursor (cursor=  = "" "CREATE TABLE USER1 (id INT auto_increment PRIMARY KEY, name CHAR (Ten) not NULL unique,age TINYINT not NUL L) Engine=innodb DEFAULT Charset=utf8;"" # Execute SQL statement cursor.execute (SQL) # Close Cursor Object Cursor.close () # Close database connection conn.close ()

Attention:

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

Add and delete to change the operation increased

= Pymysql.connect (host= "Your database Address", user= "user name", password= "password", database= "database name", charset=== "INSERT Into USER1 (name, age) VALUES (%s,%s);  = "Alex"=# Execute SQL statement cursor.execute (SQL, [username, age]) # COMMIT TRANSACTION Conn.commit () Cursor.close () Conn.close ()

Failed to insert data 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