Python3 MySQL Database Connection – PyMySQL Driver

This article introduces how to use PyMySQL in Python3 to connect to a database and implement simple CRUD operations.

What is PyMySQL?

PyMySQL is a library for connecting to MySQL servers in Python3.x versions. In Python2, mysqldb is used instead.

PyMySQL follows the Python Database API v2.0 specification and includes a pure-Python MySQL client library.

PyMySQL Installation

Before using PyMySQL, we need to ensure that PyMySQL is installed.

PyMySQL download address: https://github.com/PyMySQL/PyMySQL.

If it is not yet installed, we can use the following command to install the latest version of PyMySQL:

$ pip3 install PyMySQL

If your system does not support the pip command, you can use the following methods to install:

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

If you need to specify a version number, you can use the curl command to install:

$ # X.X is the version number of PyMySQL
$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
$ cd PyMySQL*
$ python3 setup.py install
$ # Now you can delete the PyMySQL* directory

Note: Please ensure you have root privileges to install the above module.

During installation, you may encounter an error message “ImportError: No module named setuptools”, which means you do not have setuptools installed. You can visit https://pypi.python.org/pypi/setuptools to find installation methods for various systems.

Linux system installation example:
$ wget https://bootstrap.pypa.io/ez_setup.py
$ python3 ez_setup.py

Database Connection

Before connecting to the database, please confirm the following:

You have created the database TESTDB.
You have created the table EMPLOYEE in the TESTDB database.
The EMPLOYEE table fields are FIRST_NAME, LAST_NAME, AGE, SEX, and INCOME.
The username used to connect to the TESTDB database is “testuser” and the password is “test123”. You can set your own or directly use the root username and password. Use the Grant command for MySQL database user authorization.
The Python pymysql module is installed on your machine.
If you are not familiar with SQL statements, you can visit our SQL Basic Tutorial

Example:

The following example connects to the MySQL TESTDB database:

Example (Python 3.0+)

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect(host='localhost',
                     user='testuser',
                     password='test123',
                     database='TESTDB')

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

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

# Use fetchone() method to get a single row of data
data = cursor.fetchone()

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

# Close database connection
db.close()

Output of the above script:

Database version : 5.5.20-log

Creating a Database Table

If the database connection exists, we can use the execute() method to create a table for the database, as shown below to create the EMPLOYEE table:

Example (Python 3.0+)

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect(host='localhost',
                     user='testuser',
                     password='test123',
                     database='TESTDB')

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

# Use execute() method to execute SQL, drop table if it exists
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# Create table using prepared 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)

# Close database connection
db.close()

Database Insert Operation

The following example uses executing an SQL INSERT statement to insert a record into the EMPLOYEE table:

Example (Python 3.0+)

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect(host='localhost',
                     user='testuser',
                     password='test123',
                     database='TESTDB')

# Use cursor() method to get a 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:
   # Execute SQL statement
   cursor.execute(sql)
   # Commit to database
   db.commit()
except:
   # Rollback in case of error
   db.rollback()

# Close database connection
db.close()

The above example can also be written as follows:

Example (Python 3.0+)

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect(host='localhost',
                     user='testuser',
                     password='test123',
                     database='TESTDB')

# Use cursor() method to get a cursor
cursor = db.cursor()

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

# Close database connection
db.close()

The following code uses variables to pass parameters to SQL statements:

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

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

Database Query Operation

Python queries MySQL using the fetchone() method to get a single row of data, and the fetchall() method to get multiple rows of data.

fetchone(): This method gets the next query result set. The result set is an object.
fetchall(): Receives all returned result rows.
rowcount: This is a read-only attribute that returns the number of rows affected after executing the execute() method.

Example:

Query all data from the EMPLOYEE table where the salary (income) field is greater than 1000:

Example (Python 3.0+)

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect(host='localhost',
                     user='testuser',
                     password='test123',
                     database='TESTDB')

# Use cursor() method to get a cursor
cursor = db.cursor()

# SQL query statement
sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > %s" % (1000)
try:
   # Execute SQL statement
   cursor.execute(sql)
   # Get all record lists
   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=%s,sex=%s,income=%s" % \
             (fname, lname, age, sex, income ))
