Database Connectivity in Python

Database Connectivity in Python with Examples

In this article, I am going to discuss Database Connectivity in Python with examples. Please read our previous article where we discussed Multithreading in Python. At the end of this article, you will understand the Database Connectivity Example in Python with Oracle database.

Introduction:

Every application requires someplace to store the data like customer’s information, billing information, calling information, etc. To store the data we need storage areas. There are two storage areas normally. They are:

  1. Temporary Storage Areas
  2. Permanent Storage Areas
Temporary storage area:

These are the Memory Areas where Data will be stored temporarily. For example, Python objects like list, tuple, dictionary. Once the Python program completes its execution then these objects will be destroyed automatically, and data will be lost.

Permanent Storage Areas:

Also known as Persistent Storage Areas. Here we can store Data permanently. For example File Systems, Databases, Data warehouses, Big Data Technologies, etc

File Systems:

File Systems can be provided by a Local operating System. File Systems are best suitable to store very little Amount of Information.

Limitations of File Systems:
  1. We cannot store a huge amount of Information.
  2. There is no Query Language support and hence operations will become very complex.
  3. There is no Security for Data.
  4. There is no Mechanism to prevent duplicate Data. Hence there may be a chance of data Inconsistency Problems.
Overcoming limitations

To overcome the above Problems of File Systems, we should go for Databases.

Database:

The data in the database is stored in the form of a table. We can store a Huge Amount of Information in Databases. Query Language Support is available for every Database and hence we can perform Database Operations very easily.

To access Data present in the Database, a username and password are required, hence making it secured.

While creating Database Table Schema, Database Admin follows various Normalization Techniques and can implement various Constraints like Unique Key Constraint, Primary Key Constraints, etc which prevent Data Duplication.

Hence there is no chance of Data Inconsistency Problems.

Limitations of Databases
  1. Databases cannot hold a Huge Amount of Information like Terabytes of Data.
  2. The database can provide support only for Structured Data (Tabular Data OR Relational Data)
  3. The database cannot provide support for Semi-Structured Data (like XML Files) and
  4. Unstructured Data (like Video Files, Audio Files, Images, etc)
Overcoming the limitations

To overcome these problems, we should go for more Advanced Storage Areas like Big Data storage-related like HDFS (Hadoop), Data warehouses, etc.

Python Database Programming

Sometimes as the part of Programming requirement, we have to connect to the database programmatically and we have to perform several operations like,

  1. creating tables,
  2. inserting data,
  3. updating data,
  4. deleting data,
  5. selecting data etc.

The above operations in the database will be performed using SQL, a query language. In Python, we use some modules and send the queries/SQL commands to the database.

Python provides inbuilt support for several databases like

  1. Oracle,
  2. MySql,
  3. SqlServer,
  4. GadFly,
  5. SQLite, etc

The support python provides for each database is through different modules for each like cx_Oralce module for communicating with Oracle database, pymssql module for communicating with Microsoft SQL Server.

Standard steps for Python database Programming:
  1. import database-specific module
  2. Establish a Connection towards the database with credentials in a secured way.
  3. Create a Cursor object
  4. Use In-built methods to execute the SQL queries
  5. Commit or rollback
  6. Fetch the result from the Cursor
  7. Close the resources

Let’s see each step in detail

Step1: import database-specific module

We need to import specific database modules to work with the database. The modules can be imported using the import keyword. For example, if we are importing the cx_Orcale module, then

import cx_Oracle

Step2: Establish a connection

We can establish a connection between the python program and database by using the connect() method. If we call this connect() method then it returns a Connection object. For example,

con = cx_Oracle.connect(“username/password@systeminfo”)

We need to provide the credentials, username, and password, in order to establish a connection to the database.

Step3: Create a Cursor object:

After creating a connection object then the next step is to create a Cursor object. With the connection object in the previous step, we have to call the cursor() method, which returns the Cursor object. For example,

cursor=con.cursor()

Step4: Execute the SQL queries

To execute SQL queries we can use three predefined methods based on the requirement,
execute(“SQL query”): This method is used to execute a single SQL query
cursor.execute()

executescript(“SQL queries”): This method is used to execute SQL queries which are separated by semicolon.
cursor.executescript()

executemany(): This method is used to execute parameterized queries.
cursor.executemany(“SQL queries”)

Step5: commit or rollback

We can commit or rollback the changes based on requirements in case of DML queries like insert, update, and delete queries. We can call commit or rollback methods by using a connection object.

commit(): This method saves the changes into the database. This should be called using connection object
con.commit()

