'for' loop doesn't loop

Question

The following for loop was intended to create a dataframe made up of appended tables using the elements in col_list (of which there are currently 4 elements). However, it does not work and only produces contents from one table rather than the four.

col_list = df['ref_name'].tolist()

df_all = None
conn = sqlite3.connect('all_data.db')
for col in col_list:
    query = "SELECT * FROM " + col + ";"
    df = pd.read_sql_query(query, conn)
    if df_all is not None:
        df_all.append(df)
    else:
        df_all = df
conn.close()
df_all = df_all.sort_values(by = 'date')
df_all = df_all.reset_index(drop=True)

never [append inside a loop](https://stackoverflow.com/a/36489724/6692898) append to a python list instead and concatenate after exiting the loop — RichieV, Oct 24 '20 at 18:47
what makes you say it is not looping? assuming `col_list` has at least one value then the code should work... try calling the query outside the loop — RichieV, Oct 24 '20 at 18:50

score 2 · Answer 1 · answered Oct 24 '20 at 18:51

2

df.append() does not change the dataframe in place, it returns a new dataframe. Simple fix:

df_all = df_all.append(df)

Also, as noted in comments, it will be more efficient to:

df_all = pd.concat(
    pd.read_sql_query("SELECT * FROM " + col + ";", conn) # TODO: parametrize the query
    for col in col_list)

answered Oct 24 '20 at 18:51

Marat

15,215
2
39
48

That resolved it immediately. Great help, thank you so much – windwalker Oct 24 '20 at 18:56

'for' loop doesn't loop

1 Answers1