CODEWITHSUNDEEP
python-3.x
Sandeep Bhatt
Sandeep Bhatt

Reputation: 537

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

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.

Upvotes: 0

Views: 1405

Answers (2)

user11655135
user11655135

Reputation: 

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()

Upvotes: 0

Sandeep Bhatt
Sandeep Bhatt

Reputation: 537

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()

Upvotes: 1

Related Questions