Is there any way to efficiently append a pandas series to sql database table (as an additional column)?

Question

So I am trying to insert a pandas DataFrame into the database. The database already has a table that contains most of the content of the DataFrame except for one column.

I attempted to use df.to_sql() to insert the entire DataFrame but it takes a lot of time. Is there any function or library for just appending one column from the DataFrame to an existing database table?

You can try the answer from this link - https://stackoverflow.com/questions/53178858/insert-pandas-dataframe-created-within-python-into-sql-server — Aura, Dec 11 '19 at 16:48

score 0 · Answer 1 · answered Dec 11 '19 at 16:48

It might be that when you insert the data into the database that the table gets dropped and all the data including the new column is written to the database.

Maybe try ALTER TABLE <tablename> ADD column dtype;

And the insert can be done with an UPDATE <tablename> SET column = value where condition

You can then iterate over the pandas.Series object and insert only the new values.

something like this:

crsr = cnxn.cursor()
crsr.fast_executemany = True
crsr.executemany(
    "UPDATE <tablename> SET col1 VALUE ?",
    list(df.column.itertuples(index=False)
)
crsr.commit()

Is there any way to efficiently append a pandas series to sql database table (as an additional column)?

1 Answers1