PYTHON & SQLITE3 SEASON 1

 import sqlite3

# Connect to a database (or create it if it doesn't exist)

# Using ":memory:" creates an in-memory database that disappears when the script ends.

# Replace with "my_database.db" for a persistent file-based database.

conn = sqlite3.connect('example.db')

# Create a cursor object

cursor = conn.cursor()

# Create a table

cursor.execute('''

    CREATE TABLE IF NOT EXISTS users (

        id INTEGER PRIMARY KEY,

        name TEXT NOT NULL,

        age INTEGER

    )

''')

# Insert data

cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Alice", 30))

cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Bob", 25))

# Commit the changes

conn.commit()

# Query data

cursor.execute("SELECT * FROM users")

rows = cursor.fetchall()

print("All users:")

for row in rows:

    print(row)

# Query with a condition

cursor.execute("SELECT name FROM users WHERE age > ?", (28,))

names = cursor.fetchall()

print("\nUsers older than 28:")

for name_tuple in names:

    print(name_tuple[0]) # Access the name from the tuple

# Update data

cursor.execute("UPDATE users SET age = ? WHERE name = ?", (31, "Alice"))

conn.commit()

# Delete data

cursor.execute("DELETE FROM users WHERE name = ?", ("Bob",))

conn.commit()

# Verify changes

cursor.execute("SELECT * FROM users")

updated_rows = cursor.fetchall()

print("\nUsers after update and delete:")

for row in updated_rows:

    print(row)

# Close the connection

conn.close()