Passing a list of values from Python to the IN clause of an SQL query

Question

I am trying to pass a list like below to a sql query

x = ['1000000000164774783','1000000000253252111']

I am using sqlalchemy and pyodbc to connect to sql:

import pandas as pd
from pandas import Series,DataFrame
import pyodbc
import sqlalchemy

cnx=sqlalchemy.create_engine("mssql+pyodbc://Omnius:[email protected]:1433/Basis?driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0")

I tried using various string format functions to be used in the sql query. below is one of them

  xx = ', '.join(x)
  sql = "select * from Pretty_Txns where Send_Customer in (%s)" % xx
  df = pd.read_sql(sql,cnx)

All of them seem to convert it into a numeric format

(1000000000164774783, 1000000000253252111) instead of ('1000000000164774783','1000000000253252111')

And hence the query fails as datatype of Send_Customer is varchar(50) in SQL

 ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][SQL Server Native Client 11.0]
  [SQL Server]Error converting data type varchar to numeric. (8114) (SQLExecDirectW)') 
 [SQL: 'select * from Pretty_Txns where Send_Customer in (1000000000164774783, 1000000000253252111)']

Gord Thompson · Accepted Answer · 2022-03-10 22:45:36Z

17

As stated in the comment to the other answer, that approach can fail for a variety of reasons. What you really want to do is create an SQL statement with the required number of parameter placeholders and then use the params= parameter of .read_sql_query() to supply the values:

x = ['1000000000164774783','1000000000253252111']
placeholders = ','.join('?' for i in range(len(x)))  # '?,?'
sql = f"select * from Pretty_Txns where Send_Customer in ({placeholders})"
df = pd.read_sql_query(sql, cnx, params=x)

edited Mar 10, 2022 at 22:45

answered Feb 9, 2017 at 13:14

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

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

1 Comment

grantog Over a year ago

This WORKED! I've been trying to find the solution and was on several Stackoverflow pages, attempting their solutions but to no avail. Thanks @Gord

felipeformenti · Accepted Answer · 2021-06-09 15:39:13Z

3

Here's the SQL query you need

sql = f"select * from Pretty_Txns where Send_Customer in {tuple(x)}"
df = pd.read_sql(sql,cnx)

answered Jun 9, 2021 at 15:39

felipeformenti

1771 silver badge10 bronze badges

1 Comment

Requin Over a year ago

If x contains only one value, {tuple(x)} translates to "(my_val, )" and will cause the SQL query to fail

optimus_prime · Accepted Answer · 2017-02-08 21:50:49Z

0

Used the below approach and it worked fine:

       sql = "select * from Pretty_Txns where Send_Customer in %s" % str(tuple(x))
      df = pd.read_sql(sql,cnx)

answered Feb 8, 2017 at 21:50

optimus_prime

8272 gold badges14 silver badges35 bronze badges

3 Comments

univerio Over a year ago

Terrible idea. That's prone to SQL injection and str does not necessarily escape the values in the way that SQL expects (e.g. a unicode object in Python 2 will output u'whatever').

Mr. Lance E Sloan Over a year ago

Is the accepted answer really any safer to SQL injection attacks?

Gord Thompson Over a year ago

@Mr.LanceESloan - Yes, because the string formatting is only inserting the ?,?, … string which is under the complete control of our code. SQL injection issues arise when dynamic SQL uses user input to embed data values into the statement.

Ph03nIX · Accepted Answer · 2020-02-25 23:37:36Z

Making sqlalchemey, pyodbc to work with pandas read_sql() is a hairy and messy thing. After much frustration and bumping into various solutions and documentation from pandas and psycopg, here's the correct (so far) way to do a query with named parameter:

import pandas as pd
import psycopg2
# import pyodbc 
import sqlalchemy
from sqlalchemy import text # this is crucial

cnx=sqlalchemy.create_engine(...)
x = ['1000000000164774783','1000000000253252111']
sql = "select * from Pretty_Txns where Send_Customer in (:id);" # named parameter
df = pd.read_sql(text(sql), cnx, params={'id':x}) # note how `sql`
                                                   # string is cast with text() 
                                                   # and key-pair value is passed for 
                                                   # named parameter 'id'
df.head()

I've made it work with the PostgreSQL database. I hope it wouldn't be too much different for MySQL.

Collectives™ on Stack Overflow

Passing a list of values from Python to the IN clause of an SQL query

4 Answers 4

1 Comment

1 Comment

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related