DB-API is python’s standard way of accessing Relational DBMS. It is a standard that allows you to write a single program that works with multiple kinds of RDBMS, instead of writing a separate program for each one. So if you have learned the DB API functions, you can apply that knowledge to use any database with python. Some of its benefits are that it is easy to implement and understand, they are encouraged to have similarity between python module, achieves consistency which leading to more easily understood modules, and the code is generally more portable across databases and has a broader reach of database connectivity from python.
Each database system has its own library. Here we are only interested in the ibm_api library is used to connect to an IBM DB2 database. The two main concepts in the Python DB-API are connection objects and query objects. Connection objects are used to connect to a database and manage your transactions. Cursor objects are used to run queries. We run queries after opening a cursor object. The cursor works similar to a cursor in a text processing system where you scroll down in your result set and get your data into the application. Cursors are used to scan through the results of a database.
The DB_API includes a connect constructor for creating a connection to the database. It returns a Connection object, which is then used by the various connection methods. Cursor methods are used to manage the contents of a fetch operation.
A database cursor is a control structure that enables traversal over the records in a database. It behaves like a file name or filehandle in a programming language. Just as a program opens a file to access its contents, it opens a cursor to gain access to the retrieved query results. Similarly, the program closes a file to end its access and closes a cursor to end access to the query results. Another similarity is that just as filehandle keeps track of the program’s current position within an open file, a cursor keeps track of the program’s current position within the query results. Cursors created from the same connection are not isolated. That is, any changes done to the database by one cursor are immediately visible to the other cursors. Cursors created from different connections can or cannot be isolated depending on how the transaction support is implemented. Below is a basic python code that uses the DB-API to query a database.
from dbmodule import connect
connection = connect(‘dbname’,’username’,’password’)
#creating a connection object
cur = connection.cursor()
#creating a cursor object
cur.execute(‘select * from mytable’)
results=cur.fetchall()
#running queries
cur.close()
connection.close()
#freeing resources
The ibm_db API provides a variety of useful Python functions for accessing and manipulating data in an IBM data server database, including functions for connecting to a database, preparing and issuing SQL statements, fetching rows from result sets, calling stored procedures, committing and rolling back transactions, handling errors and retrieving metadata. The ibm_db API uses the IBM Data Server Driver for ODBC, and CLI APIs to connect to IBM, DB2, and Informix.
After importing the ibm_db library into our python application, connecting to the DB2 requires the following information: a driver name, a database name, a host DNS name or IP address, a host port, a connection protocol, a user ID, and a user password.
Here is an example of creating an IBM DB2 connection in python.
We create a connection object DSN, which stores the connection credentials. The connect function of the ibm_db API will be used to create a non-persistent connection. The DSN object is passed as a parameter to the connection function. If a connection has been established with the database, then the code returns connected, as the output otherwise, the output will be unable to connect to the database. Then we free all resources by closing the connection.
