This paper introduces how Python 3 uses PyMySQL to connect database, and implements simple addition, deletion and modification.

What is PyMySQL?

PyMySQL is a library for connecting MySQL servers in Python 3.x, and mysqldb is used in Python 2.x.

PyMySQL installation

Before using PyMySQL, we need to make sure that PyMySQL is installed.

PyMySQL Download Address: https://github.com/PyMySQL/PyMySQL.

If not, we can install the latest version of PyMySQL using the following commands:

$ pip install PyMySQL

If your system does not support the pip command, you can install it in the following way:

1. Use the git command to download the installation package (you can also download it manually):

$ git clone https://github.com/PyMySQL/PyMySQL
$ cd PyMySQL
$ python3 setup.py install

2. If you need to make version number, you can use curl command to install:

$ # X.X by PyMySQL Version number of
$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
$ cd PyMySQL*
$ python3 setup.py install

Note: Make sure you have root privileges to install the above modules.

Database Connection

Before connecting to the database, please confirm the following items:

• You have created the database TESTDB
• You have created the table EMPLOYEE in the TESTDB database
• EMPLOYEE table fields are FIRST_NAME,LAST_NAME,AGE,SEX and INCOME
• The user name and password used to connect to the database TESTDB are "testuser" and "test123". You can set or use the root user name and password directly. The Mysql database user is authorized to use the Grant command.
• PyMySQL module has been installed on your computer

Example:

The following example links Mysql's TESTDB database:

#!/usr/bin/python3
__author__ = 'mayi'

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# Use cursor() Method to create a cursor object cursor
cursor = db.cursor()

# Use execute()  Method execution SQL query 
cursor.execute("SELECT VERSION()")

# Use fetchone() Method to obtain single data.
data = cursor.fetchone()

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

# Close database connection
db.close()

Create database tables

If a database connection exists, we can use the execute() method to create tables for the database. The table EMPLOYEE is created as follows:

#!/usr/bin/python3
__author__ = 'mayi'

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# Use cursor() Method to create a cursor object cursor
cursor = db.cursor()

# Use execute() Method execution SQL，Delete tables if they exist
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# Create tables with preprocessing statements
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 database connection
db.close()

Database insertion operation

The following example inserts records into table EMPLOYEE using an execution SQL Insert statement:

#!/usr/bin/python3
__author__ = 'mayi'

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# Use cursor()Method to get operation cursor 
cursor = db.cursor()

# SQL Insert statement
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
   # implement sql Sentence
   cursor.execute(sql)
   # Submit to database for execution
   db.commit()
except:
   # Roll back if an error occurs
   db.rollback()

# Close database connection
db.close()

The above examples can also be written in the following form:

#!/usr/bin/python3
__author__ = 'mayi'

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# Use cursor()Method to get operation cursor 
cursor = db.cursor()

# SQL Insert statement
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
       ('Mac', 'Mohan', 20, 'M', 2000)
try:
   # implement sql Sentence
   cursor.execute(sql)
   # implement sql Sentence
   db.commit()
except:
   # Roll back when an error occurs
   db.rollback()

# Close database connection
db.close()

Database query operation

Python queries Mysql to get single data using fetchone() method, and fetchall() method to get multiple data.

• fetchone(): This method obtains the next query result set. The result set is an object
• fetchall(): Receives all returned result rows
• rowcount: This is a read-only property and returns the number of rows affected by executing the execute() method

Example:

Query all data in EMPLOYEE table with salary field greater than 1000:

#!/usr/bin/python3
__author__ = 'mayi'

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# Use cursor()Method to get operation cursor 
cursor = db.cursor()

# SQL Query statement
sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > '%d'" % (1000)
try:
   # implement SQL Sentence
   cursor.execute(sql)
   # Get a list of all records
   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=%s,lname=%s,age=%d,sex=%s,income=%d" % \
             (fname, lname, age, sex, income ))
except:
   print ("Error: unable to fecth data")

# Close database connection
db.close()

Database update operation

The update operation is used to update the data of the data table. The following example modifies the SEX fields in the TESTDB table to'M', and increases the AGE fields by 1:

#!/usr/bin/python3
__author__ = 'mayi'

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# Use cursor()Method to get operation cursor 
cursor = db.cursor()

# SQL Update statement
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1
                          WHERE SEX = '%c'" % ('M')
try:
   # implement SQL Sentence
   cursor.execute(sql)
   # Submit to database for execution
   db.commit()
except:
   # Roll back when an error occurs
   db.rollback()

# Close database connection
db.close()

Delete operation

The deletion operation is used to delete data in the data table. The following example demonstrates deleting all data whose AGE is greater than 20 in the data table EMPLOYEE:

#!/usr/bin/python3
__author__ = 'mayi'

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# Use cursor()Method to get operation cursor 
cursor = db.cursor()

# SQL Delete statement
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
   # implement SQL Sentence
   cursor.execute(sql)
   # Submit amendments
   db.commit()
except:
   # Roll back when an error occurs
   db.rollback()

# Close the connection
db.close()

Executive Services

Transaction mechanism can ensure data consistency.

Transactions should have four attributes: atomicity, consistency, isolation, and persistence. These four attributes are commonly referred to as ACID features.

• atomicity. A transaction is an inseparable unit of work, and all operations involved in the transaction are either done or not done.
• consistency. Transactions must change the database from one consistent state to another. consistency is closely related to atomicity.
• isolation. The execution of a transaction cannot be interfered with by other transactions. That is to say, the operation and data used within a transaction are isolated from other concurrent transactions, and the transactions executed concurrently can not interfere with each other.
• durability. Persistence, also known as permanence, means that once a transaction is committed, its changes to the data in the database should be permanent. Other subsequent operations or failures should not have any impact on it.

Example

# SQL Delete record statement
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
   # implement SQL Sentence
   cursor.execute(sql)
   # Submit to database
   db.commit()
except:
   # Roll back when an error occurs
   db.rollback()

For databases that support transactions, in Python database programming, when the cursor is established, an invisible database transaction is automatically started.

All update operations of the commit() method cursor, and rollback() method rolls back all operations of the current cursor. Each method starts a new transaction.

error handling