pgs.py — Database Layer

Thin wrapper around psycopg2 and SQLAlchemy providing connection management and CRUD operations used by all other modules.

---

Functions

Connection

conn(auto=True) → psycopg2.connection

Opens a direct psycopg2 connection to the application database (Config.DB_NAME).

con = pgs.conn()          # autocommit = True
con = pgs.conn(auto=False) # manual commit required

Reads config from Config.DB_HOST, Config.DB_PORT, Config.DB_NAME, Config.DB_USER, Config.DB_PASS.

Use 127.0.0.1 not localhost on VPS

When PostgreSQL runs in Docker, localhost triggers Unix socket lookup which fails. Set VPS_DB_HOST=127.0.0.1 in backend/.env.

database_conn(dbname, auto=True) → psycopg2.connection

Same as conn() but accepts dbname as a parameter. Useful for connecting to databases other than the default.

Read

read_sql(sql, ...) → DataFrame

Executes a SQL query via SQLAlchemy and returns a pandas DataFrame.

df = pgs.read_sql("SELECT masid, mname FROM scr_master WHERE typ = 1")
df = pgs.read_sql("SELECT * FROM scr_nseeq_eod WHERE sdate = :dt",
                  params={"dt": "2026-05-22"})

Accepts all parameters of pd.read_sql_query (index_col, parse_dates, chunksize, etc.). Internally creates a fresh SQLAlchemy engine per call — no connection pooling.

getsqlValue(sql, all=True) → tuple | None

Lightweight single-value query using psycopg2. Returns fetchall() or fetchone().

# Single value (all=False → fetchone)
result = pgs.getsqlValue("SELECT MAX(sdate) FROM scr_nseeq_eod", False)
max_date = result[0] if result else None

# Multiple rows (all=True → fetchall)
rows = pgs.getsqlValue("SELECT masid, mname FROM scr_master WHERE typ=1")

Write

insertdataframe(connection, df, table) → bool

Bulk-inserts a DataFrame into table using psycopg2.extras.execute_values (page_size=2000 for efficiency). Rolls back on error.

pgs.insertdataframe(conn, df, 'scr_master')

Also accepts a list of dicts instead of a DataFrame.

updatetable_from_df(connection, df, table, deleteSQL="", fdate="", tdate="") → bool

Delete-then-insert pattern:

Used for daily upsert: delete the day's existing rows, then insert fresh data.

update_or_delete_sql(mSql, connection) → bool

Executes a raw UPDATE or DELETE statement. Commits on success, rolls back on error.

Schema Check

checktableexists(connection, tName) → bool

Checks information_schema.tables for table existence.

checktables(connection)

Checks for categories table and creates it from keywords.csv if missing. Legacy function — likely unused in current codebase. See Junk Analysis.

---

Connection Architecture

Note: psycopg2 is used for writes (insert/update/delete) and single-value reads. SQLAlchemy is used only for read_sql (pandas DataFrames).

---

Frequently Used Tables Reference

scr_master — Security Master

Column	Type	Description
masid	SERIAL PK	Auto-increment ID
mname	VARCHAR	Symbol name (e.g., NIFTY, RELIANCE)
typ	INT	1=EQ, 2=Index, 3=FUTIDX, 4=FUTSTK, 5=OPTIDX, 6=OPTSTK
underly	INT	FK → masid of underlying (for F&O)
edate	VARCHAR	Expiry string (DDMONYY format)
monthindex	INT	0=spot, 1/-I=near month, 2/-II=mid, 3/-III=far
strike	FLOAT	Strike price (options only)
opttyp	VARCHAR	CE or PE (options only)

scr_nseeq_eod — Equity EOD

Column	Type	Description
masid	INT FK	→ scr_master
sdate	DATE	Trading date
sopen	FLOAT	Open price
shigh	FLOAT	High price
slow	FLOAT	Low price
sclose	FLOAT	Close price
svolume	FLOAT	Volume (shares traded)
svalue	FLOAT	Turnover value
slast	FLOAT	Last traded price
prevclose	FLOAT	Previous close
delqty	FLOAT	Deliverable quantity
avgprice	FLOAT	Average price (svalue/svolume)
f5min	FLOAT	First 5-min OHLCV (reserved)
f15min	FLOAT	First 15-min (reserved)
l5min	FLOAT	Last 5-min (reserved)
l15min	FLOAT	Last 15-min (reserved)

scr_nsefo_eod — F&O EOD

Column	Type	Description
masid	INT FK	→ scr_master
sdate	DATE	Trading date
sopen/shigh/slow/sclose	FLOAT	OHLC
svolume	FLOAT	Contracts traded
svaluel	FLOAT	Value in lakhs
oi	FLOAT	Open interest
coi	FLOAT	Change in open interest
avgprice	FLOAT	Average price
lotsize	INT	Lot size (reserved, default 0)

scr_indexpepb — Index PE/PB

Column	Type	Description
masid	INT FK	→ scr_master (index only)
sdate	DATE	Date
pe	FLOAT	Price-to-Earnings ratio
pb	FLOAT	Price-to-Book ratio
divyie	FLOAT	Dividend yield

tasks — Retry Queue

Column	Type	Description
tid	SERIAL PK	Task ID
typ	INT	1=IndexTask, 2=EquityTask, 3=DelVolTask, 4=NSESiteTask, 5=FOTask
notes	TEXT	Description
taskobj	JSONB	{"YYYY-MM-DD": {"retries": N, "lasttry": "YYYY-MM-DD"}}

holidays

Column	Type	Description
sdate	DATE	Holiday date
exch	INT	Exchange (1=NSE)
details	TEXT	Holiday name/description

specialdays — Muhurat Trading

Column	Type	Description
sdate	DATE	Muhurat date
exch	INT	1=NSE
starttime	TIME	Session start
endtime	TIME	Session end
details	TEXT	Description