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():
- fetchone() : This method is used to fetch one single row from the top of the result set.
- fetchmany(int): This method is used to fetch a limited number of rows based on the argument passed in it.
- 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 moduleimport cx_Oracle# Create a table in Oracle databasetry: 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 operationfinally: 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 moduleimport cx_Oracle# Inserting a record into a table in Oracle databasetry: 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 operationfinally: 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 executemanytry: 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_Oracletry: 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_Oracletry: 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.
