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

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])

CodePudding user response：

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

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.