Oracle Database Connection in Python

Last Updated : 16 Aug, 2022

Sometimes as part of programming, we are required to work with databases because we want to store a huge amount of information so we use databases, such as Oracle, MySQL, etc. So In this article, we will discuss the connectivity of Oracle database using Python. This can be done through the module name cx_Oracle.

Oracle Database: For communicating with any database through our Python program we require some connector which is nothing but the cx_Oracle module.

For installing cx-Oracle :

If you are using Python >= 3.6 use the below command in Linux: –

pip install cx-Oracle

If you are using Python >= 3.6 use the below command in Windows: –

py -m pip install cx-Oracle

By this command, you can install cx-Oracle package but it is required to install Oracle database first on your PC.

Import database specific module
Ex. import cx_Oracle
connect(): Now Establish a connection between the Python program and Oracle database by using connect() function.

con = cx_Oracle.connect('username/password@localhost')

cursor(): To execute a SQL query and to provide results some special object is required that is nothing but cursor() object.

cursor = con.cursor()

execute/executemany method:

cursor.execute(sqlquery) - - - -> to execute a single query. 
cursor.executemany(sqlqueries) - - - -> to execute a single query with multiple bind variables/place holders.

commit(): For DML(Data Manipulation Language) queries that comprise operations like update, insert, delete. We need to commit() then only the result reflects in the database.
fetchone(), fetchmany(int), fetchall():
1. fetchone() : This method is used to fetch one single row from the top of the result set.
2. fetchmany(int): This method is used to fetch a limited number of rows based on the argument passed in it.
3. fetchall() : This method is used to fetch all rows from the result set.
close(): After all done it is mandatory to close all operations.

cursor.close()
con.close()

Execution of SQL statement:

1. Creation of table

Python3

# importing module
import cx_Oracle
 
# Create a table in Oracle database
try:
 
    con = cx_Oracle.connect('tiger/scott@localhost:1521/xe')
    print(con.version)
 
    # Now execute the sqlquery
    cursor = con.cursor()
 
    # Creating a table employee
    cursor.execute(
        "create table employee(empid integer primary key, name varchar2(30), salary number(10, 2))")
 
    print("Table Created successfully")
 
except cx_Oracle.DatabaseError as e:
    print("There is a problem with Oracle", e)
 
# by writing finally if any error occurs
# then also we can close the all database operation
finally:
    if cursor:
        cursor.close()
    if con:
        con.close()

Output:

Table Created successfully

DDL statements don’t require to be committed. They are automatically committed. In the above program, I have used execute() method to execute an SQL statement.

2. Inserting a record into table using execute() method

Python3

# importing module
import cx_Oracle
 
# Inserting a record into a table in Oracle database
try:
    con = cx_Oracle.connect('tiger/scott@localhost:1521/xe')
    cursor = con.cursor()
 
    #con.autocommit = True
    # Inserting a record into table employee
    cursor.execute('insert into employee values(10001,\'Rahul\',50000.50)')
 
    # commit() to make changes reflect in the database
    con.commit()
    print('Record inserted successfully')
 
except cx_Oracle.DatabaseError as e:
    print("There is a problem with Oracle", e)
 
# by writing finally if any error occurs
# then also we can close the all database operation
finally:
    if cursor:
        cursor.close()
    if con:
        con.close()

Output:

Record inserted successfully

Once we execute any DML statement it is required to commit the transaction. You can commit a transaction in 2 ways: –

con.commit(). This is used to commit a transaction manually.
con.autocommit = True. This is used to commit a transaction automatically.

3. Inserting multiple records into a table using executemany() method

Python3

import cx_Oracle
 
# Load data from a csv file into Oracle table using executemany
try:
    con = cx_Oracle.connect('tiger/scott@localhost:1521/xe')
 
except cx_Oracle.DatabaseError as er:
    print('There is an error in Oracle database:', er)
 
