Database Programming in Python

From a construction firm to a stock exchange, every organisation 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 normalisation rules for avoiding redundancy of data. The Python programming language has powerful features for database programming. Python supports various databases like MySQL, Oracle, Sybase, PostgreSQL, etc. Python also supports Data Definition Language (DDL), Data Manipulation Language (DML) and Data Query Statements. 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:

>>>cur = conn.cursor()  //creates new cursor object for executing SQL statements
>>>conn.commit()  //Commits the transactions
>>>conn.rollback()  //Roll back the transactions
>>>conn.close()  //closes the connection
>>>conn.callproc(proc,param)  //call stored procedure for execution
>>>conn.getsource(proc)  //fetches stored procedure code

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:

1. BaseCursor is the base class for Cursor objects.
2. Cursor is the default cursor class. CursorWarningMixIn, CursorStoreResultMixIn, CursorTupleRowsMixIn, and BaseCursor are some components of the cursor class.
3. CursorStoreResultMixIn uses the mysql_store_result() function to retrieve result sets from the executed query. These result sets are stored at the client side.
4. 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().

>>>cursor.execute('SELECT * FROM books')
>>>cursor.execute('''SELECT * FROM books WHERE book_name = 'python' AND  book_author = 'Mark Lutz' )
>>>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:

1. 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:

   >>> cursor.execute('insert books values (%s,%s,%s,%s)',('Py9098','Programming With Perl',120,100))
   Traceback (most recent call last):
   File "<stdin>", line 1, in ?
   File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 95, in execute
   return self._execute(query, args)
   File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 114, in _execute
   self.errorhandler(self, exc, value)
   raise errorclass, errorvalue
   _mysql_exceptions.IntegrityError: (1062, "Duplicate entry 'Py9098' for key 1")

2. OperationalError: If there are any operation errors like no databases selected, Python DB-API will handle this error as OperationalError, shown below:

   >>> cursor.execute('Create database Library')
   >>> q='select name from books where cost>=%s order by name'
   >>>cursor.execute(q,[50])
   Traceback (most recent call last):
   File "<stdin>", line 1, in ?
   File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 95, in execute
   return self._execute(query, args)
   File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 114, in _execute
   self.errorhandler(self, exc, value)
   File "/usr/lib/python2.3/site-packages/MySQLdb/connections.py", line 33, in defaulterrorhandler
   raise errorclass, errorvalue
   _mysql_exceptions.OperationalError: (1046, 'No Database Selected')

3. ProgrammingError: If there are any programming errors like duplicate database creations, Python DB-API will handle this error as ProgrammingError, shown below:

   >>> cursor.execute('Create database Library')
   Traceback (most recent call last):>>> cursor.execute('Create database Library')
   Traceback (most recent call last):
   File "<stdin>", line 1, in ?
   File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 95, in execute
   return self._execute(query, args)
   File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 114, in _execute
   self.errorhandler(self, exc, value)
   File "/usr/lib/python2.3/site-packages/MySQLdb/connections.py", line 33, in defaulterrorhandler
   raise errorclass, errorvalue
   _mysql_exceptions.ProgrammingError: (1007, "Can't create database 'Library'. Database exists")

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:

1. Import the SQL interface with the following command:

   >>> import MySQLdb

2. 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 username and password. In case of the root, there is no need to provide a password.

3. Create a cursor for the connection with the following command:

   >>>cursor = conn.cursor()

4. 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:

   >>> cursor.execute('Create database Library')
   1L      // 1L Indicates how many rows affected
   >>> cursor.execute('use Library')
   >>>table='create table books(book_accno char(30) primary key, book_name
   char(50),no_of_copies int(5),price int(5))'
   >>> cursor.execute(table)
   0L

5. Finally, fetch the result set and iterate over this result set. In this step, the user can fetch the result sets as shown below:

   >>> cursor.execute('select * from books')
   2L
   >>> cursor.fetchall()
   (('Py9098', 'Programming With Python', 100L, 50L), ('Py9099', 'Programming With Python', 100L, 50L))

   In this example, the fetchall() function is used to fetch the result sets.

More SQL operations

We can perform all SQL operations with Python DB-API. Insert, delete, aggregate and update queries can be illustrated as follows.

1. Insert SQL Query

   >>>cursor.execute('insert books values (%s,%s,%s,%s)',('Py9098','Programming With Python',100,50))
   lL           // Rows affected.
   >>> cursor.execute('insert books values (%s,%s,%s,%s)',('Py9099','Programming With Python',100,50))
   1L      //Rows affected.

   If the user wants to insert duplicate entries for a book’s accession number, the Python DB-API will show an error as it is the primary key. The following example illustrates this:

   >>> cursor.execute('insert books values (%s,%s,%s,%s)',('Py9099','Programming With Python',100,50))
   >>>cursor.execute('insert books values (%s,%s,%s,%s)',('Py9098','Programming With Perl',120,100))
   Traceback (most recent call last):
   File "<stdin>", line 1, in ?
   File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 95, in execute
   return self._execute(query, args)
   File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 114, in _execute
   self.errorhandler(self, exc, value)
   File "/usr/lib/python2.3/site-packages/MySQLdb/connections.py", line 33, in defaulterrorhandler
   raise errorclass, errorvalue
   _mysql_exceptions.IntegrityError: (1062, "Duplicate entry 'Py9098' for key 1")

2. The Update SQL query can be used to update existing records in the database as shown below:

   >>> cursor.execute('update books set price=%s where no_of_copies<=%s',[60,101])
   2L
   >>> cursor.execute('select * from books')
   2L
   >>> cursor.fetchall()
   (('Py9098', 'Programming With Python', 100L, 60L), ('Py9099', 'Programming With Python', 100L, 60L))

3. The Delete SQL query can be used to delete existing records in the database as shown below:

   >>> cursor.execute('delete from books where no_of_copies<=%s',[101])
   2L
   >>> cursor.execute('select * from books')
   0L
   >>> cursor.fetchall()
   ()
   
   >>> cursor.execute('select * from books')
   3L
   >>> cursor.fetchall() (('Py9099', 'Python-Cookbook', 200L, 90L), ('Py9098', 'Programming With Python', 100L, 50L), ('Py9097', 'Python-Nut shell', 300L, 80L))

4. Aggregate functions can be used with Python DB-API in the database as shown below:

   >>> cursor.execute('select * from books')
   4L
   >>> cursor.fetchall()
   (('Py9099', 'Python-Cookbook', 200L, 90L), ('Py9098', 'Programming With Python', 100L, 50L), ('Py9097', 'Python-Nut shell', 300L, 80L), ('Py9096', 'Python-Nut shell', 400L, 90L))
   >>> cursor.execute("select sum(price),avg(price) from books where book_name='Python-Nut shell'")
   1L
   >>> cursor.fetchall()
   ((170.0, 85.0),)

I hope you’ll now enjoy the power of Python for database programming!

References

• Python website
• Programming Python by Mark Lutz, O’Reilly Publications
• Accessing Databases using Python DBAPI-2.0 by Federico Di Gregorio

This article was first published on 1 May 2009 and was recently updated on 10 April 2019.