Python sqlalchemy query with quote in between

Question

I'm trying to use python to query oracle database with SQLAlchemy. I am able to get data with a normal query, but it always returns an empty DataFrame when the where clause contains quote signs, such as condition below: A in ('a','b','c','d').

engine = create_engine(...)
query = '''select * from table where ( A in (:A_Value) and BIN (:B_Value))'''
df= pd.read_sql_query(query , con=engine, params = {"A_Value": A_value, "B_Value" : B_value})

A_value is the value of a dataframe. I tried to form a comma-separated string with quote as A_value = " 'a', 'b', 'c', 'd' "

I am wondering where done wrongly?

user2722968 · Accepted Answer · 2018-05-06 13:04:53Z

1

The parameters are interpreted as a single value and no row matches to IN ("'a', 'b', ...").

What you have to do is to construct the IN-clause with the appropriate number of replacement-markers and then pass this number of parameters. For example "IN (" + ','.join(['?']*len(A_Value)) + ")", which gives you "IN (?, ?, ?)" if A_Value has three values.

answered May 6, 2018 at 13:04

user2722968

17.4k5 gold badges58 silver badges77 bronze badges

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

1 Comment

Fiona Over a year ago

Yes, you are right. I tried to add ', '.join(['%s']*len(A_Value)) in, and flatten the params into tuple combined with B_value. Which give me error "an illegal variable name/number", seems like still need to pass a variable name to each value.

Collectives™ on Stack Overflow

Python sqlalchemy query with quote in between

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related