Sqlite3 Tutorial: Query Python Fixed

with conn:
    conn.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Carol","carol@example.com"))
# commits on success, rolls back on exception

SQLite3 is a lightweight, serverless, self-contained SQL database engine. It's perfect for:

You can copy and run this script directly. sqlite3 tutorial query python fixed

import sqlite3
import os
# 1. Create a temporary database file (or use ':memory:' for RAM only)
db_name = "example.db"
# Clean up if file exists from a previous run
if os.path.exists(db_name):
    os.remove(db_name)
try:
    # 2. Connect to the database
    conn = sqlite3.connect(db_name)
# --- THE FIX FOR PROPER TEXT ---
    # This ensures that text fields are returned as Python strings (str),
    # not as bytes objects (b'text').
    conn.text_factory = str
cursor = conn.cursor()
# 3. Create a table
    # We specify 'TEXT' type for clarity, though SQLite is flexible.
    print("Creating table...")
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT
        )
    ''')
# 4. Insert Data (Proper parameterized queries)
    # NEVER use f-strings or string concatenation for queries (SQL Injection risk).
    # ALWAYS use '?' placeholders.
    print("Inserting data...")
    users_to_add = [
        ('Alice Johnson', 'alice@example.com'),
        ('Bob Smith', 'bob@example.com'),
        ('Charlie Brown', 'charlie@example.com')
    ]
cursor.executemany('INSERT INTO users (name, email) VALUES (?, ?)', users_to_add)
    conn.commit()
# 5. Query Data
    print("\n--- Query Results ---")
# Select all rows
    cursor.execute('SELECT * FROM users')
    rows = cursor.fetchall()
for row in rows:
        # row is a tuple: (id, name, email)
        user_id = row[0]
        name = row[1]    # Thanks to text_factory, this is a clean <class 'str'>
        email = row[2]
print(f"ID: user_id | Name: name | Email: email")
# 6. Parameterized Search (Safe way to filter text)
    print("\n--- Search for 'Alice' ---")
    search_name = 'Alice Johnson'
# The ? placeholder handles quoting and escaping automatically
    cursor.execute('SELECT * FROM users WHERE name = ?', (search_name,))
found_user = cursor.fetchone()
    if found_user:
        print(f"Found user: found_user")
except sqlite3.Error as e:
    print(f"An error occurred: e")
finally:
    # 7. Close connection
    if conn:
        conn.close()
        print("\nDatabase connection closed.")

def find_user_by_username(username): conn = sqlite3.connect('my_database.db') cursor = conn.cursor() with conn: conn

cursor.execute(
    "SELECT * FROM users WHERE username = ?",
    (username,)
)
user = cursor.fetchone()  # Get single row
conn.close()
return user