passing list to sql in python

Question

I am trying to pass a list to sql. From what I found, there is no bindtype for a list. So, I tried converting the list to a string : Here is the sql I am trying to execute :

select a,b,sum(c) as total from t where d in ('x','y') group by 1,2

AS I had to parametrize it , I used the below:

import pandas as pd
import pyodbc
#conn_vertica -- connection object to vertica
f_list = ['x','y']
sql_test=   select a,b,sum(c) as total from t where d in (%s) group by 1,2
l = ', '.join(map(lambda x: '%s',f_list))
sql_test = sql_test % l
df_test = pd.read_sql(sql_test,conn_vertica) # to read it into a dataframe

On running this I get an error :

pandas.io.sql.DatabaseError: Execution failed on sql 'select a, b, sum(c) as total from t where d in (%s, %s)  group by 1,2': ('42601', '[42601] ERROR 4856:  Syntax error at or near "%" at character 123\n (4856) (SQLExecDirectW)')

Any suggestions on how to pass a list into sql

On printing sql_test after the line sql_test = sql_test % l , this is the output : select a,b,sum(c) as total from t where d in (%s, %s) group by 1,2 — Data Enthusiast
– Data Enthusiast, Commented Oct 7, 2015 at 20:08

Alexander · Accepted Answer · 2015-10-07 20:09:19Z

5

You can pass it as a tuple:

sql_test = "SELECT a, b, SUM(c) AS total FROM t WHERE d IN {0} GROUP BY 1, 2".format(tuple(f_list))

>>> sql_test
"SELECT a, b, SUM(c) AS total FROM t WHERE d IN ('x', 'y') GROUP BY 1, 2"

answered Oct 7, 2015 at 20:09

Alexander

111k32 gold badges212 silver badges208 bronze badges

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

7 Comments

Data Enthusiast Over a year ago

Thanks @Alexander, it worked. Are there any other ways to do this? Was my approach wrong?

Alexander Over a year ago

There are generally several ways to accomplish the same task, although "There should be one-- and preferably only one --obvious way to do it." I am not familiar enough with pyodbc to comment on your method.

Data Enthusiast Over a year ago

:okay. In my query, I need to pass multiple parameters. So, for the first parameter I used {0} and .format(tupe(list)) . For the second parameter I tried using {1} ( this number indicates the position of the parameter) : Query looks like this : sql_test = "SELECT a, b, SUM(c) AS total FROM t WHERE d IN {0} and e = to_date({1},'YYYY-mm-dd') GROUP BY 1, 2".format(tuple(f_list),historical_date) historical_date = '2015-05-01' -- this was initialized at the beginning. I am getting an error that , there is no method to_date(int,unknown) is this usage wrong

Alexander Over a year ago

...and e = {1}...format(tuple(f_list), historical_date) might work. If not, it is a pyodbc issue.

Data Enthusiast Over a year ago

I tried that...when I printed the sql ..this is how it looked . e = 2015-05-01 .........and error : was explict type cast might be required ...I tried using str(historical_start_date) but it still printed without quotes..is there a way to print it as a string or may be append quotes to it ...i.e e = '2015-05-01'

|

Collectives™ on Stack Overflow

passing list to sql in python

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related