CODEWITHSUNDEEP
pythonsqlamazon-redshift
user3490622
user3490622

Reputation: 1001

Read multiple lists from python into an SQL query

I have 3 lists of user id's and time ranges (different for each user id) for which I would like to extract data. I am querying an AWS redshift database through Python. Normally, with one list, I'd do something like this:

sql_query = "select userid from some_table where userid in {}".format(list_of_users)

where list of users is the list of user id's I want - say (1,2,3...)

This works fine, but now I need to somehow pass it along a triplet of (userid, lower time bound, upper time bound). So for example ((1,'2018-01-01','2018-01-14'),(2,'2018-12-23','2018-12-25'),...

I tried various versions of this basic query

sql_query = "select userid from some_table where userid in {} and date between {} and {}".format(list_of_users, list_of_dates_lower_bound, list_of_dates_upper_bound)

but no matter how I structure the lists in format(), it doesn't work. I am not sure this is even possible this way or if I should just loop over my lists and call the query repeatedly for each triplet?

Upvotes: 0

Views: 738

Answers (2)

Steven
Steven

Reputation: 76

suppose the list of values are something like following:

list_of_users = [1,2], 
list_of_dates_lower_bound = ['2018-01-01', '2018-12-23']
list_of_dates_lower_bound = ['2018-01-14', '2018-12-25']

the formatted sql would be:

select userid from some_table where userid in [1,2] and date between ['2018-01-01', '2018-12-23'] and ['2018-01-14', '2018-12-25']

This result should not be what you thought as is, it's just an invalid sql, the operand of between should be scalar value.

I suggest loop over the lists, and pass a single value to the placeholder.

Upvotes: 1

python_starter
python_starter

Reputation: 1561

You can select within a particular range by using

select col from table where col between range and range;

In your case it may be

select userid from some_table where date_from between yesterday and today;

or even

select userid from some_table where date_from >= yesterday and date_from <= today;

Upvotes: 0

Related Questions