Sqlite3 Tutorial Query Python Fixed [better]

SQLite3 Tutorial: Python Database Query Guide

8. Advanced Queries

# Aggregation queries
def get_user_stats():
    cursor.execute('''
        SELECT 
            COUNT(*) as total_users,
            AVG(age) as average_age,
            MIN(age) as youngest,
            MAX(age) as oldest
        FROM users
    ''')
    return cursor.fetchone()
stats = get_user_stats()
print(f"\n--- User Statistics ---")
print(f"Total users: stats[0]")
print(f"Average age: stats[1]:.1f")
print(f"Youngest: stats[2]")
print(f"Oldest: stats[3]")
Joins and Complex Queries
def advanced_queries_examples():
    conn = sqlite3.connect('my_database.db')
    cursor = conn.cursor()
# Create orders table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS orders (
        id INTEGER PRIMARY KEY,
        user_id INTEGER,
        product_name TEXT,
        quantity INTEGER,
        order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users (id)
    )
''')
# Insert sample orders
cursor.execute("INSERT INTO orders (user_id, product_name, quantity) VALUES (1, 'Laptop', 1)")
cursor.execute("INSERT INTO orders (user_id, product_name, quantity) VALUES (1, 'Mouse', 2)")
cursor.execute("INSERT INTO orders (user_id, product_name, quantity) VALUES (2, 'Keyboard', 1)")
# Join query
cursor.execute('''
    SELECT users.username, orders.product_name, orders.quantity, orders.order_date
    FROM users
    JOIN orders ON users.id = orders.user_id
    ORDER BY orders.order_date DESC
''')
results = cursor.fetchall()
for row in results:
    print(f"row[0] ordered row[2]x row[1] on row[3]")
conn.commit()
conn.close()

advanced_queries_examples()

Error Handling
def robust_database_operation():
    conn = None
    try:
        conn = sqlite3.connect('my_database.db')
        cursor = conn.cursor()
    # Your operations
    cursor.execute("INSERT INTO users (username, email, age) VALUES (?, ?, ?)",
                  ("test_user", "test@example.com", 25))
conn.commit()
except sqlite3.IntegrityError as e:
    print(f"Integrity error (duplicate key, etc): e")
    if conn:
        conn.rollback()
except sqlite3.OperationalError as e:
    print(f"Operational error (syntax, table missing, etc): e")
    if conn:
        conn.rollback()
except Exception as e:
    print(f"Unexpected error: e")
    if conn:
        conn.rollback()
finally:
    if conn:
        conn.close()


SELECT Query
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

Key Takeaways for "Proper Text"


conn.text_factory = str:
This is the most important line for text handling. It tells Python: "Treat text data in the database as standard Python strings." Without this, you might get b'text' which looks messy and cannot be easily manipulated like a normal string.


Use ? Placeholders:
Notice the line: VALUES (?, ?). sqlite3 tutorial query python fixed

Wrong: f"INSERT INTO users VALUES ('name')" (Breaks if name has quotes, security risk).
Right: cursor.execute("INSERT ... VALUES (?)", (name,)) (Handles special characters, quotes, and unicode automatically).



Tuple Syntax:
When executing a query with one variable, Python requires a tuple. Note the comma:

Correct: (search_name,)
Incorrect: (search_name) (Python treats this just as a string in parentheses).



Conclusion
In this tutorial, we've covered the basics of querying a SQLite database with Python. We've discussed setting up a database, connecting to it with Python, and executing queries. Remember to always follow best practices to ensure your code is secure and efficient. SQLite3 Tutorial: Python Database Query Guide 
 8