Pythonpostgres_advanced.py
Copy
1
import psycopg2
2
import json
3
 
4
conn = psycopg2.connect("host=localhost dbname=learndb user=postgres")
5
cur = conn.cursor()
6
 
7
# Setup
8
cur.execute("DROP TABLE IF EXISTS employees CASCADE;")
9
cur.execute("""
10
CREATE TABLE employees (
11
    id         SERIAL PRIMARY KEY,
12
    name       TEXT NOT NULL,
13
    department TEXT NOT NULL,
14
    salary     NUMERIC(10,2) NOT NULL,
15
    manager_id INTEGER REFERENCES employees(id),
16
    metadata   JSONB DEFAULT '{}'::JSONB
17
);
18
""")
19
 
20
# Seed
21
seed = [
22
    (1, "Alice CEO", "Executive", 200000, None, {"level": "C", "skills": ["strategy"]}),
23
    (2, "Bob VP Eng", "Engineering", 150000, 1, {"level": "VP", "skills": ["python", "aws"]}),
24
    (3, "Carol Mgr", "Engineering", 120000, 2, {"level": "M", "skills": ["python"]}),
25
    (4, "Dave Dev", "Engineering", 90000, 3, {"level": "IC", "skills": ["python", "js"]}),
26
    (5, "Eve Dev", "Engineering", 85000, 3, {"level": "IC", "skills": ["java"]}),
27
    (6, "Frank VP Mktg", "Marketing", 140000, 1, {"level": "VP", "skills": ["marketing"]}),
28
    (7, "Grace Mgr", "Marketing", 110000, 6, {"level": "M", "skills": ["seo", "analytics"]}),
29
]
30
cur.executemany(
31
    "INSERT INTO employees (id, name, department, salary, manager_id, metadata) "
32
    "VALUES (%s, %s, %s, %s, %s, %s::jsonb)",
33
    [(r[0], r[1], r[2], r[3], r[4], json.dumps(r[5])) for r in seed],
34
)
35
conn.commit()
36
 
37
# Recursive CTE: org chart
38
cur.execute("""
39
WITH RECURSIVE org AS (
40
    SELECT id, name, department, manager_id, 0 AS depth, name AS path
41
    FROM employees
42
    WHERE manager_id IS NULL
43
    UNION ALL
44
    SELECT e.id, e.name, e.department, e.manager_id, org.depth + 1,
45
           org.path || ' > ' || e.name
46
    FROM employees e
47
    JOIN org ON org.id = e.manager_id
48
)
49
SELECT depth, path FROM org ORDER BY path;
50
""")
51
print("Org chart:")
52
for row in cur.fetchall():
53
    print("  " + "  " * row[0] + row[1])
54
 
55
# Window function: salary rank per department
56
cur.execute("""
57
SELECT name, department, salary,
58
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
59
       LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS prev_salary
60
FROM employees;
61
""")
62
print("\nDepartment salary ranks:")
63
for row in cur.fetchall():
64
    print(f"  {row[1]}: {row[0]} | ${row[2]} | rank {row[3]}")
65
 
66
# JSONB query
67
cur.execute("CREATE INDEX IF NOT EXISTS emp_meta_idx ON employees USING gin(metadata);")
68
cur.execute("SELECT name FROM employees WHERE metadata @> '{"level": "IC"}'::jsonb;")
69
print("\nIC-level employees:", [r[0] for r in cur.fetchall()])
70
 
71
cur.close()
72
conn.close()
73