Using PyMySQL to Connect, Create, Insert, and Query a MySQL Database with Python

1. Install PyMySQL

pip install PyMySQL

2. Database Connection

Before connecting, make sure the following are ready:

PyMySQL module is installed.

Username and password for the MySQL server.

Name of the target database.

Name of the table to operate on.

Names of the fields/columns.

3. Code Example – Create Table

#encoding:utf8
import pymysql

# Open database connection
db = pymysql.connect(
    "localhost",
    "root",
    "XXXXXX",
    "world",
    charset='utf8'
)

# Create a cursor object
cursor = db.cursor()

# Drop the table if it already exists
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# SQL statement to create a new table
sql = """CREATE TABLE EMPLOYEE (
    FIRST_NAME CHAR(20) NOT NULL,
    LAST_NAME  CHAR(20),
    AGE INT,
    SEX CHAR(1),
    INCOME FLOAT
)"""

cursor.execute(sql)

# Close the connection
db.close()

4. Code Example – Insert Data

#encoding:utf8
import pymysql

# Open database connection
db = pymysql.connect(
    "localhost",
    "root",
    "XXXXXX",
    "world",
    charset='utf8'
)

cursor = db.cursor()

# Insert statement
sql = """INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""

try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()

# Close the connection
db.close()

5. Code Example – Query Data

#encoding:utf8
import pymysql

# Open database connection
db = pymysql.connect(
    "localhost",
    "root",
    "XXXXXX",
    "world",
    charset='utf8'
)

cursor = db.cursor()

# Query statement
sql = "SELECT * FROM EMPLOYEE"

try:
    cursor.execute(sql)
    results = cursor.fetchall()
    for row in results:
        fname = row[0]
        lname = row[1]
        age   = row[2]
        sex   = row[3]
        income= row[4]
        print("fname=%s, lname=%s, age=%d, sex=%s, income=%d" % (fname, lname, age, sex, income))
except:
    print("Error: unable to fetch data")

# Close the connection
db.close()

Reference URLs:

http://www.runoob.com/python3/python3-mysql.html

https://www.jianshu.com/p/0cb8de3c130a