Python PyDB Introduction

PyDB Introduction

In real life scenarios, the end user will not know anything about Database. We can't expect an end user to write queries to get his data from the Database. In all applications like facebook, gmail, internet banking, whatsapp and so on, the application is what connects to the database, retrieves the data, operates on the data and finally gives meaningful information to the user.

The underlying language of the application can be anything, including Python! In this course we will take a look at how to write Python programs which will connect to a Database.

Python Database Programming

Database programming with Python is very easy. You just need 8 lines of code to insert a record into a database table from Python. Take a look!

import cx_Oracle

	con = cx_Oracle.Connection('user/pswd192.168.2.1/john')

	cur = cx_Oracle.Cursor(con)

	cur.execute(“INSERT INTO Computer VALUES (1005,'Dell','Vostro',2013)”);

	print(cur.rowcount)

	cur.close()

	con.commit()

	con.close()

Closer look at the earlier code reveals four major steps in connecting to a Oracle Database:

Let us see each of these steps in detail

Establish a connection

cx_Oracle, a third party module for Python, allows us to connect to an Oracle Database from a Python program. This module has many important classes and functions which help us connect to a database.

To use the module, we need to import it as:

import cx_Oracle

Just the way SQL Plus was a program used to connect to Oracle database, we can make a Python program connect to a database.

The username, password, IP Address and Service Name are mentioned in a special string called a connection string.

The below is a valid connection string syntax:

username/password@ipaddress/ServiceName

The below is an example of a valid connection string:

user/pswd@192.168.2.1/john

Using the connection string

cx_Oracle module has the Connection class. Invoking the Connection class constructor by passing the connection string creates a connection object. For example,

con =cx_Oracle.Connection('user/pswd@192.168.2.1/john')

The connection object is used to perform all operations with the database.

Executing a query

To execute any type of query, we need to use the Cursor object. The Cursor object in Python is different from the database cursor you have already learnt in PL/SQL. It is created by invoking the Cursor constructor and passing the connection object as a parameter.

Any query is executed by using the execute() method of the cursor object.

import cx_Oracle
	con = cx_Oracle.Connection(‘user/pswd192.168.2.1/john’)
	cur = cx_Oracle.Cursor(con)
	cur.execute(“INSERT INTO Computer VALUES (100,“Toshiba”,5688,2013)”); #Execute method of cur object

Closing a connection

The number of concurrent connections available to a database are finite and limited. If many connections to the database are kept open, database will run out of connections for other users. Therefore at the end of all operations, the cursor and connection has to be closed. This is done by using the close() method of the connection object.

The syntax is as follows :

con.close()