Python Tkinter Project with MySQL Database

In this Python Project, we will be discussing how to integrate a MySQL Database into our Tkinter application.

Why do we need a MySQL Database?

But first, let us discuss “why” we need a MySQL Database. The need for a database arises when we need to permanently store data somewhere. Larger applications, such as Reporting software, Graphing applications, etc. need some place to store relevant data, from where it can be retrieved at a later date.

A common alternative is the use of “text files” to store data. Although the text file approach is simpler (in the short term), databases have several advantages.

Scalability
Performance
Security
Backups

However, Databases might be a bit over-kill for simpler applications. It’s really a case-by-case thing, where you need to evaluate which would be more suitable for your application.

Let’s begin with the tutorial.

Pre-requisites

We are assuming you already have a working MySQL installation setup. If not, kindly complete that step before attempting to use any of the code in this tutorial. IT IS IMPORTANT THAT YOU REMEMBER THE USERNAME AND PASSWORD YOU USED DURING ITS INSTALLATION. DON’T FORGET.

Once you have installed MySQL, the next thing we need to do is install the “mysql-connector-python” library in Python.

pip show mysql-connector-python

This library will allow us to connect with the MySQL database, from our Python code, and execute SQL queries.

Initializing a MySQL Connection for our Tkinter Project

Since this a large application, I will take a proper approach than what I usually do. We will be creating two files, one called database.py, and one called UI.py. First, we will create define some basic functions inside the database.py file, before we proceed to the UI.py file.

Here is the first function, used to initialize a connection to the MySQL Database.

def initialize_connection():
    conn = mysql.connector.connect(
        host = "localhost",
        user = "root",
        password = "db1963"
    )

    cursor = conn.cursor()
    return conn, cursor

Here we call the “connect” method with three arguments – host, user, and password.

The “host” parameter specifies the location of the MySQL database server. In this code, the location is set to “localhost”, which means that the MySQL server is running on the same machine where the Python code is being executed.

The “user” parameter specifies the username that is used to log in to the MySQL database. The “password” parameter is used to specify the password associated with the user account. Enter your own username and password here, which you used to setup your MySQL database.

After the connection object is created, the next line of code creates a cursor object. The cursor object is used to execute SQL queries and interact with the MySQL database. We then return both the cursor object, and the connection object from this function.

We will later call this function from the UI.py file.

Creating a MySQL Database

Although we have initialized a MySQL connection, we still need to create a Database for our project. There is already a default database created, called “sys”, but we will be creating a new one called “tutorial”. for this application.

The below code creates a new database in a safe manner. It first checks to ensure that there already isn’t a database called “tutorial”. If it does not exist, it will execute the “CREATE DATABASE tutorial” command to create it.

def create_database(cursor):
    cursor.execute("SHOW DATABASES")
    temp = cursor.fetchall()
    databases = [item[0] for item in temp]
    
    if "tutorial" not in databases:
        cursor.execute("CREATE DATABASE tutorial")
    
    cursor.execute("USE tutorial")

At the very end it calls the “USE tutorial” command, to switch from the default database to the new one. This line must be called regardless of whether our database exists already or not.

We still aren’t done with our setup.

Next, we have to create a “Table”. A Table is basically where we are going to be storing data for a certain entity/purpose. In larger applications, a database consists of multiple tables. For example, in a game, there might be a table for “Players”, one table for “NPC’s”, one for “Enemies”, one for “Quests”, and so on.

In our application, we will storing the records for Users. So we will only have one table for “Users”. The below code creates this table, in the same manner as we created the database.

def create_table(cursor):  
    cursor.execute("SHOW TABLES")
    temp = cursor.fetchall()
    tables = [item[0] for item in temp]
    
    if "users" not in tables:
        cursor.execute("""CREATE TABLE users(
            id INT AUTO_INCREMENT PRIMARY KEY,
            firstName VARCHAR(100),
            lastName VARCHAR(100),
            password VARCHAR(30),
            email VARCHAR(100) UNIQUE,
            gender VARCHAR(1),
            age INT,
            address VARCHAR(200)
         )""")

