python sql query where timestamp in list

Question

I have the following query in python, which I use via pd.read_sql_query. It works fine. I have a list of dates, called dates, that I use for the query.

for date i in dates:
     query = ('SELECT * FROM table WHERE id in'+ str(tuple(ids)) + ' AND date 
     between '+"'"+str(i)+"'"+' and '+"'"+str(i)+"'")

Is there an easier way to query the dates, such as the ids? I would like to have something like this:

query = ('SELECT * FROM table WHERE id in'+ str(tuple(ids)) + ' AND date in' + str(tuple(dates)) + "'")

However, this does not work because dates are saved as timestamp in my python list as well as in the sql database.

my sql data looks the following:

id  date                 variable 
 A  20180101 00:00:00       1
 B  20180101 00:00:00       2
 B  20180501 00:00:00       3
 C  20180201 00:00:00       4

I have a list of ids formatted as strings:

["B", "C"]

And a list of dates, formated as datetime64[ns]:

[20180101 00:00:00, 20180201 00:00:00]

The desired output is:

id  date                 variable 
 B  20180101 00:00:00       2
 C  20180201 00:00:00       4

Could you provide an example of how 'dates' look like? In general, I am looking for sample input and output. — MaLiN2223
– MaLiN2223, Commented Sep 14, 2018 at 20:04

MaLiN2223 · Accepted Answer · 2018-09-14 20:24:52Z

2

There is many things that you can do but in my opinion this would be the most readable:

def join(l):
    return ",".join([f"'{x}'" for x in l])
ids_list = join(["B","C"])
dates_list = join(["20180101 00:00:00", "20180201 00:00:00"])

# below works for python 3.6+
print(f"SELECT * FROM table WHERE id in ({ids_list}) and date in ({dates_list})")

# below works for python < 3.6 (you can also use .format())
print("SELECT * FROM table WHERE id in %(ids_list)s and date in %(dates_list)s" % locals())

answered Sep 14, 2018 at 20:24

MaLiN2223

1,2983 gold badges19 silver badges41 bronze badges

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

2 Comments

MaLiN2223 Over a year ago

@FriedrichFranz could you specify which python version are you using? I will change my answer accordingly.

freddy888 Over a year ago

My bad, I didnt carry the f before the string because I was unaware of itsfunction. For future referece: it worked perfectly with python 3.6.6.

Collectives™ on Stack Overflow

python sql query where timestamp in list

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related