Python3 MySQL Database Connection - PyMySQL Driver

In this article, we will introduce how to connect to a database using PyMySQL in Python3 and perform simple operations like insert, delete, update, and query.

What is PyMySQL?

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

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 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 install it using the following methods:

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

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

2. 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: Ensure you have root privileges to install the above modules.

>

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

Example installation on Linux:

$ 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 user name used to connect to the TESTDB database is "testuser" with the password "test123". You can set your own or directly use the root user and its password. MySQL database user authorization should use the Grant command.

• 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 the cursor() method to create a cursor object
cursor = db.cursor()

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

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

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

# Close the database connection
db.close()

The output of the above script is as follows:

Database version : 5.5.20-log

Creating Database Tables

If the database connection exists, we can use the execute() method to create a table in the database, such as creating 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')

# Create a cursor object using the cursor() method
cursor = db.cursor()

# Execute SQL command to delete the table if it exists
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

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

cursor.execute(sql)

# Disconnect from server
db.close()

Database Insert Operation

The following example uses the 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')

# Get cursor object using the cursor() method
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 the SQL command
   cursor.execute(sql)
   # Commit changes to the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# Disconnect from server
db.close()

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

Example (Python 3.0+)

#!/usr/bin/python3

import pymysql

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

# Get cursor object using the cursor() method
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 the SQL command
   cursor.execute(sql)
   # Commit changes to the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# Disconnect from server
db.close()

The following code uses variables to pass parameters to the SQL statement:

..................................
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 retrieve a single row of data and the fetchall() method to retrieve multiple rows of data.

• fetchone(): This method retrieves the next set of query results. The result set is an object.

• fetchall(): This method retrieves all the rows of the query result.

• 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 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 the cursor() method to get the cursor 
cursor = db.cursor()

# SQL query statement
sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > %s" % (1000)
try:
   # Execute the 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=%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()

The above script execution result is as follows:

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

Database Update Operation

The update operation is used to update data in the table. The following example increments the AGE field by 1 where SEX is 'M' in the TESTDB 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 the cursor() method to get the cursor 
cursor = db.cursor()

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

# Close database connection
db.close()

Delete Operation

The delete operation is used to delete data from the 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 the cursor() method to get the cursor 
cursor = db.cursor()

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

# Close database connection
db.close()

# Rollback on error
db.rollback()

# Close connection
db.close()

Transaction Execution

Transaction mechanisms ensure data consistency.

Transactions should have four properties: atomicity, consistency, isolation, and durability. These four properties are commonly referred to as the ACID properties.

• Atomicity. A transaction is an indivisible unit of work; either all the operations included in it are performed, or none are.

• Consistency. A transaction must transition the database from one consistent state to another. Consistency is closely related to atomicity.

• Isolation. The execution of a transaction should not be interfered with by other transactions. The operations within a transaction and the data it uses are isolated from concurrent other transactions, and the transactions executed concurrently should not interfere with each other.

• Durability. Also known as permanence, it means that once a transaction is committed, the changes it makes to the data in the database should be permanent. Subsequent operations or failures should not have any impact on them.

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

Example

Example (Python 3.0+)

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

For databases that support transactions, in Python database programming, a hidden database transaction automatically begins when a cursor is created.

The commit() method updates all 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 errors and exceptions for database operations, as listed in the table below:

The following is the inheritance structure of exceptions:

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