CODEWITHSUNDEEP
python-3.xoracle-databaseoracle12ccx-oracle
Praveen Mishra
Praveen Mishra

Reputation: 151

Executing Oracle SQL Query with multiple bind variables in Python

I have scenario where my SQL query with multiple bind variables (1 repeats multiple times in the same query) needs to be executed with python cx_Oracle db connection.

the query somewhat looks like below:

select * from tablea where colA=:cola and colB=:colb and colc=:cola and and cold=:cola

when I execute in the connection query cx_Oracle cursor in python, I am not getting expected results and suspect that variable binding is not happening properly.

Below is the python code:

data=cur.execute(qryStr,dict(colb='value1',cola= 'value2')).fetchall()

is this fine?

Would this be treated as follows:

select * from tablea where colA='value2' and colB='value1' and colc='value2' and and cold='value2'

Upvotes: 1

Views: 998

Answers (1)

Christopher Jones
Christopher Jones

Reputation: 10721

Yes.

With this schema:

drop table mytable;
create table mytable (cola varchar2(20), colb varchar2(20), colc varchar2(20), cold varchar2(20));
insert into mytable values ('value2', 'value1', 'value2', 'value2');
commit;

This code:

with connection.cursor() as cursor:
    qryStr = """select * from mytable where colA=:cola and colB=:colb and colc=:cola and cold=:cola"""
    data=cursor.execute(qryStr,dict(colb='value1',cola= 'value2')).fetchall()
    print(data)

gives:

[('value2', 'value1', 'value2', 'value2')]

Upvotes: 1

Related Questions