Creating a Database with Python: Getting Started with #PySimpleGUI and SQLite

Developing a database using Python can be a powerful and rewarding skill to have. In this tutorial, we will focus on creating a simple database using Python, specifically using PySimpleGUI and SQLite.

PySimpleGUI is a Python library that provides a simple interface for creating graphical user interfaces (GUIs). SQLite is a lightweight database engine that is included with Python and is well-suited for small-scale database applications.

Step 1: Install PySimpleGUI

Before we can start developing our database, we need to install the PySimpleGUI library. You can do this by running the following command:

pip install PySimpleGUI

Step 2: Create a GUI for our database

Next, let’s start by creating a simple GUI for our database. We will use PySimpleGUI to create a basic interface with input fields for adding data to our database.

import PySimpleGUI as sg

layout = [
    [sg.Text('Name:'), sg.InputText(key='name')],
    [sg.Text('Age:'), sg.InputText(key='age')],
    [sg.Button('Add Data'), sg.Button('Exit')],
]

window = sg.Window('Database Application', layout)

while True:
    event, values = window.read()

    if event == 'Add Data':
        # Add code to insert data into the database here
        pass
    elif event == sg.WIN_CLOSED or event == 'Exit':
        break

window.close()

This code creates a simple window with input fields for name and age, as well as buttons for adding data and exiting the application.

Step 3: Create a SQLite database

Now let’s create a SQLite database to store our data. We can use the sqlite3 module that is included with Python to interact with SQLite databases.

import sqlite3

conn = sqlite3.connect('database.db')
cursor = conn.cursor()

# Create a table to store our data
cursor.execute('''CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL,
                    age INTEGER NOT NULL
                )''')

conn.commit()
conn.close()

This code creates a SQLite database file called database.db and creates a table called users with columns for id, name, and age.

Step 4: Insert data into the database

Now we need to modify our GUI code to insert data into the database when the "Add Data" button is clicked.

import PySimpleGUI as sg
import sqlite3

conn = sqlite3.connect('database.db')
cursor = conn.cursor()

layout = [
    [sg.Text('Name:'), sg.InputText(key='name')],
    [sg.Text('Age:'), sg.InputText(key='age')],
    [sg.Button('Add Data'), sg.Button('Exit')],
]

window = sg.Window('Database Application', layout)

while True:
    event, values = window.read()

    if event == 'Add Data':
        name = values['name']
        age = values['age']

        cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', (name, age))
        conn.commit()
    elif event == sg.WIN_CLOSED or event == 'Exit':
        break

conn.close()
window.close()

This code modifies our GUI code to insert data into the users table in our SQLite database when the "Add Data" button is clicked.

Step 5: Display data from the database

Finally, let’s modify our GUI code to display the data from the database in a window when the application is run.

import PySimpleGUI as sg
import sqlite3

conn = sqlite3.connect('database.db')
cursor = conn.cursor()

layout = [
    [sg.Text('Name'), sg.Text('Age')],
    *[[sg.Text(row[1]), sg.Text(str(row[2]))] for row in cursor.execute('SELECT * FROM users')],
]

window = sg.Window('Database Application', layout)

while True:
    event, values = window.read()

    if event == sg.WIN_CLOSED or event == 'Exit':
        break

conn.close()
window.close()

This code modifies our GUI code to display the data from the users table in our SQLite database in a window when the application is run.

Conclusion

In this tutorial, we have covered how to develop a simple database using Python, PySimpleGUI, and SQLite. By following these steps, you can create your own database applications with a graphical user interface using Python. You can also expand upon this example by adding additional functionality, such as updating and deleting data, or by creating more complex database structures. Happy coding!