except:
   print ("Error: unable to fetch data")

# Close database connection
db.close()

Output of the above script:

fname=Mac, lname=Mohan, age=20, sex=M, income=2000

Database Update Operation

The update operation is used to update data in a data table. The following example increments the AGE field by 1 for records in the TESTDB table where SEX is ‘M’:

Example (Python 3.0+)

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect(host='localhost',
                     user='testuser',
                     password='test123',
                     database='TESTDB')

# Use cursor() method to get a cursor
cursor = db.cursor()

# SQL update statement
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
try:
   # Execute SQL statement
   cursor.execute(sql)
   # Commit to database
   db.commit()
except:
   # Rollback in case of error
   db.rollback()

# Close database connection
db.close()

Delete Operation

The delete operation is used to remove data from a data table. The following example demonstrates deleting all data from the EMPLOYEE table where AGE is greater than 20:

Example (Python 3.0+)

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect(host='localhost',
                     user='testuser',
                     password='test123',
                     database='TESTDB')

# Use cursor() method to get a cursor
cursor = db.cursor()

# SQL delete statement
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try:
   # Execute SQL statement
   cursor.execute(sql)
   # Commit the change
   db.commit()
except:
   # Rollback in case of error
   db.rollback()

# Close connection
db.close()

Executing Transactions

The transaction mechanism ensures data consistency.

A transaction should have four properties: atomicity, consistency, isolation, and durability. These four properties are commonly referred to as ACID characteristics.

Atomicity. A transaction is an indivisible unit of work. All operations included in a transaction are either all done or none done.
Consistency. A transaction must change the database from one consistent state to another consistent state. Consistency is closely related to atomicity.
Isolation. The execution of a transaction cannot be interfered with by other transactions. That is, the operations and data used within a transaction are isolated from other concurrent transactions, and concurrently executing transactions cannot interfere with each other.
Durability. Durability is also called permanence, meaning that once a transaction is committed, its changes to the data in the database should be permanent. Subsequent operations or failures should not have any impact on it.

Python DB API 2.0 provides two methods for transactions: commit and rollback.

Example

Example (Python 3.0+)

# SQL delete record statement
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try:
   # Execute SQL statement
   cursor.execute(sql)
   # Commit to database
   db.commit()
except:
   # Rollback in case of error
   db.rollback()

For databases that support transactions, in Python database programming, when a cursor is established, an implicit database transaction automatically begins.

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

Error Handling

The DB API defines some database operation errors and exceptions. The following table lists these errors and exceptions:


Exception	Description
Warning	Triggered when there is a serious warning, such as data being truncated during insertion, etc. Must be a subclass of StandardError.
Error	All other error classes except Warning. Must be a subclass of StandardError.
InterfaceError	Triggered when there is an error in the database interface module itself (not a database error). Must be a subclass of Error.
DatabaseError	Triggered when a database-related error occurs. Must be a subclass of Error.
DataError	Triggered when there is a data processing error, such as: division by zero, data out of range, etc. Must be a subclass of DatabaseError.
OperationalError	Refers to errors that are not user-controlled but occur when operating the database. For example: unexpected connection disconnection, database name not found, transaction processing failure, memory allocation error, etc. Must be a subclass of DatabaseError.
IntegrityError	Integrity-related errors, such as foreign key check failure, etc. Must be a subclass of DatabaseError.
InternalError	Internal database errors, such as cursor failure, transaction synchronization failure, etc. Must be a subclass of DatabaseError.
ProgrammingError	Programming errors, such as table not found or already exists, SQL statement syntax error, wrong number of parameters, etc. Must be a subclass of DatabaseError.
NotSupportedError	Unsupported errors, referring to the use of functions or APIs not supported by the database. For example, using .rollback() on a connection object when the database does not support transactions or the transaction has been closed. Must be a subclass of DatabaseError.

The following is the inheritance structure of exceptions:

Exception
|__Warning
|__Error
   |__InterfaceError
   |__DatabaseError
      |__DataError
      |__OperationalError
      |__IntegrityError
      |__InternalError
      |__ProgrammingError
      |__NotSupportedError