Python Data Persistence – Python DB-API
The previous chapter was an overview of SQL fundamentals with the help of the SQLite database. However, as mentioned therein short, there are several equally popular RDBMS in use worldwide. Some of them are open-source and others for enterprise use. Although all of them use SQL underneath them, there are a lot of differences in the implementation of SQL standards by each of them. This also reflected in Python’s interface modules written by individuals for interaction with databases. Since each module defined its own functionality for interaction with the respective database product, its major fallout is a lack of compatibility. If for some reason, a user is required to switch to a different database product, almost the entire code that handles the back-end processing had to be rewritten.
To find a solution for this incompatibility issue, a ‘Special Interest Group’ was formed in 1996. This group (db-sig) recommended a set of specifications by raising ‘Python Enhancement Proposal (PEP 248)’ for a consistent interface to relational databases known as DB-API. The specifications have since been modified by subsequent enhancement proposals (PEP 249). Its recommendations are called DB-API Version 2.0.
As various Python database modules have been made DB-API compliant, most of the functionality required to interact with any database is uniform. Hence, even if the database itself is switched, only a couple of changes in the code should be sufficient.
Oracle, the world’s most popular relational database can be interface with Python with more than one module. cx_OracIe is a Python extension module that enables access to Oracle Database with some features of its owrt in addition to DB-API. It can be used with Oracle 11.2,12.1, and 12.2, and 18.3 client libraries. There is pyodbc module that acts as a Python- ODBC bridge driver that can be used for Python-Oracle interaction as well.
To use Microsoft’s SQL Server database with Python also, there are a couple of alternatives. The pymysql module is there in addition to pyodbc module.
As far as PostgreSQL is concerned, psycopg2 module is the most popular PostgreSQL adapter for the Python programming language.
MySQL is also a very popular relational database, especially in the open-source domain. MySQL Connector/Python is a standardized database driver provided by MySQL itself. There is a mysqldb module for the Python interface but is not still compatible with Python 3.x. You can use pymysql module as a drop-in replacement for mysqldb module while using the Python 3.x version.
As mentioned in the previous chapter, Python’s standard library consists of the sqlite3 module which is a DB-AP1 compliant module for handling the SQLite database through Python program. While other modules mentioned above should be installed in the current Python installation – either by pip utility or by using a customized installer (as in case of MySQL Connector/ Python), the sqlite3 module needs no such installation.
- Data Persistence – Python – PyMongo
- Python Data Persistence – SQLAlchemy Core
- Python Data Persistence – Relational Database
sqlite3 Module
SQLite is often used as a prototyping tool for larger databases. The fact that SQLite is extremely lightweight and doesn’t require any server but still is a fully-featured implementation of SQL, it is a common choice in the developmental stage of an application and is eventually replaced by enterprise RDBMS products such as Oracle. Likewise, you can think of the sqlite3 module as a prototype DB-API module. We shall explore the DB-API specifications with the help of the sqlite3 module. We will soon discover how easy it is to switch to other databases just by modifying a couple of statements.
Let us start by importing the sqlite3 module. Note that, its target SQLite library version may be different from the SQLite binary downloaded by you as shown in the previous chapter.
Example
>>> import sqlite3 >>> sqlite3.sqlite_version '3.21..0'
Connection Object
The all-important connection object is set up by the module level connect ( ) function. The first positional argument to this function is a string representing path (relative or absolute) to an SQLite database file. The function returns a connection object referring to the database file – either existing or new. Assuming that, ‘newdb. sqlite’ doesn’t already exist, the following statement opens it:
>>> conn=sqlite3.connect('newdb.sqlite')
>>> type(conn)
<class 'sqlite3.Connection'>
As we’ve seen in the previous chapter, SQLite supports an in-memory database. To open the same programmatically use “:memory: ” as its path.
>>> conn=sqlite3.connect(":memory:")
The connection object has access to various methods in the connection class. One of them is a cursor () method that returns a cursor object, which we shall know in the next section. Transaction control is achieved by commit () and rollback () methods of the connection object. Connection class has important methods to define custom functions and aggregates to be used in SQL queries. Later in this chapter create_ function () and create_aggregate () methods are explained.
cursor Object
After opening the database, the next step is to get a cursor object from it which is essential to perform any operation on the database. A database cursor enables traversal over the records in a database. It facilitates CRUD operations on a table. The database cursor can be considered similar to the concept of an iterator. The cursor ( ) method on the connection object returns the cursor object.
>>> cur=conn.cursor() >>> type(cur) <class 'sqlite3.Cursor'>
Once we get hold of the cursor object, we can perform all SQL query operations, primarily with the help of its execute () method. This method needs a strong argument which must be a valid SQL statement. String argument having incorrect SQL statement raises exceptions as defined in the sqlite3 module. Hence, it is recommended that a standard exception handling mechanism is used.
Updating Data
It is fairly straightforward to programmatically perform update operations on a table in the SQLite database. As pointed out in the previous chapter, the update query is normally a conditional operation unless you intend to update all rows of a certain table. Hence a parameterized query is ideal for this purpose.
Following script (1 updateqry.py!) asks the user to input the name of the product and new price and performs update operation accordingly.
Example
import sqlite3
conn=sqlite3.connect(1mydb.sqlite')
nm=input(1 enter name of product:')
p=int(input('new price:'))
qry='update Products set price=? where name=?'
cur=conn.cursor()
try;
cur.execute(qry, (p,nm))
print ('record updated')
conn.commit()
except:
print ('error in update operation')
conn.rollback()
conn.close( )
Run the above script from a command prompt:
E:\python37>python updateqry.py enter the name of the product: TV new price:32000 record updated
The SQLite console can confirm the above action.
sqlite> select * from products where name='TV'; ProductID Name Price ------------ -------- -------- 2 TV 32000