How to append another excel data into sqlite3 database in python?

Question

I need to import multiple excel file data into an SQLite database, these excel files generated weekly basis having the same naming convention and data structure.

in my code 1st file data imported successfully, when I am trying to import the 2nd file it is giving me an error.

import sqlite3
import pandas as pd
filename="cps"
con=sqlite3.connect(filename+".db")
wb = pd.read_excel('CPS\cps29.xlsx',sheet_name = None)
for sheet in wb:

    wb[sheet].to_sql(sheet,con,index=False)
    con.commit()
    con.close()

I need to append data into my database.

unutbu · Accepted Answer · 2019-07-18 11:51:36Z

1

Use if_exists='append' to insert new rows if the table already exists (and if the table does not exist, it will be created):

for sheet in wb:
    wb[sheet].to_sql(sheet, con, index=False, if_exists='append')
    con.commit()
con.close()

Also note that con.close() should not be called inside the for-loop. Call it once after the for-loop, after all the data has been committed.

edited Jul 18, 2019 at 11:51

answered Jul 18, 2019 at 11:38

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to append another excel data into sqlite3 database in python?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related