else:
    try:
        cur = con.cursor()
        data = [[10007, 'Vikram', 48000.0], [10008, 'Sunil', 65000.1], [10009, 'Sameer', 75000.0]]
 
        cur = con.cursor()
        # Inserting multiple records into employee table
        # (:1,:2,:3) are place holders. They pick data from a list supplied as argument
        cur.executemany('insert into employee values(:1,:2,:3)', data)
 
    except cx_Oracle.DatabaseError as er:
        print('There is an error in Oracle database:', er)
 
    except Exception as er:
        print(er)
 
    else:
        # To commit the transaction manually
        con.commit()
        print('Multiple records are inserted successfully')
 
finally:
    if cur:
        cur.close()
    if con:
        con.close()

Output:

Multiple records are inserted successfully

There might be times when it is required to execute a SQL statement multiple times based on the different values supplied to it each time. This can be achieved using executemany() method. We supply a list containing a list of values that will replace placeholders in a SQL query to be executed.

From the above case

:1 is substituted by value 10007
:2 is substituted by value ‘Vikram’
:3 is substituted by value 48000.0

And so on(next list of values in a given list)

Similarly, you can supply a list of dictionaries. But instead of placeholder, we will use the bind variable( discussed later).

4. View result set from a select query using fetchall(), fetchmany(int), fetchone()

Python3

import cx_Oracle
 
try:
    con = cx_Oracle.connect('tiger/scott@localhost:1521/xe')
 
except cx_Oracle.DatabaseError as er:
    print('There is an error in the Oracle database:', er)
 
else:
    try:
        cur = con.cursor()
 
        # fetchall() is used to fetch all records from result set
        cur.execute('select * from employee')
        rows = cur.fetchall()
        print(rows)
 
        # fetchmany(int) is used to fetch limited number of records from result set based on integer argument passed in it
        cur.execute('select * from employee')
        rows = cur.fetchmany(3)
        print(rows)
 
        # fetchone() is used fetch one record from top of the result set
        cur.execute('select * from employee')
        rows = cur.fetchone()
        print(rows)
 
    except cx_Oracle.DatabaseError as er:
        print('There is an error in the Oracle database:', er)
 
    except Exception as er:
        print('Error:'+str(er))
 
    finally:
        if cur:
            cur.close()
 
finally:
    if con:
        con.close()

Output:

[(10001, 'Rahul', 50000.5), (10002, 'Sanoj', 40000.75), (10003, 'Soumik', 30000.25), (10004, 'Sayan', 45000.0), (10005, 'Sobhan', 60000.1), (10006, 'Gururaj', 70000.0), (10007, 'Vikram', 48000.0), (10008, 'Sunil', 65000.1), (10009, 'Sameer', 75000.0)]
[(10001, 'Rahul', 50000.5), (10002, 'Sanoj', 40000.75), (10003, 'Soumik', 30000.25)]
(10001, 'Rahul', 50000.5)

In the above program, we have used 3 methods

fetchall() : The fetchall() is used to fetch all records from the result set.
fetchmany(int) : The fetchmany(int) is used to fetch the limited number of records from the result set based on the integer argument passed in it.
fetchone() : The fetchone() is used to fetch one record from the top of the result set.

5. View result set from a select query using bind variable

Python3

import cx_Oracle
 
try:
    con = cx_Oracle.connect('tiger/scott@localhost:1521/xe')
 
except cx_Oracle.DatabaseError as er:
    print('There is error in the Oracle database:', er)
 
else:
    try:
        cur = con.cursor()
 
        cur.execute('select * from employee where salary > :sal', {'sal': 50000})
        rows = cur.fetchall()
        print(rows)
 
    except cx_Oracle.DatabaseError as er:
        print('There is error in the Oracle database:', er)
 
    except Exception as er:
        print('Error:', er)
 
    finally:
        if cur:
            cur.close()
 
finally:
    if con:
        con.close()

Output:

[(10001, 'Rahul', 50000.5), (10005, 'Sobhan', 60000.1), (10006, 'Gururaj', 70000.0),
 (10008, 'Sunil', 65000.1), (10009, 'Sameer', 75000.0)]

In this case, I have passed a dictionary in execute() method. This dictionary contains the name of the bind variable as a key, and it’s corresponding value. When the SQL query is executed, value from the key is substituted in place of bind variable.