Import XLS data to sqlite database using Python script

Question

I'm a beginner in python3 and sqlite3 coding; I'm working to code a small program to manage my production process: I need to import database from my old xlsx file to the sqlite3 database to speed the input process.

When I run it, nothing has been imported to the sqlite database even though the result says, data imported successfully!

I'm using Fedora Linux with Python3 and Sqlite3.

My script:

import pandas as pd
from sqlalchemy import create_engine
import sqlite3
import os

# Path to your SQLite database
db_path = "sqlite:///peanut_butter.db"
engine = create_engine(db_path)

# Path to your Excel file
excel_file_path = "/home/redbahi/Greenland_management_software / pandas_excel_database91224.xlsx"

# Read sheets from Excel
clients_df = pd.read_excel(excel_file_path, sheet_name="clients")
suppliers_df = pd.read_excel(excel_file_path, sheet_name="suppliers")
products_df = pd.read_excel(excel_file_path, sheet_name="products")

# Upload data to SQLite
clients_df.to_sql('clients', engine, if_exists='replace', index=False)
suppliers_df.to_sql('suppliers', engine, if_exists='replace', index=False)
products_df.to_sql('products', engine, if_exists='replace', index=False)

print("Data successfully uploaded to the database.")

Answer 1

here's a trivial example (not using sqlalchemy as I can't see a reason to use it)

import pandas as pd
import sqlite3
import sys

def importXsheets(xlFiles, sqliteDB, table_name_prefix="table"):
    conn = sqlite3.connect(sqliteDB)

    for idx, file in enumerate(xlFiles):
        table_name = f"{table_name_prefix}_{idx + 1}"
        print(f"Processing {file} into table {table_name}...")

        df = pd.read_excel(file, sheet_name=None)  # All sheets !

        for sheet_name, sheet_df in df.items():
            sheet_table_name = f"{table_name}_{sheet_name}"
            print(f" - Importing sheet: {sheet_name} into table {sheet_table_name}")

            sheet_df.to_sql(sheet_table_name, conn, if_exists="replace", index=False) # add to sqlite db

    conn.commit()
    conn.close()

if __name__ == "__main__":
    if len(sys.argv) > 2:
        theDb = sys.argv[1]
        theFiles = sys.argv[2:]
    else:
        print(f"usage: {sys.argv[0]} SQLITE2-DATABASE excelfile(s)")
        exit(1)


    importXsheets(theFiles, theDb)


#
# tested on a bunch of local test sheets 
#
python loaddb.py testdb.db  *.xlsx
Processing 2022-FIFA-World-Cup-Performance-Sample-Data.xlsx into table table_1...
 - Importing sheet: FIFA World Cup Performance into table table_1_FIFA World Cup Performance
Processing Call-Center-Sentiment-Sample-Data.xlsx into table table_2...
 - Importing sheet: Call Center Data into table table_2_Call Center Data
Processing Employee-Management-Sample-Data.xlsx into table table_3...
 - Importing sheet: Employee Management Data into table table_3_Employee Management Data
Processing Engineering-and-Manufacturing-Sample-Data.xlsx into table table_4...
 - Importing sheet: Engineering and Manufacturing into table table_4_Engineering and Manufacturing
Processing Healthcare-Insurance-Sample-Data.xlsx into table table_5...
 - Importing sheet: Healthcare Insurance into table table_5_Healthcare Insurance
Processing Inventory-Records-Sample-Data.xlsx into table table_6...
 - Importing sheet: Inventory Records Data into table table_6_Inventory Records Data
Processing Project-Management-Sample-Data.xlsx into table table_7...
 - Importing sheet: Project Management Data into table table_7_Project Management Data
Processing Students-Marksheet-Sample-Data.xlsx into table table_8...
 - Importing sheet: Student Marks into table table_8_Student Marks
Processing Supermarket-Sales-Sample-Data.xlsx into table table_9...
 - Importing sheet: SuperMarket Sales into table table_9_SuperMarket Sales
Processing Technological-Products-Sample-Data.xlsx into table table_10...
 - Importing sheet: Tech Products into table table_10_Tech Products
Processing Tokyo-Olympic-Sample-Data.xlsx into table table_11...
 - Importing sheet: Tokyo Olympic into table table_11_Tokyo Olympic
Processing Travel-Destination-Distance-Sample-Data.xlsx into table table_12...
 - Importing sheet: Distance Related into table table_12_Distance Related

#
# show the results
#
~/SQLiteStudio/sqlitestudiocli testdb.db 

SQLiteStudio (3.4.12)
------------------------

Current database: testdb
Type .help for help

testdb>.tables

Database Table
---------------------------
main     table_1_Tokyo Olympic
main     table_1_Distance Related
main     table_1_Engineering and Manufacturing
main     table_1_FIFA World Cup Performance
main     table_2_Call Center Data
main     table_3_Employee Management Data
main     table_4_Engineering and Manufacturing
main     table_5_Healthcare Insurance
main     table_6_Inventory Records Data
main     table_7_Project Management Data
main     table_8_Student Marks
main     table_9_SuperMarket Sales
main     table_10_Tech Products
main     table_11_Tokyo Olympic
main     table_12_Distance Related

testdb>.quit

hope this helps