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 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.

Pip Install Pymysql

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

= 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 Tinyin T not NULL) ENGINE=innodb DEFAULT charset=UTF8; """ # Execute SQL statement cursor.execute (SQL) # Close Cursor Object Cursor.close () # Close database connection conn.close ()

Returns the dictionary format data:

# Importing Pymysql module import pymysql# connection Databaseconn= Pymysql.connect (host= "Your database address", user="User name", password="Password", database="Database name", charset="UTF8") # Gets a cursor that can execute an SQL statement and return the result as a dictionary = Conn.cursor (cursor= pymysql.cursors.DictCursor) # define SQL statement to execute SQL="""CREATE TABLE USER1 (id INT auto_increment PRIMARY KEY, name CHAR (Ten) NOT null unique,age TINYINT NOT NULL) 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 a piece of data

= Pymysql.connect (host= "Your database Address", user= "username", 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 ()

Add more than one piece of data

# Establish Connection conn=Pymysql.connect (Host="127.0.0.1", Port=3306, # is not a string type (do not quote) user="Root", Password="123456", Database="day59", CharSet="UTF8"# no-!!!!) # gets one cursor=conn.cursor () # defines SQL statement to be executed="INSERT into UserInfo (name,"Data= [    (" the Elite","Woyouqian"),    ("Changjiang","Huanghe"),    ("Zhanshen","Jishuchapiqida"]# Stitching and executing SQL statement cursor.executemany (SQL, data) # batch creation, commit once # involved write action note to commit conn.commit () # Close Connection cursor.close () Conn.close () 

Insert Data Failure rollback

# Importing Pymysql module import pymysql# connection Databaseconn= Pymysql.connect (host= "Your database Address", user= "user name", password= "password", database= "database name", charset="UTF8") # Gets a cursor object that can execute the SQL statement=conn.cursor () SQL="INSERT into USER1 (name, age) VALUES (%s,%s);"username="Alex" Age= -Try: # Execute SQL statement cursor.execute (SQL, [username, age]) # COMMIT TRANSACTION Conn.commit () except Exception ase: # There is an exception, ROLLBACK TRANSACTION conn.rollback () Cursor.close () Conn.close ( )

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

# Importing Pymysql module import pymysql# connection Databaseconn= Pymysql.connect (host= "Your database Address", user= "user name", password= "password", database= "database name", charset="UTF8") # Gets a cursor object that can execute the SQL statement=conn.cursor () SQL="INSERT into USER1 (name, age) VALUES (%s,%s);"username="Alex" Age= -Try: # Execute SQL statement cursor.execute (SQL, [username, age]) # COMMIT TRANSACTION Conn. Commit () # After committing, get the ID of the data just inserted last_id=cursor. lastrowidexcept Exception ase: # There is an exception, ROLLBACK TRANSACTION conn. rollback () cursor.close () Conn.close ( )

Batch Execution

# Importing Pymysql module import pymysql# connection Databaseconn= Pymysql.connect (host= "Your database Address", user= "user name", password= "password", database= "database name", charset="UTF8") # Gets a cursor object that can execute the SQL statement=conn.cursor () SQL="INSERT into USER1 (name, age) VALUES (%s,%s);"Data= [("Alex", -), ("Egon", -), ("Yuan", +)]Try: # Bulk EXECUTE multiple INSERT SQL statements Cursor.executemany (SQL, data) # COMMIT TRANSACTION Conn.commit () except Exception ase: # There is an exception, ROLLBACK TRANSACTION conn.rollback () Cursor.close () Conn.close ( )

= Pymysql.connect (host= "Your database Address", user= "username", password= "password", database= "database name", charset==" DELETE from USER1 WHERE id=%s; " Try :    cursor.execute (SQL, [4])    # COMMIT TRANSACTION       as E:    # with exception, ROLLBACK TRANSACTION    Conn.rollback () cursor.close () Conn.close ( )

# Importing Pymysql module import pymysql# connection Databaseconn= Pymysql.connect (host= "Your database Address", user= "user name", password= "password", database= "database name", charset="UTF8") # Gets a cursor object that can execute the SQL statement=conn.cursor () # SQL statement SQL to modify data="UPDATE USER1 SET age=%s WHERE name=%s;"username="Alex" Age= theTry: # Execute SQL statement cursor.execute (SQL, [age, username]) # COMMIT TRANSACTION Conn.commit () except Exception ase: # There is an exception, ROLLBACK TRANSACTION conn.rollback () Cursor.close () Conn.close ( )

Querying single data

= Pymysql.connect (host= "Your database Address", user= "username", password= "password", database= "database name", charset==" SELECT id,name,age from USER1 WHERE id=1; "  = cursor.fetchone () cursor.close () conn.close () # Print under Query result print (ret)

Querying more than one piece of data

= Pymysql.connect (host= "Your database Address", user= "username", password= "password", database= "database name", charset==" SELECT id,name,age from USER1; "  = cursor.fetchall () cursor.close () conn.close () # Print under Query result print (ret)

# can get a specified number of data Cursor.fetchmany (3) # cursor moves in 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