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.
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.