# 8.3 SQLite Hands-on with Python

 

SQLite Shenanigans: The Funniest Database Adventure Ever

Welcome to the World of SQLite!

Ah, SQLite. The database that doesn't need a server, yet still has the audacity to demand our attention! Today, we embark on a hilarious yet educational journey into the land of SQL wizardry, where databases exist in .db files and SQL statements are our spells.

So, buckle up and get ready to dive into the wonderful (and sometimes frustrating) world of SQLite with Python!

---

Ex 1: Opening a Database – The "Hello World" of SQLite

Before we can do anything, we need to open a database. It’s like saying "hi" before you ask for a favor.

import sqlite3

try:
    with sqlite3.connect("my.db") as conn:
        print(f"Opened SQLite database with version {sqlite3.sqlite_version} successfully.")
except sqlite3.OperationalError as e:
    print("Failed to open database:", e)

If all goes well, SQLite gives us a nod of approval. If not, well, expect an error message that makes you question your life choices.

---

Ex 2: Creating Tables – Because We Need a Place to Put Stuff

We can’t just throw our data into the void. We need tables! Here’s how we create them:

import sqlite3

sql_statements = [
    """CREATE TABLE IF NOT EXISTS projects (
            id INTEGER PRIMARY KEY,
            name text NOT NULL,
            begin_date DATE,
            end_date DATE
        );""",

    """CREATE TABLE IF NOT EXISTS tasks (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            priority INT,
            project_id INT NOT NULL,
            status_id INT NOT NULL,
            begin_date DATE NOT NULL,
            end_date DATE NOT NULL,
            FOREIGN KEY (project_id) REFERENCES projects (id)
        );"""
]

try:
    with sqlite3.connect('my.db') as conn:
        cursor = conn.cursor()
        for statement in sql_statements:
            cursor.execute(statement)
        conn.commit()
        print("Tables created successfully.")
except sqlite3.OperationalError as e:
    print("Failed to create tables:", e)

And just like that, we have tables! SQLite doesn’t complain—yet.

---

Ex 3: Inserting Records – Filling Our Database with Meaningful Chaos

Creating tables is nice, but they’re just empty shells without data. Let's add some records!

import sqlite3

def add_project(conn, project):
    sql = ''' INSERT INTO projects(name,begin_date,end_date)
              VALUES(?,?,?) '''
    cur = conn.cursor()
    cur.execute(sql, project)
    conn.commit()
    return cur.lastrowid

def add_task(conn, task):
    sql = '''INSERT INTO tasks(name,priority,status_id,project_id,begin_date,end_date)
             VALUES(?,?,?,?,?,?) '''
    cur = conn.cursor()
    cur.execute(sql, task)
    conn.commit()
    return cur.lastrowid

def main():
    try:
        with sqlite3.connect('my.db') as conn:
            project = ('Cool App with SQLite & Python', '2015-01-01', '2015-01-30')
            project_id = add_project(conn, project)
            print(f'Created a project with the id {project_id}')
            tasks = [
                ('Analyze the requirements of the app', 1, 1, project_id, '2015-01-01', '2015-01-02'),
                ('Confirm with user about the top requirements', 1, 1, project_id, '2015-01-03', '2015-01-05')
            ]
            for task in tasks:
                task_id = add_task(conn, task)
                print(f'Created task with the id {task_id}')
    except sqlite3.Error as e:
        print(e)

if __name__ == '__main__':
    main()

Now our database has some actual data! It’s alive! 🤯

---

Ex 4: Updating Data – Because Plans Always Change

First, let's change a single column, because why not?

import sqlite3

sql = 'UPDATE tasks SET priority = ? WHERE id = ?'

try:
    with sqlite3.connect('my.db') as conn:
        cursor = conn.cursor()
        cursor.execute(sql, (2,1))
        conn.commit()
except sqlite3.OperationalError as e:
    print(e)

Now, let's go crazy and update multiple columns!

import sqlite3

sql = 'UPDATE tasks SET end_date = ?'

try:
    with sqlite3.connect('my.db') as conn:
        cursor = conn.cursor()
        cursor.execute(sql, ('2015-02-03',))
        conn.commit()
except sqlite3.Error as e:
    print(e)

Change is good, right?

---

Ex 5 & 6: Querying the Database – Let’s See What We Did

To check if we actually did things right, let's retrieve some data:

import sqlite3

try:
    with sqlite3.connect('my.db') as conn:
        cur = conn.cursor()
        cur.execute('select id, name, priority from tasks')
        rows = cur.fetchall()
        for row in rows:
            print(row)
except sqlite3.OperationalError as e:
    print(e)

Want a specific record? Say no more!

import sqlite3

try:
    with sqlite3.connect('my.db') as conn:
        cur = conn.cursor()
        cur.execute('SELECT id, name, priority FROM tasks WHERE id =?', (1,))
        row = cur.fetchone()
        if row:
            print(row)
except sqlite3.OperationalError as e:
    print(e)

---

Ex 7: Reusable Code – The Lazy Programmer’s Dream

We love functions. They do things so we don’t have to.

import sqlite3

def get_task_by_id(id: int) -> tuple:
    try:
        with sqlite3.connect('my.db') as conn:
            cur = conn.cursor()
            cur.execute('select id, name, priority from tasks where id =?', (id,))
            row = cur.fetchone()
            return row, None
    except sqlite3.OperationalError as e:
        return None, e      

if __name__ == '__main__':
    task, error = get_task_by_id(1)
    if error is not None:
        print(f'Error: {error}')
    else:
        print(task)

---

And that’s a wrap! 🎉 You now have the power to create, update, and retrieve data like a true SQLite ninja. Use it wisely (or recklessly, your call). Happy coding! 😆

Try these Qz: 

1. SQL https://forms.gle/2Y7SAvG1BewqfoKWA

2. Advanced SQL https://forms.gle/Q32yrJsQ21RQhvna8