Getting Started with SQLite in Python: Events Database

HERCULES — Sat, 06 May 2023 19:54:04 +0000

To create an events database using SQLite3 in Python, we first need to define an Event class that represents the structure of each event. The Event class should have attributes for the event's category 📅, date 📆, start time ⏰, location 📍, and age restriction (as an integer) 🧑‍🦳.

Example implementation of the Event class

class Event:
    def __init__(self, category, date, start_time, location, age_restriction):
        self.category = category
        self.date = date
        self.start_time = start_time
        self.location = location
        self.age_restriction = age_restriction

    def __str__(self):
        return f"🎉 {self.category} on {self.date} at ⏰ {self.start_time} 📍 {self.location}. Age restriction: {self.age_restriction} 👴🏼👵🏼"

To manage our events data in a SQLite3 database, we'll need a Database class that can connect to the database and perform *CRUD *(create, read, update, delete) operations on the data.

Let's implement this class in Python


import sqlite3
from event import Event


class EventDatabase:
    def __init__(self, db_file):
        self.connection = sqlite3.connect(db_file)
        self.cursor = self.connection.cursor()
        self.create_table()

    def create_table(self):
        self.cursor.execute("""CREATE TABLE IF NOT EXISTS events (
                                id INTEGER PRIMARY KEY AUTOINCREMENT,
                                category TEXT,
                                date TEXT,
                                start_time TEXT,
                                location TEXT,
                                age_restriction INTEGER
                            );""")
        self.connection.commit()

    def add_event(self, event):
        self.cursor.execute("""INSERT INTO events (category, date, start_time, location, age_restriction)
                               VALUES (?, ?, ?, ?, ?);""",
                            (event.category, event.date, event.start_time, event.location, event.age_restriction))
        self.connection.commit()

    def update_event(self, event_id, new_event):
        self.cursor.execute("""UPDATE events SET
                                category = ?,
                                date = ?,
                                start_time = ?,
                                location = ?,
                                age_restriction = ?
                            WHERE id = ?;""", (
            new_event.category, new_event.date, new_event.start_time, new_event.location, new_event.age_restriction,
            event_id))
        self.connection.commit()

    def delete_event(self, event_id):
        self.cursor.execute("DELETE FROM events WHERE id = ?;", (event_id,))
        self.connection.commit()

    def get_all_events(self):
        self.cursor.execute("SELECT * FROM events;")
        rows = self.cursor.fetchall()
        events = []
        for row in rows:
            event = Event(row[1], row[2], row[3], row[4], row[5])
            events.append(event)
        return events

    def get_event_by_id(self, event_id):
        self.cursor.execute("SELECT * FROM events WHERE id = ?;", (event_id,))
        row = self.cursor.fetchone()
        if row:
            event = Event(row[1], row[2], row[3], row[4], row[5])
            return event
        else:
            return None

    def get_events(self, date):
        self.cursor.execute("SELECT * FROM events WHERE date = ?;", (date,))
        rows = self.cursor.fetchall()
        events = []
        for row in rows:
            event = Event(row[1], row[2], row[3], row[4], row[5])
            events.append(event)
        return events

    def close(self):
        self.connection.close()

To interact with the events database, we'll need a way for the user to perform CRUD operations on the events data. We can create a simple command-line menu that prompts the user to choose an option from a list of available operations. Let's implement this menu in the main file of our Python program.


db = EventDatabase("events.db")

while True:
    print("\nSelect an option:")
    print("1. Add an event 🎉")
    print("2. View all events 👀")
    print("3. Update an event 📝")
    print("4. Delete an event 🗑️")
    print("5. View events by date 📅")
    print("6. Quit 🚪")

    choice = input("Your Choice >> ")

    if choice == "1":
        print("\nAdd an event 🎉")
        category = input("Category: ")
        date = input("Date (DD-MM-YYYY): ")
        start_time = input("Start time (HH:MM): ")
        location = input("Location: ")
        age_restriction = input("Age restriction: ")
        event = Event(category, date, start_time, location, age_restriction)
        db.add_event(event)
        print("Event added. 🎉")
    elif choice == "2":
        print("\nView all events 👀")
        events = db.get_all_events()
        for event in events:
            print(event)
    elif choice == "3":
        print("\nUpdate an event 📝")
        event_id = input("Event ID: ")
        new_category = input("New category (leave blank to keep current value): ")
        new_date = input("New date (leave blank to keep current value): ")
        new_start_time = input("New start time (leave blank to keep current value): ")
        new_location = input("New location (leave blank to keep current value): ")
        new_age_restriction = input("New age restriction (leave blank to keep current value): ")
        old_event = db.get_event_by_id(event_id)
        if old_event:
            if not new_category:
                new_category = old_event.category
            if not new_date:
                new_date = old_event.date
            if not new_start_time:
                new_start_time = old_event.start_time
            if not new_location:
                new_location = old_event.location
            if not new_age_restriction:
                new_age_restriction = old_event.age_restriction
            new_event = Event(new_category, new_date, new_start_time, new_location, new_age_restriction)
            db.update_event(event_id, new_event)
            print("Event updated. 📝")
        else:
            print("Event not found.")
    elif choice == "4":
        print("\nDelete an event 🗑️")
        event_id = input("Event ID: ")
        db.delete_event(event_id)
        print("Event deleted. 🗑️")
    elif choice == "5":
        print("\nView events by date 📅")
        date = input("Date (DD-MM-YYYY): ")
        events = db.get_events(date)
        for event in events:
            print(event)
    elif choice == "6":
        break
    else:
        print("Invalid choice.")

db.close()

I've added some appropriate emojis to make the code more fun and engaging.

DEV Community: HERCULES

Getting Started with SQLite in Python: Events Database