The above code is a little bit SQL-heavy due to the large table, with several fields. Each field has a name, a datatype, and some optional constraints. We have used three such constraints here. First we have “PRIMARY KEY” which designates the “id” field as a unique identifier we can use to query a specific user.

Likewise, we also declare the “email” as a unique column. These constraints will automatically prevent duplicates from being entered. We also have AUTO_INCREMENT for “id”, which means I will not be specifying an id, rather it will be auto generated in the numeric sequence.

We can also define lengths for each text-based field. The password has a limit of 30, names and email have a limit of 100, and gender has a limit of 1 character.

Finally after all this, we need to call these two methods somewhere.

We will place them both into the initialize_connection() function we created earlier.

def initialize_connection():
    conn = mysql.connector.connect(
        host = "localhost",
        user = "root",
        password = "db1963"
    )

    cursor = conn.cursor()
    create_database(cursor)
    create_table(cursor)

    return conn, cursor

Our setup is now complete!

Creating the Tkinter Project

The below code features a small Tkinter project we put together, which will we integrate the MySQL Database code into. There is nothing extra in the application, just a register and login window. The main window is blank, as it doesn’t really have anything to do with this tutorial. Our only concern is registering a user into our database, and logging them in later with their credentials.

Most of this is just standard tkinter code, so take your time going through it. The only thing of note right now, is the Login and Register windows, along with the imports we made from the database.py file (second line). We called the initialize_connection() method, which returned the connection and cursor object. We will be needing these later, when calling the login and register and functions.

(Screenshot of the application shown after the below code)

import tkinter as tk
from database import *

conn, cursor = initialize_connection()

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):
        pass

    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="First 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="Last Name:").grid(row=1, column=0, sticky="w")
        self.last_name_entry = tk.Entry(self, width=26)
        self.last_name_entry.grid(row=1, 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):
        pass

    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.pack()


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

Welcome Page:

Python Tkinter Project with MySQL Database

It is important to note, that in the above code, the “submit” functions for both the Login and Register window are empty. We will developing them in the next two sections.

Registering a User

Let’s implement the registration functionality into our code. The first thing we need to do is extract the required data from our tkinter widgets (in the register window) and then pass this data to a “register” function in our database.py file, which we will create soon.

Here is the submit function in the Register window that we have filled out. We just extract all the data into a dictionary, then pass this into a register function, along with the connection and cursor we created earlier with the initialize_connection() function.

    def submit(self):
        data = {}
        data["firstName"]= self.first_name_entry.get()
        data["lastName"]= self.last_name_entry.get()
        data["password"]= self.password_entry.get()
        data["email"]= self.email_entry.get()
        data["gender"]= self.gender_entry.get()
        data["age"]= self.age_entry.get()
        data["address"]= self.address_entry.get(1.0, tk.END)

        register(cursor, conn, data)

Next, we will implement this register function in the database.py file.

We have made use of string formatting here, to insert all the values from the dictionary into a SQL INSERT INTO command, which inserts values into a Table. The values must be in the sequence that we defined the tables. (The first value is NULL, because that is the id, which will be assigned automatically)


def register(cursor, conn, data):

    cursor.execute(f"""INSERT INTO users values( 
        NULL,
        '{data["firstName"]}', 
        '{data["lastName"]}', 
        '{data["password"]}', 
        '{data["email"]}', 
        '{data["gender"]}', 
        '{data["age"]}', 
        '{data["address"]}'
    )""")

    conn.commit()

Lastly, we need to call the “commit” method, which basically implements the change we made. Think of it like “saving” changes to the database. The concept of commits is important when it comes to transaction control, and recovery in databases, which is a whole separate topic.

Our register function is now complete! We can begin registering users, but let’s wait until we have implemented the login functionality.

Logging a User in

Next, we will work on the two functions for login functionality, the submit() function inside the Login Window, and login() function inside the database.py file.