rollback(): This method rolls the temporary changes back. This should also be called using connection object
con.rollback()

Step6: Fetch the results from the Cursor object

We can fetch or get the result from the cursor object in case of select queries. While fetching the result we can use three types of methods based on the requirement.

fetchone(): This method is used to fetch one record. Internally this method returns one record as a tuple. If there are no more records, then it returns None
cursor.fetchone()

fetchmany(number of records): By using this method, we can fetch or get a number of records. This method accepts a number of records (integer value) to fetch and returns a tuple of tuples where each record itself is a tuple. If there are no more records, then it returns an empty tuple.
cursor.fetchmany(2)

Step7: Close the resources:

In the above steps, we have created a connection object and then a cursor object. It’s our responsibility to close these as a part of good programming practice.

Closing cursor: cursor.close()
Closing connection: con.close()

Summary:

The following is the list of important methods which can be used for python database programming

  1. connect()
  2. cursor()
  3. execute()
  4. executescript()
  5. executemany()
  6. commit()
  7. rollback()
  8. fetchone()
  9. fetchall()
  10. fetchmany(n)
  11. close()

Since these methods are common for all databases they won’t be change from database to database.

Working with Oracle Database:

Driver: Driver is an application that works as an interface or translator between a python program and database.

Translator: Translator is a program that helps to communicate to databases from a python program. The translator translates python calls into database-specific calls and database-specific calls into Python calls. This translator is nothing but Driver/Connector.

cx_Oracle: For the Oracle database the name of the driver needed is cx_Oracle. Here, the driver cx_Oracle is nothing but a python module that enables access to Oracle Database. It can be used for both Python2 and Python3. It can work with any version of the Oracle database like 9,10,11 and 12.

Installing cx_Oracle: The module is an external module and hence doesn’t come with python inbuilt packages. We need to install it using the command

pip install cx_Oracle

When you execute the above command, the console will show you the following:

Database Connectivity in Python

The prerequisite for the below programs or examples is you should have an oracle DB setup.

Program: Create employees table in the oracle database (demo1.py)
import cx_Oracle
try:
   con=cx_Oracle.connect('system/root@localhost')
   cursor=con.cursor()
   cursor.execute("create table employees(eno number, ename varchar2(10),esal number(10,2),eaddr varchar2(10))")
   print("Table created successfully")
except cx_Oracle.DatabaseError as e:
   if con:
       con.rollback()
       print(e)
finally:
   if cursor:
       cursor.close()
   if con:
       con.close()

Output: Create employees table in the oracle database

Confirmation:

Database Connectivity in Python

During the execution of the above code, if you face any issues, those are mostly related to the database. You can refer to https://www.performatune.com/en/python-oracle-connection-options/ for different approaches on the issues.

Program: Insert a single row in the employees table (demo2.py)
import cx_Oracle
try:
   con=cx_Oracle.connect('system/root@localhost')
   cursor=con.cursor()
   cursor.execute("insert into employees values(100,'Durga',1000,'Hyd')")
   con.commit()
   print("Record Inserted Successfully")
except cx_Oracle.DatabaseError as e:
   if con:
       con.rollback()
       print(e)
finally:
   if cursor:
       cursor.close()
   if con:
       con.close()

Output: Insert single row in the employees table

Confirmation:

Database Connectivity in Python

Program: Insert a single row in the employees table (demo3.py)
import cx_Oracle
try:
   con=cx_Oracle.connect('system/root@localhost')
   cursor=con.cursor()
   cursor.execute("insert into employees values(2,'Prasad',20000,'Banglore')")
   con.commit()
   print("Record Inserted Successfully")
except cx_Oracle.DatabaseError as e:
   if con:
       con.rollback()
       print(e)
finally:
   if cursor:
       cursor.close()
   if con:
       con.close()

Output: Insert single row in the employees table

Confirmation:

Database Connectivity in Python

While performing DML Operations (insert | update | delete), compulsory we have to use the commit() method then only the results will be reflected in the database.

Program: Insert multiple rows into the employees table by using executemany() method (demo4.py)
import cx_Oracle
try:
   con=cx_Oracle.connect('system/root@localhost')
   cursor=con.cursor()
   sql="insert into employees values(:eno, :ename, :esal, :eaddr)"
   records=[(3,'Hari',30000,'Mumbai'),(4,'Hema',40000,'BZA'),(5,'Mohan',50000,'Banglore')]
   cursor.executemany(sql, records)
   con.commit()
   print("Record Inserted Successfully")
