how to create table into SQLite3 from importing excel data in python?

Question

In my code, I am importing data from excel file into an SQLite database using python.

it doesn't give any error but it converts every excel column name into a table.

I have multiple excel files with the same data structure, containing 40K rows and 52 columns each file.

when I am importing these file data into SQLite database using python code it converts each column header name into a table.

import sqlite3
import pandas as pd

filename= gui_fname()
con=sqlite3.connect("cps.db")
wb = pd.read_excel(filename,sheet_name ='Sheet2')
for sheet in wb: 
    wb[sheet].to_sql(sheet,con,index=False,if_exists = 'append')
con.commit()
con.close()

it should create a table with the name of Sheet which I am importing.

Sandeep Bhatt · Accepted Answer · 2019-07-20 11:01:55Z

1

I do some hit and trial and found the solution: I just put con.commit() within the for loop and it works as required, but I didn't get the logic.

I will appreciate if anyone can explain to me this.

import sqlite3
import pandas as pd

filename= gui_fname()
con=sqlite3.connect("cps.db")
wb = pd.read_excel(filename,sheet_name = 'Sheet2')
for sheet in wb:

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

answered Jul 20, 2019 at 11:01

Sandeep Bhatt

5752 gold badges8 silver badges18 bronze badges

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

Comments

user11655135 · Accepted Answer · 2021-12-09 09:06:15Z

0

import pandas as pd
def import_excel_to_sqlite_db(excelFile):
    df = pd.read_excel(excelFile)
    con = sqlite3.connect("SQLite.db")
    cur = con.cursor()
    results = cur.execute("Select * from TableName")
    final = df.to_sql("TableName", con, if_exists="append", index=False)
    pd.DataFrame(results, columns=final)
    con.commit()
    cur.close()

answered Dec 9, 2021 at 9:06

user11655135

Collectives™ on Stack Overflow

how to create table into SQLite3 from importing excel data in python?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related