import sqlite3

conn = sqlite3.connect("app.db")
cursor = conn.cursor()

conn = sqlite3.connect(":memory:")

cursor.execute("""
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
)
""")

conn.commit()

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

conn.commit()

users = [
    ("Bob", 25),
    ("Carol", 40)
]

cursor.executemany(
    "INSERT INTO users (name, age) VALUES (?, ?)",
    users
)

conn.commit()

cursor.execute("SELECT * FROM users")

rows = cursor.fetchall()

print(rows)

row = cursor.fetchone()

rows = cursor.fetchmany(10)

rows = cursor.fetchall()

cursor.execute(
    "SELECT * FROM users WHERE age > ?",
    (30,)
)

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

conn.commit()

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

conn.commit()

cursor.lastrowid

cursor.execute("SELECT COUNT(*) FROM users")

count = cursor.fetchone()[0]

SELECT * FROM users ORDER BY age DESC

SELECT * FROM users LIMIT 5

cursor.execute(
    "SELECT * FROM users WHERE name LIKE ?",
    ("%Joe%",)
)

conn.row_factory = sqlite3.Row

# Then:
row = cursor.fetchone()

print(row["name"])

conn.execute("BEGIN")

cursor.execute(...)
cursor.execute(...)

conn.commit()

# Rollback:
conn.rollback()

with sqlite3.connect("app.db") as conn:
    conn.execute(
        "INSERT INTO users (name, age) VALUES (?, ?)",
        ("Dave", 22)
    )

cursor.execute(
    "CREATE INDEX idx_users_name ON users(name)"
)

cursor.execute("DROP TABLE users")

cursor.execute("""
SELECT name FROM sqlite_master
WHERE type='table'
""")

print(cursor.fetchall())

cursor.execute("PRAGMA table_info(users)")
print(cursor.fetchall())

SELECT users.name, orders.total
FROM users
JOIN orders ON users.id = orders.user_id

cursor.execute(
    "SELECT * FROM users WHERE id = ?",
    (1,)
)

cursor.execute(
    f"SELECT * FROM users WHERE id = {x}"
)

conn.close()

import sqlite3

with sqlite3.connect("notes.db") as conn:
    cur = conn.cursor()

    cur.execute("""
    CREATE TABLE IF NOT EXISTS notes (
        id INTEGER PRIMARY KEY,
        text TEXT
    )
    """)

    cur.execute(
        "INSERT INTO notes (text) VALUES (?)",
        ("Learn sqlite3",)
    )

    cur.execute("SELECT * FROM notes")

    print(cur.fetchall())