SQL with Python

Introduction

Python can interact with databases using various libraries. SQLite comes built-in with Python.

Basic SQLite Operations

import sqlite3

# Create connection
conn = sqlite3.connect("database.db")
cursor = conn.cursor()

# Create table
cursor.execute("""
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        name TEXT,
        email TEXT
    )
""")

# Insert data
cursor.execute("INSERT INTO users VALUES (1, 'Alice', 'alice@email.com')")

# Commit and close
conn.commit()
conn.close()

Querying Data

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

# Fetch all
cursor.execute("SELECT * FROM users")
all_users = cursor.fetchall()

# Fetch one
cursor.execute("SELECT * FROM users WHERE id = 1")
user = cursor.fetchone()

# With parameters (safe from SQL injection)
cursor.execute("SELECT * FROM users WHERE name = ?", (name,))

Using Pandas with SQLite

df = pd.read_sql("SELECT * FROM users", conn)
df.to_sql("new_table", conn, if_exists="replace")

Practice Problems

1. Create database for a library system
2. Insert multiple records efficiently
3. Query with WHERE clauses
4. Update and delete records
5. Join tables in SQL