Python - Database Access

Data input and generated during execution of a program is stored in RAM. If it is to be stored persistently, it needs to be stored in database tables. There are various relational database management systems (RDBMS) available.

GadFly
MySQL
PostgreSQL
Microsoft SQL Server
Informix
Oracle
Sybase
SQLite
and many more...

In this chapter, we shall learn how to access database using Python, how to store data of Python objects in a SQLite database, and how to retrieve data from SQLite database and process it using Python program.

Relational databases use SQL (Structured Query Language) for performing INSERT/DELETE/UPDATE operations on the database tables. However, implementation of SQL varies from one type of database to other. This raises incompatibility issues. SQL instructions for one database do not match with other.

To overcome this incompatibility, a common interface was proposed in PEP (Python Enhancement Proposal) 249. This proposal is called DB-API and requires that a database driver program used to interact with Python should be DB-API compliant.

Python's standard library includes sqlite3 module which is a DB_API compatible driver for SQLite3 database, it is also a reference implementation of DB-API.

Since the required DB-API interface is built-in, we can easily use SQLite database with a Python application. For other types of databases, you will have to install the relevant Python package.

Database	Python Package
Oracle	cx_oracle, pyodbc
SQL Server	pymssql, pyodbc
PostgreSQL	psycopg2
MySQL	MySQL Connector/Python, pymysql

A DB-API module such as sqlite3 contains connection and cursor classes. The connection object is obtained with connect() method by providing required connection credentials such as name of server and port number, and username and password if applicable. The connection object handles opening and closing the database, and transaction control mechanism of committing or rolling back a transaction.

The cursor object, obtained from the connection object, acts as the handle of the database when performing all the CRUD operations.

The sqlite3 Module

SQLite is a server-less, file-based lightweight transactional relational database. It doesn't require any installation and no credentials such as username and password are needed to access the database.

Python's sqlite3 module contains DB-API implementation for SQLite database. It is written by Gerhard Häring. Let us learn how to use sqlite3 module for database access with Python.

Let us start by importing sqlite3 and check its version.

>>> import sqlite3
>>> sqlite3.sqlite_version
'3.39.4'

The Connection Object

A connection object is set up by connect() function in sqlite3 module. First positional argument to this function is a string representing path (relative or absolute) to a SQLite database file. The function returns a connection object referring to the database.

>>> conn=sqlite3.connect('testdb.sqlite3')
>>> type(conn)
<class 'sqlite3.Connection'>

Various methods are defined in connection class. One of them is cursor() method that returns a cursor object, about which we shall know in next section. Transaction control is achieved by commit() and rollback() methods of connection object. Connection class has important methods to define custom functions and aggregates to be used in SQL queries.

The Cursor Object

Next, we need to get the cursor object from the connection object. It is your handle to the database when performing any CRUD operation on the database. The cursor() method on connection object returns the cursor object.

>>> cur=conn.cursor()
>>> type(cur)
<class 'sqlite3.Cursor'>

We can now perform all SQL query operations, with the help of its execute() method available to cursor object. This method needs a string argument which must be a valid SQL statement.

Creating a Database Table

We shall now add Employee table in our newly created 'testdb.sqlite3' database. In following script, we call execute() method of cursor object, giving it a string with CREATE TABLE statement inside.

import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry='''
CREATE TABLE Employee (
EmpID INTEGER PRIMARY KEY AUTOINCREMENT,
FIRST_NAME TEXT (20),
LAST_NAME TEXT(20),
AGE INTEGER,
SEX TEXT(1),
INCOME FLOAT
);
'''
try:
   cur.execute(qry)
   print ('Table created successfully')
except:
   print ('error in creating table')
conn.close()

When the above program is run, the database with Employee table is created in the current working directory.

We can verify by listing out tables in this database in SQLite console.

sqlite> .open mydb.sqlite
sqlite> .tables
Employee

INSERT Operation

The INSERT Operation is required when you want to create your records into a database table.

Example

The following example, executes SQL INSERT statement to create a record in the EMPLOYEE table −

