Python Tkinter GUI with SQLite Tutorial

by

In this tutorial we will explore how we can use Tkinter GUI alongside the SQLite Database to store and access data required by our application.

Permanent data storage is required in most applications, especially those which deal with data. A very popular option for data storage is “text files”, which is almost always used instead of databases, especially if the amount of data is very small (e.g. a settings file)

As the size of data increases, and the need to frequently access it increases, Databases become a much better option. They are also much more reliable, more secure and have other helping features such as backups, collision detection, and scalability that are the bread and butter of any professional application.

Why SQLite?

There are various types of SQL databases available for us to freely use. One such popular option is SQLite, which as the name implies, is a lightweight version of SQL (no fancy features or extra frills).

SQLite offers several benefits that make it a popular choice for certain use cases:

  1. Lightweight and Embedded:
    • SQLite is a self-contained, serverless, and zero-configuration database engine. It is lightweight and can be embedded directly into applications, requiring minimal setup.
  2. Zero Configuration:
    • No separate server process or setup is needed. Applications can connect to an SQLite database by simply opening a file, making it easy to deploy and manage.
  3. Cross-Platform Compatibility:
    • SQLite is cross-platform and can run on various operating systems without modification. This makes it suitable for applications that need to work seamlessly across different platforms.
  4. Low Resource Consumption:
    • SQLite has low memory and disk space requirements. This is beneficial for resource-constrained environments, such as mobile devices or embedded systems.
  5. Ease of Use:
    • SQLite is known for its simplicity and ease of use. It uses a straightforward SQL syntax, making it accessible to developers who may not have extensive database administration experience.

How to install SQLite?

You don’t have to install SQLite! That’s one of the greatest advantages of it. It ships natively with Python, and is included as part of the standard Python library. This makes it a good choice for deployments as well, as you do not need to include an extra dependency.

In the very rare case you do not have one of these libraries, you can install this library using the following command:

pip install sqlite3

For those with older versions of Python (2.x), use the following:

pip install pysqlite

If import sqlite3 still does not work, it likely means you’ve compiled Python without SQLite support, and you need to build it again after installing the libsqlite3-dev package. For windows users, I would simply recommended reinstalling Python. For Linux users, refer to this thread which explains how to add missing dependencies (sqlite3 in this case).

Setting up your Tkinter Application

Like sqlite3, Tkinter is also included within the standard Python library. This means that you can potentially develop an entire GUI application + Database without even installing a single dependency!

Here is our code for our Tkinter Application (before adding any of the SQL-related code). We have placeholders for where the SQL is meant to go.

import tkinter as tk


