How to execute query in Python when list in SQL query as parameter

Question

I have a list with three elements in it and I want to iterate over the list to execute query one after another element in the list. For example,

zipcode_list = ['10000', '10018', '11201'] 
sql = "SELECT name, zipcode, state FROM survey WHERE zipcode IN (%s)"

I want to execute query "SELECT name, zipcode, state FROM survey WHERE zipcode IN 1000"first, and then "SELECT name, zipcode, state FROM survey WHERE zipcode IN 10018" and last query "SELECT name, zipcode, state FROM survey WHERE zipcode IN 11201"

I also want to put the retrieved data in 3 separate dataframes. Here's how I executed the query,

zipcode_list = ['10000', '10018', '11201']
sql = "SELECT name, zipcode, state FROM survey WHERE zipcode IN (%s)"    
in_p = ', '.join(list(map(lambda x: '%s', zipcode_list)))
sql = sql % in_p
df = cursor.execute(sql, zipcode_list).fetchall()
for dfs in df:
    df = pd.DataFrame(df)

You need to `SELECT name, zipcode, state FROM survey WHERE zipcode IN (10000, 10018, 11201)` and pick up on every row returned... Why would you need to separate the query into three? — casraf, Apr 18 '17 at 15:33
@casraf Because separating the query I won't need to subset the data by zipcode. — Peggy, Apr 18 '17 at 15:43

score 0 · Accepted Answer · answered Apr 18 '17 at 17:21

0

Consider pandas.read_sql to directly query a database without need of a cursor. Also, try saving three dataframes in a list or dictionary.

import pandas as pd

conn = ...              # DB CONNECTION OBJECT

zipcode_list = ['10000', '10018', '11201']  
sql = "SELECT name, zipcode, state FROM survey WHERE zipcode = %s"  

dfList = []
for z in zipcode_list:
   dfList.append(pd.read_sql(sql, conn, params=[z]))

dfDict = {}
for z in zipcode_list:
   dfDict[z] = pd.read_sql(sql, conn, params=[z])

conn.close()

answered Apr 18 '17 at 17:21

Parfait

104,375
17
94
125

Thanks a lot. May I know is it possible to retrieve data one query after another and store the data in separate dictionaries instead of querying all the data at once and store them in one dictionary. – Peggy Apr 19 '17 at 15:43
All data is not queried all at once but in a loop. Also, why separate dictionaries especially for similarly structured datasets? This will flood your global environment with maintenance needs especially since you need to assign by string if using in loop. See [pythonic way](http://stackoverflow.com/a/2321633/1422451): collection vs separate objects. – Parfait Apr 19 '17 at 15:47
In the above solution, you can easily reference df element in dictionary by zipcode key (e.g., `dfDict['10000']` would be the dataframe for the *10000* zipcode). Any pandas method can work off it `dfDict['10000'].groupby()`, `dfDict['10000'].pivot_table()`, `dfDict['10000'].merge()`, etc. – Parfait Apr 19 '17 at 15:48

How to execute query in Python when list in SQL query as parameter

1 Answers1