import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry="""INSERT INTO EMPLOYEE(FIRST_NAME,
   LAST_NAME, AGE, SEX, INCOME)
   VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
   cur.execute(qry)
   conn.commit()
   print ('Record inserted successfully')
except:
   conn.rollback()
print ('error in INSERT operation')
conn.close()

You can also use the parameter substitution technique to execute the INSERT query as follows −

import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry="""INSERT INTO EMPLOYEE(FIRST_NAME,
   LAST_NAME, AGE, SEX, INCOME)
   VALUES (?, ?, ?, ?, ?)"""
try:
   cur.execute(qry, ('Makrand', 'Mohan', 21, 'M', 5000))
   conn.commit()
   print ('Record inserted successfully')
except Exception as e:
   conn.rollback()
   print ('error in INSERT operation')
conn.close()

READ Operation

READ Operation on any database means to fetch some useful information from the database.

Once the database connection is established, you are ready to make a query into this database. You can use either fetchone() method to fetch a single record or fetchall() method to fetch multiple values from a database table.

fetchone() − It fetches the next row of a query result set. A result set is an object that is returned when a cursor object is used to query a table.
fetchall() − It fetches all the rows in a result set. If some rows have already been extracted from the result set, then it retrieves the remaining rows from the result set.
rowcount − This is a read-only attribute and returns the number of rows that were affected by an execute() method.

Example

In the following code, the cursor object executes SELECT * FROM EMPLOYEE query. The resultset is obtained with fetchall() method. We print all the records in the resultset with a for loop.

import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry="SELECT * FROM EMPLOYEE"

try:
   # Execute the SQL command
   cur.execute(qry)
   # Fetch all the rows in a list of lists.
   results = cur.fetchall()
   for row in results:
      fname = row[1]
      lname = row[2]
      age = row[3]
      sex = row[4]
      income = row[5]
      # Now print fetched result
      print ("fname={},lname={},age={},sex={},income={}".format(fname, lname, age, sex, income ))
except Exception as e:
   print (e)
   print ("Error: unable to fecth data")

conn.close()

It will produce the following output −

fname=Mac,lname=Mohan,age=20,sex=M,income=2000.0
fname=Makrand,lname=Mohan,age=21,sex=M,income=5000.0

Update Operation

UPDATE Operation on any database means to update one or more records, which are already available in the database.

The following procedure updates all the records having income=2000. Here, we increase the income by 1000.

import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry="UPDATE EMPLOYEE SET INCOME = INCOME+1000 WHERE INCOME=?"

try:
   # Execute the SQL command
   cur.execute(qry, (1000,))
   # Fetch all the rows in a list of lists.
   conn.commit()
   print ("Records updated")
except Exception as e:
   print ("Error: unable to update data")
conn.close()

DELETE Operation

DELETE operation is required when you want to delete some records from your database. Following is the procedure to delete all the records from EMPLOYEE where INCOME is less than 2000.

import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry="DELETE FROM EMPLOYEE WHERE INCOME<?"

try:
   # Execute the SQL command
   cur.execute(qry, (2000,))
   # Fetch all the rows in a list of lists.
   conn.commit()
   print ("Records deleted")
except Exception as e:
   print ("Error: unable to delete data")

conn.close()

Performing Transactions

Transactions are a mechanism that ensure data consistency. Transactions have the following four properties −

Atomicity − Either a transaction completes or nothing happens at all.
Consistency − A transaction must start in a consistent state and leave the system in a consistent state.
Isolation − Intermediate results of a transaction are not visible outside the current transaction.
Durability − Once a transaction was committed, the effects are persistent, even after a system failure.

The Python DB API 2.0 provides two methods to either commit or rollback a transaction.

Example

You already know how to implement transactions. Here is a similar example −

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

COMMIT Operation

Commit is an operation, which gives a green signal to the database to finalize the changes, and after this operation, no change can be reverted back.

Here is a simple example to call the commit method.

db.commit()

ROLLBACK Operation

If you are not satisfied with one or more of the changes and you want to revert back those changes completely, then use the rollback() method.

Here is a simple example to call the rollback() method.

db.rollback()

The PyMySQL Module

PyMySQL is an interface for connecting to a MySQL database server from Python. It implements the Python Database API v2.0 and contains a pure-Python MySQL client library. The goal of PyMySQL is to be a drop-in replacement for MySQLdb.

Installing PyMySQL

Before proceeding further, you make sure you have PyMySQL installed on your machine. Just type the following in your Python script and execute it −

import PyMySQL

If it produces the following result, then it means MySQLdb module is not installed −

Traceback (most recent call last):
   File "test.py", line 3, in <module>
      Import PyMySQL
ImportError: No module named PyMySQL

The last stable release is available on PyPI and can be installed with pip −

pip install PyMySQL

Note − Make sure you have root privilege to install the above module.

MySQL Database Connection

Before connecting to a MySQL database, make sure of the following points −

You have created a database TESTDB.
You have created a table EMPLOYEE in TESTDB.
This table has fields FIRST_NAME, LAST_NAME, AGE, SEX and INCOME.
User ID "testuser" and password "test123" are set to access TESTDB.
Python module PyMySQL is installed properly on your machine.
You have gone through MySQL tutorial to understand MySQL Basics.

Example

To use MySQL database instead of SQLite database in earlier examples, we need to change the connect() function as follows −

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

Apart from this change, every database operation can be performed without difficulty.

Handling Errors

There are many sources of errors. A few examples are a syntax error in an executed SQL statement, a connection failure, or calling the fetch method for an already cancelled or finished statement handle.

The DB API defines a number of errors that must exist in each database module. The following table lists these exceptions.

Sr.No.	Exception & Description
1	Warning Used for non-fatal issues. Must subclass StandardError.
2	Error Base class for errors. Must subclass StandardError.
3	InterfaceError Used for errors in the database module, not the database itself. Must subclass Error.
4	DatabaseError Used for errors in the database. Must subclass Error.
5	DataError Subclass of DatabaseError that refers to errors in the data.
6	OperationalError Subclass of DatabaseError that refers to errors such as the loss of a connection to the database. These errors are generally outside of the control of the Python scripter.
7	IntegrityError Subclass of DatabaseError for situations that would damage the relational integrity, such as uniqueness constraints or foreign keys.
8	InternalError Subclass of DatabaseError that refers to errors internal to the database module, such as a cursor no longer being active.
9	ProgrammingError Subclass of DatabaseError that refers to errors such as a bad table name and other things that can safely be blamed on you.
10	NotSupportedError Subclass of DatabaseError that refers to trying to call unsupported functionality.

Print Page