Database Access in Python: Connecting and Querying Databases

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.

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.

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.

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.

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.

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.

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 −

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

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.

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.

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.

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 −

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.

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.

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 −

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 −

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 −

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.

The End! should you have any inquiries, we encourage you to reach out to the Vercaa Support Center without hesitation.