Operating MySQL database in Python 3

To use mysql database in Python 3, you need to install the mysql Library

pip install pymysql

Operating MySQL

Guide bag

import pymysql

Step 1: open the database connection

db = pymysql.connect(host="Database address", 
                     user="user name", 
                     password="Password", 
                     port="port", 
                     database="Database name", 
                     charset='utf8')

Step 2: create a cursor

cursor = db.cursor()

Step 3: operate the database

1. Create table

# If the data table already exists, use the execute() method to delete the table.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

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

cursor.execute(sql)

2. Query data

1. Python queries Mysql uses the fetchone() method to get a single piece of data, and fetchall() method to get multiple pieces of data.
2. fetchone(): all the returned result lines should be collected
3. rowcount: This is the method to get the next query result set. Result set is an object
4. fetchall(): next to a read-only property and returns the number of rows affected after executing the execute() method.

# SQL query statement
sql = "SELECT * FROM EMPLOYEE WHERE INCOME > {}".format(1000)
try:
    # Execute SQL statement
    cursor.execute(sql)
    # Get all records list
    results = cursor.fetchall()
    for row in results:
        fname = row[0]
        lname = row[1]
        age = row[2]
        sex = row[3]
        income = row[4]
        # Print results
        print("fname={},lname={},age={},sex={},income={}".format(fname, lname, age, sex, income))

except:
    print("Error: unable to fecth data")

3. Add data

# SQL insert statement
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
    cursor.execute(sql)
    # Commit to database execution
    db.commit()
except:
    # Rollback on error
    db.rollback()

4. Modify data

# SQL UPDATE statement
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '{}'".format('M')
try:
    cursor.execute(sql)
    # Commit to database execution
    db.commit()
except:
    # Rollback on error
    db.rollback()

5. Delete data

# SQL delete statement
sql = "DELETE FROM EMPLOYEE WHERE AGE > {}".format(20)
try:
    cursor.execute(sql)
    # Commit to database execution
    db.commit()
except:
    # Rollback on error
    db.rollback()

Step 4: close the cursor and connect the database

cursor.close()
db.close()