except cx_Oracle.DatabaseError as e:
   if con:
       con.rollback()
       print(e)
finally:
   if cursor:
       cursor.close()
   if con:
       con.close()

Output: Insert multiple rows into employees table by using executemany() method

Confirmation:

Database Connectivity in Python

Program: Insert multiple rows in the employees table with dynamic input from the keyboard (demo5.py)
import cx_Oracle
try:
   con=cx_Oracle.connect('system/root@localhost')
   cursor=con.cursor()
   while True:
       eno=int(input("Enter Employee Number:"))
       ename=input("Enter Employee Name:")
       esal=float(input("Enter Employee Salary:"))
       eaddr=input("Enter Employee Address:")
       sql="insert into employees values(%d, '%s', %f, '%s')"
       cursor.execute(sql %(eno, ename, esal, eaddr))
       print("Record Inserted Successfully")
       option=input("Do you want to insert one more record[Yes| No] :")
       if option=="No":
           con.commit()
           break
except cx_Oracle.DatabaseError as e:
   if con:
       con.rollback()
       print(e)
finally:
   if cursor:
       cursor.close()
   if con:
       con.close()

Output:

Insert multiple rows in the employees table with dynamic input from the keyboard

Confirmation:

Database Connectivity in Python

In the above example, you have to give ‘No’ with the same case to exit from the code, because it’s handled in that way. You could also take as below, to handle irrespective of the case.

if option.lower()=="no":
           con.commit()
           break
Program: Increment all employee salaries by 2000 whose salary < 50000 (demo6.py)
import cx_Oracle
try:
   con=cx_Oracle.connect('system/root@localhost')
   cursor=con.cursor()
   increment=float(input("How much amount need to Increment:"))
   salrange=float(input("Enter Salary Range:"))
   sql="update employees set esal=esal+%f where esal<%f"
   cursor.execute(sql %(increment, salrange))
   print("Records Updated Successfully")
   con.commit()
except cx_Oracle.DatabaseError as e:
   if con:
       con.rollback()
       print(e)
finally:
   if cursor:
       cursor.close()
   if con:
       con.close()

Output:

Increment all employee salaries by 2000 whose salary < 50000

Confirmation

Database Connectivity in Python

Program: select all employees info by using fetchone() method (demo7.py)
import cx_Oracle
try:
   con=cx_Oracle.connect('system/root@localhost')
   cursor=con.cursor()
   cursor.execute("select * from employees")
   row=cursor.fetchone()
   while row is not None:
       print(row)
       row=cursor.fetchone()
except cx_Oracle.DatabaseError as e:
   if con:
       con.rollback()
       print(e)
finally:
   if cursor:
       cursor.close()
   if con:
       con.close()

Output:

select all employees info by using fetchone() method

Program: select all employees info by using fetchmany() method (demo8.py)
import cx_Oracle
try:
   con=cx_Oracle.connect('system/root@localhost')
   cursor=con.cursor()
   cursor.execute("select * from employees")
   n=int(input("Enter the number of required rows:"))
   data=cursor.fetchmany(n)
   for row in data:
       print(row)
except cx_Oracle.DatabaseError as e:
   if con:
       con.rollback()
       print(e)
finally:
   if cursor:
       cursor.close()
   if con:
       con.close()

Output:

select all employees info by using fetchmany() method

Program: select all employees info by using fetchall() method (demo9.py)
import cx_Oracle
try:
   con=cx_Oracle.connect('system/root@localhost')
   cursor=con.cursor()
   cursor.execute("select * from employees")
   data=cursor.fetchall()
   print(data)
except cx_Oracle.DatabaseError as e:
   if con:
       con.rollback()
       print(e)
finally:
   if cursor:
       cursor.close()
   if con:
       con.close()

Output:

select all employees info by using fetchall() method

Program: select a specific field from the record by index (demo10.py)
import cx_Oracle
try:
   con=cx_Oracle.connect('system/root@localhost')
   cursor=con.cursor()
   cursor.execute("select * from employees")
   data=cursor.fetchall()
   print(data[0][0])
   print(data[0][1])  
except cx_Oracle.DatabaseError as e:
   if con:
       con.rollback()
       print(e)
finally:
   if cursor:
       cursor.close()
   if con:
       con.close()

Output:

select a specific field from record by index

Here, in this article, I try to explain Database Connectivity in Python with Examples. I hope you enjoy this Database Connectivity in Python with Examples article. I would like to have your feedback. Please post your feedback, question, or comments about this Database Connectivity in Python with Examples article.

Leave a Reply

Your email address will not be published. Required fields are marked *