Append empty dataframe from SQL query

Question

Why doesn't this work. And is there a way to doing this. I want to use the table names and create a df for each table.

table1= pd.DataFrame()
table2= pd.DataFrame()
table3= pd.DataFrame()
table4= pd.DataFrame()

tables_name = ['table1', 'table2', 'table3', 'table4']
tables = [table1, table2, table3, table4]

for i, j in zip(tables_name, tables):

    q = f"""
            SELECT
            *
            FROM email.{i}
        """
    crm = pd.read_sql_query(q, con=db)
  
    j = crm.copy() 

print(table1.head(3))

score 0 · Accepted Answer · answered Mar 26 '21 at 15:15

You are overwiting the reference to the dataframe not the dataframe itself. Take a look at This answer on pass by reference in python.

To solve you should not re-assign j, you need to mutate j using a dataframe method. Since df.append is not a inplace operation you can use assign on columns to acheive it.

j[crm.columns] = crm.copy()

So your full code will be

table1= pd.DataFrame()
table2= pd.DataFrame()
table3= pd.DataFrame()
table4= pd.DataFrame()

tables_name = ['table1', 'table2', 'table3', 'table4']
tables = [table1, table2, table3, table4]

for i, j in zip(tables_name, tables):

    q = f"""
            SELECT
            *
            FROM email.{i}
        """
    crm = pd.read_sql_query(q, con=db)
  
    j[crm.columns] = crm.copy()

print(table1.head(3))

Append empty dataframe from SQL query

1 Answers1