def center_window(width, height):
    x = (root.winfo_screenwidth() // 2) - (width // 2)
    y = (root.winfo_screenheight() // 2) - (height // 2)
    root.geometry(f'{width}x{height}+{x}+{y}')


class WelcomeWindow(tk.Frame):
    def __init__(self, master):
        super().__init__()
        self.master = master
        self.master.title("Welcome")
        center_window(240, 120)
        
        login_button = tk.Button(self, text="Login", width=10, command=self.open_login_window)
        login_button.pack(padx=20, pady=(20, 10))
        
        register_button = tk.Button(self, text="Register", width=10, command=self.open_register_window)
        register_button.pack(pady=10)
        self.pack()
        
    def open_login_window(self):
        for widget in self.winfo_children(): 
            widget.destroy()
        self.destroy()
        LoginWindow(self.master)
        
    def open_register_window(self):
        for widget in self.winfo_children(): 
            widget.destroy()
        self.destroy()
        RegisterWindow(self.master)


class LoginWindow(tk.Frame):
    def __init__(self, master):
        super().__init__()
        self.master = master
        self.master.title("Login")
        self.master.resizable(False, False)
        center_window(240, 150)
        
        tk.Label(self, text="Username:").grid(row=0, column=0)
        self.username_entry = tk.Entry(self)
        self.username_entry.grid(row=0, column=1, padx=10, pady=10)
        
        tk.Label(self, text="Password:").grid(row=1, column=0)
        self.password_entry = tk.Entry(self, show="*")
        self.password_entry.grid(row=1, column=1, padx=10, pady=10)
        
        submit_button = tk.Button(self, text="Submit", width=8, command=self.submit)
        submit_button.grid(row=2, column=1, sticky="e", padx=10, pady=(10, 0))

        submit_button = tk.Button(self, text="Back", width=8, command=self.back)
        submit_button.grid(row=2, column=0, sticky="w", padx=10, pady=(10, 0))
        self.pack()
            
    def submit(self):
        username = self.username_entry.get()
        password = self.password_entry.get()

        # PLACEHOLDER (Authenticate User)

        if user:
            # Logged in
            MainWindow(self.master)
            self.destroy()
        else:
            print("You have typed in the wrong details")
            self.username_entry.delete(0, tk.END)
            self.password_entry.delete(0, tk.END)


    def back(self):
        for widget in self.winfo_children(): 
            widget.destroy()
        self.destroy()
        WelcomeWindow(self.master)


class RegisterWindow(tk.Frame):
    def __init__(self, master):
        super().__init__()
        self.master = master
        self.master.title("Register")
        self.master.resizable(False, False)
        center_window(320, 350)
        
        tk.Label(self, text="Name:").grid(row=0, column=0, sticky="w")
        self.first_name_entry = tk.Entry(self, width=26)
        self.first_name_entry.grid(row=0, column=1, padx=10, pady=10, sticky="e")
        
        tk.Label(self, text="Password:").grid(row=2, column=0, sticky="w")
        self.password_entry = tk.Entry(self, show="*", width=26)
        self.password_entry.grid(row=2, column=1, padx=10, pady=10, sticky="e")
        
        tk.Label(self, text="Email:").grid(row=3, column=0, sticky="w")
        self.email_entry = tk.Entry(self, width=26)
        self.email_entry.grid(row=3, column=1, padx=10, pady=10, sticky="e")
        
        tk.Label(self, text="Gender:").grid(row=4, column=0, sticky="w")
        self.gender_entry = tk.Entry(self, width=10)
        self.gender_entry.grid(row=4, column=1, padx=10, pady=10, sticky="e")
        
        tk.Label(self, text="Age:").grid(row=5, column=0, sticky="w")
        self.age_entry = tk.Entry(self, width=10)
        self.age_entry.grid(row=5, column=1, padx=10, pady=10, sticky="e")
        
        tk.Label(self, text="Address:").grid(row=6, column=0, sticky="w")
        self.address_entry = tk.Text(self, width=20, height=3)
        self.address_entry.grid(row=6, column=1, padx=10, pady=10, sticky="e")
        
        submit_button = tk.Button(self, text="Submit", width=8, command=self.submit)
        submit_button.grid(row=7, column=1, padx=10, pady=10, sticky="e")

        submit_button = tk.Button(self, text="Back", width=8, command=self.back)
        submit_button.grid(row=7, column=0, sticky="w", padx=10, pady=(10, 10))
        self.pack()
        
    def submit(self):        
        user_data = (self.first_name_entry.get().strip(), 
                    self.password_entry.get().strip(), 
                    self.email_entry.get().strip(), 
                    self.age_entry.get().strip(),
                    self.gender_entry.get().strip(), 
                    self.address_entry.get(1.0, tk.END).strip())
        
      # PLACEHOLDER (Insert new user data in database)

        self.destroy()
        MainWindow(self.master)


    def back(self):
        for widget in self.winfo_children(): 
            widget.destroy()
        self.destroy()
        WelcomeWindow(self.master)


class MainWindow(tk.Frame):
    def __init__(self, master):
        super().__init__()
        self.master = master
        center_window(600, 400)

        self.generateUserList()
        self.pack()

    def generateUserList(self):
        # PLACEHOLDER (Fetch Users Data)

        columns = ["ID", "Name", "Password", "Email", "Age", "Gender", "Address"]
        column_widths = [5, 10, 10, 30, 5, 10, 20]
        for col_index, col_name in enumerate(columns):
            label = tk.Label(self, text=col_name, padx=5, pady=5)
            label.grid(row=0, column=col_index)

        # Iterate over users and create Entry widgets
        for row_index, user in enumerate(users):
            for col_index, value in enumerate(user):
                entry = tk.Entry(self, width=column_widths[col_index], disabledforeground="black")
                entry.grid(row=row_index + 1, column=col_index)
                entry.insert(0, str(value))  # Insert user data into Entry widget
                entry.configure(state="disabled")


root = tk.Tk()
root.eval('tk::PlaceWindow . center')
WelcomeWindow(root)
root.mainloop()

Integrating SQLite into our Tkinter Application

First, we need to initialize the SQL database connection to communicate with it.

import sqlite3

conn = sqlite3.connect("tutorial.db")
cursor = conn.cursor()

Next, we create a Table for our user data to be stored.

Python

add_user_query = """CREATE TABLE IF NOT EXISTS users (
                            id integer PRIMARY KEY AUTOINCREMENT,
                            name text NOT NULL,
                            password text NOT NULL,
                            email text NOT NULL,
                            age integer,
                            gender text,
                            address text
                ); """  
cursor.execute(add_user_query)
conn.commit()

Remember to commit your changes after making changes to the database, otherwise they won’t get saved!

The first proper method we will write is for the registration of a user, in the submit() method of the Registration window. All we need to do is get the text from the UI, and insert it into the Table we just created. Make sure the order of the values is the same as the order of columns.

Python
    def submit(self):
        insert_user_data = """INSERT INTO users(name, password, email, age, gender, address)
                              VALUES (?, ?, ?, ?, ?, ?)"""
        
        user_data = (self.first_name_entry.get().strip(), 
                    self.password_entry.get().strip(), 
                    self.email_entry.get().strip(), 
                    self.age_entry.get().strip(),
                    self.gender_entry.get().strip(), 
                    self.address_entry.get(1.0, tk.END).strip())
        
        cursor.execute(insert_user_data, user_data)
        conn.commit()

        self.destroy()
        MainWindow(self.master)

Next, in the submit method for the Login window, we write some SQL to retrieve a user against the given username and password that the user has provided. If a record is returned (i.e. the user exists) we forward him to the main window.

Python
    def submit(self):
        username = self.username_entry.get()
        password = self.password_entry.get()

        cursor.execute("SELECT * FROM users WHERE name=? AND password=?", (username, password))
        user = cursor.fetchone()

        if user:
            # Logged in
            MainWindow(self.master)
            self.destroy()
        else:
            print("You have typed in the wrong details")
            self.username_entry.delete(0, tk.END)
            self.password_entry.delete(0, tk.END)

Lastly, we will write a simple Query to retrieve all user data and display it on the main page.

Python
    def generateUserList(self):
        cursor.execute("SELECT * FROM users")
        users = cursor.fetchall()

        columns = ["ID", "Name", "Password", "Email", "Age", "Gender", "Address"]
        column_widths = [5, 10, 10, 30, 5, 10, 20]
        for col_index, col_name in enumerate(columns):
            label = tk.Label(self, text=col_name, padx=5, pady=5)
            label.grid(row=0, column=col_index)

        # Iterate over users and create Entry widgets
        for row_index, user in enumerate(users):
            for col_index, value in enumerate(user):
                entry = tk.Entry(self, width=column_widths[col_index], disabledforeground="black")
                entry.grid(row=row_index + 1, column=col_index)
                entry.insert(0, str(value))  # Insert user data into Entry widget
                entry.configure(state="disabled")

The Complete Code

Python
import tkinter as tk
import sqlite3

conn = sqlite3.connect("tutorial.db")
cursor = conn.cursor()


add_user_query = """CREATE TABLE IF NOT EXISTS users (
                            id integer PRIMARY KEY AUTOINCREMENT,
                            name text NOT NULL,
                            password text NOT NULL,
                            email text NOT NULL,
                            age integer,
                            gender text,
                            address text
                ); """  
cursor.execute(add_user_query)
conn.commit()


def center_window(width, height):
    x = (root.winfo_screenwidth() // 2) - (width // 2)
    y = (root.winfo_screenheight() // 2) - (height // 2)
    root.geometry(f'{width}x{height}+{x}+{y}')


class WelcomeWindow(tk.Frame):
    def __init__(self, master):
        super().__init__()
        self.master = master
        self.master.title("Welcome")
        center_window(240, 120)
        
        login_button = tk.Button(self, text="Login", width=10, command=self.open_login_window)
        login_button.pack(padx=20, pady=(20, 10))
        
        register_button = tk.Button(self, text="Register", width=10, command=self.open_register_window)
        register_button.pack(pady=10)
        self.pack()
        
    def open_login_window(self):
        for widget in self.winfo_children(): 
            widget.destroy()
        self.destroy()
        LoginWindow(self.master)
        
    def open_register_window(self):
        for widget in self.winfo_children(): 
            widget.destroy()
        self.destroy()
        RegisterWindow(self.master)


class LoginWindow(tk.Frame):
    def __init__(self, master):
        super().__init__()
        self.master = master
        self.master.title("Login")
        self.master.resizable(False, False)
        center_window(240, 150)
        
        tk.Label(self, text="Username:").grid(row=0, column=0)
        self.username_entry = tk.Entry(self)
        self.username_entry.grid(row=0, column=1, padx=10, pady=10)
        
        tk.Label(self, text="Password:").grid(row=1, column=0)
        self.password_entry = tk.Entry(self, show="*")
        self.password_entry.grid(row=1, column=1, padx=10, pady=10)
        
        submit_button = tk.Button(self, text="Submit", width=8, command=self.submit)
        submit_button.grid(row=2, column=1, sticky="e", padx=10, pady=(10, 0))

        submit_button = tk.Button(self, text="Back", width=8, command=self.back)
        submit_button.grid(row=2, column=0, sticky="w", padx=10, pady=(10, 0))
        self.pack()
            
    def submit(self):
        username = self.username_entry.get()
        password = self.password_entry.get()

        cursor.execute("SELECT * FROM users WHERE name=? AND password=?", (username, password))
        user = cursor.fetchone()

        if user:
            # Logged in
            MainWindow(self.master)
            self.destroy()
        else:
            print("You have typed in the wrong details")
            self.username_entry.delete(0, tk.END)
            self.password_entry.delete(0, tk.END)


    def back(self):
        for widget in self.winfo_children(): 
            widget.destroy()
        self.destroy()
        WelcomeWindow(self.master)


class RegisterWindow(tk.Frame):
    def __init__(self, master):
        super().__init__()
        self.master = master
        self.master.title("Register")
        self.master.resizable(False, False)
        center_window(320, 350)
        
        tk.Label(self, text="Name:").grid(row=0, column=0, sticky="w")
        self.first_name_entry = tk.Entry(self, width=26)
        self.first_name_entry.grid(row=0, column=1, padx=10, pady=10, sticky="e")
        
        tk.Label(self, text="Password:").grid(row=2, column=0, sticky="w")
        self.password_entry = tk.Entry(self, show="*", width=26)
        self.password_entry.grid(row=2, column=1, padx=10, pady=10, sticky="e")
        
        tk.Label(self, text="Email:").grid(row=3, column=0, sticky="w")
        self.email_entry = tk.Entry(self, width=26)
        self.email_entry.grid(row=3, column=1, padx=10, pady=10, sticky="e")
        
        tk.Label(self, text="Gender:").grid(row=4, column=0, sticky="w")
        self.gender_entry = tk.Entry(self, width=10)
        self.gender_entry.grid(row=4, column=1, padx=10, pady=10, sticky="e")
        
        tk.Label(self, text="Age:").grid(row=5, column=0, sticky="w")
        self.age_entry = tk.Entry(self, width=10)
        self.age_entry.grid(row=5, column=1, padx=10, pady=10, sticky="e")
        
        tk.Label(self, text="Address:").grid(row=6, column=0, sticky="w")
        self.address_entry = tk.Text(self, width=20, height=3)
        self.address_entry.grid(row=6, column=1, padx=10, pady=10, sticky="e")
        
        submit_button = tk.Button(self, text="Submit", width=8, command=self.submit)
        submit_button.grid(row=7, column=1, padx=10, pady=10, sticky="e")

        submit_button = tk.Button(self, text="Back", width=8, command=self.back)
        submit_button.grid(row=7, column=0, sticky="w", padx=10, pady=(10, 10))
        self.pack()
        
    def submit(self):
        insert_user_data = """INSERT INTO users(name, password, email, age, gender, address)
                              VALUES (?, ?, ?, ?, ?, ?)"""
        
        user_data = (self.first_name_entry.get().strip(), 
                    self.password_entry.get().strip(), 
                    self.email_entry.get().strip(), 
                    self.age_entry.get().strip(),
                    self.gender_entry.get().strip(), 
                    self.address_entry.get(1.0, tk.END).strip())
        
        cursor.execute(insert_user_data, user_data)
        conn.commit()

        self.destroy()
        MainWindow(self.master)


    def back(self):
        for widget in self.winfo_children(): 
            widget.destroy()
        self.destroy()
        WelcomeWindow(self.master)


class MainWindow(tk.Frame):
    def __init__(self, master):
        super().__init__()
        self.master = master
        center_window(600, 400)

        self.generateUserList()
        self.pack()

    def generateUserList(self):
        cursor.execute("SELECT * FROM users")
        users = cursor.fetchall()

        columns = ["ID", "Name", "Password", "Email", "Age", "Gender", "Address"]
        column_widths = [5, 10, 10, 30, 5, 10, 20]
        for col_index, col_name in enumerate(columns):
            label = tk.Label(self, text=col_name, padx=5, pady=5)
            label.grid(row=0, column=col_index)

        # Iterate over users and create Entry widgets
        for row_index, user in enumerate(users):
            for col_index, value in enumerate(user):
                entry = tk.Entry(self, width=column_widths[col_index], disabledforeground="black")
                entry.grid(row=row_index + 1, column=col_index)
                entry.insert(0, str(value))  # Insert user data into Entry widget
                entry.configure(state="disabled")


root = tk.Tk()
root.eval('tk::PlaceWindow . center')
WelcomeWindow(root)
root.mainloop()

This marks the end of the Python Tkinter GUI with SQLite Tutorial. Any suggestions or contributions for CodersLegacy are more than welcome. Questions regarding the tutorial content can be asked in the comments section below.

facebookShare on Facebook
TwitterTweet
FollowFollow us

Leave a Comment