Select CSV file to create DataFrame and finally store data in SQLite database table

Code Explanation: Breaking the Code into Functional Parts

1. Importing Required Libraries

    import pandas as pd # Import pandas library for data handling
    import tkinter as tk  # Import tkinter for GUI development
    from tkinter import filedialog # For showing file dialog for file selection
    from sqlalchemy import create_engine # For SQLite database connection

• pandas: Used for creating and manipulating DataFrames.
• tkinter: Used for creating GUI components like windows, labels, and buttons.
• filedialog: Provides file selection dialog functionality.
• sqlalchemy: Provides database connection functionality for storing data in SQLite.

2. Setting Up the SQLite Database Connection

    path='F:\\testing\\sqlite\\test.db' # Path to the SQLite database file
    my_conn = create_engine('sqlite:///'+path) # Create the connection string
    my_conn=my_conn.connect() # Connect to the database

• The path variable specifies the location of the SQLite database.
• The create_engine function establishes the database connection.
• The my_conn.connect() method returns the connection object for executing SQL queries.

3. Creating the Tkinter GUI Window

    my_w = tk.Tk() # Initialize the main window
    my_w.geometry("400x300") # Set the window dimensions
    my_w.title('www.plus2net.com') # Set the window title
	
	

• Initialize a Tkinter main window using tk.Tk().
• Set the dimensions of the window using geometry.
• Set the title of the window with title.

4. Adding GUI Components

    my_font1=('times', 8, 'bold') # Define font for labels

    l1 = tk.Label(my_w, text='Read File & create DataFrame',
              anchor='w', width=50, font=my_font1)  
    l1.grid(row=1, column=1)

    b1 = tk.Button(my_w, text='Browse File', 
   width=20, command=lambda:upload_file())
    b1.grid(row=2, column=1, pady=5)

    t1=tk.Text(my_w, width=40, height=3)
    t1.grid(row=3, column=1, padx=2)

    l2=tk.Label(my_w, bg='lightgreen')
    l2.grid(row=4, column=1, pady=5)
	
	

• Label: Displays static text in the GUI.
• Button: Triggers the upload_file function for file browsing.
• Text: Displays rows and columns information about the CSV file.
• Label: Displays the path to the SQLite database after uploading data.

5. Defining the File Upload Function

    def upload_file():
    f_types = [('CSV files',),('All',)] # File types for selection
    file = filedialog.askopenfilename(filetypes=f_types) # Open file dialog
    if file: 
        l1.config(text=file) # Display the file path
        df = pd.read_csv(file) # Read the file into a DataFrame
        str1 = 'Rows:' + str(df.shape[0]) + '\nColumns:' + str(df.shape[1])
        t1.insert(tk.END, str1) # Insert information into Text widget
        # Save DataFrame to SQLite
        df.to_sql(con=my_conn, name='student', if_exists='replace', index=False) 
        l2.config(text=path) # Display database path
		
		

• f_types: Defines the file types the user can select.
• askopenfilename: Opens the file selection dialog box.
• pd.read_csv: Reads the selected CSV file into a Pandas DataFrame.
• to_sql: Saves the DataFrame into the SQLite database, replacing the table if it already exists.

6. Running the Main Application Loop

    my_w.mainloop() # Run the Tkinter event loop
		

• Keeps the Tkinter window open and responsive to user actions.

import pandas as pd # import pandas library
import tkinter  as tk  # get Tkitner library 
from tkinter import filedialog # To show file dialog for selection

from sqlalchemy import create_engine # for database connection 

path='F:\\testing\\sqlite\\test.db' # update the databas path
my_conn = create_engine('sqlite:///'+path)
my_conn=my_conn.connect() # connection object or string

my_w = tk.Tk() # Main window
my_w.geometry("400x300")  # Size of the window width x height
my_w.title('www.plus2net.com') # title 

my_font1=('times', 8, 'bold') # higher size font 
l1 = tk.Label(my_w,text='Read File & create DataFrame'
              ,anchor='w',width=50,font=my_font1)  
l1.grid(row=1,column=1)
b1 = tk.Button(my_w, text='Browse File', 
   width=20,command = lambda:upload_file())