Here is the submit() function. We haven’t defined the login() function yet, but we have already decided beforehand that it will be returning either True, or False, depending on whether the login was successful or not.

    def submit(self):
        data = {}
        data["email"] = self.username_entry.get()
        data["password"] = self.password_entry.get()
        
        if login(cursor, data) == True:
            print("successful login")
            for widget in self.winfo_children(): 
                widget.destroy()
            self.destroy()
            MainWindow(self.master)
        else:
            print("unsuccessful login")

Here is the login() function, where we execute a “SELECT” command by filtering out users based on the given “email” and “password” using the “WHERE” clause. If the login was successful, we destroy the current window, and open the main window.

def login(cursor, data):
    cursor.execute(f"""SELECT * FROM users WHERE email = '{data["email"]}' 
                       AND password = '{data["password"]}' """)
    
    if cursor.fetchone() != None:
        return True
    return False

We then return a True or False value, depending on the whether a record was successfully return or not. A “None” result means no records were found.

We decided to use “email” as the “username”, since it was a unique field. It’s not a good idea to expect the user to use a numerical digit as their login, and no other field is unique to be used as a username. You may wish to create a separate field for “username” during registration if you want.

Testing our Tkinter Project + MySQL Database

Let’s fire up our application now and try it out. The first thing to do is go and register a new user, in the register window.

Next, I will hit submit to register the user, then navigate to the login page to try and login. If the login succeeds, we should be re-directed to the main window.

Here is the complete code so that you can try it out for yourself.

The UI.py file.

import tkinter as tk
from database import *

conn, cursor = initialize_connection()

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):
        data = {}
        data["email"] = self.username_entry.get()
        data["password"] = self.password_entry.get()
        
        if login(cursor, data) == True:
            print("successful login")
            for widget in self.winfo_children(): 
                widget.destroy()
            self.destroy()
            MainWindow(self.master)
        else:
            print("unsuccessful login")

    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="First 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="Last Name:").grid(row=1, column=0, sticky="w")
        self.last_name_entry = tk.Entry(self, width=26)
        self.last_name_entry.grid(row=1, 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):
        data = {}
        data["firstName"] = self.first_name_entry.get()
        data["lastName"] = self.last_name_entry.get()
        data["password"] = self.password_entry.get()
        data["email"] = self.email_entry.get()
        data["gender"] = self.gender_entry.get()
        data["age"] = self.age_entry.get()
        data["address"] = self.address_entry.get(1.0, tk.END)

        register(cursor, conn, data)

    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.pack()


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

The database.py file.

import mysql.connector 

def initialize_connection():
    conn = mysql.connector.connect(
        host = "localhost",
        user = "root",
        password = "db1963"
    )

    cursor = conn.cursor()
    create_database(cursor)
    create_table(cursor)

    return conn, cursor

def create_database(cursor):
    cursor.execute("SHOW DATABASES")
    temp = cursor.fetchall()
    databases = [item[0] for item in temp]
    
    if "tutorial" not in databases:
        cursor.execute("CREATE DATABASE tutorial")
    
    cursor.execute("USE tutorial")

def create_table(cursor):  
    cursor.execute("SHOW TABLES")
    temp = cursor.fetchall()
    tables = [item[0] for item in temp]
    
    if "users" not in tables:
        cursor.execute("""CREATE TABLE users(
            id INT AUTO_INCREMENT PRIMARY KEY,
            firstName VARCHAR(100),
            lastName VARCHAR(100),
            password VARCHAR(30),
            email VARCHAR(100) UNIQUE,
            gender VARCHAR(1),
            age INT,
            address VARCHAR(200)
         )""")

def login(cursor, data):
    cursor.execute(f"""SELECT * FROM users WHERE email = '{data["email"]}' 
                       AND password = '{data["password"]}' """)
    
    if cursor.fetchone() != None:
        return True
    return False

def register(cursor, conn, data):
    print(data)

    cursor.execute(f"""INSERT INTO users values( 
        NULL,
        '{data["firstName"]}', 
        '{data["lastName"]}', 
        '{data["password"]}', 
        '{data["email"]}', 
        '{data["gender"]}', 
        '{data["age"]}', 
        '{data["address"]}'
    )""")

    conn.commit()

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

Share on Facebook