Seventh article: Python3 connection MySQL

Seventh article: Python3 connection MySQL 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 ()

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])  //If there is a mistake, the missing thing will be an error, We're going to go. The following rollback    # 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 EXECUTE multiple INSERT SQL statements    cursor.executemany (sql, data     # COMMIT TRANSACTION    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 () /c5>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 ") # Get a Cursor object that can execute SQL statements cursor = 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 of Exception problems

# can get a specified number of data Cursor.fetchmany (3)                 //  get 3 # cursor to move 1cursor.scroll (1, mode= "absolute") in absolute position   //positioning at 1 starting from 2 # Cursor moves in relative position (current position) 1cursor.scroll (1, mode= "relative")   //positioned at 1 starting from 5

Seventh article: Python3 connection MySQL