Executing SQL Queries

Creating Tables

with conn.cursor() as cur:
    cur.execute("""
        CREATE TABLE IF NOT EXISTS users (
            id SERIAL PRIMARY KEY,
            username VARCHAR(50) UNIQUE NOT NULL,
            email VARCHAR(100) NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)
    conn.commit()

INSERT Operations

# Single insert with parameterized query (SAFE)
cur.execute(
    "INSERT INTO users (username, email) VALUES (%s, %s)",
    ("john_doe", "john@example.com")
)

# Get the inserted ID
cur.execute(
    "INSERT INTO users (username, email) VALUES (%s, %s) RETURNING id",
    ("jane_doe", "jane@example.com")
)
user_id = cur.fetchone()[0]
print(f"Inserted user ID: {user_id}")

# Bulk insert with executemany
users = [
    ("user1", "user1@email.com"),
    ("user2", "user2@email.com"),
    ("user3", "user3@email.com"),
]
cur.executemany(
    "INSERT INTO users (username, email) VALUES (%s, %s)",
    users
)

conn.commit()

SELECT Operations

# Fetch all results
cur.execute("SELECT id, username, email FROM users")
rows = cur.fetchall()
for row in rows:
    print(f"ID: {row[0]}, Username: {row[1]}")

# Fetch one result
cur.execute("SELECT * FROM users WHERE id = %s", (1,))
user = cur.fetchone()

# Fetch many (batch)
cur.execute("SELECT * FROM users")
batch = cur.fetchmany(10)  # Fetch 10 rows

UPDATE and DELETE

# UPDATE
cur.execute(
    "UPDATE users SET email = %s WHERE username = %s",
    ("newemail@example.com", "john_doe")
)
print(f"Updated {cur.rowcount} rows")

# DELETE
cur.execute("DELETE FROM users WHERE id = %s", (5,))
print(f"Deleted {cur.rowcount} rows")

conn.commit()

⚠️ Security Warning: NEVER use string formatting for queries!

# DANGEROUS - SQL Injection vulnerability!
cur.execute(f"SELECT * FROM users WHERE username = '{username}'")

# SAFE - Use parameterized queries
cur.execute("SELECT * FROM users WHERE username = %s", (username,))

Basic SQL Operations

Basic SQL Operations

Executing SQL Queries

Creating Tables

INSERT Operations

SELECT Operations

UPDATE and DELETE