b1.grid(row=2,column=1,pady=5) 
t1=tk.Text(my_w,width=40,height=3)
t1.grid(row=3,column=1,padx=2)
l2=tk.Label(my_w,bg='lightgreen')
l2.grid(row=4,column=1,pady=5)
def upload_file():
    f_types = [('CSV files',"*.csv"),('All',"*.*")] # file type for selection
    file = filedialog.askopenfilename(filetypes=f_types) # show file dialog
    if file : # if the user has not cancelled the file browser 
        l1.config(text=file) # display the path 
        df=pd.read_csv(file) # create DataFrame
        str1="Rows:" + str(df.shape[0])+ "\nColumns:"+str(df.shape[1])
        t1.insert(tk.END, str1) # add to Text widget        
        # Dataframe is added to SQLite database, table name student
        df.to_sql(con=my_conn,name='student',if_exists='replace',index=False) 
        l2.config(text=path) # show the path to SQLite database 
my_w.mainloop()  # Keep the window open

Displaying SQLite data with error handling

    from sqlalchemy import text # Import text for query execution
    from sqlalchemy.exc import SQLAlchemyError # For handling database errors
	

• text: Used to safely create SQL queries for execution.
• SQLAlchemyError: Handles exceptions that may occur during database operations.

    q = 'SELECT * FROM student LIMIT 0,5' # Query to fetch 5 rows from the table
    try: 
        r_set = my_conn.execute(text(q)) 
        for row in r_set: 
        str1 += ' '.join(map(str, row)) + '\n'  
    except SQLAlchemyError as e: 
        error = str(e.__dict__.get('orig', e)) # Extract error message
        print(error)
		
		

• q: SQL query to select the first 5 rows from the "student" table.
• r_set: Executes the query and retrieves the result set.
• str1 += ' '.join(map(str, row)) + '\n': Converts each tuple (row) into a string, joins its elements with a space, and appends it to str1.
• SQLAlchemyError: Catches database-related errors.
• e.__dict__.get('orig', e): Extracts the original error message for debugging.

import pandas as pd # import pandas library
import tkinter  as tk  # get Tkitner library 
from tkinter import filedialog # To show file dialog for selection

from sqlalchemy import create_engine,text # for database connection 
from sqlalchemy.exc import SQLAlchemyError # error handling 
path='F:\\testing\\sqlite\\test.db' # update the databas path
my_conn = create_engine('sqlite:///'+path)
my_conn=my_conn.connect() # connection object or string

my_w = tk.Tk() # Parent Tkinter window 
my_w.geometry("400x300")  # Size of the window width x height
my_w.title('www.plus2net.com') # title 

my_font1=('times', 8, 'bold') # higher size font 
l1 = tk.Label(my_w,text='Read File & create DataFrame'
              ,anchor='w',width=50,font=my_font1)  
l1.grid(row=1,column=1)
b1 = tk.Button(my_w, text='Browse File', 
   width=20,command = lambda:upload_file())
b1.grid(row=2,column=1,pady=5) 
t1=tk.Text(my_w,width=40,height=8)
t1.grid(row=3,column=1,padx=2)
l2=tk.Label(my_w,bg='lightgreen')
l2.grid(row=4,column=1,pady=5)
def upload_file():
    f_types = [('CSV files',"*.csv"),('All',"*.*")] # file type for selection
    file = filedialog.askopenfilename(filetypes=f_types) # show file dialog
    if file : # if the user has not cancelled the file browser 
        l1.config(text=file) # display the path 
        df=pd.read_csv(file) # create DataFrame
        str1="Rows:" + str(df.shape[0])+ "\nColumns:"+str(df.shape[1]) +"\n"
                
        # Dataframe is added to SQLite database, table name student
        df.to_sql(con=my_conn,name='student',if_exists='replace',index=False) 
        l2.config(text=path) # show the path to SQLite database 
        # Select 5 rows to display inside text widget 
        q='SELECT * FROM student LIMIT 0,5'

        try:
            r_set=my_conn.execute(text(q))
            for row in r_set:
                 # Converts each element of the tuple to a string and joins with a space
                 str1 += ' '.join(map(str, row)) + '\n'  
        except SQLAlchemyError as e:
            error = str(e.__dict__.get('orig', e)) # get error message 
            print(error)
        t1.insert(tk.END, str1) # add to Text widget
my_w.mainloop()  # Keep the window open