16 - Database - Python

53 min read

16. Database - Python

Quote

“Future users of large data banks must be protected from having to know how the data is organized in the machine.”

— Edgar F. Codd, A Relational Model of Data for Large Shared Data Banks (1970)

Setup imports and notebook display helpers.

import os
import sqlite3
import pyodbc
import pandas as pd
import urllib.parse
import duckdb
import polars as pl
from sqlalchemy import create_engine, text
from sqlalchemy.orm import Session, DeclarativeBase, Mapped, mapped_column
from datetime import date as Date, datetime
import time
 
DATA = "C:/Users/aperi/DEV/LANG/data"
 
# Polars: strip quotes from string values in HTML display
_html_fmt = get_ipython().display_formatter.formatters["text/html"]
_html_fmt.for_type(pl.DataFrame, lambda df: df.to_pandas().style.hide(axis="index").to_html())

SQLite — Built-in Embedded Database

Connection, Schema, and CRUD

SQLite — connect and CREATE TABLE

SQLite basics

  • sqlite3.connect(":memory:") — in-memory; or pass a file path
  • cursor.execute(sql, params) — ? placeholders for parameterized queries
  • Context manager (with conn:) — auto-commits on success, rolls back on exception
  • Built-in — no pip install, no server
  • Use for tests, prototyping, local caches; for concurrent access, use SQL Server or PostgreSQL

SQL injection

Never use f-strings in SQL — always use ? parameter placeholders.

Use parameterized queries

Example: Use parameterized queries.

# Safe: parameter placeholder
cur.execute("SELECT * FROM trades WHERE ticker = ?", (ticker,))
# Safe: multiple parameters
cur.execute("INSERT INTO trades VALUES (?, ?, ?)", (id, ticker, price))

Example: SQLite — connect and CREATE TABLE.

conn = sqlite3.connect(":memory:")
conn.row_factory = sqlite3.Row  # dict-like row access
cur = conn.cursor()
 
cur.execute("""
    CREATE TABLE trades (
        trade_id   TEXT PRIMARY KEY,
        ticker     TEXT NOT NULL,
        side       TEXT NOT NULL CHECK(side IN ('BUY', 'SELL')),
        quantity   INTEGER NOT NULL CHECK(quantity > 0),
        price      REAL NOT NULL CHECK(price > 0),
        trade_date TEXT NOT NULL DEFAULT (Date('now'))
    )""")
