prazy1208/text2sql

1"""2Create app_schema and tables (sessions, intent_agent_output, table_agent_output,3column_agent_output, few_shot_agent_output, gen_sql_agent_output) in text2sql_db.4Uses .env from project root: DATABASE_URL or DB_HOST, DB_PORT, DB_USER, DB_PASSWORD, DB_NAME.5Run from project root: python scripts/run_create_app_schema.py6"""7 8import os9import sys10from pathlib import Path11 12# Project root = parent of scripts/13PROJECT_ROOT = Path(__file__).resolve().parent.parent14if str(PROJECT_ROOT) not in sys.path:15    sys.path.insert(0, str(PROJECT_ROOT))16 17os.chdir(PROJECT_ROOT)18 19from dotenv import load_dotenv20from sqlalchemy import create_engine21 22load_dotenv()23 24 25def get_engine():26    """Build SQLAlchemy engine from .env (same pattern as build_vector_store.py)."""27    database_url = os.getenv("DATABASE_URL")28    if database_url:29        return create_engine(database_url)30    host = os.getenv("DB_HOST", "localhost")31    port = os.getenv("DB_PORT", "5432")32    user = os.getenv("DB_USER", "postgres")33    password = os.getenv("DB_PASSWORD", "")34    dbname = os.getenv("DB_NAME", "text2sql_db")35    url = f"postgresql://{user}:{password}@{host}:{port}/{dbname}"36    return create_engine(url)37 38 39def main():40    sql_file = PROJECT_ROOT / "scripts" / "create_app_schema.sql"41    if not sql_file.exists():42        print(f"SQL file not found: {sql_file}")43        sys.exit(1)44 45    sql = sql_file.read_text(encoding="utf-8")46    engine = get_engine()47    # Use raw connection so multiple statements in one execute() work (psycopg2 supports this)48    raw_conn = engine.raw_connection()49    try:50        cur = raw_conn.cursor()51        cur.execute(sql)52        raw_conn.commit()53    finally:54        raw_conn.close()55 56    print("Done. app_schema and agent output tables (including gen_sql_agent_output) applied.")57 58 59if __name__ == "__main__":60    main()61