Looping over a list to create multiple data frames from SQL queries

Question

I've got an SQLite query I want to run multiple times to create multiple Pandas data frames based on the year of interest. Something like this (but this definitely doesn't work). Basically I'm trying to loop the data frame creation over the year list to create the 4 data frames (1 for each year) and I'm now stuck at how to do this even after quite a bit of Googling.

year = [2018, 2019, 2020, 2021]

query = '''
SELECT 
    some stuff
FROM table
WHERE table.YEAR = ?
'''
for x in year:
  df[x] = pd.read_sql_query(query, db, params=[x])

Vishal A. · Accepted Answer · 2021-12-17 07:36:57Z

1

It is a bad idea to create a new data frame in every iteration of the for loop. There are a number of reasons, the most salient being:

Created names might easily conflict with variables already used by your logic.

Since the names are dynamically created, you typically also end up using dynamic techniques to retrieve the data.

So you can use dictionary for that purpose.

I've got these quotes from this post.

Here's how you can do it: Note: I've used a representative example using sample SQL table that I have.

The data looks like this:

Your code will look like this:

query = '''
SELECT 
    *
FROM books
WHERE id = {id};
'''

d = {}
for i in id:
    sql = query.format(id = i)
    temp = pd.read_sql_query(sql, db_connection)
    d[i] = pd.DataFrame(temp)

You can access the dataframe inside the dictionary using indexing.

answered Dec 17, 2021 at 7:36

Vishal A.

1,39110 silver badges19 bronze badges

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

1 Comment

Ryan Miller Over a year ago

Thanks! Your solution worked like a charm. I ran across the post you linked to as well but didn't quite comprehend what was being said and thought my SQL query might have been one of the culprits making my situation different. The big aha moment for me with this is that a dictionary can contain data frames. That makes them way more powerful than I realized.

Collectives™ on Stack Overflow

Looping over a list to create multiple data frames from SQL queries

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related