print(cur.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='trades'").fetchone()[0])
trades

SQLite — INSERT with ? parameterised queries

executemany() bulk-inserts a list of tuples in a single call. Use ? placeholders for every user-supplied value — never build SQL strings with f-strings. conn.commit() persists the inserts to disk.

Example: SQLite — INSERT with ? parameterised queries.

trades = [
    ("TRD_001", "ASML.AS", "BUY",  100, 685.40, "2026-03-15"),
    ("TRD_002", "MC.PA",   "BUY",   50, 890.20, "2026-03-15"),
    ("TRD_003", "SAP.DE",  "SELL",  75, 245.80, "2026-03-15"),
    ("TRD_004", "ASML.AS", "SELL",  30, 690.00, "2026-03-16"),
    ("TRD_005", "RMS.PA",  "BUY",   20, 2850.0, "2026-03-16"),
    ("TRD_006", "SIE.DE",  "BUY",  200, 198.50, "2026-03-17"),
]
cur.executemany("INSERT INTO trades VALUES (?,?,?,?,?,?)", trades)
conn.commit()
print(len(trades))
6

SQLite — SELECT into pandas DataFrame

cursor.fetchall() loads the entire result

cursor.fetchall() loads the entire result set into memory For large tables (millions of rows), fetchall() or pd.read_sql() without a WHERE/LIMIT clause can exhaust RAM and crash your process. Use fetchmany(batch_size) for streaming, or push filtering to SQL with WHERE/LIMIT. For analytics, prefer DuckDB which streams columnar data efficiently.

Stream large results with fetchmany or push filtering to SQL

Example: Stream large results with fetchmany or push filtering to SQL.

# Stream in batches instead of loading all rows
cur.execute("SELECT * FROM trades")
while batch := cur.fetchmany(1000):
    process(batch)
# Or push filtering to SQL
cur.execute("SELECT * FROM trades WHERE trade_date >= ?", ("2024-01-01",))

Example: SQLite — SELECT into pandas DataFrame.

# SELECT — display as pandas DataFrame
 
pd.read_sql("SELECT *, quantity * price AS notional FROM trades ORDER BY trade_date, trade_id", conn)
trade_idtickersidequantitypricetrade_datenotional
0TRD_001ASML.ASBUY100685.42026-03-1568540.0
1TRD_002MC.PABUY50890.22026-03-1544510.0
2TRD_003SAP.DESELL75245.82026-03-1518435.0
3TRD_004ASML.ASSELL30690.02026-03-1620700.0
4TRD_005RMS.PABUY202850.02026-03-1657000.0
5TRD_006SIE.DEBUY200198.52026-03-1739700.0

SQLite — SELECT with WHERE parameter

Pass a parameter tuple to params= in pd.read_sql() to safely inject filter values into a WHERE clause. The ? placeholder is substituted by the underlying cursor, preventing SQL injection.

Example: SQLite — SELECT with WHERE parameter.

pd.read_sql("SELECT * FROM trades WHERE ticker = ?", conn, params=("ASML.AS",))
trade_idtickersidequantitypricetrade_date
0TRD_001ASML.ASBUY100685.42026-03-15
1TRD_004ASML.ASSELL30690.02026-03-16

SQLite — aggregate with GROUP BY

GROUP BY with conditional aggregation computes net position per ticker in a single pass. SUM(CASE WHEN side='BUY' THEN quantity ELSE -quantity END) is more efficient than two separate queries.

Example: SQLite — aggregate with GROUP BY.

pd.read_sql("""
    SELECT ticker,
           SUM(CASE WHEN side='BUY' THEN quantity ELSE -quantity END) AS net_shares,
           ROUND(SUM(CASE WHEN side='BUY' THEN quantity*price ELSE -quantity*price END), 2) AS net_notional,
           COUNT(*) AS trade_count
    FROM trades GROUP BY ticker ORDER BY net_notional DESC""", conn)
tickernet_sharesnet_notionaltrade_count
0RMS.PA2057000.01
1ASML.AS7047840.02
2MC.PA5044510.01
3SIE.DE20039700.01
4SAP.DE-75-18435.01

SQLite — UPDATE and DELETE

cursor.rowcount returns the number of rows affected after an UPDATE or DELETE. Changes are not persisted until conn.commit() is called (or the connection context manager exits successfully).

Example: SQLite — UPDATE and DELETE.

cur.execute("UPDATE trades SET price = ? WHERE trade_id = ?", (700.00, "TRD_004"))
cur.rowcount  # UPDATE rows affected
 
cur.execute("DELETE FROM trades WHERE trade_id = ?", ("TRD_006",))
cur.rowcount  # DELETE rows affected
conn.commit()
1 row
1 row

Transactions

SQLite — transaction with context manager

Using with conn: as a context manager automatically commits on success and rolls back on any exception — without needing an explicit conn.commit() call. This is the safest way to group multiple DML statements atomically.

Example: SQLite — transaction with context manager.

try:
    with conn:
        conn.execute("INSERT INTO trades VALUES (?,?,?,?,?,?)",
            ("TRD_007", "TTE.PA", "BUY", 150, 58.30, Date.today().isoformat()))
        conn.execute("INSERT INTO trades VALUES (?,?,?,?,?,?)",
            ("TRD_008", "BNP.PA", "BUY", 80, 72.10, Date.today().isoformat()))
    print("Transaction committed (2 trades)")
except Exception as e:
    print(f"Transaction rolled back: {e}")
 
conn.execute('SELECT COUNT(*) FROM trades').fetchone()[0]  # Total trades
Transaction committed (2 trades)
7

PRAGMA and Indexes

SQLite — PRAGMA settings for performance

PRAGMA statements configure SQLite behavior per connection. WAL mode allows concurrent readers while a writer is active. synchronous=NORMAL reduces fsync calls for a significant write speed boost with minimal durability risk. Settings do not persist — they must be re-applied each time a connection is opened.

Example: SQLite — PRAGMA settings for performance.

for pragma, value in [
    ("journal_mode", "WAL"),
    ("synchronous", "NORMAL"),
    ("cache_size", "-20000"),
    ("busy_timeout", "5000"),
    ("foreign_keys", "ON"),
]:
    cur.execute(f"PRAGMA {pragma}={value}")
    result = cur.execute(f"PRAGMA {pragma}").fetchone()[0]
    print(f"  {pragma:20s} = {result}")
  journal_mode         = memory
  synchronous          = 1
  cache_size           = -20000
  busy_timeout         = 5000
  foreign_keys         = 1

SQLite — CREATE INDEX and EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN shows whether SQLite uses a full table scan or an index seek for a given query. Use it to verify that indexes are being picked up — the output changes from SCAN trades to SEARCH trades USING INDEX once the index exists.

Example: SQLite — CREATE INDEX and EXPLAIN QUERY PLAN.

cur.execute("CREATE INDEX IF NOT EXISTS idx_trades_ticker ON trades(ticker)")
print(cur.execute("SELECT name FROM sqlite_master WHERE type='index' AND name='idx_trades_ticker'").fetchone()[0])
 
plan = cur.execute("EXPLAIN QUERY PLAN SELECT * FROM trades WHERE ticker = 'ASML.AS'").fetchall()
for row in plan:
    print(f"  {dict(row)}")
 
conn.close()
idx_trades_ticker
  {'id': 3, 'parent': 0, 'notused': 0, 'detail': 'SEARCH trades USING INDEX idx_trades_ticker (ticker=?)'}

SQL Server — pyodbc (ODBC Driver 18)

The SQL patterns used below (parameterised queries, window functions, CTEs) follow the same T-SQL dialect covered in sql-fundamentals. For how connection pooling interacts with SQL Server lock behavior under concurrent writes, see blocking-and-locking.

Connection and CRUD

SQL Server — connect and list schemas/tables

Connection pool exhaustion

Connection pool exhausting — always close connections pyodbc.connect() without with or explicit .close() leaks connections. SQL Server defaults to a max pool of 100 connections — once exhausted, new connections block or fail with timeout errors. Always use with conn: or wrap in try/finally. For SQLAlchemy, engine.dispose() reclaims all pooled connections.

Always close connections with a context manager or try/finally

Example: Always close connections with a context manager or try/finally.

# Preferred: context manager auto-closes on exit
with pyodbc.connect(conn_str) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT 1")
# Alternative: explicit close in finally
conn = pyodbc.connect(conn_str)
try:
    ...
finally:
    conn.close()

SQL Server connection pattern

  • pyodbc.connect() — direct cursor for DML (fast, rowcount available)
  • SQLAlchemy create_engine() — for pd.read_sql() (avoids DBAPI2 warnings, adds connection pooling)
  • Both use ODBC Driver 18 underneath
  • For ORM scenarios, use SQLAlchemy ORM instead

Set SQLSERVER_STOXX_ODBC to the full ODBC connection string before running the SQL Server cells. Use Windows authentication or a secret-managed SQL login instead of embedding credentials in the note body.

Example: SQL Server — connect and list schemas/tables.

import urllib.parse
 
conn_str = os.environ["SQLSERVER_STOXX_ODBC"]
 
# pyodbc connection for DML (INSERT/UPDATE/DELETE)
sql_conn = pyodbc.connect(conn_str)
cur = sql_conn.cursor()
 
# SQLAlchemy engine for pd.read_sql (no warnings)
odbc_params = urllib.parse.quote_plus(conn_str)
sql_engine = create_engine(f"mssql+pyodbc:///?odbc_connect={odbc_params}")
 
print(f"{sql_conn.getinfo(pyodbc.SQL_DATABASE_NAME)} database")
stoxx database

SQL Server — SELECT with parameterised query

pd.read_sql() passes bound parameters through the underlying driver. Use ? placeholders with raw pyodbc cursors and :name bind parameters with SQLAlchemy text() — never use f-string interpolation.

Example: SQL Server — SELECT with parameterised query.

pd.read_sql(text("""
    SELECT TOP 10 symbol, date, [open], high, low, [close], volume
    FROM silver.eurostoxx50_ohlcv
    WHERE symbol = :symbol
    ORDER BY date DESC"""), con=sql_engine, params={"symbol": "SAP.DE"})
symboldateopenhighlowclosevolume
0SAP.DE2026-03-12163.00166.74162.80166.52806722
1SAP.DE2026-03-11167.10168.96163.02165.442953782
2SAP.DE2026-03-10171.60172.88166.46169.603187246
3SAP.DE2026-03-09173.72173.86168.52171.881990823
4SAP.DE2026-03-06173.66175.10170.24172.743347221
5SAP.DE2026-03-05167.50172.80166.48170.982961032
6SAP.DE2026-03-04169.22169.22165.94167.382443582
7SAP.DE2026-03-03165.60166.16161.28165.483971985
8SAP.DE2026-03-02166.62169.10164.86167.102776438
9SAP.DE2026-02-27172.00173.34168.28170.962673448

SQL Server — aggregate with GROUP BY

SUM(CAST(volume AS BIGINT)) is required here because SQL Server’s volume column is stored as INT and summing 1300+ trading days across 50 stocks would overflow a 32-bit integer. ROUND(AVG(CAST([close] AS FLOAT)), 2) avoids integer division truncation.

Example: SQL Server — aggregate with GROUP BY.

pd.read_sql(text("""
    SELECT TOP 10 symbol,
           COUNT(*) AS trading_days,
           ROUND(AVG(CAST([close] AS FLOAT)), 2) AS avg_close,
           SUM(CAST(volume AS BIGINT)) AS total_volume
    FROM silver.eurostoxx50_ohlcv
    GROUP BY symbol
    ORDER BY total_volume DESC"""), con=sql_engine)
symboltrading_daysavg_closetotal_volume
0ISP.MI13213.15115704541969
1SAN.MC13294.4355513641918
2ENEL.MI13216.8232600561934
3BBVA.MC13298.6522133773194
4UCG.MI132128.4618366801099
5ENI.MI132113.4017141570967
6INGA.AS133114.0417041577555
7IBE.MC132912.2615994295949
8DTE.DE132422.4310029411390
9NDA-FI.HE130610.857020342991

SQL Server — INSERT, UPDATE, DELETE

pyodbc passes positional parameters directly after the SQL string (not as a tuple), unlike sqlite3. cur.rowcount returns the number of rows affected. Every DML batch must end with conn.commit() — changes are not visible to other connections until committed.

Example: SQL Server — INSERT, UPDATE, DELETE.

cur.execute("""
    IF OBJECT_ID('dbo.trades_demo', 'U') IS NOT NULL DROP TABLE dbo.trades_demo;
    CREATE TABLE dbo.trades_demo (
        trade_id NVARCHAR(20) PRIMARY KEY, ticker NVARCHAR(10),
        side NVARCHAR(4), quantity INT, price DECIMAL(10,2))""")
cur.execute("INSERT INTO dbo.trades_demo VALUES (?,?,?,?,?)",
    "TRD_001", "ASML.AS", "BUY", 100, 685.40)
print(cur.rowcount)
 
# UPDATE
cur.execute("UPDATE dbo.trades_demo SET price = ? WHERE trade_id = ?", 700.00, "TRD_001")
print(cur.rowcount)
 
# DELETE
cur.execute("DELETE FROM dbo.trades_demo WHERE trade_id = ?", "TRD_001")
print(cur.rowcount)
 
cur.execute("DROP TABLE dbo.trades_demo")
sql_conn.commit()
1
1
1

Silent rollback when commit() is missing

pyodbc operates with autocommit=False by default. Any INSERT, UPDATE, or DELETE that is not followed by conn.commit() will be silently rolled back when the connection closes — no error is raised, data simply disappears. This is the most common source of “my writes don’t persist” bugs with pyodbc.

Always commit after DML, or use autocommit for DDL

Example: Always commit after DML, or use autocommit for DDL.

# Preferred: explicit commit after DML
cur.execute("INSERT INTO dbo.trades VALUES (?,?,?,?,?)", row)
conn.commit()
 
# For DDL (CREATE TABLE, DROP TABLE) that cannot run in a transaction:
conn = pyodbc.connect(conn_str, autocommit=True)
cur.execute("CREATE TABLE ...")

Indexes and Query Performance

SQL Server — list indexes on a table

sys.indexes joined with sys.index_columns and sys.columns reveals all indexes on a table including their key columns. STRING_AGG() concatenates key column names in ordinal order. Use this to verify coverage before writing expensive queries.

Example: SQL Server — list indexes on a table.

pd.read_sql(text("""
    SELECT i.name AS index_name, i.type_desc, i.is_unique,
           STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS columns
    FROM sys.indexes i
    JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    WHERE i.object_id = OBJECT_ID('silver.eurostoxx50_ohlcv')
    GROUP BY i.name, i.type_desc, i.is_unique
    ORDER BY i.name"""), con=sql_engine)
index_nametype_descis_uniquecolumns
0IX_silver_eurostoxx50_ohlcv_symbol_dateNONCLUSTEREDTruesymbol, date
1PK__eurostox__3213E83FDF67D274CLUSTEREDTrueid

SQL Server — index fragmentation

sys.dm_db_index_physical_stats returns fragmentation percentages for each index. The 'LIMITED' scan mode is fast and suitable for production — it samples page headers rather than reading all pages. The rule of thumb: < 10% = OK, 10–30% = REORGANIZE, > 30% = REBUILD.

Example: SQL Server — index fragmentation.

pd.read_sql(text("""
    SELECT TOP 10 OBJECT_NAME(ips.object_id) AS [table],
           i.name AS [index], ips.index_type_desc AS type,
           ROUND(ips.avg_fragmentation_in_percent, 1) AS frag_pct,
           ips.page_count AS pages,
           CASE WHEN ips.avg_fragmentation_in_percent < 10 THEN 'OK'
                WHEN ips.avg_fragmentation_in_percent < 30 THEN 'REORGANIZE'
                ELSE 'REBUILD' END AS action
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
    JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
    WHERE ips.page_count > 10
    ORDER BY ips.avg_fragmentation_in_percent DESC"""), con=sql_engine)
tableindextypefrag_pctpagesaction
0index_dimPK__index_di__3213E83FDB4E5BA9CLUSTERED INDEX13.688REORGANIZE
1index_performanceUX_gold_index_performanceNONCLUSTERED INDEX5.319OK
2index_performancePK__index_pe__3213E83FBBB2393ECLUSTERED INDEX4.981OK
3stoxxusa50_ohlcvPK__stoxxusa__3213E83FC84E3F24CLUSTERED INDEX1.4724OK
4index_dimPK__index_di__3213E83F590AA69ECLUSTERED INDEX1.285OK
5stoxxusa50_ohlcvIX_silver_stoxxusa50_ohlcv_symbol_dateNONCLUSTERED INDEX0.6163OK
6stoxxasia50_ohlcvIX_silver_stoxxasia50_ohlcv_symbol_dateNONCLUSTERED INDEX0.5183OK
7stoxxasia50_ohlcvPK__stoxxasi__3213E83F66A8DE5ECLUSTERED INDEX0.4729OK
8eurostoxx50_ohlcvPK__eurostox__3213E83FDF67D274CLUSTERED INDEX0.4757OK
9oil20_ohlcvPK__oil20_oh__3213E83F544EB286CLUSTERED INDEX0.4275OK

SQL Server — database and table sizes

sys.database_files returns total database file size. sys.allocation_units tracks actual page usage per table partition. Together they give a two-level view: total DB size and per-table footprint — useful for capacity planning and identifying bloated tables.

Example: SQL Server — database and table sizes.

display(pd.read_sql(text("SELECT DB_NAME() AS db, CAST(SUM(size)*8.0/1024 AS DECIMAL(10,2)) AS size_mb FROM sys.database_files"), con=sql_engine))
 
pd.read_sql(text("""
    SELECT TOP 10 s.name + '.' + t.name AS [table],
           FORMAT(SUM(p.rows), 'N0') AS rows,
           CAST(SUM(a.total_pages)*8.0/1024 AS DECIMAL(10,2)) AS size_mb
    FROM sys.tables t
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    JOIN sys.indexes i ON t.object_id = i.object_id
    JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
    JOIN sys.allocation_units a ON p.partition_id = a.container_id
    GROUP BY s.name, t.name ORDER BY SUM(a.total_pages) DESC"""), con=sql_engine)
dbsize_mb
0stoxx272.0
tablerowssize_mb
0silver.eurostoxx50_ohlcv132,7107.52
1silver.stoxxasia50_ohlcv128,0907.27
2silver.stoxxusa50_ohlcv130,2007.08
3silver.oil20_ohlcv49,4762.77
4bronze.trading_calendar58,6701.58
5bronze.index_dim6761.02
6gold.index_performance10,5621.02
7silver.index_dim6760.77
8bronze.eurostoxx50_ohlcv1000.33
9gold.scores_daily9320.33

Server Configuration and Administration

SQL Server — server info and configuration

@@VERSION returns the full SQL Server build string — SUBSTRING extracts just the version name. sys.configurations shows instance-level settings like max server memory (MB), max degree of parallelism (MAXDOP), and cost threshold for parallelism — critical parameters for tuning query plan behavior.

Example: SQL Server — server info and configuration.

display(pd.read_sql(text("""
    SELECT SUBSTRING(@@VERSION, 1, CHARINDEX(' (', @@VERSION)-1) AS version,
           CAST(SERVERPROPERTY('Edition') AS NVARCHAR(100)) AS edition,
           CAST(SERVERPROPERTY('Collation') AS NVARCHAR(100)) AS collation"""), con=sql_engine))
 
pd.read_sql(text("""
    SELECT name AS setting, CAST(value_in_use AS NVARCHAR(30)) AS value
    FROM sys.configurations
    WHERE name IN ('max server memory (MB)', 'max degree of parallelism', 'cost threshold for parallelism')
    ORDER BY name"""), con=sql_engine)
versioneditioncollation
0Microsoft SQL Server 2022Developer Edition (64-bit)SQL_Latin1_General_CP1_CI_AS
settingvalue
0cost threshold for parallelism5
1max degree of parallelism0
2max server memory (MB)2147483647

pandas Integration — pd.read_sql and to_sql

pandas — read_sql into DataFrame with SQLAlchemy engine

pd.read_sql(text(sql), con=engine) executes SQL and returns a DataFrame in one line. SQLAlchemy engine handles connection pooling and dialect translation, and works with any database SQLAlchemy supports. For streaming large results row by row, use cursor.fetchmany() instead.

Anti-patterns

  • pd.read_sql with raw pyodbc — works but triggers Pylance/UserWarning
  • Reading entire large table — add WHERE/LIMIT clauses

Use a SQLAlchemy engine and add filters

Example: Use a SQLAlchemy engine and add filters.

# Pass a SQLAlchemy engine, not a raw pyodbc connection
engine = create_engine(f"mssql+pyodbc:///?odbc_connect={odbc_params}")
df = pd.read_sql(text("SELECT * FROM trades WHERE trade_date >= :start_date"), con=engine, params={"start_date": "2024-01-01"})

Example: pandas — read_sql into DataFrame with SQLAlchemy engine.

odbc_params = urllib.parse.quote_plus(os.environ["SQLSERVER_STOXX_ODBC"])
engine = create_engine(f"mssql+pyodbc:///?odbc_connect={odbc_params}")
 
pd.read_sql(text("SELECT TOP 5 symbol, date, [close], volume FROM silver.eurostoxx50_ohlcv ORDER BY date DESC"), con=engine)
symboldateclosevolume
0ASML.AS2026-03-121190.80128223
1MC.PA2026-03-12494.35171997
2RMS.PA2026-03-121906.0018681
3OR.PA2026-03-12360.8082621
4SAP.DE2026-03-12166.52806722

pandas — to_sql to write DataFrame to database

df.to_sql(table, engine, if_exists=) writes a DataFrame to a database table. if_exists='replace' drops and recreates the table; 'append' adds rows without altering the schema. Pass index=False unless you want the DataFrame index as a column.

Example: pandas — to_sql to write DataFrame to database.

 
sample = pd.DataFrame({
    "ticker": ["TEST1", "TEST2", "TEST3"],
    "price": [100.0, 200.0, 300.0],
    "volume": [1000, 2000, 3000],
})
 
sample.to_sql("pandas_demo", engine, schema="dbo", if_exists="replace", index=False)
print("Written to dbo.pandas_demo")
 
# Read back
display(pd.read_sql(text("SELECT * FROM dbo.pandas_demo"), con=engine))
 
# Cleanup
with engine.connect() as c:
    c.execute(text("DROP TABLE dbo.pandas_demo"))
    c.commit()
Written to dbo.pandas_demo
tickerpricevolume
0TEST1100.01000
1TEST2200.02000
2TEST3300.03000

SQLAlchemy — ORM

SQLAlchemy — define ORM model classes

Python’s equivalent of EF Core. Define model classes inheriting from DeclarativeBase with Mapped[type] for typed columns. Session manages transactions — session.add() + session.commit() generates INSERT SQL automatically. Use Alembic for migrations (like dotnet ef). For complex analytics SQL, use raw SQL or DuckDB instead.

Anti-patterns

  • N+1 queries — use joinedload() or selectinload()
  • Session per query — reuse sessions within a request

Eager-load relationships and reuse sessions

Example: Eager-load relationships and reuse sessions.

# Eager load to avoid N+1
stmt = select(Portfolio).options(joinedload(Portfolio.positions))
portfolios = session.scalars(stmt).unique().all()
# Reuse session within a unit of work
with Session(engine) as session:
    session.add(obj1)
    session.add(obj2)
    session.commit()

Example: SQLAlchemy — define ORM model classes.

class Base(DeclarativeBase):
    pass
 
class StockPrice(Base):
    __tablename__ = "stock_prices"
    id: Mapped[int] = mapped_column(primary_key=True)
    symbol: Mapped[str] = mapped_column()
    trade_date: Mapped[Date] = mapped_column()
    close: Mapped[float] = mapped_column()
    volume: Mapped[int] = mapped_column()
 
# Create in-memory SQLite for demo
orm_engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(orm_engine)
print(", ".join(sorted(Base.metadata.tables)))
stock_prices

SQLAlchemy — INSERT with session.add() and commit()

session.add() marks an ORM object as pending. session.commit() flushes all pending changes and generates the appropriate INSERT SQL. session.add_all() is the equivalent of executemany() — use it for bulk inserts rather than calling add() in a loop.

Example: SQLAlchemy — INSERT with session.add() and commit().

with Session(orm_engine) as session:
    session.add_all([
        StockPrice(symbol="ASML.AS", trade_date=Date(2025, 3, 15), close=685.40, volume=2500000),
        StockPrice(symbol="SAP.DE",  trade_date=Date(2025, 3, 15), close=245.80, volume=1800000),
        StockPrice(symbol="MC.PA",   trade_date=Date(2025, 3, 15), close=890.20, volume=900000),
    ])
    session.commit()
    print(f"Inserted {session.query(StockPrice).count()} rows")
Inserted 3 rows

SQLAlchemy — SELECT with session.query() and filter()

session.query(Model).filter(condition).all() generates a SELECT with a WHERE clause. .first() fetches one row with LIMIT 1. The ORM translates Python attribute access (StockPrice.symbol) to column names automatically.

Example: SQLAlchemy — SELECT with session.query() and filter().

with Session(orm_engine) as session:
    results = session.query(StockPrice).filter(
        StockPrice.symbol == "ASML.AS"
    ).order_by(StockPrice.trade_date.desc()).all()
 
    for r in results:
        print(f"  {r.symbol} | {r.trade_date} | {r.close} | {r.volume}")
ASML.AS | 2025-03-15 | 685.4 | 2500000

SQLAlchemy — UPDATE and DELETE

Mutate an object fetched within a session, then call session.commit() to generate the UPDATE. For DELETE, call session.delete(obj) then commit. SQLAlchemy tracks object state — you do not write UPDATE/DELETE SQL manually.

Example: SQLAlchemy — UPDATE and DELETE.

with Session(orm_engine) as session:
    stock = session.query(StockPrice).filter(StockPrice.symbol == "SAP.DE").first()
    stock.close = 999.99
    session.commit()
    print(f"Updated SAP.DE close to {stock.close}")
 
# DELETE
with Session(orm_engine) as session:
    stock = session.query(StockPrice).filter(StockPrice.symbol == "MC.PA").first()
    session.delete(stock)
    session.commit()
    print(f"Deleted MC.PA, remaining: {session.query(StockPrice).count()}")
Updated SAP.DE close to 999.99
Deleted MC.PA, remaining: 2

SQLAlchemy — raw SQL with text()

text() wraps a raw SQL string for safe execution through SQLAlchemy. Named parameters use :name syntax (not ?), passed as a dict. This is the escape hatch for complex queries (window functions, CTEs) where the ORM is too verbose.

Example: SQLAlchemy — raw SQL with text().

with engine.connect() as c:
    result = c.execute(text("""
        SELECT TOP 5 symbol, date, [close], volume
        FROM silver.eurostoxx50_ohlcv
        WHERE symbol = :symbol
        ORDER BY date DESC"""), {"symbol": "ASML.AS"})
    df = pd.DataFrame(result.fetchall(), columns=result.keys())
df
symboldateclosevolume
0ASML.AS2026-03-121190.8128223
1ASML.AS2026-03-111198.8562904
2ASML.AS2026-03-101200.0800815
3ASML.AS2026-03-091147.6689086
4ASML.AS2026-03-061147.0857271

DuckDB — Embedded Analytical SQL Database

DuckDB — connect and CREATE TABLE

Embedded columnar database — no server, in-process, 10-100x faster than row-stores for analytics. Full SQL:2003 with window functions, CTEs, QUALIFY, PIVOT. Queries files directly (SELECT * FROM 'data.parquet') and returns pandas DataFrames natively with .df(). Not suited for OLTP or concurrent writers — use SQL Server for those.

Example: DuckDB — connect and CREATE TABLE.

duck = duckdb.connect(":memory:")
duck.execute("""
    CREATE OR REPLACE TABLE ohlcv (
        symbol VARCHAR, date DATE, open DOUBLE,
        high DOUBLE, low DOUBLE, close DOUBLE, volume BIGINT
    )""")
print(duck.execute("SHOW TABLES").fetchone()[0])
ohlcv

DuckDB — load data from SQL Server

executemany() bulk-inserts rows fetched from pyodbc into DuckDB. Explicit casting (float(), int()) is necessary because pyodbc returns Decimal and datetime objects that DuckDB’s schema expects as native Python floats and ints.

fetchall() on large tables loads

fetchall() on large tables loads everything into Python memory The 66K rows below are fine, but fetchall() on a million-row table can OOM your process. For large transfers, use fetchmany(batch_size) in a loop, or let DuckDB read files directly (SELECT * FROM 'data.parquet').

Stream with fetchmany or query files directly in DuckDB

Example: Stream with fetchmany or query files directly in DuckDB.

# Stream in batches from pyodbc into DuckDB
mssql_cur.execute("SELECT * FROM large_table")
while batch := mssql_cur.fetchmany(10_000):
    ddb.executemany("INSERT INTO target VALUES (?, ?)", batch)
# Or let DuckDB read the file directly — no Python memory overhead
ddb.execute("CREATE TABLE target AS SELECT * FROM 'export.parquet'")

Example: DuckDB — load data from SQL Server.

# Load from SQL Server via pyodbc into DuckDB
 
cur = sql_conn.cursor()
cur.execute("SELECT symbol, date, [open], high, low, [close], volume FROM silver.eurostoxx50_ohlcv")
rows = cur.fetchall()
 
duck.executemany("INSERT INTO ohlcv VALUES (?,?,?,?,?,?,?)",
    [(r[0], r[1], float(r[2]), float(r[3]), float(r[4]), float(r[5]), int(r[6])) for r in rows])
 
print(duck.execute('SELECT COUNT(*) FROM ohlcv').fetchone()[0])
66355

DuckDB — Appender for fastest bulk load

duckdb.Appender is the highest-throughput path for inserting rows into DuckDB — faster than executemany() because it bypasses SQL parsing and parameter binding overhead. Use it when loading millions of rows from Python (e.g., from a pyodbc cursor or a Parquet-to-DuckDB ETL). Call .flush() periodically to avoid accumulating too many rows in memory, and .close() to commit the final batch.

Example: DuckDB — Appender for fastest bulk load.

import duckdb
 
duck2 = duckdb.connect()
duck2.execute("""
    CREATE OR REPLACE TABLE ohlcv (
        symbol VARCHAR, date DATE, open DOUBLE,
        high DOUBLE, low DOUBLE, close DOUBLE, volume BIGINT
    )""")
 
# Appender — fastest bulk insert path (bypasses SQL parsing)
with duck2.cursor() as cur:
    cur.execute("SELECT symbol, date, open, high, low, close, volume FROM silver.eurostoxx50_ohlcv", [], sql_conn)
    rows = cur.fetchall()
 
with duck2.appender("ohlcv") as app:
    for r in rows:
        app.append_row(r[0], r[1], float(r[2]), float(r[3]), float(r[4]), float(r[5]), int(r[6]))
 
duck2.execute("SELECT COUNT(*) FROM ohlcv").fetchone()[0]
66355

Use Appender with fetchmany() for streaming bulk loads

For very large tables, combine fetchmany(10_000) with the Appender to avoid loading all rows into Python memory before inserting. Call .flush() every N batches if memory pressure is a concern: Example: Use Appender with fetchmany() for streaming bulk loads.

with duck2.appender("ohlcv") as app:
    while batch := cur.fetchmany(10_000):
        for r in batch:
            app.append_row(*r)

DuckDB — SELECT with .df() for pandas DataFrame

.df() converts the DuckDB result set to a pandas DataFrame in one step — equivalent to .fetchdf(). For raw Python tuples use .fetchall(), or .fetchone() for a single row. Use LIMIT to avoid loading large results into memory.

Example: DuckDB — SELECT with .df() for pandas DataFrame.

duck.execute("SELECT symbol, date, close, volume FROM ohlcv WHERE symbol = 'SAP.DE' ORDER BY date DESC LIMIT 5").df()
symboldateclosevolume
0SAP.DE2026-03-12166.52806722
1SAP.DE2026-03-11165.442953782
2SAP.DE2026-03-10169.603187246
3SAP.DE2026-03-09171.881990823
4SAP.DE2026-03-06172.743347221

DuckDB — aggregate with GROUP BY

Standard SQL GROUP BY with COUNT, AVG, and SUM. DuckDB executes this in-process using columnar vectorised execution — significantly faster than row-based databases for aggregations over millions of rows.

Example: DuckDB — aggregate with GROUP BY.

duck.execute("""
    SELECT symbol, COUNT(*) AS days, ROUND(AVG(close), 2) AS avg_close,
           SUM(volume) AS total_volume
    FROM ohlcv GROUP BY symbol ORDER BY total_volume DESC LIMIT 10""").df()
symboldaysavg_closetotal_volume
0ISP.MI13213.151.157045e+11
1SAN.MC13294.435.551364e+10
2ENEL.MI13216.823.260056e+10
3BBVA.MC13298.652.213377e+10
4UCG.MI132128.461.836680e+10
5ENI.MI132113.401.714157e+10
6INGA.AS133114.041.704158e+10
7IBE.MC132912.261.599430e+10
8DTE.DE132422.431.002941e+10
9NDA-FI.HE130610.857.020343e+09

DuckDB — window function: LAG for daily returns

LAG(col) OVER (PARTITION BY symbol ORDER BY date) accesses the previous row’s value within each symbol group. DuckDB supports all standard window functions including LAG, LEAD, ROW_NUMBER, RANK, and NTILE. SQLite does not support window functions.

Example: DuckDB — window function: LAG for daily returns.

duck.execute("""
    SELECT symbol, date, close,
           LAG(close) OVER (PARTITION BY symbol ORDER BY date) AS prev_close,
           ROUND((close - LAG(close) OVER (PARTITION BY symbol ORDER BY date))
               / LAG(close) OVER (PARTITION BY symbol ORDER BY date) * 100, 2) AS daily_return_pct
    FROM ohlcv WHERE symbol = 'ASML.AS' ORDER BY date DESC LIMIT 10""").df()
symboldatecloseprev_closedaily_return_pct
0ASML.AS2026-03-121190.81198.8-0.67
1ASML.AS2026-03-111198.81200.0-0.10
2ASML.AS2026-03-101200.01147.64.57
3ASML.AS2026-03-091147.61147.00.05
4ASML.AS2026-03-061147.01186.0-3.29
5ASML.AS2026-03-051186.01199.8-1.15
6ASML.AS2026-03-041199.81161.83.27
7ASML.AS2026-03-031161.81210.4-4.02
8ASML.AS2026-03-021210.41233.4-1.86
9ASML.AS2026-02-271233.41232.40.08

DuckDB — CTE for annualized volatility

A CTE (WITH ... AS (...)) names an intermediate result for reuse in the outer query. Here, daily_returns computes per-day returns, and the outer query aggregates over a trailing window. Annualized volatility = STDDEV(daily_return) * SQRT(252) — the standard market convention.

Example: DuckDB — CTE for annualized volatility.

duck.execute("""
    WITH daily_returns AS (
        SELECT symbol, date, close,
               (close - LAG(close) OVER (PARTITION BY symbol ORDER BY date))
                   / LAG(close) OVER (PARTITION BY symbol ORDER BY date) AS daily_return
        FROM ohlcv
    )
    SELECT symbol, COUNT(*) AS days,
           ROUND(STDDEV(daily_return) * SQRT(252) * 100, 2) AS annualized_vol_pct
    FROM daily_returns WHERE daily_return IS NOT NULL
    GROUP BY symbol ORDER BY annualized_vol_pct DESC""").df().head()
symboldaysannualized_vol_pct
0ADYEN.AS133050.30
1ENR.DE132350.05
2RHM.DE132340.85
3PRX.AS133039.72
4ARGX.BR133039.31

DuckDB — SUMMARIZE for data profiling

SUMMARIZE table returns per-column statistics including min, max, approximate distinct count, mean, std, quartiles, total count, and null percentage — equivalent to pandas df.describe() but faster and with null tracking. Useful as the first step in data quality checks.

Example: DuckDB — SUMMARIZE for data profiling.

duck.execute("SUMMARIZE ohlcv").df()
column_namecolumn_typeminmaxapprox_uniqueavgstdq25q50q75countnull_pct
0symbolVARCHARABI.BRWKL.AS51NoneNoneNoneNoneNone663550.0
1dateDATE2021-01-042026-03-1215162023-08-05 00:56:42.354005None2022-04-182023-08-032024-11-18663550.0
2openDOUBLE1.6012926.025981197.04363.1529.9370.86186.66663550.0
3highDOUBLE1.66282957.030967199.36367.8730.0772.03188.90663550.0
4lowDOUBLE1.58422813.034449194.59358.0129.5470.57184.59663550.0
5closeDOUBLE1.60662839.031796197.03363.0530.0071.37186.45663550.0
6volumeBIGINT0376391539756685942123.6916156185.5351004114153554094451663550.0

DuckDB — COPY TO export to Parquet

COPY (query) TO 'file.parquet' (FORMAT PARQUET) exports a query result directly to a file without materialising a DataFrame. This is the most efficient export path — no Python memory allocation required.

Example: DuckDB — COPY TO export to Parquet.

duck.execute(f"""
    COPY (SELECT symbol, COUNT(*) AS days, ROUND(AVG(close), 2) AS avg_close
          FROM ohlcv GROUP BY symbol ORDER BY avg_close DESC)
    TO '{DATA}/duckdb_py_export.parquet' (FORMAT PARQUET)""")
print("duckdb_py_export.parquet")
duckdb_py_export.parquet

DuckDB vs SQL Server — benchmark same queries

Compares wall-clock time for GROUP BY and LAG window function queries on the same 66K-row dataset — once via DuckDB (in-process columnar) and once via SQL Server (network round-trip + row-based executor). The columnar execution and lack of network overhead make DuckDB 5–10x faster for analytics.

Example: DuckDB vs SQL Server — benchmark same queries.

results = []
for label, fn in [
    ("DuckDB GROUP BY",   lambda: duck.execute("SELECT symbol, AVG(close) FROM ohlcv GROUP BY symbol").fetchall()),
    ("SQL Server GROUP BY", lambda: pd.read_sql(text("SELECT symbol, AVG(CAST([close] AS FLOAT)) FROM silver.eurostoxx50_ohlcv GROUP BY symbol"), con=sql_engine)),
    ("DuckDB LAG()",      lambda: duck.execute("SELECT symbol, date, close, LAG(close) OVER (PARTITION BY symbol ORDER BY date) FROM ohlcv").fetchall()),
    ("SQL Server LAG()",  lambda: pd.read_sql(text("SELECT symbol, date, [close], LAG([close]) OVER (PARTITION BY symbol ORDER BY date) FROM silver.eurostoxx50_ohlcv"), con=sql_engine)),
]:
    start = time.perf_counter()
    fn()
    elapsed = (time.perf_counter() - start) * 1000
    results.append({"Engine": label.split()[0], "Query": " ".join(label.split()[1:]), "Time (ms)": round(elapsed, 1)})
 
pd.DataFrame(results)
EngineQueryTime (ms)
0DuckDBGROUP BY3.2
1SQLServer GROUP BY17.2
2DuckDBLAG()23.9
3SQLServer LAG()181.3

Querying Files — DuckDB vs Polars vs Pandas

Read Parquet

DuckDB — read Parquet file with SELECT

DuckDB reads Parquet directly from disk with a SQL SELECT — no intermediate load step. The file path is embedded in the FROM clause as a string literal. Columnar pushdown means only the requested columns are read from disk.

Example: DuckDB — read Parquet file with SELECT.

duck.execute("SELECT symbol, date, close, volume FROM 'C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet' LIMIT 5").df()
symboldateclosevolume
0ABI.BR2021-01-0457.211513937
1ABI.BR2021-01-0557.181382722
2ABI.BR2021-01-0658.771370204
3ABI.BR2021-01-0758.401469911
4ABI.BR2021-01-0857.861428681

Polars — read Parquet file with pl.read_parquet()

pl.read_parquet() loads a Parquet file into a Polars DataFrame. .select() pushes column projection to the file reader — only specified columns are deserialised. Polars reads Parquet with Arrow-native columnar decoding, which is faster than pandas’ default path.

Example: Polars — read Parquet file with pl.read_parquet().

pl.read_parquet("C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet").select("symbol", "date", "close", "volume").head(5)
symboldateclosevolume
0ABI.BR2021-01-04 00:00:0057.2100001513937
1ABI.BR2021-01-05 00:00:0057.1800001382722
2ABI.BR2021-01-06 00:00:0058.7700001370204
3ABI.BR2021-01-07 00:00:0058.4000001469911
4ABI.BR2021-01-08 00:00:0057.8600001428681

Pandas — read Parquet file with pd.read_parquet()

pd.read_parquet() uses PyArrow (default) to load a Parquet file. The columns= parameter pushes column projection to the reader — unselected columns are not read from disk. Dates are returned as datetime64[ns] (vs Polars’ Date type or DuckDB’s DATE).

Example: Pandas — read Parquet file with pd.read_parquet().

pd.read_parquet("C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet", columns=["symbol", "date", "close", "volume"]).head(5)
symboldateclosevolume
0ABI.BR2021-01-0457.211513937
1ABI.BR2021-01-0557.181382722
2ABI.BR2021-01-0658.771370204
3ABI.BR2021-01-0758.401469911
4ABI.BR2021-01-0857.861428681

Read CSV

DuckDB — read CSV file with SELECT

DuckDB auto-detects CSV schema (delimiter, types, header) and reads the file directly in SQL. No pandas intermediary is needed. CSV is significantly slower than Parquet for large files — see the benchmark in ### Performance — format comparison.

Example: DuckDB — read CSV file with SELECT.

duck.execute("SELECT symbol, date, close, volume FROM 'C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.csv' LIMIT 5").df()
symboldateclosevolume
0ABI.BR2021-01-0457.211513937
1ABI.BR2021-01-0557.181382722
2ABI.BR2021-01-0658.771370204
3ABI.BR2021-01-0758.401469911
4ABI.BR2021-01-0857.861428681

Polars — read CSV file with pl.read_csv()

pl.read_csv() infers schema automatically. Column selection via .select() happens after loading (CSV doesn’t support columnar pushdown). For large CSVs, pl.scan_csv() with lazy evaluation avoids loading all columns into memory.

Example: Polars — read CSV file with pl.read_csv().

pl.read_csv("C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.csv").select("symbol", "date", "close", "volume").head(5)
symboldateclosevolume
0ABI.BR2021-01-0457.2100001513937
1ABI.BR2021-01-0557.1800001382722
2ABI.BR2021-01-0658.7700001370204
3ABI.BR2021-01-0758.4000001469911
4ABI.BR2021-01-0857.8600001428681

Pandas — read CSV file with pd.read_csv()

pd.read_csv() with usecols= loads only the specified columns into memory. Unlike Parquet, CSV is read row-by-row, so usecols saves memory but not I/O time. Parse date columns explicitly with parse_dates=["date"] if you need datetime64 types.

Example: Pandas — read CSV file with pd.read_csv().

pd.read_csv("C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.csv", usecols=["symbol", "date", "close", "volume"]).head(5)
symboldateclosevolume
0ABI.BR2021-01-0457.211513937
1ABI.BR2021-01-0557.181382722
2ABI.BR2021-01-0658.771370204
3ABI.BR2021-01-0758.401469911
4ABI.BR2021-01-0857.861428681

Filter rows

DuckDB — filter with WHERE

DuckDB pushes the WHERE predicate into the Parquet reader — rows that don’t match the filter are never loaded into memory. This makes DuckDB significantly more efficient than loading the full file into pandas and filtering afterwards.

Example: DuckDB — filter with WHERE.

duck.execute("SELECT symbol, date, close, volume FROM 'C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet' WHERE symbol = 'SAP.DE' ORDER BY date DESC LIMIT 5").df()
symboldateclosevolume
0SAP.DE2026-03-12166.52806722
1SAP.DE2026-03-11165.442953782
2SAP.DE2026-03-10169.603187246
3SAP.DE2026-03-09171.881990823
4SAP.DE2026-03-06172.743347221

Polars — filter with filter() and select()

.filter(pl.col("symbol") == "SAP.DE") applies a row predicate. Polars pushes this filter into the Parquet reader via scan_parquet() in lazy mode — read_parquet() loads first then filters. For large files, use pl.scan_parquet().filter(...).collect() instead.

Example: Polars — filter with filter() and select().

pl.read_parquet("C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet").filter(pl.col("symbol") == "SAP.DE").select("symbol", "date", "close", "volume").sort("date", descending=True).head(5)
symboldateclosevolume
0SAP.DE2026-03-12 00:00:00166.520000806722
1SAP.DE2026-03-11 00:00:00165.4400002953782
2SAP.DE2026-03-10 00:00:00169.6000003187246
3SAP.DE2026-03-09 00:00:00171.8800001990823
4SAP.DE2026-03-06 00:00:00172.7400003347221

Pandas — filter with boolean indexing

Boolean indexing df[df["col"] == value] creates a boolean Series and selects matching rows. The full file is loaded into memory first (no pushdown). The result retains original integer row indexes (not reset to 0).

Example: Pandas — filter with boolean indexing.

df = pd.read_parquet("C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet", columns=["symbol", "date", "close", "volume"])
df[df["symbol"] == "SAP.DE"].sort_values("date", ascending=False).head(5)
symboldateclosevolume
57061SAP.DE2026-03-12166.52806722
57060SAP.DE2026-03-11165.442953782
57059SAP.DE2026-03-10169.603187246
57058SAP.DE2026-03-09171.881990823
57057SAP.DE2026-03-06172.743347221

Group and aggregate

DuckDB — aggregate with GROUP BY

GROUP BY with aggregate functions run directly on the Parquet file — DuckDB reads only the required columns and applies the aggregation without loading the full dataset into Python memory.

Example: DuckDB — aggregate with GROUP BY.

duck.execute("""
    SELECT symbol, COUNT(*) AS days, ROUND(AVG(close), 2) AS avg_close, SUM(volume) AS total_volume
    FROM 'C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet'
    GROUP BY symbol ORDER BY total_volume DESC LIMIT 10""").df()
symboldaysavg_closetotal_volume
0ISP.MI13213.151.157045e+11
1SAN.MC13294.435.551364e+10
2ENEL.MI13216.823.260056e+10
3BBVA.MC13298.652.213377e+10
4UCG.MI132128.461.836680e+10
5ENI.MI132113.401.714157e+10
6INGA.AS133114.041.704158e+10
7IBE.MC132912.261.599430e+10
8DTE.DE132422.431.002941e+10
9NDA-FI.HE130610.857.020343e+09

Polars — aggregate with group_by() and agg()

.group_by("symbol").agg(...) groups by one or more columns and computes aggregations. Polars agg() takes named expressions — pl.col("close").count().alias("days") — rather than strings. Results are unordered by default; chain .sort() for a consistent output.

Example: Polars — aggregate with group_by() and agg().

pl.read_parquet("C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet").group_by("symbol").agg(
    pl.col("close").count().alias("days"),
    pl.col("close").mean().alias("avg_close"),
    pl.col("volume").sum().alias("total_volume"),
).sort("total_volume", descending=True).head(10)
symboldaysavg_closetotal_volume
0ISP.MI13213.147987115704541969
1SAN.MC13294.42584855513641918
2ENEL.MI13216.82043832600561934
3BBVA.MC13298.65195422133773194
4UCG.MI132128.45710418366801099
5ENI.MI132113.39762517141570967
6INGA.AS133114.03818817041577555
7IBE.MC132912.25531215994295949
8DTE.DE132422.43009710029411390
9NDA-FI.HE130610.8480797020342991

Pandas — aggregate with groupby() and agg()

df.groupby("symbol").agg(named_agg=...) uses pandas’ named aggregation syntax. The groupby key becomes the row index (shown as symbol in the output). Use .reset_index() to convert it back to a regular column.

Example: Pandas — aggregate with groupby() and agg().

df = pd.read_parquet("C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet")
df.groupby("symbol").agg(
    days=("close", "count"),
    avg_close=("close", "mean"),
    total_volume=("volume", "sum"),
).sort_values("total_volume", ascending=False).head(10)
symboldaysavg_closetotal_volume
ISP.MI13213.147987115704541969
SAN.MC13294.42584855513641918
ENEL.MI13216.82043832600561934
BBVA.MC13298.65195422133773194
UCG.MI132128.45710418366801099
ENI.MI132113.39762517141570967
INGA.AS133114.03818817041577555
IBE.MC132912.25531215994295949
DTE.DE132422.43009710029411390
NDA-FI.HE130610.8480797020342991

Select specific columns from file

DuckDB — select columns with SELECT

Standard SQL column projection — only listed columns are read from the Parquet file. DuckDB’s Parquet reader skips unselected column chunks entirely, reducing I/O proportionally to how many columns are omitted.

Example: DuckDB — select columns with SELECT.

duck.execute("SELECT symbol, close FROM 'C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet' LIMIT 5").df()
symbolclose
0ABI.BR57.21
1ABI.BR57.18
2ABI.BR58.77
3ABI.BR58.40
4ABI.BR57.86

Polars — select columns with select()

.select("col1", "col2") returns a DataFrame with only the listed columns. When used in a lazy pipeline (scan_parquet().select()), Polars pushes column projection into the file reader; with read_parquet() (eager), all columns are loaded first.

Example: Polars — select columns with select().

pl.read_parquet("C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet").select("symbol", "close").head(5)
symbolclose
0ABI.BR57.210000
1ABI.BR57.180000
2ABI.BR58.770000
3ABI.BR58.400000
4ABI.BR57.860000

Pandas — select columns with usecols

pd.read_parquet(..., columns=[...]) passes the column list directly to the PyArrow Parquet reader — unselected columns are skipped at read time. This is the most memory-efficient way to load a subset of a wide Parquet file in pandas.

Example: Pandas — select columns with usecols.

pd.read_parquet("C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet", columns=["symbol", "close"]).head(5)
symbolclose
0ABI.BR57.21
1ABI.BR57.18
2ABI.BR58.77
3ABI.BR58.40
4ABI.BR57.86

Sort and limit directly from file

DuckDB — sort with ORDER BY and LIMIT

ORDER BY col DESC LIMIT n in DuckDB uses a tournament sort that only retains the top-N rows in memory — equivalent to a heap sort. This is far more efficient than sorting all rows and taking a slice.

Example: DuckDB — sort with ORDER BY and LIMIT.

# DuckDB — sort by close descending, top 5
 
duck.execute("SELECT symbol, date, close FROM 'C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet' ORDER BY close DESC LIMIT 5").df()
symboldateclose
0RMS.PA2025-02-142839.0
1RMS.PA2025-02-132816.0
2RMS.PA2025-02-172809.0
3RMS.PA2025-02-182806.0
4ADYEN.AS2021-08-242766.0

Polars — sort with sort() and head()

.sort("col", descending=True).head(n) sorts the full DataFrame and takes the top N rows. Unlike DuckDB’s tournament sort, Polars sorts all rows first — for very large datasets use .top_k(n, by="col") (Polars 0.19+) for efficient top-N without full sort.

Example: Polars — sort with sort() and head().

# Polars — sort by close descending, top 5
 
pl.read_parquet("C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet").select("symbol", "date", "close").sort("close", descending=True).head(5)
symboldateclose
0RMS.PA2025-02-14 00:00:002839.000000
1RMS.PA2025-02-13 00:00:002816.000000
2RMS.PA2025-02-17 00:00:002809.000000
3RMS.PA2025-02-18 00:00:002806.000000
4ADYEN.AS2021-08-24 00:00:002766.000000

Pandas — sort with sort_values() and head()

.sort_values("col", ascending=False).head(n) sorts a pandas DataFrame in place (returns a view) then slices. Like Polars, this sorts all rows — use df.nlargest(n, "col") for a more efficient top-N path.

Example: Pandas — sort with sort_values() and head().

# Pandas — sort by close descending, top 5
 
pd.read_parquet("C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet", columns=["symbol", "date", "close"]).sort_values("close", ascending=False).head(5)
symboldateclose
51473RMS.PA2025-02-142839.0
51472RMS.PA2025-02-132816.0
51474RMS.PA2025-02-172809.0
51475RMS.PA2025-02-182806.0
4150ADYEN.AS2021-08-242766.0

Multiple filters (AND / OR) directly from file

DuckDB — multi-condition filter with WHERE AND

Combine conditions with AND / OR directly in the WHERE clause. DuckDB evaluates compound predicates in the Parquet reader — only rows matching all conditions reach the query engine.

Example: DuckDB — multi-condition filter with WHERE AND.

# DuckDB — WHERE with AND + OR
 
duck.execute("""
    SELECT symbol, date, close, volume
    FROM 'C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet'
    WHERE symbol = 'ASML.AS' AND close > 700
    ORDER BY date DESC LIMIT 5""").df()
symboldateclosevolume
0ASML.AS2026-03-121190.8128223
1ASML.AS2026-03-111198.8562904
2ASML.AS2026-03-101200.0800815
3ASML.AS2026-03-091147.6689086
4ASML.AS2026-03-061147.0857271

Polars — multi-condition filter with & and |

Combine conditions with & (AND) and | (OR). Each condition must be a pl.col() expression — use parentheses around each condition when combining, as Python operator precedence can cause unexpected grouping.

Example: Polars — multi-condition filter with & and |.

# Polars — AND filter with &
 
pl.read_parquet("C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet").filter(
    (pl.col("symbol") == "ASML.AS") & (pl.col("close") > 700)
).select("symbol", "date", "close", "volume").sort("date", descending=True).head(5)
symboldateclosevolume
0ASML.AS2026-03-12 00:00:001190.800000128223
1ASML.AS2026-03-11 00:00:001198.800000562904
2ASML.AS2026-03-10 00:00:001200.000000800815
3ASML.AS2026-03-09 00:00:001147.600000689086
4ASML.AS2026-03-06 00:00:001147.000000857271

Pandas — multi-condition filter with & and |

Same parentheses rule as Polars: wrap each condition in parentheses. & is bitwise AND on boolean Series; and is a Python keyword and will not work with pandas. For more readable filters, use df.query("symbol == 'ASML.AS' and close > 700").

Example: Pandas — multi-condition filter with & and |.

# Pandas — AND filter with &
 
df = pd.read_parquet("C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet", columns=["symbol", "date", "close", "volume"])
df[(df["symbol"] == "ASML.AS") & (df["close"] > 700)].sort_values("date", ascending=False).head(5)
symboldateclosevolume
11964ASML.AS2026-03-121190.8128223
11963ASML.AS2026-03-111198.8562904
11962ASML.AS2026-03-101200.0800815
11961ASML.AS2026-03-091147.6689086
11960ASML.AS2026-03-061147.0857271

Add computed column directly from file

DuckDB — computed column with SELECT expression

Computed columns in SQL are expressions in the SELECT list, aliased with AS. ROUND(high - low, 2) is evaluated per row — no intermediate storage. DuckDB can push the ORDER BY over computed columns without materialising them first.

Example: DuckDB — computed column with SELECT expression.

# DuckDB — add daily range column
 
duck.execute("""
    SELECT symbol, date, high, low, ROUND(high - low, 2) AS daily_range
    FROM 'C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet'
    WHERE symbol = 'ASML.AS'
    ORDER BY daily_range DESC LIMIT 5""").df()
symboldatehighlowdaily_range
0ASML.AS2024-10-15804.6665.0139.6
1ASML.AS2026-01-281309.01185.4123.6
2ASML.AS2026-02-261304.31210.893.5
3ASML.AS2024-08-05749.9657.092.9
4ASML.AS2025-04-07596.2508.487.8

Polars — computed column with with_columns()

.with_columns(expr.alias("name")) adds one or more computed columns without modifying existing ones. Expressions operate on full columns (vectorised), not row-by-row — no Python loop is needed.

Example: Polars — computed column with with_columns().

# Polars — add daily range column
 
pl.read_parquet("C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet").filter(
    pl.col("symbol") == "ASML.AS"
).with_columns(
    (pl.col("high") - pl.col("low")).round(2).alias("daily_range")
).select("symbol", "date", "high", "low", "daily_range").sort("daily_range", descending=True).head(5)
symboldatehighlowdaily_range
0ASML.AS2024-10-15 00:00:00804.600000665.000000139.600000
1ASML.AS2026-01-28 00:00:001309.0000001185.400000123.600000
2ASML.AS2026-02-26 00:00:001304.3000001210.80000093.500000
3ASML.AS2024-08-05 00:00:00749.900000657.00000092.900000
4ASML.AS2025-04-07 00:00:00596.200000508.40000087.800000

Pandas — computed column with assign()

.assign(col=lambda d: expr) adds a computed column in a method-chain-friendly way. The lambda receives the current DataFrame (d), allowing reference to other columns. round() is a Python built-in here — use df["col"].round(n) for the pandas vectorised version.

Example: Pandas — computed column with assign().

# Pandas — add daily range column
 
df = pd.read_parquet("C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet", columns=["symbol", "date", "high", "low"])
df[df["symbol"] == "ASML.AS"].assign(daily_range=lambda d: round(d["high"] - d["low"], 2)).sort_values("daily_range", ascending=False).head(5)
symboldatehighlowdaily_range
11606ASML.AS2024-10-15804.6665.0139.6
11933ASML.AS2026-01-281309.01185.4123.6
11954ASML.AS2026-02-261304.31210.893.5
11555ASML.AS2024-08-05749.9657.092.9
11727ASML.AS2025-04-07596.2508.487.8

Window function directly from file (DuckDB only)

DuckDB — LAG() window function on Parquet file

DuckDB can apply window functions directly on a Parquet file without loading it into memory first — unique to DuckDB among the three tools. Polars and pandas must load the full dataset before computing shifted values.

Example: DuckDB — LAG() window function on Parquet file.

# DuckDB — daily returns with LAG() directly on Parquet (not possible in Polars/Pandas without loading)
 
duck.execute("""
    SELECT symbol, date, close,
           LAG(close) OVER (PARTITION BY symbol ORDER BY date) AS prev_close,
           ROUND((close - LAG(close) OVER (PARTITION BY symbol ORDER BY date))
               / LAG(close) OVER (PARTITION BY symbol ORDER BY date) * 100, 2) AS daily_return_pct
    FROM 'C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet'
    WHERE symbol = 'ASML.AS'
    ORDER BY date DESC LIMIT 10""").df()
symboldatecloseprev_closedaily_return_pct
0ASML.AS2026-03-121190.81198.8-0.67
1ASML.AS2026-03-111198.81200.0-0.10
2ASML.AS2026-03-101200.01147.64.57
3ASML.AS2026-03-091147.61147.00.05
4ASML.AS2026-03-061147.01186.0-3.29
5ASML.AS2026-03-051186.01199.8-1.15
6ASML.AS2026-03-041199.81161.83.27
7ASML.AS2026-03-031161.81210.4-4.02
8ASML.AS2026-03-021210.41233.4-1.86
9ASML.AS2026-02-271233.41232.40.08

Polars — equivalent with shift() (must load data first)

.shift(1).over("symbol") is Polars’ LAG equivalent — it shifts values by N positions within each group defined by .over(). The full dataset must be in memory first; there is no Parquet-level pushdown for window functions in Polars.

Example: Polars — equivalent with shift() (must load data first).

# Polars — LAG equivalent with shift().over() (data must be loaded, not lazy on file)
 
pl.read_parquet("C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet").filter(
    pl.col("symbol") == "ASML.AS"
).sort("date").with_columns(
    pl.col("close").shift(1).over("symbol").alias("prev_close")
).with_columns(
    ((pl.col("close") - pl.col("prev_close")) / pl.col("prev_close") * 100).round(2).alias("daily_return_pct")
).select("symbol", "date", "close", "prev_close", "daily_return_pct").sort("date", descending=True).head(10)
symboldatecloseprev_closedaily_return_pct
0ASML.AS2026-03-12 00:00:001190.8000001198.800000-0.670000
1ASML.AS2026-03-11 00:00:001198.8000001200.000000-0.100000
2ASML.AS2026-03-10 00:00:001200.0000001147.6000004.570000
3ASML.AS2026-03-09 00:00:001147.6000001147.0000000.050000
4ASML.AS2026-03-06 00:00:001147.0000001186.000000-3.290000
5ASML.AS2026-03-05 00:00:001186.0000001199.800000-1.150000
6ASML.AS2026-03-04 00:00:001199.8000001161.8000003.270000
7ASML.AS2026-03-03 00:00:001161.8000001210.400000-4.020000
8ASML.AS2026-03-02 00:00:001210.4000001233.400000-1.860000
9ASML.AS2026-02-27 00:00:001233.4000001232.4000000.080000

Performance — format comparison

DuckDB — benchmark same query on CSV vs Parquet vs JSON

Same GROUP BY query on three file formats — same data, different encodings. Parquet is columnar and compressed so only the symbol and close columns are read; CSV and JSON require full row parsing. The result quantifies the I/O advantage of columnar formats.

Example: DuckDB — benchmark same query on CSV vs Parquet vs JSON.

# Format comparison timing
 
results = []
for fmt, path in [("CSV", "C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.csv"),
                  ("Parquet", "C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet"),
                  ("JSON", "C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.json")]:
    start = time.perf_counter()
    rows = duck.execute(f"SELECT symbol, COUNT(*), AVG(close) FROM '{path}' GROUP BY symbol").fetchall()
    elapsed = (time.perf_counter() - start) * 1000
    results.append({"Format": fmt, "Rows": len(rows), "Time (ms)": round(elapsed, 1)})
pd.DataFrame(results)
FormatRowsTime (ms)
0CSV5059.8
1Parquet503.1
2JSON5076.7

DuckDB vs Polars vs Pandas — reference

OperationDuckDB SQLPolarsPandas
Read ParquetSELECT FROM 'file.parquet'pl.read_parquet(path)pd.read_parquet(path)
Read CSVSELECT FROM 'file.csv'pl.read_csv(path)pd.read_csv(path)
FilterWHERE col = 'val'df.filter(pl.col("c")==v)df[df["c"]==v]
Select colsSELECT a, bdf.select("a","b")df[["a","b"]]
SortORDER BY col DESCdf.sort("c", descending=True)df.sort_values("c", ascending=False)
LimitLIMIT 10df.head(10)df.head(10)
Group + AggGROUP BY ... AVG(c)df.group_by("c").agg(...)df.groupby("c").agg(...)
WindowLAG() OVER (PARTITION BY ...)pl.col("c").shift(1).over("g")df.groupby("g")["c"].shift(1)
ExportCOPY TO 'file.parquet'df.write_parquet(path)df.to_parquet(path)
Lazy evalNopl.scan_parquet(path)No
Returns.df() → pandas DataFramePolars DataFramepandas DataFrame

Operational Risks

Query safety and transaction durability

Interpolated SQL with f"...{value}..."

Direct interpolation pushes user-controlled values into the SQL parser. Use ? with sqlite3 or pyodbc, and use :name bind parameters inside SQLAlchemy text().

Example: Interpolated SQL with f"...{value}...".

name = "alice' OR 1=1 --"
unsafe = f"SELECT * FROM users WHERE name = '{name}'"
safe_sql = "SELECT * FROM users WHERE name = ?"
safe_params = (name,)
print(unsafe)
print(safe_sql)
print(safe_params)
SELECT * FROM users WHERE name = 'alice' OR 1=1 --'
SELECT * FROM users WHERE name = ?
("alice' OR 1=1 --",)

DML without conn.commit()

Manual-commit drivers leave inserts, updates, and deletes pending until conn.commit() runs. If the session rolls back or closes first, the write set disappears.

Example: DML without conn.commit().

import sqlite3
 
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE trades (ticker TEXT)")
conn.execute("INSERT INTO trades VALUES ('ASML.AS')")
print(conn.total_changes)
conn.rollback()
print(conn.execute("SELECT COUNT(*) FROM trades").fetchone()[0])
conn.close()
1
0

SQLAlchemy lifecycle and API drift

Recreating create_engine() inside hot paths

Each create_engine() call creates a separate connection pool. Building one per request discards pooling benefits and increases connection churn.

Example: Recreating create_engine() inside hot paths.

from sqlalchemy import create_engine
 
engines = [create_engine("sqlite:///:memory:") for _ in range(3)]
print(len({id(engine.pool) for engine in engines}))
3

Long-lived Session objects

A long-lived Session keeps transaction state and identity-map data resident for longer than necessary. Keep a Session scoped to one unit of work, then close it.

Example: Long-lived Session objects.

from sqlalchemy import create_engine
from sqlalchemy.orm import Session
 
engine = create_engine("sqlite:///:memory:")
with Session(engine) as short_lived:
    print(short_lived.is_active)
 
long_lived = Session(engine)
long_lived.info["pending_batch"] = ["ASML.AS", "SAP.DE", "MC.PA"]
print(len(long_lived.info["pending_batch"]))
long_lived.close()
True
3

Raw strings in pd.read_sql() with SQLAlchemy 2.x

When the connection layer is SQLAlchemy, wrap the statement in text() before calling pd.read_sql(). That keeps the query aligned with SQLAlchemy 2.x expectations.

Example: Raw strings in pd.read_sql() with SQLAlchemy 2.x.

from sqlalchemy import text
 
statement = text("SELECT 1 AS value")
print(type(statement).__name__)
print("pd.read_sql(text('SELECT 1 AS value'), con=engine)")
TextClause
pd.read_sql(text('SELECT 1 AS value'), con=engine)

Secure connection and transaction setup

Use ? or :name placeholders with context managers

Pair bound parameters with a with sqlite3.connect(...) as conn: block or a short-lived Session. That gives safe input handling and a clear transaction boundary in the same pattern.

Example: Use ? or :name placeholders with context managers.

import sqlite3
 
with sqlite3.connect(":memory:") as conn:
    conn.execute("CREATE TABLE trades (ticker TEXT)")
    conn.execute("INSERT INTO trades VALUES (?)", ("ASML.AS",))
    print(conn.execute("SELECT COUNT(*) FROM trades WHERE ticker = ?", ("ASML.AS",)).fetchone()[0])
1

Load SQL Server connection strings from SQLSERVER_STOXX_ODBC

Keep SQLSERVER_STOXX_ODBC in the environment or a secret store so the note body never becomes the credential system of record. The same variable can hold either Trusted_Connection=yes or a SQL login supplied elsewhere.

Example: Load SQL Server connection strings from SQLSERVER_STOXX_ODBC.

import os
 
os.environ["SQLSERVER_STOXX_ODBC"] = (
    "Driver={ODBC Driver 18 for SQL Server};"
    "Server=localhost,1434;Database=stoxx;"
    "Trusted_Connection=yes;Encrypt=yes;TrustServerCertificate=yes;"
)
print("Database=stoxx" in os.environ["SQLSERVER_STOXX_ODBC"])
print("PWD=" in os.environ["SQLSERVER_STOXX_ODBC"])
True
False

Batch related writes inside one with conn: block so the unit of work either commits together or rolls back together. This is the simplest way to keep INSERT and UPDATE steps atomic.

Example: Group related DML in one explicit transaction.

import sqlite3
 
with sqlite3.connect(":memory:") as conn:
    conn.execute("CREATE TABLE trades (ticker TEXT, qty INTEGER)")
    conn.executemany("INSERT INTO trades VALUES (?, ?)", [("ASML.AS", 10), ("SAP.DE", 20)])
    print(conn.execute("SELECT SUM(qty) FROM trades").fetchone()[0])
30

Efficient reads and ORM defaults

Reuse one engine and short-lived Session objects

Create one shared engine, then open and close a Session around each unit of work. That reuses the pool while keeping transaction scope tight.

Example: Reuse one engine and short-lived Session objects.

from sqlalchemy import create_engine
from sqlalchemy.orm import Session
 
engine = create_engine("sqlite:///:memory:")
with Session(engine) as first:
    print(first.bind is engine)
with Session(engine) as second:
    print(second.bind is engine)
True
True

Stream large reads with chunksize

pd.read_sql(..., chunksize=...) returns an iterator of DataFrames instead of materialising the whole result set at once. Use it when the query is large but row order is still important.

Example: Stream large reads with chunksize.

import pandas as pd
from sqlalchemy import create_engine, text
 
engine = create_engine("sqlite:///:memory:")
with engine.begin() as conn:
    conn.execute(text("CREATE TABLE ticks (id INTEGER PRIMARY KEY, symbol TEXT)"))
    conn.execute(text("INSERT INTO ticks (symbol) VALUES ('ASML.AS'), ('SAP.DE'), ('MC.PA')"))
 
with engine.connect() as conn:
    for chunk in pd.read_sql(text("SELECT * FROM ticks ORDER BY id"), con=conn, chunksize=2):
        print(len(chunk))
2
1

Prefer mapped_column() with Mapped[T]

mapped_column() and Mapped[T] keep SQLAlchemy 2.x models typed and explicit. That pattern reads better than mixing legacy Column() declarations into otherwise typed models.

Example: Prefer mapped_column() with Mapped[T].

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
 
class DemoBase(DeclarativeBase):
    pass
 
class Trade(DemoBase):
    __tablename__ = "trades"
    id: Mapped[int] = mapped_column(primary_key=True)
    ticker: Mapped[str] = mapped_column()
 
print(list(Trade.__table__.c.keys()))
['id', 'ticker']

Use DuckDB for file-based analytics

DuckDB is optimized for analytical scans and SQL over files. Reach for duckdb.connect() and file-backed SQL when the workload is ad hoc analytics rather than row-by-row OLTP.

Example: Use DuckDB for file-based analytics.

import duckdb
 
duck = duckdb.connect(":memory:")
print(duck.execute("SELECT SUM(i) FROM range(5) tbl(i)").fetchone()[0])
10

Python Database Troubleshooting

Minimal probes below are designed to confirm the failure boundary quickly before you revisit the larger examples earlier in the note.

SQLite state and filesystem issues

OperationalError: no such table

If OperationalError: no such table appears, confirm that the schema was created in the same database file or in the same :memory: connection before you query it.

Example: OperationalError: no such table.

import sqlite3
 
with sqlite3.connect(":memory:") as conn:
    conn.execute("CREATE TABLE trades (id INTEGER)")
    print(conn.execute("SELECT name FROM sqlite_master WHERE type='table'").fetchone()[0])
trades

Lost writes after script exit without commit()

If rows disappear after the script exits, inspect the transaction boundary first. A missing commit() or an explicit rollback() is enough to erase the pending DML.

Example: Lost writes after script exit without commit().

import sqlite3
 
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE trades (id INTEGER)")
conn.execute("INSERT INTO trades VALUES (1)")
conn.rollback()
print(conn.execute("SELECT COUNT(*) FROM trades").fetchone()[0])
conn.close()
0

sqlite3.ProgrammingError after conn.close()

A closed connection cannot be reused. If you see sqlite3.ProgrammingError, reopen the database and rebuild any cursor tied to the old conn.

Example: sqlite3.ProgrammingError after conn.close().

import sqlite3
 
conn = sqlite3.connect(":memory:")
conn.close()
try:
    conn.execute("SELECT 1")
except sqlite3.ProgrammingError as exc:
    print(exc)
Cannot operate on a closed database.

SQL Server connectivity and parameter binding

pyodbc.Error: [08001] on connect

A pyodbc.Error: [08001] usually points to a reachability or naming issue outside the query text. Verify Server=..., the firewall rule, and the SQL Server Browser service before changing the SQL itself.

Example: pyodbc.Error: [08001] on connect.

checks = [
    "Server=localhost,1434",
    "Firewall allows TCP 1434",
    "SQL Server Browser running",
]
for item in checks:
    print(item)
Server=localhost,1434
Firewall allows TCP 1434
SQL Server Browser running

InterfaceError: parameter marker not supported

InterfaceError: parameter marker not supported almost always means the placeholder style does not match the driver layer. Check whether the call site expects ?, %s, or :name before you retry.

Example: InterfaceError: parameter marker not supported.

param_styles = {
    "pyodbc": "?",
    "psycopg2": "%s",
    "sqlalchemy.text": ":name",
}
for layer, marker in param_styles.items():
    print(f"{layer}: {marker}")
pyodbc: ?
psycopg2: %s
sqlalchemy.text: :name

SQLAlchemy and pandas integration

RemovedIn20Warning from raw-string pd.read_sql()

If SQLAlchemy 2.x warns about raw strings, wrap the statement in text() and pass the engine or connection through con= explicitly.

Example: RemovedIn20Warning from raw-string pd.read_sql().

from sqlalchemy import text
 
print(type(text("SELECT 1")).__name__)
print("pd.read_sql(text('SELECT 1'), con=engine)")
TextClause
pd.read_sql(text('SELECT 1'), con=engine)

DetachedInstanceError after the session closes

A DetachedInstanceError means the ORM object wants data from a Session that is already gone. Use joinedload(), expire_on_commit=False, or access the relationship inside the Session block.

Example: DetachedInstanceError after the session closes.

fixes = ["joinedload()", "expire_on_commit=False", "access related rows before session exit"]
for item in fixes:
    print(item)
joinedload()
expire_on_commit=False
access related rows before session exit

Slow DataFrame.to_sql() writes

If DataFrame.to_sql() is slow, switch from row-by-row inserts to batched inserts with method="multi" and a practical chunksize.

Example: Slow DataFrame.to_sql() writes.

options = {"method": "multi", "chunksize": 1000}
for key, value in options.items():
    print(f"{key}={value}")
method=multi
chunksize=1000

ORM insert appears to do nothing without session.commit()

session.add() only stages the object. Query the table after the block if you need to prove whether session.commit() actually ran.

Example: ORM insert appears to do nothing without session.commit().

from sqlalchemy import create_engine, text
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column
 
class ItemBase(DeclarativeBase):
    pass
 
class Item(ItemBase):
    __tablename__ = "items"
    id: Mapped[int] = mapped_column(primary_key=True)
 
engine = create_engine("sqlite:///:memory:")
ItemBase.metadata.create_all(engine)
 
with Session(engine) as session:
    session.add(Item(id=1))
 
with engine.connect() as conn:
    print(conn.execute(text("SELECT COUNT(*) FROM items")).scalar())
 
with Session(engine) as session:
    session.add(Item(id=1))
    session.commit()
 
with engine.connect() as conn:
    print(conn.execute(text("SELECT COUNT(*) FROM items")).scalar())
0
1

DuckDB schema diagnostics

BinderException: referenced column not found

When DuckDB raises BinderException: referenced column not found, inspect the schema first. DESCRIBE SELECT * FROM file is the fastest way to confirm the available column names before you rework the query.

Example: BinderException: referenced column not found.

import duckdb
 
duck = duckdb.connect(":memory:")
print(duck.execute("DESCRIBE SELECT * FROM range(1) tbl(id)").fetchone()[0])
id

Graph View

Backlinks