Executing Oracle SQL Query with multiple bind variables in Python

Question

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'

Christopher Jones · Accepted Answer · 2021-08-27 22:28:02Z

1

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')]

answered Aug 27, 2021 at 22:28

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Executing Oracle SQL Query with multiple bind variables in Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related