Not able to connect to a table using python cx_oracle

user_18606944 0
Viewed 49

I'm getting the mentioned error while I'm using a WHERE condition in cx_oracle. However, there is no error while I fetch all rows. Please find my code below. The input - cols is a list of all the columns I want to fetch. Similarly, I want to pass the where condition as variable too, wherein I'm passing on the values in a loop.

For testing, lets keep it static.

Thanks

 cols = [
        'ID',
        'CODE',
        'LOGINDEX_CODE',
        'IS_ACTIVE',
        'IS_PORT_GROUP'
        ]
table_name = 'PORT'

os.chdir(os.path.dirname(__file__))
main_dir = os.getcwd()

import cx_Oracle

try:
    cx_Oracle.init_oracle_client(lib_dir= main_dir + "\\instantclient_21_3\\")
except:
    pass

dsn_tns = cx_Oracle.makedsn(r'some_db', 1521, service_name=r'some_service')
conn = cx_Oracle.connect(user='abcdef', password=r'ghijkl', dsn=dsn_tns).cursor()

query_cols = ', '.join(cols)
named_params = {
                'varx'  :   query_cols,
                'vary'  :   'LEG',
                'varz'  :   242713
                }


sql_query = 'SELECT :varx FROM :vary WHERE START_PORT_ID IN :varz'
conn.prepare(sql_query)
conn.execute(sql_query, named_params)
cx-oracle sql python
1 Answers

Bind variables cannot be used to replace parts of the SQL statement itself. They can only be used to supply data that is sent to the database. So you would need to do something like this instead:

sql_query = f"select {', '.join(cols)} from LEG where start_port_id = :varz"
with conn.cursor() as cursor:
    for row in cursor.execute(sql_query, varz=242713):
        print(row)
user_25103470
Related