1. What is? PyMySQL？

PyMySQL It's from Python Connect to MySQL Database server interface . It has achieved Python database API v2.0, And contains a pure Python Of MySQL Client library . PyMySQL The goal is to be MySQLdb substitute .
PyMySQL Reference documents ：http://pymysql.readthedocs.io/

2. How to install PyMySQL？

adopt pip Command to install -

3. The database connection is connected to MySQL Before the database , Please make sure the following points ：

• A database has been created ：test.
• Already in test Created a table in :employee.
• employee The table contains :fist_name,last_name,age,sex and income Field .
• MySQL user “root” And password “123456” You can visit ：test.
• Python modular PyMySQL Installed correctly on your computer .
• Have gone through MySQL Learn about MySQL Basic knowledge of .

Create table employee The sentence is ：

CREATE TABLE `employee` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `first_name` char(20) NOT NULL,
  `last_name` char(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `income` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

example
Here are Python adopt PyMySQL Module interface connection MySQL database “test” An example of -

Be careful ： stay Windows On the system ,import PyMySQL and import pymysql There's a difference .

#!/usr/bin/python3
#coding=utf-8

import pymysql

# Open database connection
db = pymysql.connect("localhost","root","123456","test" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")

# Fetch a single row using fetchone() method.
data = cursor.fetchone()

print ("Database version : %s " % data)

# disconnect from server
db.close()

When running this script , Will produce the following results -

Database version : 5.7.14-log

If you use a data source to establish a connection , The connection object will be returned and saved to db For further use , Otherwise it would be db Set to None. Next ,db Object is used to create a cursor object , Used to perform SQL Inquire about . Last , Before the results are printed , It ensures that the database connection is closed and releases resources .

4. Create database tables

After establishing the database connection , You can use the of the cursor you created execute Method to create a database table or record into a database table .
Example
Here's how to create a database ：test Create a database table in ：employee -

#!/usr/bin/python3
#coding=utf-8

import pymysql

# Open database connection
db = pymysql.connect("localhost","root","123456","test" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Drop table if it already exist using execute() method.
cursor.execute("DROP TABLE IF EXISTS employee")

# Create table as per requirement
sql = """CREATE TABLE `employee` ( `id` int(10) NOT NULL AUTO_INCREMENT, `first_name` char(20) NOT NULL, `last_name` char(20) DEFAULT NULL, `age` int(11) DEFAULT NULL, `sex` char(1) DEFAULT NULL, `income` float DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""

cursor.execute(sql)
print("Created table Successfull.")
# disconnect from server
db.close()

When running this script , Will produce the following results -

Created table Successfull.

5. The insert

When you want to create a record into a database table , You need to perform INSERT operation .
Example
The following example performs SQL Of INSERT Statement to EMPLOYEE Create a table ( multiple ) Record -

#!/usr/bin/python3
#coding=utf-8

import pymysql

# Open database connection
db = pymysql.connect("localhost","root","123456","test" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('Mac', 'Su', 20, 'M', 5000)"""
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

##  Insert a record again 
# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('Kobe', 'Bryant', 40, 'M', 8000)"""
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()
print (sql)
print('Yes, Insert Successfull.')
# disconnect from server
db.close()

When running this script , Will produce the following results -

Yes, Insert Successfull.

The above insertion example can be written as the following dynamic creation SQL Inquire about -

#!/usr/bin/python3
#coding=utf-8

import pymysql

# Open database connection
db = pymysql.connect("localhost","root","123456","test" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \ LAST_NAME, AGE, SEX, INCOME) \ VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
   ('Max', 'Su', 25, 'F', 2800)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

Example
The following code snippet is another way to execute , Parameters can be passed directly -

..................................
user_id = "test123"
password = "password"

con.execute('insert into Login values("%s", "%s")' % \
             (user_id, password))
..................................

6. Read operation

A read operation on any database means that you want to read some useful information from the database .
After establishing the database connection , You can query this database . have access to fetchone() Method to get a single record or fetchall() Method to get multiple values from the database table .

fetchone() - It gets the next row of the query result set . The result set is the object returned when the table is queried using a cursor object .
fetchall() - It gets all the rows in the result set . If you have extracted some rows from the result set , Then the remaining rows are retrieved from the result set .
rowcount - This is a read-only property , And return to the affected execute() Number of rows affected by method .

Example
The following procedure queries EMPLOYEE All the wages recorded in the table exceed 1000 Employee record information -

#!/usr/bin/python3
#coding=utf-8

import pymysql

# Open database connection
db = pymysql.connect("localhost","root","123456","test" )

# prepare a cursor object using cursor() method
cursor = db.cursor()
#  Press dictionary to return to  
# cursor = db.cursor(pymysql.cursors.DictCursor)

# Prepare SQL query to select a record from the table.
sql = "SELECT * FROM EMPLOYEE \ WHERE INCOME > %d" % (1000)
#print (sql)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Fetch all the rows in a list of lists.
   results = cursor.fetchall()
   for row in results:
      #print (row)
      fname = row[1]
      lname = row[2]
      age = row[3]
      sex = row[4]
      income = row[5]
      # Now print fetched result
      print ("name = %s %s,age = %s,sex = %s,income = %s" % \
             (fname, lname, age, sex, income ))
except:
   import traceback
   traceback.print_exc()

   print ("Error: unable to fetch data")

# disconnect from server
db.close()

name = Mac Su,age = 20,sex = M,income = 5000.0
name = Kobe Bryant,age = 40,sex = M,income = 8000.0

7. update operation

UPDATE Statement can update the data in any database , It can be used to update one or more existing records in the database .
The following procedure will all SEX The value of the field is “M” The age of the record (age Field ) Updated to add one year .

#!/usr/bin/python3
#coding=utf-8

import pymysql

# Open database connection
db = pymysql.connect("localhost","root","123456","test" )

# prepare a cursor object using cursor() method
#cursor = db.cursor()
cursor = db.cursor(pymysql.cursors.DictCursor)
# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to UPDATE required records
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 \ WHERE SEX = '%c'" % ('M')
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

8. Delete operation

When you want to delete some records from the database , Then you can execute DELETE operation . Here is the deletion EMPLOYEE in AGE exceed 40 All recorded procedures -

#!/usr/bin/python3
#coding=utf-8

import pymysql

# Open database connection
db = pymysql.connect("localhost","root","123456","test" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (40)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()