CODEWITHSUNDEEP
pythonpandassqliteloops
Ryan Miller
Ryan Miller

Reputation: 133

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

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

Upvotes: 0

Views: 916

Answers (1)

Vishal A.
Vishal A.

Reputation: 1381

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.

Upvotes: 1

Related Questions