In this tutorial we will use cx_Oracle – an extension module in Python that includes database access Oracle. Use Scenario: Here we will cover how to execute PL/SQL procedures and expressions in Python. This example shows how to execute a procedure that updates a table (e.g. table1), and how to query that table from Python and convert it to a dataframe pandas. First, let’s create a main class called Oracle()
, which has numerous functions, as shown below.
connect_node()
. This function will begin communicating with the Oracle database by passing database details such asuser
,password
,hostname
,port
иservicename
. We will usetry
andexcept
to handle exceptions and document errors.disconnect_node()
. This function will run a disconnect from the Oracle database, closing the database andcursor
. If there is no connection, the function will ignore the exception.execute_node()
. Executes any SQL expressions passed to the functioncommit
if specified. Here, we will not highlightfetchall()
, because the SQL expression may not beselect
. It is better to take care of this separately when creating the dataframe from the SQL table.execute_proc_node()
. Executes any SQL procedure passed to the functioncommit
, if specified.
import cx_Oracle
import pandas as pd
import json
conf = json.loads(open('conf.json').read())
password_key=list(conf['dict'].keys())[0]
password=conf['dict'][password_key]
class Oracle():
def __init__(self):
print('Database Connection')
def connect_node(self, username=user, password=password, hostname=hostname, port=port, servicename=servicename):
""" Connect to the database. """
try:
self.db = cx_Oracle.connect(username, password
, hostname + ':' + port + '/' + servicename)
except cx_Oracle.DatabaseError as e:
# Logs an error if necessary
raise
# If the connection to the database is successful, create a cursor,
# that we're going to use.
self.cursor = self.db.cursor()
def disconnect_node(self):
"""
Disconnect from the database. If this fails, for instance
if the connection instance doesn't exist, ignore the exception.
"""
try:
self.cursor.close()
self.db.close()
except cx_Oracle.DatabaseError:
pass
def execute_node(self, sql, commit=False):
"""
Execute whatever SQL statements are passed to the method;
commit if specified. Do not specify fetchall() in here as
the SQL statement may not be a select.
"""
try:
self.cursor.execute(sql)
except cx_Oracle.DatabaseError as e:
# Logs an error if necessary
raise
# Commit is executed only if needed
if commit:
self.db.commit()
def execute_proc_node(self, sql, commit=False):
"""
Execute whatever SQL procedures are passed to the method;
commit if specified.
"""
try:
self.cursor.callproc(sql)
except cx_Oracle.DatabaseError as e:
# Logs an error if necessary
raise
# Commit is executed only if needed
if commit:
self.db.commit()
Importing a module oracle_connection
. It is necessary to execute a function from the module called execute_proc_node
and pass the name of the procedure, which will be stored in the variable query
. In this procedure we update the table (e.g. table1). Thus, we pass true
for commit
to save the data after disconnecting from the database. Finally, we disconnect from the database using the function disconnect_node
.
import oracle_connection as oracle_connect
query = 'procedure_name'
# parsing data
orc = oracle_connect.Oracle()
orc.connect_node()
try:
orc.execute_proc_node(sql=query, commit=True)
finally:
orc.disconnect_node()
Let’s show another example in the same Python document. Let’s execute an SQL expression and extract (fetchall()
) of the raw data, and then convert everything into a pandas dataframe.We also import the oracle_connection
. We execute a method from the module called execute_node
and pass the SQL expression, which is stored in the query variable. We save the raw data in a variable data
and loop through all columns. We save it all to a list col_names
. Next we need to convert the data to a pandas dataframe and disconnect from the database. At the end, let’s name the columns by passing the list col_names
в dataset.columns
and renaming the columns.
import oracle_connection as oracle_connect
import pandas as pd
query = 'select * from schema.table_name'
# parsing data
orc = Oracle()
orc.connect_node()
try:
orc.execute_node(sql=query, commit=False)
data = orc.cursor.fetchall()
col_names= []
for i in range(0, len(orc.cursor.description)):
col_names.append(orc.cursor.description[i][0])
dataset = pd.DataFrame(data)
finally:
orc.disconnect_node()
dataset.columns = col_names
dataset.head()
We hope you found this article useful. Thank you for reading it!