How to pass params in jaydebeapi .execute?

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?

Contents hide

Answers:

Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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)

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating