SQL: getting data out of a database

import pandas as pd
import sqlite3

df = pd.read_csv(DATA / "uy_ijara.csv")

# Way 1: pandas, which first loads EVERYTHING into memory
result_pandas = df.groupby("tuman").narx_usd.mean().round(1).sort_values(ascending=False)
print("pandas:")
print(result_pandas.to_string())

show(pd.DataFrame([
    {"": "pandas", "How it works": "Loads the whole table INTO MEMORY, then computes",
     "On 40M rows": "Runs out of memory or the computer freezes"},
    {"": "SQL", "How it works": "Computes INSIDE THE DATABASE, only the result comes back",
     "On 40M rows": "A few seconds, 8 rows come back"},
]))

db = sqlite3.connect(":memory:")              # an in-memory database (no file needed)
df.to_sql("rentals", db, index=False, if_exists="replace")

# a second table, needed for JOIN
districts = pd.DataFrame([
    {"tuman": "Yunusobod", "metro_line": "Yunusobod", "population_k": 265},
    {"tuman": "Chilonzor", "metro_line": "Chilonzor", "population_k": 245},
    {"tuman": "Mirzo Ulug'bek", "metro_line": "Chilonzor", "population_k": 215},
    {"tuman": "Shayxontohur", "metro_line": "O'zbekiston", "population_k": 180},
    {"tuman": "Yakkasaroy", "metro_line": "O'zbekiston", "population_k": 115},
    {"tuman": "Olmazor", "metro_line": "Chilonzor", "population_k": 290},
    {"tuman": "Sergeli", "metro_line": "Sergeli", "population_k": 195},
    # NOTE: Bektemir is MISSING, we need that for the LEFT JOIN lesson
])
districts.to_sql("districts", db, index=False, if_exists="replace")

print("Tables:", pd.read_sql("SELECT name FROM sqlite_master WHERE type='table'", db).name.tolist())
print(f"rentals   : {pd.read_sql('SELECT COUNT(*) n FROM rentals', db).n[0]} rows")
print(f"districts : {pd.read_sql('SELECT COUNT(*) n FROM districts', db).n[0]} rows")

def query(sql):
    """Runs the SQL and returns the result as a DataFrame."""
    return pd.read_sql(sql, db)

show(query("SELECT tuman, xonalar, maydon_m2, narx_usd FROM rentals LIMIT 5"))

show(pd.DataFrame([
    {"SQL": "SELECT col1, col2", "In plain English": "GIVE ME these columns"},
    {"SQL": "FROM table", "In plain English": "From this table"},
    {"SQL": "WHERE condition", "In plain English": "Only the rows that match this condition"},
    {"SQL": "ORDER BY col DESC", "In plain English": "Sorted by this column, largest first"},
    {"SQL": "LIMIT 10", "In plain English": "Only the first 10"},
]))

show(query("""
    SELECT tuman, xonalar, maydon_m2, metro_km, narx_usd
    FROM rentals
    WHERE tuman = 'Yunusobod'
      AND xonalar = 2
      AND mebel = 1
    ORDER BY narx_usd DESC
    LIMIT 6
"""))

show(query("""
    SELECT
        COUNT(*)                                        AS total,
        SUM(CASE WHEN narx_usd BETWEEN 400 AND 700 THEN 1 ELSE 0 END) AS mid_price,
        SUM(CASE WHEN tuman IN ('Sergeli', 'Bektemir') THEN 1 ELSE 0 END) AS outer_district,
        SUM(CASE WHEN qurilgan_yil IS NULL THEN 1 ELSE 0 END)          AS no_year
    FROM rentals
"""))

print("Proof:")
print("  = NULL  result:", query("SELECT COUNT(*) n FROM rentals WHERE qurilgan_yil = NULL").n[0], "rows")
print("  IS NULL result:", query("SELECT COUNT(*) n FROM rentals WHERE qurilgan_yil IS NULL").n[0], "rows")

show(query("""
    SELECT
        tuman,
        COUNT(*)              AS listings,
        ROUND(AVG(narx_usd))  AS avg_price,
        MIN(narx_usd)         AS cheapest,
        MAX(narx_usd)         AS priciest,
        ROUND(AVG(ijaraga_berildi_30k) * 100, 1) AS fast_rent_pct
    FROM rentals
    GROUP BY tuman
    ORDER BY avg_price DESC
"""))

show(query("""
    SELECT xonalar, COUNT(*) AS n, ROUND(AVG(narx_usd)) AS average
    FROM rentals
    GROUP BY xonalar
    HAVING COUNT(*) > 100
    ORDER BY xonalar
"""))

show(query("""
    SELECT
        r.tuman,
        d.metro_line,
        d.population_k,
        COUNT(*)             AS listings,
        ROUND(AVG(r.narx_usd)) AS avg_price
    FROM rentals r
    INNER JOIN districts d ON r.tuman = d.tuman
    GROUP BY r.tuman, d.metro_line, d.population_k
    ORDER BY avg_price DESC
"""))

print("districts in rentals  :", query("SELECT COUNT(DISTINCT tuman) n FROM rentals").n[0])
print("rows in districts     :", query("SELECT COUNT(*) n FROM districts").n[0])
print("after the INNER JOIN  :", len(query("SELECT DISTINCT r.tuman FROM rentals r INNER JOIN districts d ON r.tuman=d.tuman")))
print()
print("The district that went missing:")
show(query("""
    SELECT r.tuman, COUNT(*) AS listings
    FROM rentals r
    LEFT JOIN districts d ON r.tuman = d.tuman
    WHERE d.tuman IS NULL
    GROUP BY r.tuman
"""))

show(pd.DataFrame([
    {"JOIN type": "INNER JOIN", "What it keeps": "Only rows that match in both"},
    {"JOIN type": "LEFT JOIN", "What it keeps": "EVERY row on the LEFT + the matches (NULL if none)"},
    {"JOIN type": "RIGHT JOIN", "What it keeps": "EVERY row on the RIGHT (not in SQLite: swap the tables)"},
    {"JOIN type": "FULL OUTER JOIN", "What it keeps": "Every row from both"},
]))

print("With LEFT JOIN every district is kept:")
show(query("""
    SELECT r.tuman,
           COALESCE(d.metro_line, 'no metro') AS metro_line,
           COUNT(*) AS listings
    FROM rentals r
    LEFT JOIN districts d ON r.tuman = d.tuman
    GROUP BY r.tuman, d.metro_line
    ORDER BY listings DESC
"""))

show(query("""
    SELECT
        tuman,
        xonalar,
        narx_usd,
        ROUND(AVG(narx_usd) OVER (PARTITION BY tuman))            AS district_avg,
        narx_usd - ROUND(AVG(narx_usd) OVER (PARTITION BY tuman)) AS diff,
        RANK() OVER (PARTITION BY tuman ORDER BY narx_usd DESC)   AS rank_in_district
    FROM rentals
    WHERE tuman IN ('Yunusobod', 'Sergeli')
    ORDER BY tuman, rank_in_district
    LIMIT 10
"""))

show(pd.DataFrame([
    {"Task": "Pull 10 thousand rows out of 40 million", "Which one": "SQL",
     "Why": "It won't all fit in memory"},
    {"Task": "Grouping, aggregating, JOIN", "Which one": "SQL",
     "Why": "The database does it fast with indexes"},
    {"Task": "Drawing a chart", "Which one": "pandas",
     "Why": "SQL doesn't draw charts"},
    {"Task": "Training a model", "Which one": "pandas",
     "Why": "sklearn expects a DataFrame/array"},
    {"Task": "Complex cleaning, apply", "Which one": "pandas",
     "Why": "You can use Python functions"},
]))

# Pull ONLY the part we need from the database: this is the real-project pattern
for_model = query("""
    SELECT r.xonalar, r.maydon_m2, r.metro_km, r.mebel, r.tuman,
           COALESCE(d.population_k, 0) AS population_k,
           r.ijaraga_berildi_30k
    FROM rentals r
    LEFT JOIN districts d ON r.tuman = d.tuman
    WHERE r.narx_usd BETWEEN 200 AND 1500
""")
print(f"From the database: {for_model.shape[0]} rows, {for_model.shape[1]} columns")
print()
show(for_model.head(5))

show(pd.DataFrame([
    {"Command": "SELECT / FROM / WHERE", "What it does": "Choose columns and rows"},
    {"Command": "IS NULL", "What it does": "Check for missing values (= NULL doesn't work)"},
    {"Command": "GROUP BY + AVG/COUNT", "What it does": "Compute per group"},
    {"Command": "HAVING", "What it does": "Filter groups (WHERE filters rows)"},
    {"Command": "INNER JOIN", "What it does": "Connects the matches, and LOSES rows"},
    {"Command": "LEFT JOIN", "What it does": "Keeps everything on the left"},
    {"Command": "OVER (PARTITION BY)", "What it does": "Keeps the row and adds a group calculation"},
]))