text-to-sql-explorer/setup_turso_db.py at main · Assyrian91/text-to-sql-explorer · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
"""
Sets up the Turso (libSQL/SQLite) database for the Text-to-SQL portfolio project.
Creates two tables: suburb_prices (yearly median prices) and suburb_cagr (growth summary).
Run this once to initialize and seed the database.

Uses libsql-client (pure Python, HTTP-based, no compilation required).
"""

import os
import asyncio
import libsql_client
from dotenv import load_dotenv

load_dotenv()

TURSO_DATABASE_URL = os.getenv("TURSO_DATABASE_URL")
TURSO_AUTH_TOKEN = os.getenv("TURSO_AUTH_TOKEN")

# libsql_client wants a URL that starts with https:// or wss://, not libsql://
DB_URL = TURSO_DATABASE_URL.replace("libsql://", "https://")


async def main():
    client = libsql_client.create_client(
        url=DB_URL,
        auth_token=TURSO_AUTH_TOKEN
    )

    # ---------- Schema ----------

    await client.execute("""
    CREATE TABLE IF NOT EXISTS suburb_prices (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        suburb TEXT NOT NULL,
        year INTEGER NOT NULL,
        median_price REAL NOT NULL
    )
    """)

    await client.execute("""
    CREATE TABLE IF NOT EXISTS suburb_cagr (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        suburb TEXT NOT NULL,
        start_price REAL NOT NULL,
        end_price REAL NOT NULL,
        start_year INTEGER NOT NULL,
        end_year INTEGER NOT NULL,
        cagr_pct REAL NOT NULL
    )
    """)

    print("Tables created.")

    # ---------- Seed data: suburb_cagr (from your real Project 9 figures) ----------

    cagr_data = [
        ("Mickleham", 168000, 710300, 2014, 2026, 12.29),
        ("Kalkallo", 180000, 650100, 2014, 2026, 12.22),
        ("Beveridge", 351500, 642000, 2014, 2026, 6.04),
        ("Craigieburn", 362000, 730000, 2014, 2026, 5.13),
        ("Wallan", 356000, 640000, 2014, 2026, 4.73),
        ("Roxburgh Park", 385000, 630000, 2014, 2026, 4.59),
        ("Donnybrook", 530000, 650000, 2014, 2026, 3.46),
    ]

    await client.execute("DELETE FROM suburb_cagr")
    for row in cagr_data:
        await client.execute(
            "INSERT INTO suburb_cagr (suburb, start_price, end_price, start_year, end_year, cagr_pct) VALUES (?, ?, ?, ?, ?, ?)",
            row
        )

    # ---------- Seed data: suburb_prices (yearly snapshots, lean version) ----------

    years_snapshot = [2014, 2018, 2020, 2022, 2024, 2026]

    price_rows = []
    for suburb, start_price, end_price, start_year, end_year, _ in cagr_data:
        for y in years_snapshot:
            fraction = (y - start_year) / (end_year - start_year)
            price = round(start_price + (end_price - start_price) * fraction, 0)
            price_rows.append((suburb, y, price))

    await client.execute("DELETE FROM suburb_prices")
    for row in price_rows:
        await client.execute(
            "INSERT INTO suburb_prices (suburb, year, median_price) VALUES (?, ?, ?)",
            row
        )

    print(f"Seeded {len(cagr_data)} CAGR rows and {len(price_rows)} price rows.")

    # ---------- Verify ----------

    result = await client.execute("SELECT COUNT(*) FROM suburb_prices")
    print(f"suburb_prices row count: {result.rows[0][0]}")

    result = await client.execute("SELECT COUNT(*) FROM suburb_cagr")
    print(f"suburb_cagr row count: {result.rows[0][0]}")

    await client.close()
    print("Done.")


if __name__ == "__main__":
    asyncio.run(main())