From a construction firm to a stock exchange, every organization depends on large databases. These are essentially collections of tables, and’ connected with each other through columns.

These database systems support SQL, the Structured Query Language, which is used to create, access and manipulate the data. SQL is used to access data, and also to create and exploit the relationships between the stored data. Additionally, these databases support database normalization rules for avoiding redundancy of data.

For database programming, the Python DB API is a widely used module that provides a database application programming interface.

Benefits of Python for database programming

There are many good reasons to use Python for programming database applications:

Programming in Python is arguably more efficient and faster compared to other languages.

Python is famous for its portability.

It is platform independent.

Python supports SQL cursors.

In many programming languages, the application developer needs to take care of the open and closed connections of the database, to avoid further exceptions and errors. In Python, these connections are taken care of.

Python supports relational database systems.

Python database APIs are compatible with various databases, so it is very easy to migrate and port database application interfaces.

DB-API (SQL-API) for Python

Python DB-API is independent of any database engine, which enables you to write Python scripts to access any database engine. The Python DB API implementation for MySQL is MySQLdb. For PostgreSQL, it supports psycopg, PyGresQL and pyPgSQL modules. DB-API implementations for Oracle are dc_oracle2 and cx_oracle. Pydb2 is the DB-API implementation for DB2. Python’s DB-API consists of connection objects, cursor objects, standard exceptions and some other module contents, all of which we will discuss.

Connection objects

Connection objects create a connection with the database and these are further used for different transactions. These connection objects are also used as representatives of the database session.
A connection is created as follows:

>>>conn = MySQLdb.connect('library', user='suhas', password='python')

You can use a connection object for calling methods like commit(), rollback() and close() as shown below:

Cursor objects

Cursor is one of the powerful features of SQL. These are objects that are responsible for submitting various SQL statements to a database server. There are several cursor classes in MySQLdb.cursors:

BaseCursor is the base class for Cursor objects.

Cursor is the default cursor class. CursorWarningMixIn, CursorStoreResultMixIn, CursorTupleRowsMixIn, and BaseCursor are some components of the cursor class.

CursorStoreResultMixIn uses the mysql_store_result() function to retrieve result sets from the executed query. These result sets are stored at the client side.

CursorUseResultMixIn uses the mysql_use_result() function to retrieve result sets from the executed query. These result sets are stored at the server side.

The following example illustrates the execution of SQL commands using cursor objects. You can use execute to execute SQL commands like SELECT. To commit all SQL operations you need to close the cursor as cursor.close().

Error and exception handling in DB-API

Exception handling is very easy in the Python DB-API module. We can place warnings and error handling messages in the programs. Python DB-API has various options to handle this, like Warning, InterfaceError, DatabaseError, IntegrityError, InternalError, NotSupportedError, OperationalError and ProgrammingError.
Let’s take a look at them one by one:

IntegrityError: Let’s look at integrity error in detail. In the following example, we will try to enter duplicate records in the database. It will show an integrity error, _mysql_exceptions.IntegrityError, as shown below:

Python and MySQL

Python and MySQL are a good combination to develop database applications. After starting the MySQL service on Linux, you need to acquire MySQLdb, a Python DB-API for MySQL to perform database operations. You can check whether the MySQLdb module is installed in your system with the following command:

>>>import MySQLdb

If this command runs successfully, you can now start writing scripts for your database.

To write database applications in Python, there are five steps to follow:

Import the SQL interface with the following command:

>>> import MySQLdb

Establish a connection with the database with the following command:

>>> conn=MySQLdb.connect(host='localhost',user='root',passwd='')

…where host is the name of your host machine, followed by the user name and password. In case of the root, there is no need to provide a password.

Create a cursor for the connection with the following command:

>>>cursor = conn.cursor()

Execute any SQL query using this cursor as shown below—here the outputs in terms of 1L or 2L show a number of rows affected by this query: