How to pass values in dictionary to sql query in python?

Question

I have a dictionary, where I have stored 1000s of Ids and in a set i have few dates.

deliveryDict={} //-->Contains 1000s of IDs, need to be passed in sql to query the DB
timestamp_set = set([]) //-->contains dates

My sql query is like;

    sqlstatement = 'SELECT * FROM [dbo].[ObservationData]  as obs
     where obs.TankSystemId in< IDs from dictionary> and obs.TimeStamp in <date from set>'

 cursor.execute(sqlstatement)

How can I pass this to my sql query? I expect my query would be like;

 sqlstatement = 'SELECT * FROM [dbo].[ObservationData]  as obs
         where obs.TankSystemId in (1,2,3,4,5...) and obs.TimeStamp in ('2019-01-01','2019-01-02') '

so my output would be; table of records

It would be good if you could include sample input and expected output. — Abdul Niyas P M, Jun 28 '19 at 04:44
Possible duplicate of [python list in sql query as parameter](https://stackoverflow.com/questions/283645/python-list-in-sql-query-as-parameter) — Zhenhir, Jun 28 '19 at 04:55
@Ratha do you really want to pass all the 1000s of ids into a WHERE clause for a single query? Or do you want to pass one ID per query? — gregory, Jun 28 '19 at 04:56
@gregory all ids at once, because i need to update 1000s of records in DB. SO wanted to query DB first with all Ids and do update once rater update one by one. — Ratha, Jun 28 '19 at 04:57
@Ratha, ah..that seems awfully expensive and perhaps infeasible: https://stackoverflow.com/questions/1013797/is-sql-in-bad-for-performance — gregory, Jun 28 '19 at 05:02

hsen · Answer 1 · 2019-06-28T09:21:22.210

0

Include variables as follows:

sqlstatement = 'SELECT * FROM [dbo].[ObservationData]  as obs
         where obs.TankSystemId in '+ str(deliveryDict) +' and obs.TimeStamp in '+ str(timestamp_set)

If you want to pass values individually, query from within a for loop.

edited Jun 28 '19 at 09:21

answered Jun 28 '19 at 05:06

hsen

11
3

How to pass values in dictionary to sql query in python?

1 Answers1