SQLite3 connector application to browse connect and manage database with error display

Tkinter SQLite SQLite Connector Part -2 Part -3

SQLite connector to create and manage database with error reporting using Python Tkinter

SQLite is a file based database. We will create one file browser using Tkinter to browse the local directory or file system and connect to SQLite3 database file. Our application will have the following features.

Use file browser (Tkinter filedialog ) to browse local directory system and select the database file.
On selection of the database file, it will show all the tables inside it.
If a wrong file which is not a database file is selected then error message will be displayed.
There will be one button to create one new SQLite 3 database after browsing the local directory – file system.

We will keep all our connection and file handling code inside the file connection.py. Our main file connector-sqlit-part-1.py will import the function my_connect from connection.py.
Copy the source code below.
In next part we will display the list of available tables inside database with number of records, empty table and delete table commands. We will also add query window to run any SQL.

Using file browser to locate the file

We will use one file browser or directory locator to select one database file. If this database file is having tables, then we will display the name of all tables.

def my_connect(type):
    global my_conn
    f_types = [('All Files', '*.*'), 
              ('Text Document', '*.txt'),
              ('Database files',"*.db")]
    if(type=='existing'):
        file = filedialog.askopenfilename(filetypes=f_types)
    else:
        file = filedialog.asksaveasfilename(filetypes=f_types,defaultextension=".db",)

Error handling

If we select wrong file then the error message will be displayed. For this we will use our exception handling with try except code block.

try:
        file1='sqlite:///'+ file 
        my_conn = create_engine(file1)
        
        my_conn.execute("SELECT name FROM sqlite_master WHERE type = 'table'")
        my_output='ok'
    except SQLAlchemyError as e:
        error = str(e.__dict__['orig'])
        my_output=error

The main file connector-sqlit-part-1.py will import the connection.py file. On click of the Browse & Connect button, the function my_connect_p() will execute and it will first establish the connection by calling my_connect().

If the connection is established and there is no error then we will check the number of tables available inside the database.

We will enable or disable the query button b2 ( discussed in Next part ) based on connection status.

connection.py
connector-sqlite-part-1

from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError
import tkinter as tk
from tkinter import END
from connection import my_connect
global my_conn
### connect to database ####
def my_connect_p(status):
    global my_conn
    my_conn,my_output,file=my_connect(status) # connection details 
    t1.delete('1.0',END)
    t1.update()
    if(my_conn and my_output=='ok'):
        my_output=''
        r_set=my_conn.execute("SELECT name FROM sqlite_master WHERE type='table'")
        my_list_of_tables=r_set.fetchall() # table list 
        if(len(my_list_of_tables)>0):
            for i in my_list_of_tables:
                my_output=my_output + str(i[0]) + '\n'
        else:
            my_output=" No table found "
        t1.config(bg='white')
        b2.config(state='normal') # query window 
        l1_path.config(text=file)
    else:
        t1.config(bg='red') # background colour for error message 
        b2.config(state='disabled') # query window 
    t1.insert(tk.END,my_output)

### layout design of the widgets in window ##
my_w = tk.Tk()
my_w.geometry("800x500") # width & height of window
sql_str=tk.StringVar()
font1=('Times',26,'bold') # higer size font
l0=tk.Label(my_w,text='plus2net.com SQLite Connector',font=font1)
l0.grid(row=0,column=1,columnspan=8)
b1=tk.Button(my_w,text='Browse & Connect',width=20,command=lambda:my_connect_p('existing'))
b1.grid(row=1,column=1)
l1_path=tk.Label(my_w,text='') # to display the path only
l1_path.grid(row=1,column=2)
b2=tk.Button(my_w,text='Query window ',state='disabled') # query button 
b2.grid(row=2,column=9)
b3=tk.Button(my_w,text='New Database',command=lambda:my_connect_p('new'))
b3.grid(row=3,column=9)

t1=tk.Text(my_w,height=20,width=40) # Display details 
t1.grid(row=2,column=1,columnspan=8)
###############

my_w.mainloop()

SQLite Connector : Using Query window in Part - 2 SQLite Connector Part -3 (Run Queries)

Python Tkinter Dictionary Python- Tutorials

Subscribe to our YouTube Channel here

SQLite Connector to Manage Database

Using file browser to locate the file

Error handling

Subscribe