df.to_sql raises ValueError("Empty table or column name specified")

user_17323606 0
Viewed 345

list(df) retrieves the column headers of a df dataframe. headers_list retrieves the existing column headers of an existing 438393848 table. What I am trying to do is to DROP the column headers of the 438393848 table that do not exist in the df and thereafter use the to_sql method to push the df dataframe into the 438393848 SQLite database. However, df.to_sql raises ValueError("Empty table or column name specified").

with conn:
        c.execute(f"CREATE TABLE IF NOT EXISTS '438393848' ({list(df)[0]})")
        try:
            for header in list(df)[1:]:
                c.execute(f"ALTER TABLE '438393848' ADD COLUMN {header}")
        except:
               pass
        colnames = conn.execute("SELECT * FROM '438393848'").description
        headers_list = list(map(lambda x: x[0], colnames))
        for column in headers_list:
            if column not in list(df):
                c.execute(f"ALTER TABLE '438393848' DROP COLUMN {column}")
        colnamesnew = conn.execute("SELECT * FROM '438393848'").description
    df.to_sql('438393848', conn, if_exists='replace', index=False)

For context, I editted the df dataframe in a different set of code, which makes df not be in sync with the SQLite table 438393848. What I want to do is to use this set of code to update the columns of my table 438393848 so that the df can be pushed to the SQLite table now that the columns are matching. After running the code once, by printing the table 438393848, the column is DROPed and in accordance to the column headers of the dataframe df.

Does anyone know what the problem is?

pandas dataframe sqlite sql python
1 Answers

After re-reading the df.to_sql documentation, the if_exists=replace parameter drops the table and replaces the table with an entirely new data set, which is df in this case. Hence, there is no need for the above code to replace the column header names.

user_173236060
Related