Pandas read_sql_query with parameters for a string with no quotes

Question

I have want to insert a string of identifiers into a piece of sql code using

df = pd.read_sql_query(query, self.connection,params=sql_parameter)

my parameter dictionary looks like this

sql_parameter = {'itemids':itemids_str}

where itemids_str is a string like

282940499, 276686324, 2665846, 46875436, 530272885, 2590230, 557021480, 282937154, 46259344

The SQL code looks like

SELECT 
xxx, 
yyy, 
zzz
FROM tablexyz
where some_column_name in ( %(itemids)s )

My current code gets my the parameter inserted with its quotes

where some_column_name in ( '282940499, 276686324, 2665846, 46875436, 530272885, 2590230, 557021480, 282937154, 46259344' )

How can I prevent the string being inserted including the ', these are not part of my string, but I assume they come from the parameter type string when using %s

Well, itemids_str is a string or no? What you want to do instead is have one placeholder for each itemid. There are many SO questions and answers about how to do this. You can use * to build up a list of placeholders from the length of itemids_str. You would also then need to do itemids_str.split(',') to get that into a list. — mechanical_meat
– mechanical_meat, Commented Aug 18, 2021 at 12:45
Yes it is a string, I construct that string from the individual itemids and thought I can pass it as a single argument — ThatQuantDude
– ThatQuantDude, Commented Aug 18, 2021 at 12:48
If you prefer to do that -- which, please note, could be a potential security flaw -- you would need to use Python string formatting on your query before sending it to pd.read_sql_query(). — mechanical_meat
– mechanical_meat, Commented Aug 18, 2021 at 12:51

Amith Lakkakula · Accepted Answer · 2021-08-18 12:55:42Z

2

I don't think there is a provision in params to send a list of numeric values for one condition. I always add such condition directly to the query

item_ids = [str(item_id) for item_id in item_ids]

where_str = ','.join(item_ids)

query = f"""SELECT 
    xxx, 
    yyy, 
    zzz
    FROM tablexyz
    where some_column_name in ({where_str})"""

answered Aug 18, 2021 at 12:55

Amith Lakkakula

5163 silver badges8 bronze badges

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

2 Comments

ThatQuantDude Over a year ago

thanks, would this also work if the query would be outside the code in a separate .sql file ?

Amith Lakkakula Over a year ago

You can use to string.Template to load the query from file. $ is used for the placeholders. Do check the documentation, this should work even if the query is in a file. stackoverflow.com/a/68832412/14292065

Collectives™ on Stack Overflow

Pandas read_sql_query with parameters for a string with no quotes

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related