1

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?

Improve this question

1 Answer 1

Reset to default
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.

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

1 Comment

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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.