Python series articles - detailed explanation of Python operation MySQL

• create_table

import pymysql
# Open database connection
conn = pymysql.connect(
host="127.0.0.1",
user="root",
password="123456",
database="test_db",
charset="utf8")
# Get cursor under connection
cursor_test = conn.cursor()
# Use execute() Method execution SQL, Delete... If the table exists
cursor_test.execute("DROP TABLE IF EXISTS EMPLOYEE")
# Use preprocessing statements to create tables
sql = """CREATE TABLE user1 (
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT )"""
cursor_test.execute(sql)
# Close database connection
conn.close()

• delete_data

# The import module
import pymysql
# Open database connection
conn = pymysql.connect(
host="127.0.0.1",
user="root",
password="123456",
database="test_db",
charset="utf8")
# print(conn)
# print(type(conn))
# Get cursor under connection
cursor_test = conn.cursor()
sql = "DELETE * FROM user1"
try:
# perform SQL sentence
cursor_test.execute(sql)
# Commit to database execution
conn.commit()
except:
# Rollback on error
conn.rollback()
# Close database connection
conn.close()

• insert_data

import pymysql
# Open database connection
conn = pymysql.connect(
host="127.0.0.1",
user="root",
password="123456",
database="test_db",
charset="utf8")
# Get cursor under connection
cursor_test = conn.cursor()
# Use preprocessing statements to create tables
sql = """INSERT INTO user1(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME)
VALUES ('Fei', 'Fei', 20, 'M', 1000)"""
try:
# perform sql sentence
cursor_test.execute(sql)
# Commit to database execution
conn.commit()
except:
# Roll back if an error occurs
conn.rollback()
# Close database connection
conn.close()

• mysql_test

# The import module
import pymysql
# Open database connection
conn = pymysql.connect(
host="127.0.0.1",
user="root",
password="123456",
database="test_db",
charset="utf8")
# print(conn)
# print(type(conn))
# Get cursor under connection
cursor_test = conn.cursor()
# print(cursor_test)
# Use execute() Method execution SQL Inquire about , Query database version
cursor_test.execute("SELECT VERSION()")
# Use fetchone() Method returns a piece of data .
data = cursor_test.fetchone()
print("Database version : %s " % data)
# Close database connection
conn.close()

• query_data

import pymysql
# Open database connection
conn = pymysql.connect(
host="127.0.0.1",
user="root",
password="123456",
database="test_db",
charset="utf8")
# Get cursor under connection
cursor_test = conn.cursor()
sql = """
select * from user1"""
try:
# perform sql sentence
cursor_test.execute(sql)
# Show all data
data_result = cursor_test.fetchall()
for row in data_result:
fname = row[0]
lname = row[1]
age = row[2]
sex = row[3]
income = row[4]
# Print the results
print("fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \
(fname, lname, age, sex, income))
except:
print("Error: unable to fetch data")
# Close database connection
conn.close()

• update_test

# The import module
import pymysql
# Open database connection
conn = pymysql.connect(
host="127.0.0.1",
user="root",
password="123456",
database="test_db",
charset="utf8")
# print(conn)
# print(type(conn))
# Get cursor under connection
cursor_test = conn.cursor()
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
try:
# perform SQL sentence
cursor_test.execute(sql)
# Commit to database execution
conn.commit()
except:
# Rollback on error
conn.rollback()
# Close database connection
conn.close()