Spaces:
Sleeping
Sleeping
File size: 4,959 Bytes
df6b63c | 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 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 | """
Shared DuckDB connection and schema initialization for CommodiSense.
All collectors import get_conn() from here.
"""
import duckdb
from pathlib import Path
DB_PATH = Path(__file__).parent / "commodisense.duckdb"
def get_conn() -> duckdb.DuckDBPyConnection:
"""Return a persistent DuckDB connection. Creates the DB file if missing."""
return duckdb.connect(str(DB_PATH))
def init_schema() -> None:
"""Create all tables if they don't already exist. Safe to call repeatedly."""
conn = get_conn()
conn.execute("""
CREATE TABLE IF NOT EXISTS prices (
date DATE NOT NULL,
symbol TEXT NOT NULL,
open DOUBLE,
high DOUBLE,
low DOUBLE,
close DOUBLE,
volume DOUBLE,
adj_close DOUBLE,
PRIMARY KEY (date, symbol)
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS news_raw (
id TEXT PRIMARY KEY,
source TEXT,
published_date TIMESTAMP,
title TEXT,
summary TEXT,
url TEXT,
commodity_tags TEXT,
sentiment_score DOUBLE,
processed BOOLEAN DEFAULT FALSE
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS weather_features (
date DATE NOT NULL,
region TEXT NOT NULL,
commodity TEXT NOT NULL,
temp_max DOUBLE,
temp_min DOUBLE,
precipitation DOUBLE,
soil_moisture DOUBLE,
drought_index DOUBLE,
heat_stress_days INTEGER,
precip_anomaly_pct DOUBLE,
PRIMARY KEY (date, region, commodity)
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS geopolitical_events (
date DATE NOT NULL,
event_type TEXT,
region TEXT,
commodity TEXT,
risk_score DOUBLE,
headline TEXT,
source TEXT
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS sentiment_daily (
date DATE NOT NULL,
commodity TEXT NOT NULL,
sentiment_score DOUBLE,
article_count INTEGER,
positive_count INTEGER,
negative_count INTEGER,
PRIMARY KEY (date, commodity)
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS extracted_events (
date DATE,
headline TEXT,
event_type TEXT,
commodity TEXT,
location TEXT,
severity INTEGER,
direction TEXT,
source TEXT
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS accuracy_log (
date DATE,
symbol TEXT,
forecast_direction TEXT,
actual_direction TEXT,
was_correct BOOLEAN,
confidence TEXT
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS cot_data (
date DATE NOT NULL,
symbol TEXT NOT NULL,
commercial_net_long DOUBLE,
commercial_net_pct DOUBLE,
mm_net_long DOUBLE,
mm_net_pct DOUBLE,
commercial_chg_1w DOUBLE,
mm_chg_1w DOUBLE,
open_interest DOUBLE,
PRIMARY KEY (date, symbol)
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS fred_data (
date DATE NOT NULL PRIMARY KEY,
dxy DOUBLE,
inflation_exp DOUBLE,
vix DOUBLE,
treasury_10y DOUBLE,
financial_stress DOUBLE,
indpro DOUBLE,
fedfunds DOUBLE
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS eia_inventory (
date DATE NOT NULL,
series TEXT NOT NULL,
value DOUBLE,
chg_1w DOUBLE,
vs_5yr_avg DOUBLE,
PRIMARY KEY (date, series)
)
""")
conn.execute("""
CREATE TABLE IF NOT EXISTS usda_crop (
date DATE NOT NULL,
commodity TEXT NOT NULL,
metric TEXT NOT NULL,
value DOUBLE,
yoy_chg_pct DOUBLE,
PRIMARY KEY (date, commodity, metric)
)
""")
conn.close()
if __name__ == "__main__":
init_schema()
print(f"Schema initialized at {DB_PATH}")
|