How to execute PL/SQL expressions and procedures in Python

• connect_node(). This function will begin communicating with the Oracle database by passing database details such as user, password, hostname, port и servicename. We will use try and except to 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())[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 committo 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!