import pandas as pd
import sqlite3
from custom_table import get_dataframe

# Specify the path to your Excel file
# excel_file_path = 'C:\\Users\\rakes\\sql\\table_data.xlsx'
# excel_file_path = "https://huggingface.co/spaces/rakeshkumar1812/text-to-SQL-GenAI-App/tree/main/table_data.xlsx"

# Read the Excel file into a pandas DataFrame
# df = pd.read_excel(excel_file_path, sheet_name='Customer_Data')
df = get_dataframe()

# Display the DataFrame
# print(df.shape)
# print(df.head())


# connect to SQLite3
connection = sqlite3.connect("test_db.db")

# Create a Cursor object to insert Record, Create Table
cursor = connection.cursor()

# Delete the table if already exists the table.
cursor.execute("DROP TABLE IF EXISTS tbl_customer");

# Convert DataFrame to SQL table and insert data
df.to_sql('tbl_customer', connection, if_exists='replace', index=False)


## Display All the records
print('the inserted records are ....')
data = cursor.execute('''
Select CustomerKey,GeographyKey,CustomerAlternateKey,Title,FirstName,
MiddleName,LastName from tbl_customer LIMIT 1''')

for row in data:
    print(row)

# Commit changes
connection.commit()

# Close the connection
connection.close()