How to dynamically pass values to the Oracle bind variable using Python (Using cx_oracle module)

Question

I need to get values a from client dynamically(using input function) and I need to pass that value to Oracle bind variable. I am using SQL queries.

Answer 1

Here's an example:

import cx_Oracle as oracledb
import os
import platform

if platform.system() == "Darwin":
    oracledb.init_oracle_client(lib_dir=os.environ.get("HOME")+"/Downloads/instantclient_19_8")
elif platform.system() == "Windows":
     oracledb.init_oracle_client(lib_dir=r"C:\oracle\instantclient_19_14")

username = os.environ.get("PYTHON_USERNAME")
password = os.environ.get("PYTHON_PASSWORD")
connect_string = os.environ.get("PYTHON_CONNECTSTRING")

connection = oracledb.connect(user=username, password=password, dsn=connect_string)

e = input('Enter employee number: ')

with connection.cursor() as cursor:
    try:

        sql = """select * from emp where empno = :bv"""
        for r in cursor.execute(sql, {"bv": e}):
            print(r)

    except oracledb.Error as e:
        error, = e.args
        print(sql)
        print('*'.rjust(error.offset+1, ' '))
        print(error.message)

Running it gives:

$ python so27.py 
Enter employee number: 7369
(7369, 'SMITH', 'CLERK', 7902, datetime.datetime(1980, 12, 17, 0, 0), 800.0, None, 20)