For loop to create a dataframe using pandas read_sql in python

Question

I have a SQLite database named Europe.db. I want to import, filter and save the results as several pandas data frames. The current code works, but I'm sure it could somehow be simplified with a for loop.

Current code:

company = "THEP.PA"
database = "sqlite:///Europe.db"

sqlite_table = f"SELECT * FROM balance_sheet WHERE symbol='{company}'"
bs_df = pd.read_sql(sqlite_table, database)

sqlite_table = f"SELECT * FROM cashflow_statement WHERE symbol='{company}'"
cf_df = pd.read_sql(sqlite_table, database)

sqlite_table = f"SELECT * FROM income_statement WHERE symbol='{company}'"
is_df = pd.read_sql(sqlite_table, database)

sqlite_table = f"SELECT * FROM key_executives WHERE company='{company}'"
key_executives_df = pd.read_sql(sqlite_table, database)

sqlite_table = f"SELECT * FROM key_metrics WHERE symbol='{company}'"
metrics_df = pd.read_sql(sqlite_table, database)

user17242583 · Accepted Answer · 2022-03-11 02:03:37Z

2

A dict is great for this:

company = "THEP.PA"
database = "sqlite:///Europe.db"

tables = {
    'balance_sheet': None,
    'cashflow_statement': None,
    'income_statement': None,
    'key_executives': None,
    'key_metrics': None,
}

for table_name in tables.keys():
    sqlite_table = f"SELECT * FROM {table_name} WHERE symbol='{company}'"
    tables[table_name] = pd.read_sql(sqlite_table, database)

Now, to get at one of those dataframes, use, e.g. tables['balance_sheet'] where you would have previously used bs_sheet.

answered Mar 11, 2022 at 2:03

user17242583

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

Comments

Toms · Accepted Answer · 2022-03-11 02:13:54Z

0

Dict would be the better solution but another way could be:

table_names = [
"balance_sheet",
"cashflow_statement",
..
..
]
tables = []

for table_name in table_names:
    sqlite_table = f"SELECT * FROM { table_name } WHERE symbol='{ company }'"
    tables.append(pd.read_sql(sqlite_table, database))

answered Mar 11, 2022 at 2:13

Toms

2191 silver badge7 bronze badges

Collectives™ on Stack Overflow

For loop to create a dataframe using pandas read_sql in python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related