Python3 connecting MySQL

Python3 connecting MySQL

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

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

# Importing Pymysql module import pymysql# Connection databaseconn = Pymysql.connect (host= "Your database Address", user= "username", password= "password", database= " Database name ", charset=" UTF8 ") # Gets a cursor object that can execute SQL statements cursors = conn.cursor () # defines the SQL statement to execute sql =" "" CREATE TABLE USER1 (id INT auto_incr Ement 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 ()

Returns the dictionary format data:

# Importing Pymysql module import pymysql# Connection databaseconn = Pymysql.connect (host= "Your database Address", user= "username", password= "password", database= " Database name ", charset=" UTF8 ") # gets a cursor that can execute the SQL statement and return the result as a dictionary cursor = 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 N ULL) 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

# Importing Pymysql module import pymysql# Connection databaseconn = Pymysql.connect (host= "Your database Address", user= "username", password= "password", database= " Database name ", charset=" UTF8 ") # Gets a cursor object that can execute SQL statements cursor = conn.cursor () sql =" INSERT into USER1 (name, age) VALUES (%s,%s); " Username = "Alex" age = 18# Execute SQL statement cursor.execute (SQL, [username, age]) # COMMIT TRANSACTION Conn.commit () Cursor.close () Conn.close ()

Insert Data Failure rollback

# Importing Pymysql module import pymysql# Connection databaseconn = Pymysql.connect (host= "Your database Address", user= "username", password= "password", database= " Database name ", charset=" UTF8 ") # Gets a cursor object that can execute SQL statements cursor = conn.cursor () sql =" INSERT into USER1 (name, age) VALUES (%s,%s); " Username = "Alex" age = 18try:    # Execute SQL statement    cursor.execute (SQL, [username, age])    # COMMIT TRANSACTION    Conn.commit () Except Exception as E:    # with 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= "username", password= "password", database= " Database name ", charset=" UTF8 ") # Gets a cursor object that can execute SQL statements cursor = conn.cursor () sql =" INSERT into USER1 (name, age) VALUES (%s,%s); " Username = "Alex" age = 18try:    # Execute SQL statement    cursor.execute (SQL, [username, age])    # COMMIT TRANSACTION    Conn.commit ()    # After submission, get the ID of the data just inserted    last_id = cursor.lastrowidexcept Exception as e:    # 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= "username", password= "password", database= " Database name ", charset=" UTF8 ") # Gets a cursor object that can execute SQL statements cursor = conn.cursor () sql =" INSERT into USER1 (name, age) VALUES (%s,%s); " data = [("Alex"), ("Egon", +), ("Yuan", +)]try:    # Bulk execution of multiple insert SQL statements    cursor.executemany (SQL, data)    # Commit a transaction C3/>conn.commit () except Exception as E:    # There is an exception, ROLLBACK TRANSACTION    conn.rollback () cursor.close () Conn.close ()

By deleting

# Importing Pymysql module import pymysql# Connection databaseconn = Pymysql.connect (host= "Your database Address", user= "username", password= "password", database= " Database name ", charset=" UTF8 ") # Gets a cursor object that can execute SQL statements cursor = conn.cursor () sql =" DELETE from USER1 WHERE id=%s; " Try:    cursor.execute (SQL, [4])    # COMMIT TRANSACTION    conn.commit () except Exception as E:    # There is an exception, ROLLBACK TRANSACTION    Conn.rollback () Cursor.close () Conn.close ()

Change

# Importing Pymysql module import pymysql# Connection databaseconn = Pymysql.connect (host= "Your database Address", user= "username", password= "password", database= " Database name ", charset=" UTF8 ") # Gets a cursor object that can execute SQL statements cursors = conn.cursor () # SQL statement to modify data sql =" UPDATE USER1 SET age=%s WHERE name=%s; " Username = "Alex" age = 80try:    # Execute SQL statement    cursor.execute (SQL, [age, Username])    # COMMIT TRANSACTION    Conn.commit () Except Exception as E:    # with exception, ROLLBACK TRANSACTION    conn.rollback () cursor.close () Conn.close ()

Check

Querying single data

# Importing Pymysql module import pymysql# Connection databaseconn = Pymysql.connect (host= "Your database Address", user= "username", password= "password", database= " Database name ", charset=" UTF8 ") # Gets a cursor object that can execute SQL statements cursors = conn.cursor () # Query data SQL statement sql =" Select Id,name,age from USER1 WHERE id= 1; " # Execute SQL statement cursor.execute (SQL) # get a single query data ret = Cursor.fetchone () cursor.close () conn.close () # Print out the query results printed (ret)

Querying more than one piece of data

# Importing Pymysql module import pymysql# Connection databaseconn = Pymysql.connect (host= "Your database Address", user= "username", password= "password", database= " Database name ", charset=" UTF8 ") # Gets a cursor object that can execute SQL statements cursors = conn.cursor () # Query data SQL statement sql =" Select Id,name,age from USER1; " # Execute SQL statement cursor.execute (SQL) # get multiple query data ret = Cursor.fetchall () cursor.close () conn.close () # Print out the query results printed (ret)

Advanced usage

# 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")

Python3 connecting MySQL