Unpack Dictionary of Dataframes

Question

I have used sqlalchemy to read in tables from a PostgreSQL database, and they are stored in a dictionary (keys are table names, values are the dataframes). I can access them one at a time, but would like to scale the solution so that I have all tables from the given database/schema as pandas dataframes. My code as-is to import is the following:

import os
import pandas as pd
import psycopg2
from sqlalchemy import create_engine

engine = create_engine(os.environ['postgres_credentials'] +'db')
conn = engine.connect()
trans = conn.begin()

#Initialize empty dictionary
frames_dict = {}

#Use loop and pd.read_sql() to read tables from DB
table_names = engine.table_names()
select_template = 'SELECT * FROM {table_name}'

for tname in table_names:
    query = select_template.format(table_name = tname)
    frames_dict[tname] = pd.read_sql(query, conn)

# Close connection
conn.close()

This successfully gets the key:value of table_name:dataframe. The main goal is to be able to reference the dataframes by the same names they hold as keys and in the database (I can play with the data from there).

I've tried the following:

Create lists using .keys() and .values() and indexing the lists for assignment
Used setattr to iterate over lists of keys and values to assign the df names (as strings, in a list) to the corresponding dataframes.

Also I know it's a rehash of this question from ~four years ago, but there was no definitive answer given.

If the keys, as a list, are:

['name1','name2','name3']

and the values are corresponding dataframes, I'd like to be able to run:

[In] name1
[Out] Col1 |  Col2 | Col3
      --------------------
      foo  |    2  |  a
      bar  |    17 |  b
      ...

Thank you all for any help you can offer!

score 1 · Accepted Answer · answered Sep 27 '19 at 05:17

One thing I could think of is to use namedtuple from collections:

from collections import namedtuple
import pandas as pd

supposedly_your_dict = {
    'table_1': pd.DataFrame(columns=['t_1_col_1', 't_1_col_2']),
    'table_2': pd.DataFrame(columns=['t_2_col_1', 't_2_col_2']),
    'table_3': pd.DataFrame(columns=['t_3_col_1', 't_3_col_2'])
}

DBSnapshot = namedtuple('DBSnapshot', supposedly_your_dict.keys())

db_tables = DBSnapshot(**supposedly_your_dict)

Now you should be able to access the tables the way you want. db_tables.table_1 results in:

Empty DataFrame
Columns: [t_1_col_1, t_1_col_2]
Index: []

Unpack Dictionary of Dataframes

1 Answers1