0

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])
Ryan Miller
  • 133
  • 9

1 Answers1

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:

  1. Created names might easily conflict with variables already used by your logic.
  1. 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:

enter image description here

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.

Vishal A.
  • 1,373
  • 8
  • 19
  • 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. – Ryan Miller Dec 17 '21 at 16:16