In the previous blog post, I detailed how to execute sql statement on an Oracle database with Python. (See here for more details: https://laurent-leturgez.com/2018/08/30/executing-a-sql-statement-on-oracle-with-python/)

As you have seen, I used two kind of data structure to get the results: numpy array, and pandas array and for each, it’s possible to get the results in these structures and filter it directly in the Python script. The problem is that it will cause some issues regarding the dataset size in memory, network roundtrips etc.

So, as usual it’s always better to use Oracle capacity to filter data, joins them (in case of tables that resides in the Oracle database). And if you want to reduce parsing inside Oracle, you will probably want to use bind variables inside your SQL Statements … so how do we do this with python?

That will mostly depend on your result set data structure (numpy or pandas).

Just before we start the details, I consider that you are now able to get a Connection from an Oracle Database using cx_Oracle package (if not … see: https://laurent-leturgez.com/2018/07/31/connecting-python-to-an-oracle-database/).

Using numpy

For this purpose, we will have to declare an associative array of bind variables.

For example, the code below will declare, in an associative array “p” the bind variable named “param” and its value “log_archive_dest_1”  :

p = {'param': "log_archive_dest_1"}

Of course, in your statement declaration, you have to name your bind variable the same as declared in your associative array (in our case, param). If you have many bind variables in the same statement, let’s say p1 and p2, you will have to declare your associative array as follows:

p = {'p1': "log_archive_dest_1", 'p2': "log_archive_dest_2"}

Once declared, you just have to execute the statement and associate the array as parameters:

from __future__ import print_function

import cx_Oracle
import os
import numpy as np
import pandas as pd


oh="D:/tools/Oracle/instantclient_12_2_x8664"
os.environ["ORACLE_HOME"]=oh
os.environ["PATH"]=oh+os.pathsep+os.environ["PATH"]
os.environ["NLS_LANG"]="AMERICAN_AMERICA.AL32UTF8"

.../...

def executeStmt(conn, stmt, parameters):
    if conn is not None and isinstance (conn, cx_Oracle.Connection):
        cur = conn.cursor()
        if parameters is None:
            cur.execute (stmt)
        else:
            cur.execute(stmt,parameters)
    return cur

.../...

if __name__ == '__main__':
    c=cx_Oracle.Connection
    stmt="select name,value from v$parameter where name = :param"
    try:
        c=connectToOracle("192.168.99.2:1521/orcl","sys","oracle",mode=cx_Oracle.SYSDBA)
        p = {'param': "log_archive_dest_1"}
        
        print (">>>>>>    NUMPY STYLE")
        curs=executeStmt(c,stmt,p)
        if curs.rowcount!=0:
            curs.scroll(value=0)
        r = curs.fetchall()
        print("type(r) = ",type(r))
        n = np.array (r)
        print("type(n) =",type(n))
        print("n=", n)

        curs.close()
    except cx_Oracle.DatabaseError as ex:
        err, =ex.args
        print("Error code    = ",err.code)
        print("Error Message = ",err.message)
        os._exit(1)
    c.close()

Please notice that I converted the list return by fetchall call to a numpy array. If you prefer to use list, you can work directly on it without converting it to a numpy array.

This piece of code will produce the results below:

>>>>>>    NUMPY STYLE
type(r) =  <type 'list'>
type(n) = <type 'numpy.ndarray'>
n= [['log_archive_dest_1' None]]

Using Pandas

Using pandas dataframe is a bit different and, as mentioned in the previous post, I prefer to use pandas dataframes when dealing with a rdbms (like Oracle). Why? because of its capabilities (filtering, joining etc.)

Ok, but now, how do we deal with SQL Statement and bind variable with python and pandas dataframes ?

The principle is nearly the same as sql statement without bind variables.

You have to call read_sql function, and pass the same associative array as parameters … and that’s it 🙂 … See below

from __future__ import print_function

import cx_Oracle
import os
import numpy as np
import pandas as pd


oh="D:/tools/Oracle/instantclient_12_2_x8664"
os.environ["ORACLE_HOME"]=oh
os.environ["PATH"]=oh+os.pathsep+os.environ["PATH"]
os.environ["NLS_LANG"]="AMERICAN_AMERICA.AL32UTF8"

.../...

if __name__ == '__main__':
    c=cx_Oracle.Connection
    stmt="select name,value from v$parameter where name = :param"
    try:
        c=connectToOracle("192.168.99.2:1521/orcl","sys","oracle",mode=cx_Oracle.SYSDBA)
        p = {'param': "log_archive_dest_1"}

        print(">>>>>>    PANDAS STYLE")
        dataframe=pd.read_sql(stmt,con=c,params=p)
        print("type(dataframe)",type(dataframe))
        print(dataframe)

        # Panda conversion to numpy is always possible.
        # n=dataframe.values
        curs.close()
    except cx_Oracle.DatabaseError as ex:
        err, =ex.args
        print("Error code    = ",err.code)
        print("Error Message = ",err.message)
        os._exit(1)
    c.close()

That will produce the same kind of results:

>>>>>>    PANDAS STYLE
type(dataframe) <class 'pandas.core.frame.DataFrame'>
                 NAME VALUE
0  log_archive_dest_1  None

On the database side

On the database side, you will find this kind of statement on the shared pool:

SQL> select sql_id,sql_fulltext,executions
  2  from v$sql
  3  where sql_text like 'select name,value from v%';

SQL_ID        SQL_FULLTEXT                                                                     EXECUTIONS
------------- -------------------------------------------------------------------------------- ----------
4zqq6y8cdbqmu select name,value from v$parameter where name = :param                                   17

You can see the bind variable name can be found in the statement and not an anonymous :1 or :B1 😉

In a next blogpost, I will show how to call a PLSQL stored procedure / function / package from Python.

Source code is available on my gitlab: https://gitlab.com/lolo115/python/tree/master/oracle

That’s it for today 😉