Use of the Pymysql module

Pymysql Introduction

Pymysql is a library used in the python3.x version to connect to the MySQL server, and MySQLdb is used in the Python2 series. You can also use Pymysql to connect to the MySQL database in Django.

Pymysql Installation

Install in CMD terminal:

Pip Install Pymysql

can also be installed in the pycharm.

Connection Database considerations:

• There is a MySQL database, and it has been started;
• Have a user name and password that can connect to the database;
• There is a database that has permission to operate

Basic use:

#Import Pymysql ModuleImportPymysql#Connect to Databaseconn =Pymysql.connect (Host="Your database Address",
port= Port number 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",
port= Port number 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 ()

Note: Be sure to pay attention to the connection database when the writing format, such as the port number is a numeric type, the password is a string type, charset= "UTF8" is not "utf-8".

Adding and removing changes and checking operation

Increase

#Import Pymysql ModuleImportPymysql#Connect to Databaseconn =Pymysql.connect (Host="Your database Address", Port=Port number 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", Port=Port number 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", Port=Port number 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", Port=Port number 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", Port=Port number 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", Port=Port number 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", Port=Port number 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", Port=Port number 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") )

Use of the Pymysql module