How to pass tuple in read_sql 'where in' clause in pandas python

Question

I am passing a tuple converted to a string in a read_sql method as

sql = "select * from table1 where col1 in " + str(tuple1) + " and col2 in " + str(tuple2)

df = pd.read_sql(sql, conn)

This is working fine but, when tuple have only one value sql fails with

ORA-00936: missing expression

as single element tuple has an extra comma

For example

tuple1 = (4011,)
tuple2 = (23,24)

SQL formed is as

select * from table1 where col1 in (4011,) + " and col2 in (23,24)
                                        ^

ORA-00936: missing expression

Is there any better way doing this, other than removal of comma with string operations?

Is there a better way to parametrize read_sql function?

greg_data · Accepted Answer · 2017-09-05 10:31:46Z

3

the reason you're getting the error is because of SQL syntax.

When you have a WHERE col in (...) list, a trailing comma will cause a syntax error.

Either way, putting values into SQL statements using string concatenation is frowned upon, and will ultimately lead you to more problems down the line.

Most Python SQL libraries will allow for parameterised queries. Without knowing which library you're using to connect, I can't link exact documentation, but the principle is the same for psycopg2:

http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries

This functionality is also exposed in pd.read_sql, so to acheive what you want safely, you would do this:

sql = "select * from table1 where col1 in %s and col2 in %s"

df = pd.read_sql(sql, conn, params = [tuple1, tuple2])

answered Sep 5, 2017 at 10:31

greg_data

2,29414 silver badges20 bronze badges

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

2 Comments

pratish_v Over a year ago

good way, thanks but still gttig error with this as -pandas.io.sql.DatabaseError: Execution failed on sql : Variable_TypeByValue(): unhandled data type tuple

greg_data Over a year ago

I've worked out from your comment that you're probably using cx_oracle. I can't find anything in the docs about why a tuple wouldn't work, cx-oracle.readthedocs.io/en/latest/cursor.html#Cursor.execute says that a sequence is fine. Perhaps just try converting each tuple to a list first?

Clíodhna · Accepted Answer · 2018-01-08 17:21:12Z

3

There might be a better way to do it but I would add an if statement around making the query and would use .format() instead of + to parameterise the query.

Possible if statement:

if len(tuple1) < 2:
    tuple1 = tuple1[0]

This will vary based on what your input is. If you have a list of tuples you can do this:

tuples = [(4011,), (23, 24)]
new_t = []
for t in tuples:
    if len(t) == 2:
         new_t.append(t)
    elif len(t) == 1:
         new_t.append(t[0])

Ouput:

[4011, (23, 24)]

Better way of parameterising querys using .format():

sql = "select * from table1 where col1 in {} and col2 in {}".format(str(tuple1), str(tuple2))

Hope this helps!

edited Jan 8, 2018 at 17:21

answered Sep 4, 2017 at 15:17

Clíodhna

8281 gold badge15 silver badges30 bronze badges

1 Comment

pratish_v Over a year ago

too much code to handle too little thing, something better I need for this , below answer from @greg_data is good , have a look but still facing error with that

Ashish Kumar · Accepted Answer · 2019-05-31 16:03:05Z

-1

select * from table_name where 1=1 and (column_a, column_b) not in ((28,1),(25,1))

answered May 31, 2019 at 16:03

Ashish Kumar

71 bronze badge

Collectives™ on Stack Overflow

How to pass tuple in read_sql 'where in' clause in pandas python

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related