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 as
servicename. We will use
exceptto handle exceptions and document errors.
disconnect_node(). This function will run a disconnect from the Oracle database, closing the database and
cursor. If there is no connection, the function will ignore the exception.
execute_node(). Executes any SQL expressions passed to the function
commitif specified. Here, we will not highlight
fetchall(), because the SQL expression may not be
select. 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 function
commit, if specified.
import cx_Oracle import pandas as pd import json conf = json.loads(open('conf.json').read()) password_key=list(conf['dict'].keys()) 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
committo save the data after disconnecting from the database. Finally, we disconnect from the database using the function
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
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]) 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!