How to pass params in jaydebeapi .execute?

Question

I want to select data from oracle DB using python and jaydebeapi. I'm trying to get data using next script:

bank_accounts = """
    SELECT ACCOUNT_NUM, VALID_TO, CLIENT, CREATE_DT, UPDATE_DT  
    FROM ACCOUNTS where client = ?"""

clients = ['6948','3603','3919']
curs.execute(bank_accounts, clients[0])

But as result I'm getting an error:

java.sql.SQLException: java.sql.SQLException: Invalid column index

What can be wrong with it? How to pass params to .execute?

Answer 1

In Python's DB-API specification, PEP 249, for cursor.execute, it specifies the parameters argument which jaydebeapi may adhere to:

Parameters may be provided as sequence or mapping and will be bound to variables in the operation.

Therefore, instead of the scalar as you attempt sliced from first item in list, consider passing the single value within a sequence such as a tuple or list:

# param as one-item tuple
curs.execute(bank_accounts, (clients[0],))

# params as one-item list
curs.execute(bank_accounts, [clients[0]])

Alternatively, had you used an IN operator for multiple clients, pass your list directly:

bank_accounts = (
    "SELECT ACCOUNT_NUM, VALID_TO, CLIENT, CREATE_DT, UPDATE_DT "
    "FROM ACCOUNTS "
    "WHERE client IN (?, ?, ?)"
)

clients = ['6948','3603','3919']
curs.execute(bank_accounts, clients)