09 - Database and SQL Interface - Python

Database and SQL Interface - Python

Quote

“Show me your flowcharts and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won’t usually need your flowcharts; they’ll be obvious.”

— Fred Brooks, The Mythical Man-Month (1975)

Summary

Brings together both sides of DataFrame database work: SQL over in-memory frames with Polars SQLContext and DuckDB, and direct SQL Server access through SQLAlchemy and pyodbc. The note focuses on where each interface belongs, how reads, writes, and stored procedures behave, and which performance, security, and memory constraints determine whether SQL should run inside the DataFrame toolchain or in the database itself.

In-memory SQL on DataFrames

• Use Polars SQLContext to register DataFrames as virtual tables, run SELECT/JOIN/GROUP BY queries, and materialize LazyFrame results only when needed
• Use DuckDB when analytical SQL patterns such as CTEs, richer window functions, direct Parquet scans, and set operations are easier to express in SQL than in chained DataFrame expressions

SQL Server Integration

• Connect through SQLAlchemy and pyodbc, build connection strings safely, and read relational data into Pandas or Polars with the right balance between convenience and low-level control
• Write DataFrames back to SQL Server, execute DDL/DML and stored procedures, inspect schema, and compare SQLAlchemy versus raw pyodbc for portability, bulk performance, and SQL Server-specific behavior

Operations and safety

• When to use each interface: SQLContext for SQL-fluent work on in-memory Polars data, DuckDB for embedded analytical SQL, SQLAlchemy for standard ETL/database access, pyodbc for cursor-level SQL Server control
• Limits: in-memory SQL engines do not replace server-side processing for larger-than-RAM queries, high-concurrency writes, CDC, or multi-procedure orchestration
• Warnings: slow default to_sql(), SQL injection from string-built queries, SQLAlchemy 2.0 API changes, large read_sql() loads, and plaintext credentials in source
• Recommendations and troubleshooting: parameterize queries, use pooling and batched reads, enable fast_executemany for bulk loads, validate schema after reads, and diagnose common ODBC, connectivity, table-exists, truncation, and type-inference failures

Glossary

SQLContext

• A Polars interface that registers DataFrames as virtual tables and lets SQL compile into Polars query plans.

• It matters because the note uses it as the bridge for SQL-first exploration without leaving the Polars execution model.

Dialect is Polars SQL, not T-SQL

SQLContext supports a useful SQL subset, but not every PostgreSQL, DuckDB, or SQL Server construct transfers directly.

---

LazyFrame

• Polars’ deferred execution object, where transformations are planned first and executed only when materialized.

• It matters because SQLContext.execute() returns a LazyFrame, so SQL results are not concrete until .collect() runs.

Execution boundary matters

Keeping work lazy lets Polars optimize before touching memory, but downstream notebook display still requires explicit materialization.

---

DuckDB

• An embedded analytical database that runs full SQL directly inside the Python process against DataFrames, Parquet files, and other local data sources.

• It matters because the note uses DuckDB when the SQL itself is the clearest way to express joins, CTEs, and window-heavy analysis.

Separate optimizer, separate result conversion

DuckDB does not become Polars internally; it executes its own plan and then hands the result back in the requested DataFrame form.

---

SQLAlchemy

• Python’s standard database toolkit for engines, connections, SQL execution, and DataFrame integration across relational systems.

• It matters because Pandas relies on it for read_sql()/to_sql(), and the note uses it as the main portable path into SQL Server.

SQLAlchemy 2.0 changed old habits

Legacy engine.execute() patterns no longer apply. Use explicit connections and text() for executable SQL.

---

pyodbc

• A lower-level ODBC driver interface for direct cursor-based access to SQL Server and other ODBC-capable databases.

• It matters because the note uses it where stored procedures, driver-specific options, and bulk insert tuning need more control than high-level wrappers provide.

Driver installation is part of the runtime contract

Missing or mismatched ODBC drivers cause connection failures before any Python-side logic runs.

---

Connection string

• The DSN-style or URL-style string that specifies server, database, driver, and authentication details for a database session.

• It matters because every read, write, and procedure call depends on getting this boundary object exactly right.

Authentication mode confusion is common

Mixing trusted authentication with username/password settings is a routine source of “access denied” and handshake errors.

---

read_sql() / read_database()

• DataFrame APIs that execute a SQL query and materialize the result into Pandas or Polars.

• It matters because they are the default bridge from relational result sets into in-memory analytical work.

Reads are materialization events

These helpers pull result sets into memory. For large tables, chunking or server-side filtering must happen before the DataFrame is built.

---

Chunked read

• A pattern where a large SQL result is consumed in batches rather than loaded all at once.

• It matters because the note uses chunksize= and SQL paging patterns to keep large reads inside realistic memory limits.

Chunking changes workflow shape

Once results arrive in batches, downstream code must aggregate or persist incrementally instead of assuming one monolithic DataFrame.

---

to_sql()

• The Pandas method that writes DataFrame rows into a relational table using a database engine.

• It matters because it is the most direct write-back path in Python, but its defaults are often too slow for serious loads.

Default insert mode is conservative

Row-by-row inserts are easy to use and easy to regret. Bulk options matter quickly once row counts stop being tiny.

---

fast_executemany

• A pyodbc execution mode that batches parameterized inserts more efficiently for SQL Server workloads.

• It matters because the note uses it as one of the main bulk-load levers when to_sql() defaults become a bottleneck.

Performance comes from batching, not magic

The gain is largest when inserts are parameterized and sent in sizable batches rather than as individual statements.

---

Stored procedure

• A named SQL routine stored in the database and executed with parameters from client code.

• It matters because the note covers how DataFrame workflows call existing database logic without rewriting it as inline application SQL.

ORM abstraction ends quickly here

Output parameters, multi-result behavior, and procedure-specific conventions often require raw cursor handling.

---

Parameterized query

• A SQL statement where values are bound separately from the SQL text rather than interpolated into the string.

• It matters because the note treats parameterization as the default safe execution pattern for both correctness and security.

String-formatted SQL is a security bug

F-strings and .format() are acceptable for note prose, not for executable query construction against real systems.

---

Connection pooling

• Reusing existing database connections across operations instead of opening a brand-new session for every query.

• It matters because SQLAlchemy pools by default, and the note recommends preserving that behavior for stable ETL and notebook workloads.

Pooling is a throughput feature

Creating engines or connections per query adds latency and unnecessary load even when each individual statement is cheap.

---

import pandas as pd
import polars as pl
import polars.selectors as cs
import numpy as np
from pathlib import Path
 
from IPython.display import display, Markdown
html_formatter = get_ipython().display_formatter.formatters['text/html'] # type: ignore
html_formatter.for_type(pd.DataFrame, lambda df: df.to_html())
html_formatter.for_type(pd.Series, lambda s: s.to_frame().to_html())
 
pl.Config.set_tbl_rows(100)
pd.set_option("display.max_rows", 100)
 
DATA = Path("../data")
 
# Core datasets
ohlcv_pd = pd.read_parquet(DATA / "eurostoxx50_ohlcv.parquet")  # 66K rows, daily OHLCV
ohlcv_pl = pl.read_parquet(DATA / "eurostoxx50_ohlcv.parquet")
dim_pd = pd.read_parquet(DATA / "index_dim.parquet")            # 169 rows, stock metadata
dim_pl = pl.read_parquet(DATA / "index_dim.parquet")
scores_pd = pd.read_parquet(DATA / "scores_daily.parquet")      # 466 rows, composite scores
scores_pl = pl.read_parquet(DATA / "scores_daily.parquet")
 
print(f"OHLCV: {ohlcv_pd.shape}, Dim: {dim_pd.shape}, Scores: {scores_pd.shape}")
import duckdb
import tempfile
import time
import shutil
import os
from dotenv import load_dotenv
from urllib.parse import quote_plus
import pyodbc
import sqlalchemy as sa

OHLCV: (66355, 12), Dim: (169, 26), Scores: (466, 36)

Polars SQLContext

Register Polars DataFrames as virtual SQL tables and query them with standard SQL. pl.SQLContext compiles SQL into Polars lazy expressions — no data copy, no network roundtrip. Results are returned as LazyFrame and must be materialized with .collect().

pl.SQLContext is Python-only — no C# equivalent in Polars.NET 0.4.0

The .NET bindings (Polars.NET 0.4.0) do not implement SQLContext. C# code must use the Polars expression API directly, or route SQL through DuckDB.NET. See 09_cs_database_interface for the DuckDB.NET approach.

Basic SELECT

Polars | Register tables in SQLContext and query with SELECT

Register DataFrames as named tables in a SQLContext instance, then run SQL against them. The execute() method returns a LazyFrame; call .collect() to materialize rows into a DataFrame.

Registers three DataFrames (ohlcv, dim, scores) as named virtual tables, then executes SELECT * FROM ohlcv LIMIT 5 — materializing 5 OHLCV rows with all 12 columns as a Polars DataFrame.

ctx=pl.SQLContext(ohlcv=ohlcv_pl, dim=dim_pl, scores=scores_pl)
display(ctx.execute("SELECT * FROM ohlcv LIMIT 5").collect())

id	symbol	date	open	high	low	close	adj_close	volume	dividends	stock_splits	is_filled
i64	str	date	f64	f64	f64	f64	f64	i64	f64	f64	bool
21160	ABI.BR	2021-01-04	58.15	58.85	56.78	57.21	53.5761	1513937	0.0	0.0	false
21161	ABI.BR	2021-01-05	56.9	57.98	56.75	57.18	53.548	1382722	0.0	0.0	false
21162	ABI.BR	2021-01-06	57.96	58.94	57.39	58.77	55.037	1370204	0.0	0.0	false
21163	ABI.BR	2021-01-07	58.68	58.86	57.88	58.4	54.6905	1469911	0.0	0.0	false
21164	ABI.BR	2021-01-08	58.16	58.4	57.43	57.86	54.1848	1428681	0.0	0.0	false

GROUP BY Aggregation

Polars | GROUP BY with AVG and COUNT across all symbols

Aggregate over all rows grouped by a key column using standard GROUP BY ... ORDER BY SQL. The query runs against the registered ohlcv table and returns per-symbol statistics across the full date range.

Groups 66,355 OHLCV rows by symbol, computes average close price and trading-day count per stock, and returns the top-10 by average close — confirming Hermès (RMS.PA, €1,761) and Adyen (ADYEN.AS, €1,545) as the highest-priced index constituents.

display(ctx.execute("""
    SELECT symbol, AVG(close) as avg_close, COUNT(*) as days
    FROM ohlcv
    GROUP BY symbol
    ORDER BY avg_close DESC
    LIMIT 10
""").collect())

symbol	avg_close	days
str	f64	u32
RMS.PA	1761.555748	1331
ADYEN.AS	1545.976409	1331
ASML.AS	671.348911	1331
MC.PA	662.404508	1331
RHM.DE	544.661533	1324
ARGX.BR	413.691961	1331
OR.PA	377.544365	1331
MUV2.DE	374.659932	1324
RACE.MI	289.753823	1321
ALV.DE	252.193731	1324

JOIN Queries

Polars | INNER JOIN with WHERE filter on country

Join two registered tables on a shared key using JOIN ... USING (column). Equivalent to a Polars expression join(dim_pl, on="symbol"), but expressed in SQL for readability when combining many tables or when porting SQL from another system.

Joins ohlcv and dim on symbol, filters to German-listed stocks only, and returns the top-10 closing prices — demonstrating that Rheinmetall (RHM.DE) holds the highest recorded close (€1,988.50) in the dataset.

display(ctx.execute("""
    SELECT ohlcv.symbol, dim.short_name, dim.sector, ohlcv.close
    FROM ohlcv
    JOIN dim USING (symbol)
    WHERE dim.country = 'Germany'
    ORDER BY ohlcv.close DESC
    LIMIT 10
""").collect())

symbol	short_name	sector	close
str	str	str	f64
RHM.DE	RHEINMETALL AG	Industrials	1988.5
RHM.DE	RHEINMETALL AG	Industrials	1988.5
RHM.DE	RHEINMETALL AG	Industrials	1986.0
RHM.DE	RHEINMETALL AG	Industrials	1978.5
RHM.DE	RHEINMETALL AG	Industrials	1978.5
RHM.DE	RHEINMETALL AG	Industrials	1962.5
RHM.DE	RHEINMETALL AG	Industrials	1962.5
RHM.DE	RHEINMETALL AG	Industrials	1960.5
RHM.DE	RHEINMETALL AG	Industrials	1951.0
RHM.DE	RHEINMETALL AG	Industrials	1950.0

Window Functions in SQL

The SQL syntax used in Polars SQLContext follows the same patterns as sql-fundamentals for SQL Server and bq-fundamentals for BigQuery. For direct Python database access with pyodbc and SQLAlchemy outside of DataFrames, see 16_py_database.

ROWS BETWEEN is unsupported

Polars SQLContext does not implement the ROWS BETWEEN N PRECEDING AND CURRENT ROW frame specification as of Polars 1.x. Sliding-window SQL written for DuckDB, PostgreSQL, or SQL Server therefore does not port over directly.

[!success] Use rolling expressions or DuckDB

For moving averages and other bounded windows, switch to the Polars expression API (pl.col().rolling_mean(window_size)). When you specifically need full SQL frame semantics, route the query through DuckDB instead of pl.SQLContext.

SQL and Expression Window Functions

Polars | Cumulative average with SQL OVER(PARTITION BY ORDER BY)

Compute a cumulative average over all rows up to and including the current row, partitioned by symbol and ordered by date, using AVG(col) OVER (PARTITION BY ... ORDER BY ...). This form of window function is supported in pl.SQLContext; ROWS BETWEEN frame specifications are not.

Computes a running cumulative average of ASML.AS close prices partitioned by symbol and ordered by date — confirming that with ORDER BY but no ROWS BETWEEN frame, the window expands to include all prior rows, yielding the dataset-wide average (671.35) at every position.

result = (
    ctx.execute("""
        SELECT symbol, date, close,
            AVG(close) OVER (PARTITION BY symbol ORDER BY date) as cumulative_avg
        FROM ohlcv
        WHERE symbol = 'ASML.AS'
        ORDER BY date DESC
        LIMIT 10
    """).collect()
)
display(result)

symbol	date	close	cumulative_avg
str	date	f64	f64
ASML.AS	2026-03-12	1190.8	671.348911
ASML.AS	2026-03-11	1198.8	671.348911
ASML.AS	2026-03-10	1200.0	671.348911
ASML.AS	2026-03-09	1147.6	671.348911
ASML.AS	2026-03-06	1147.0	671.348911
ASML.AS	2026-03-05	1186.0	671.348911
ASML.AS	2026-03-04	1199.8	671.348911
ASML.AS	2026-03-03	1161.8	671.348911
ASML.AS	2026-03-02	1210.4	671.348911
ASML.AS	2026-02-27	1233.4	671.348911

Polars | SMA-7 via expression API rolling_mean()

For sliding-window aggregations (e.g., a 7-day simple moving average), use pl.col().rolling_mean(window_size) via the Polars expression API. This is the preferred approach when ROWS BETWEEN SQL syntax is needed but not yet available in pl.SQLContext.

Filters ASML.AS rows, sorts chronologically, and computes a strict 7-day rolling mean on close — showing that the most recent SMA-7 (≈1,181) smooths the daily close swings visible in the sorted-descending output.

sma_result = (
    ohlcv_pl
    .filter(pl.col("symbol") == "ASML.AS")
    .sort("date")
    .with_columns(pl.col("close").rolling_mean(7).alias("sma_7"))
    .select("symbol", "date", "close", "sma_7")
    .sort("date", descending=True)
    .head(10)
)
display(sma_result)

symbol	date	close	sma_7
str	date	f64	f64
ASML.AS	2026-03-12	1190.8	1181.428571
ASML.AS	2026-03-11	1198.8	1177.285714
ASML.AS	2026-03-10	1200.0	1178.942857
ASML.AS	2026-03-09	1147.6	1183.714286
ASML.AS	2026-03-06	1147.0	1195.828571
ASML.AS	2026-03-05	1186.0	1216.028571
ASML.AS	2026-03-04	1199.8	1227.085714
ASML.AS	2026-03-03	1161.8	1234.142857
ASML.AS	2026-03-02	1210.4	1247.542857
ASML.AS	2026-02-27	1233.4	1251.514286

DuckDB — Embedded Analytical Database

DuckDB is an in-process OLAP database (like SQLite for analytics). It queries Pandas/Polars DataFrames in-place, reads Parquet/CSV directly, and supports full SQL including window functions, CTEs, and JSON. No server needed — runs in the notebook process.

Setup & Create Tables from Parquet

# In-memory database (default)
db = duckdb.connect()
 
# Load the same datasets as SQL Server tables — directly from Parquet
db.execute("CREATE TABLE eurostoxx50_ohlcv AS SELECT * FROM read_parquet('../data/eurostoxx50_ohlcv.parquet')")
db.execute("CREATE TABLE index_dim AS SELECT * FROM read_parquet('../data/index_dim.parquet')")
db.execute("CREATE TABLE scores_daily AS SELECT * FROM read_parquet('../data/scores_daily.parquet')")
 
# Verify
for t in ["eurostoxx50_ohlcv", "index_dim", "scores_daily"]:
    row = db.execute(f"SELECT COUNT(*) FROM {t}").fetchone()
    count = row[0] if row else 0
    print(f"{t}: {count:,} rows")

eurostoxx50_ohlcv: 66,355 rows index_dim: 169 rows scores_daily: 466 rows

Basic Queries

# SELECT with filtering and ordering
display(db.execute("""
    SELECT symbol, date, close, volume
    FROM eurostoxx50_ohlcv
    WHERE symbol = 'ASML.AS'
    ORDER BY date DESC
    LIMIT 10
""").df())

	symbol	date	close	volume
0	ASML.AS	2026-03-12	1190.8	128223
1	ASML.AS	2026-03-11	1198.8	562904
2	ASML.AS	2026-03-10	1200.0	800815
3	ASML.AS	2026-03-09	1147.6	689086
4	ASML.AS	2026-03-06	1147.0	857271
5	ASML.AS	2026-03-05	1186.0	778081
6	ASML.AS	2026-03-04	1199.8	714587
7	ASML.AS	2026-03-03	1161.8	941945
8	ASML.AS	2026-03-02	1210.4	871267
9	ASML.AS	2026-02-27	1233.4	1010698

# Aggregation
display(db.execute("""
    SELECT symbol,
           COUNT(*) as days,
           ROUND(AVG(close), 2) as avg_close,
           ROUND(MIN(close), 2) as min_close,
           ROUND(MAX(close), 2) as max_close
    FROM eurostoxx50_ohlcv
    GROUP BY symbol
    ORDER BY avg_close DESC
    LIMIT 10
""").df())

	symbol	days	avg_close	min_close	max_close
0	RMS.PA	1331	1761.56	842.60	2839.0
1	ADYEN.AS	1331	1545.98	630.80	2766.0
2	ASML.AS	1331	671.35	397.45	1288.4
3	MC.PA	1331	662.40	437.55	902.0
4	RHM.DE	1324	544.66	77.00	1988.5
5	ARGX.BR	1331	413.69	208.80	803.0
6	OR.PA	1331	377.54	290.10	456.9
7	MUV2.DE	1324	374.66	209.15	610.6
8	RACE.MI	1321	289.75	154.70	487.9
9	ALV.DE	1324	252.19	159.62	392.7

# JOIN tables
display(db.execute("""
    SELECT d.symbol, d.short_name, d.sector, d.country,
           ROUND(AVG(o.close), 2) as avg_close,
           ROUND(AVG(o.volume), 0) as avg_volume
    FROM eurostoxx50_ohlcv o
    JOIN index_dim d USING (symbol)
    GROUP BY d.symbol, d.short_name, d.sector, d.country
    ORDER BY avg_close DESC
    LIMIT 10
""").df())

	symbol	short_name	sector	country	avg_close	avg_volume
0	RMS.PA	HERMES INTL	Consumer Cyclical	France	1761.56	61333.0
1	ADYEN.AS	ADYEN	Technology	Netherlands	1545.98	82946.0
2	ASML.AS	ASML HOLDING	Technology	Netherlands	671.35	710046.0
3	MC.PA	LVMH	Consumer Cyclical	France	662.40	419125.0
4	RHM.DE	RHEINMETALL AG	Industrials	Germany	544.66	232900.0
5	ARGX.BR	ARGENX SE	Healthcare	Netherlands	413.69	71069.0
6	OR.PA	L'OREAL	Consumer Defensive	France	377.54	363723.0
7	MUV2.DE	MUENCHENER RUECKVERS.-GES. AG N	Financial Services	Germany	374.66	301211.0
8	RACE.MI	FERRARI	Consumer Cyclical	Italy	289.75	360852.0
9	ALV.DE	Allianz SE	Financial Services	Germany	252.19	832296.0

Query DataFrames Directly (Zero-Copy)

DuckDB resolves Python variable names in the caller’s scope as SQL table references. Any Pandas DataFrame or Polars DataFrame/LazyFrame in scope can be queried by name — no CREATE TABLE or data copy needed.

Zero-copy scan: DuckDB reads Pandas/Polars DataFrames without duplicating data

When you reference ohlcv_pd in a DuckDB SQL query, DuckDB uses Apache Arrow zero-copy to scan the DataFrame’s underlying memory buffers directly. This means you can run analytical SQL against a 66K-row Pandas DataFrame without incurring a serialization/deserialization cost. Works for both Pandas (DataFrame) and Polars (DataFrame and LazyFrame).

# DuckDB can query Pandas DataFrames by variable name — no import needed
display(db.execute("""
    SELECT symbol, AVG(close) as avg_close
    FROM ohlcv_pd
    GROUP BY symbol
    ORDER BY avg_close DESC
    LIMIT 5
""").df())

	symbol	avg_close
0	RMS.PA	1761.555748
1	ADYEN.AS	1545.976409
2	ASML.AS	671.348911
3	MC.PA	662.404508
4	RHM.DE	544.661533

# Also works with Polars DataFrames
display(db.execute("""
    SELECT sector, COUNT(*) as stocks
    FROM dim_pl
    GROUP BY sector
    ORDER BY stocks DESC
""").df())

	sector	stocks
0	Financial Services	32
1	Technology	26
2	Energy	24
3	Industrials	22
4	Consumer Cyclical	18
5	Healthcare	15
6	Consumer Defensive	12
7	Communication Services	12
8	Basic Materials	6
9	Utilities	2

Window Functions

# Rank stocks by avg close within each sector
display(db.execute("""
    SELECT symbol, sector, avg_close,
           RANK() OVER (PARTITION BY sector ORDER BY avg_close DESC) as sector_rank
    FROM (
        SELECT o.symbol, d.sector, ROUND(AVG(o.close), 2) as avg_close
        FROM eurostoxx50_ohlcv o
        JOIN index_dim d USING (symbol)
        GROUP BY o.symbol, d.sector
    )
    QUALIFY sector_rank <= 3
    ORDER BY sector, sector_rank
""").df())

	symbol	sector	avg_close	sector_rank
0	AI.PA	Basic Materials	145.43	1
1	BAS.DE	Basic Materials	50.56	2
2	DTE.DE	Communication Services	22.43	1
3	RMS.PA	Consumer Cyclical	1761.56	1
4	MC.PA	Consumer Cyclical	662.40	2
5	RACE.MI	Consumer Cyclical	289.75	3
6	OR.PA	Consumer Defensive	377.54	1
7	BN.PA	Consumer Defensive	60.29	2
8	ABI.BR	Consumer Defensive	54.86	3
9	TTE.PA	Energy	53.14	1
10	ENI.MI	Energy	13.40	2
11	MUV2.DE	Financial Services	374.66	1
12	ALV.DE	Financial Services	252.19	2
13	DB1.DE	Financial Services	184.80	3
14	ARGX.BR	Healthcare	413.69	1
15	EL.PA	Healthcare	191.91	2
16	SAN.PA	Healthcare	90.30	3
17	RHM.DE	Industrials	544.66	1
18	SU.PA	Industrials	179.03	2
19	SAF.PA	Industrials	171.83	3
20	ADYEN.AS	Technology	1545.98	1
21	ASML.AS	Technology	671.35	2
22	SAP.DE	Technology	154.33	3
23	IBE.MC	Utilities	12.26	1
24	ENEL.MI	Utilities	6.82	2

# Running average and lag/lead
display(db.execute("""
    SELECT date, close,
           ROUND(AVG(close) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) as sma_7,
           ROUND(AVG(close) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW), 2) as sma_30,
           LAG(close, 1) OVER (ORDER BY date) as prev_close,
           ROUND((close - LAG(close, 1) OVER (ORDER BY date)) / LAG(close, 1) OVER (ORDER BY date) * 100, 2) as daily_return_pct
    FROM eurostoxx50_ohlcv
    WHERE symbol = 'ASML.AS'
    ORDER BY date DESC
    LIMIT 15
""").df())

	date	close	sma_7	sma_30	prev_close	daily_return_pct
0	2026-03-12	1190.8	1181.43	1204.41	1198.8	-0.67
1	2026-03-11	1198.8	1177.29	1204.45	1200.0	-0.10
2	2026-03-10	1200.0	1178.94	1204.31	1147.6	4.57
3	2026-03-09	1147.6	1183.71	1204.89	1147.0	0.05
4	2026-03-06	1147.0	1195.83	1205.91	1186.0	-3.29
5	2026-03-05	1186.0	1216.03	1206.95	1199.8	-1.15
6	2026-03-04	1199.8	1227.09	1206.63	1161.8	3.27
7	2026-03-03	1161.8	1234.14	1205.13	1210.4	-4.02
8	2026-03-02	1210.4	1247.54	1204.40	1233.4	-1.86
9	2026-02-27	1233.4	1251.51	1201.40	1232.4	0.08
10	2026-02-26	1232.4	1253.14	1199.19	1288.4	-4.35
11	2026-02-25	1288.4	1248.40	1196.43	1263.4	1.98
12	2026-02-24	1263.4	1235.06	1189.62	1249.2	1.14
13	2026-02-23	1249.2	1224.63	1184.26	1255.6	-0.51
14	2026-02-20	1255.6	1214.71	1178.83	1238.2	1.41

# NTILE, PERCENT_RANK, CUME_DIST
display(db.execute("""
    SELECT symbol, avg_close,
           NTILE(4) OVER (ORDER BY avg_close) as quartile,
           ROUND(PERCENT_RANK() OVER (ORDER BY avg_close), 3) as pct_rank,
           ROUND(CUME_DIST() OVER (ORDER BY avg_close), 3) as cume_dist
    FROM (
        SELECT symbol, ROUND(AVG(close), 2) as avg_close
        FROM eurostoxx50_ohlcv
        GROUP BY symbol
    )
    ORDER BY avg_close DESC
    LIMIT 15
""").df())

	symbol	avg_close	quartile	pct_rank	cume_dist
0	RMS.PA	1761.56	4	1.000	1.00
1	ADYEN.AS	1545.98	4	0.980	0.98
2	ASML.AS	671.35	4	0.959	0.96
3	MC.PA	662.40	4	0.939	0.94
4	RHM.DE	544.66	4	0.918	0.92
5	ARGX.BR	413.69	4	0.898	0.90
6	OR.PA	377.54	4	0.878	0.88
7	MUV2.DE	374.66	4	0.857	0.86
8	RACE.MI	289.75	4	0.837	0.84
9	ALV.DE	252.19	4	0.816	0.82
10	ADS.DE	205.43	4	0.796	0.80
11	EL.PA	191.91	4	0.776	0.78
12	DB1.DE	184.80	3	0.755	0.76
13	SU.PA	179.03	3	0.735	0.74
14	SAF.PA	171.83	3	0.714	0.72

Common Table Expressions (CTEs)

# Multi-level CTE
display(db.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) * 100 as ret
        FROM eurostoxx50_ohlcv
    ),
    volatility AS (
        SELECT symbol,
               ROUND(STDDEV(ret), 2) as daily_vol,
               ROUND(AVG(ret), 4) as avg_ret,
               COUNT(*) as days
        FROM daily_returns
        WHERE ret IS NOT NULL
        GROUP BY symbol
    )
    SELECT v.symbol, d.sector, v.daily_vol, v.avg_ret,
           ROUND(v.daily_vol * SQRT(252), 2) as annualized_vol
    FROM volatility v
    JOIN index_dim d USING (symbol)
    ORDER BY annualized_vol DESC
    LIMIT 10
""").df())

	symbol	sector	daily_vol	avg_ret	annualized_vol
0	ADYEN.AS	Technology	3.17	-0.0010	50.32
1	ENR.DE	Industrials	3.15	0.1762	50.00
2	RHM.DE	Industrials	2.57	0.2498	40.80
3	PRX.AS	Consumer Cyclical	2.50	0.0400	39.69
4	ARGX.BR	Healthcare	2.48	0.1015	39.37
5	ASML.AS	Technology	2.37	0.1091	37.62
6	IFX.DE	Technology	2.35	0.0455	37.31
7	VOW.DE	Consumer Cyclical	2.24	-0.0194	35.56
8	UCG.MI	Financial Services	2.24	0.1891	35.56
9	ADS.DE	Consumer Cyclical	2.17	-0.0330	34.45

Recursive CTE

# Generate a date series using recursive CTE
display(db.execute("""
    WITH RECURSIVE dates AS (
        SELECT DATE '2024-01-01' as dt
        UNION ALL
        SELECT dt + INTERVAL 1 DAY FROM dates WHERE dt < DATE '2024-01-10'
    )
    SELECT dt, DAYNAME(dt) as day_name FROM dates
""").df())

	dt	day_name
0	2024-01-01	Monday
1	2024-01-02	Tuesday
2	2024-01-03	Wednesday
3	2024-01-04	Thursday
4	2024-01-05	Friday
5	2024-01-06	Saturday
6	2024-01-07	Sunday
7	2024-01-08	Monday
8	2024-01-09	Tuesday
9	2024-01-10	Wednesday

Read Files Directly (No Import Step)

# Query Parquet files without loading into memory
display(db.execute("""
    SELECT symbol, date, close
    FROM read_parquet('../data/eurostoxx50_ohlcv.parquet')
    WHERE symbol = 'SAP.DE'
    ORDER BY date DESC
    LIMIT 5
""").df())

	symbol	date	close
0	SAP.DE	2026-03-12	166.52
1	SAP.DE	2026-03-11	165.44
2	SAP.DE	2026-03-10	169.60
3	SAP.DE	2026-03-09	171.88
4	SAP.DE	2026-03-06	172.74

# Query CSV files directly
display(db.execute("""
    SELECT *
    FROM read_csv_auto('../data/scores_daily.csv')
    LIMIT 5
""").df())

	id	_index	symbol	score_date	sector	pe_zscore	pb_zscore	ev_ebitda_zscore	yield_zscore	relative_value_score	relative_value_rank	relative_strength	sma_50_ratio	sma_200_ratio	dist_from_52w_high	momentum_score	momentum_rank	implied_upside	recommendation_mean	price_falling_analysts_bullish	sentiment_score	sentiment_rank	composite_score	composite_rank	_scored_at	sma_30_close	sma_90_close	market_cap	index_weight	short_name	country	current_price	day_change_pct	five_day_change_pct	ytd_change_pct	currency
0	163	euro_stoxx_50	BNP.PA	2026-03-04	Financial Services	0.913389	1.261140	NaN	2.388962	1.521163	1	0.016123	1.009090	1.130264	0.082486	0.477966	16	0.153157	1.84211	False	0.052711	25	0.683947	1	2026-03-04 22:40:25.489180	92.085000	81.181889	99751215104	0.019525	BNP PARIBAS ACT.A	France	89.320	0.011437	-0.073156	0.105582	EUR
1	168	euro_stoxx_50	DTE.DE	2026-03-04	Communication Services	0.326587	0.387463	0.379532	-0.127867	0.241429	24	-0.205598	1.120650	1.112416	0.055524	0.685752	8	0.121212	1.33333	False	0.617835	10	0.515005	2	2026-03-04 22:40:25.489180	30.838000	28.554556	164294311936	0.032159	DEUTSCHE TELEKOM AG	Germany	33.000	0.011649	-0.019608	0.193059	EUR
2	174	euro_stoxx_50	IFX.DE	2026-03-04	Technology	0.509398	0.637215	0.677068	-0.696662	0.281755	22	0.000965	1.048244	1.198626	0.088845	0.675764	9	0.126408	1.37500	False	0.579187	11	0.512235	3	2026-03-04 22:40:25.489180	43.480333	38.855556	57222533120	0.011201	INFINEON TECHNOLOGIES AG	Germany	43.945	0.054343	-0.066490	0.164723	EUR
3	172	euro_stoxx_50	ENR.DE	2026-03-04	Industrials	-0.902738	-0.743338	-1.693212	-1.326075	-1.166341	46	1.645455	1.137007	1.474095	0.051850	2.541889	1	0.075269	1.80000	False	-0.123264	29	0.417428	4	2026-03-04 22:40:25.489180	155.675000	129.122000	139207262208	0.027249	Siemens Energy AG	Germany	162.750	0.047297	-0.039256	0.351744	EUR
4	149	euro_stoxx_50	ABI.BR	2026-03-04	Consumer Defensive	0.474084	0.844075	0.552739	-0.975005	0.223973	25	-0.029791	1.058542	1.142783	0.063063	0.651891	10	0.186198	1.69231	False	0.344755	17	0.406873	5	2026-03-04 22:40:25.489180	64.342000	57.869333	125566156800	0.024579	AB INBEV	Belgium	64.480	-0.017073	-0.040762	0.174499	EUR

# Query multiple Parquet files with glob
display(db.execute("""
    SELECT COUNT(*) as total_rows, MIN(date) as earliest, MAX(date) as latest
    FROM read_parquet('../data/*_ohlcv.parquet')
""").df())

	total_rows	earliest	latest
0	156193	2021-01-04	2026-03-12

Export Results

TMP = Path(tempfile.mkdtemp())
 
# Export query result to Parquet
db.execute(f"""
    COPY (
        SELECT symbol, date, close FROM eurostoxx50_ohlcv
        WHERE symbol = 'ASML.AS'
    ) TO '{TMP}/asml.parquet' (FORMAT PARQUET, COMPRESSION ZSTD)
""")
print(f"Parquet: {(TMP / 'asml.parquet').stat().st_size:,} bytes")
 
# Export to CSV
db.execute(f"""
    COPY (
        SELECT symbol, date, close FROM eurostoxx50_ohlcv
        WHERE symbol = 'ASML.AS'
    ) TO '{TMP}/asml.csv' (FORMAT CSV, HEADER TRUE)
""")
print(f"CSV: {(TMP / 'asml.csv').stat().st_size:,} bytes")
 
# Export to JSON
db.execute(f"""
    COPY (
        SELECT symbol, date, close FROM eurostoxx50_ohlcv
        WHERE symbol = 'ASML.AS'
        LIMIT 5
    ) TO '{TMP}/asml.json' (FORMAT JSON)
""")
print(f"JSON: {(TMP / 'asml.json').stat().st_size:,} bytes")
print((TMP / "asml.json").read_text()[:300])

Parquet: 6,823 bytes CSV: 33,410 bytes JSON: 278 bytes {“symbol”:“ASML.AS”,“date”:“2021-01-04”,“close”:406.25} {“symbol”:“ASML.AS”,“date”:“2021-01-05”,“close”:406.9} {“symbol”:“ASML.AS”,“date”:“2021-01-06”,“close”:402.85} {“symbol”:“ASML.AS”,“date”:“2021-01-07”,“close”:403.9} {“symbol”:“ASML.AS”,“date”:“2021-01-08”,“close”:416.05}

Result Conversion: Pandas, Polars, Arrow

query = "SELECT symbol, date, close FROM eurostoxx50_ohlcv WHERE symbol = 'ASML.AS' LIMIT 5"
 
# To Pandas
df_pd = db.execute(query).df()
print(f"Pandas: {type(df_pd).__name__}, shape={df_pd.shape}")
 
# To Polars
df_pl = db.execute(query).pl()
print(f"Polars: {type(df_pl).__name__}, shape={df_pl.shape}")
 
# To Arrow
table = db.execute(query).arrow().read_all()
print(f"Arrow:  {type(table).__name__}, rows={table.num_rows}")
 
# To Python lists
rows = db.execute(query).fetchall()
print(f"Python: {len(rows)} rows, first={rows[0]}")
 
# To numpy
arr = db.execute("SELECT close FROM eurostoxx50_ohlcv WHERE symbol = 'ASML.AS' LIMIT 5").fetchnumpy()
print(f"NumPy:  {arr['close']}")

Pandas: DataFrame, shape=(5, 3) Polars: DataFrame, shape=(5, 3) Arrow: Table, rows=5 Python: 5 rows, first=(‘ASML.AS’, datetime.date(2021, 1, 4), 406.25) NumPy: [406.25 406.9 402.85 403.9 416.05]

Persistent Database (on disk)

# Create a persistent DuckDB file
db_path = TMP / "stoxx.duckdb"
pdb = duckdb.connect(str(db_path))
 
# Create tables from Parquet
pdb.execute("CREATE OR REPLACE TABLE ohlcv AS SELECT * FROM read_parquet('../data/eurostoxx50_ohlcv.parquet')")
pdb.execute("CREATE OR REPLACE TABLE dim AS SELECT * FROM read_parquet('../data/index_dim.parquet')")
print(f"Database file: {db_path.stat().st_size:,} bytes")
 
# Close and reopen — data persists
pdb.close()
pdb = duckdb.connect(str(db_path))
row_count = pdb.execute("SELECT COUNT(*) FROM ohlcv").fetchone()
if row_count:
    print(f"After reopen: {row_count[0]:,} rows")
pdb.close()

Database file: 12,288 bytes After reopen: 66,355 rows

Views & Macros

# Create a view (virtual table — query runs on access)
db.execute("""
    CREATE OR REPLACE VIEW v_stock_summary AS
    SELECT o.symbol, d.short_name, d.sector, d.country,
           COUNT(*) as days,
           ROUND(AVG(o.close), 2) as avg_close,
           ROUND(STDDEV(o.close), 2) as std_close
    FROM eurostoxx50_ohlcv o
    JOIN index_dim d USING (symbol)
    GROUP BY o.symbol, d.short_name, d.sector, d.country
""")
display(db.execute("SELECT * FROM v_stock_summary ORDER BY avg_close DESC LIMIT 5").df())

	symbol	short_name	sector	country	days	avg_close	std_close
0	RMS.PA	HERMES INTL	Consumer Cyclical	France	1331	1761.56	481.32
1	ADYEN.AS	ADYEN	Technology	Netherlands	1331	1545.98	417.81
2	ASML.AS	ASML HOLDING	Technology	Netherlands	1331	671.35	162.75
3	MC.PA	LVMH	Consumer Cyclical	France	1331	662.40	103.50
4	RHM.DE	RHEINMETALL AG	Industrials	Germany	1324	544.66	586.08

# Create a SQL macro (reusable function)
db.execute("""
    CREATE OR REPLACE MACRO sma(col, n) AS (
        AVG(col) OVER (ORDER BY date ROWS BETWEEN (n-1) PRECEDING AND CURRENT ROW)
    )
""")
 
# Use the macro
display(db.execute("""
    SELECT date, close,
           ROUND(sma(close, 7), 2) as sma_7,
           ROUND(sma(close, 30), 2) as sma_30
    FROM eurostoxx50_ohlcv
    WHERE symbol = 'ASML.AS'
    ORDER BY date DESC
    LIMIT 10
""").df())

	date	close	sma_7	sma_30
0	2026-03-12	1190.8	1181.43	1204.41
1	2026-03-11	1198.8	1177.29	1204.45
2	2026-03-10	1200.0	1178.94	1204.31
3	2026-03-09	1147.6	1183.71	1204.89
4	2026-03-06	1147.0	1195.83	1205.91
5	2026-03-05	1186.0	1216.03	1206.95
6	2026-03-04	1199.8	1227.09	1206.63
7	2026-03-03	1161.8	1234.14	1205.13
8	2026-03-02	1210.4	1247.54	1204.40
9	2026-02-27	1233.4	1251.51	1201.40

JSON Functions

# DuckDB has full JSON support
display(db.execute("""
    SELECT
        json_object('symbol', symbol, 'close', close, 'date', date) as json_row,
        json_extract(json_object('symbol', symbol, 'close', close), '$.symbol') as extracted
    FROM eurostoxx50_ohlcv
    WHERE symbol = 'ASML.AS'
    ORDER BY date DESC
    LIMIT 5
""").df())

	json_row	extracted
0	{"symbol":"ASML.AS","close":1190.8,"date":"2026-03-12"}	"ASML.AS"
1	{"symbol":"ASML.AS","close":1198.8,"date":"2026-03-11"}	"ASML.AS"
2	{"symbol":"ASML.AS","close":1200.0,"date":"2026-03-10"}	"ASML.AS"
3	{"symbol":"ASML.AS","close":1147.6,"date":"2026-03-09"}	"ASML.AS"
4	{"symbol":"ASML.AS","close":1147.0,"date":"2026-03-06"}	"ASML.AS"

String & Date Functions

display(db.execute("""
    SELECT
        symbol,
        SPLIT_PART(symbol, '.', 1) as ticker,
        SPLIT_PART(symbol, '.', 2) as exchange,
        LENGTH(symbol) as sym_len,
        UPPER(short_name) as upper_name,
        LEFT(short_name, 10) as short
    FROM index_dim
    LIMIT 10
""").df())

	symbol	ticker	exchange	sym_len	upper_name	short
0	ASML.AS	ASML	AS	7	ASML HOLDING	ASML HOLDI
1	MC.PA	MC	PA	5	LVMH	LVMH
2	RMS.PA	RMS	PA	6	HERMES INTL	HERMES INT
3	OR.PA	OR	PA	5	L'OREAL	L'OREAL
4	SAP.DE	SAP	DE	6	SAP SE	SAP SE
5	SIE.DE	SIE	DE	6	SIEMENS AG	SIEMENS AG
6	ITX.MC	ITX	MC	6	INDUSTRIA DE DISE...O TEXTIL S.	INDUSTRIA
7	DTE.DE	DTE	DE	6	DEUTSCHE TELEKOM AG	DEUTSCHE T
8	SAN.MC	SAN	MC	6	BANCO SANTANDER S.A.	BANCO SANT
9	SU.PA	SU	PA	5	SCHNEIDER ELECTRIC SE	SCHNEIDER

display(db.execute("""
    SELECT
        date,
        YEAR(date) as yr,
        MONTH(date) as mo,
        DAYNAME(date) as day_name,
        WEEKOFYEAR(date) as week,
        date - INTERVAL 7 DAY as week_ago,
        DATEDIFF('day', MIN(date) OVER (), date) as days_since_start
    FROM eurostoxx50_ohlcv
    WHERE symbol = 'ASML.AS'
    ORDER BY date DESC
    LIMIT 10
""").df())

	date	yr	mo	day_name	week	week_ago	days_since_start
0	2026-03-12	2026	3	Thursday	11	2026-03-05	1893
1	2026-03-11	2026	3	Wednesday	11	2026-03-04	1892
2	2026-03-10	2026	3	Tuesday	11	2026-03-03	1891
3	2026-03-09	2026	3	Monday	11	2026-03-02	1890
4	2026-03-06	2026	3	Friday	10	2026-02-27	1887
5	2026-03-05	2026	3	Thursday	10	2026-02-26	1886
6	2026-03-04	2026	3	Wednesday	10	2026-02-25	1885
7	2026-03-03	2026	3	Tuesday	10	2026-02-24	1884
8	2026-03-02	2026	3	Monday	10	2026-02-23	1883
9	2026-02-27	2026	2	Friday	9	2026-02-20	1880

PIVOT & UNPIVOT

# PIVOT: rows to columns
display(db.execute("""
    PIVOT (
        SELECT d.sector, YEAR(o.date) as yr, ROUND(AVG(o.close), 2) as avg_close
        FROM eurostoxx50_ohlcv o
        JOIN index_dim d USING (symbol)
        GROUP BY d.sector, YEAR(o.date)
    )
    ON yr
    USING AVG(avg_close)
    ORDER BY sector
""").df())

	sector	2021	2022	2023	2024	2025	2026
0	Basic Materials	92.40	86.26	95.60	105.90	109.07	105.97
1	Communication Services	16.67	18.06	20.68	24.50	30.82	30.17
2	Consumer Cyclical	308.44	296.79	383.32	423.22	424.14	382.46
3	Consumer Defensive	125.78	119.62	135.95	137.95	132.23	138.42
4	Energy	25.07	31.99	36.07	37.97	34.42	40.00
5	Financial Services	64.25	66.01	79.92	99.19	125.30	127.33
6	Healthcare	136.39	157.01	177.90	189.54	247.28	265.22
7	Industrials	89.13	92.61	116.08	163.54	286.22	321.53
8	Technology	599.48	452.04	408.80	473.39	506.48	517.70
9	Utilities	9.17	7.89	8.54	9.44	11.79	14.28

Parameterized Queries

# Positional parameters with $1, $2...
result = db.execute(
    "SELECT * FROM eurostoxx50_ohlcv WHERE symbol = $1 AND close > $2 ORDER BY date DESC LIMIT 5",
    ["ASML.AS", 700.0]
).df()
display(result)
 
# Named parameters (DuckDB 0.9+)
result = db.execute(
    "SELECT * FROM eurostoxx50_ohlcv WHERE symbol = $sym ORDER BY date DESC LIMIT $n",
    {"sym": "SAP.DE", "n": 5}
).df()
display(result)

	id	symbol	date	open	high	low	close	adj_close	volume	dividends	stock_splits	is_filled
0	66881	ASML.AS	2026-03-12	1194.8	1202.2	1187.8	1190.8	1190.8	128223	0.0	0.0	False
1	66733	ASML.AS	2026-03-11	1188.4	1210.8	1174.0	1198.8	1198.8	562904	0.0	0.0	False
2	66732	ASML.AS	2026-03-10	1188.4	1208.4	1172.2	1200.0	1200.0	800815	0.0	0.0	False
3	66731	ASML.AS	2026-03-09	1072.0	1147.6	1060.2	1147.6	1147.6	689086	0.0	0.0	False
4	64732	ASML.AS	2026-03-06	1186.0	1192.6	1112.8	1147.0	1147.0	857271	0.0	0.0	False

	id	symbol	date	open	high	low	close	adj_close	volume	dividends	stock_splits	is_filled
0	66885	SAP.DE	2026-03-12	163.00	166.74	162.80	166.52	166.52	806722	0.0	0.0	False
1	66745	SAP.DE	2026-03-11	167.10	168.96	163.02	165.44	165.44	2953782	0.0	0.0	False
2	66744	SAP.DE	2026-03-10	171.60	172.88	166.46	169.60	169.60	3187246	0.0	0.0	False
3	66743	SAP.DE	2026-03-09	173.72	173.86	168.52	171.88	171.88	1990823	0.0	0.0	False
4	64740	SAP.DE	2026-03-06	173.66	175.10	170.24	172.74	172.74	3347221	0.0	0.0	False

Transactions & DDL

# Create, insert, update, delete
db.execute("CREATE OR REPLACE TABLE _test (id INTEGER, name VARCHAR, score DOUBLE)")
db.execute("INSERT INTO _test VALUES (1, 'alpha', 0.9), (2, 'beta', 0.7), (3, 'gamma', 0.5)")
display(db.execute("SELECT * FROM _test").df())
 
# Update
db.execute("UPDATE _test SET score = 0.95 WHERE name = 'alpha'")
 
# Delete
db.execute("DELETE FROM _test WHERE name = 'gamma'")
display(db.execute("SELECT * FROM _test").df())
 
# Transaction
db.execute("BEGIN TRANSACTION")
db.execute("INSERT INTO _test VALUES (4, 'delta', 0.8)")
db.execute("ROLLBACK")  # undo
row = db.execute("SELECT COUNT(*) FROM _test").fetchone()
if row:
    print(f"After rollback: {row[0]} rows")
 
db.execute("DROP TABLE _test")

	id	name	score
0	1	alpha	0.9
1	2	beta	0.7
2	3	gamma	0.5

	id	name	score
0	1	alpha	0.95
1	2	beta	0.70

After rollback: 2 rows

<_duckdb.DuckDBPyConnection at 0x1e428aaf430>

Performance: DuckDB vs Pandas vs Polars

# Complex analytical query
query_sql = """
    SELECT symbol, sector,
           AVG(close) as avg_close,
           STDDEV(close) as std_close,
           COUNT(*) as days
    FROM eurostoxx50_ohlcv o
    JOIN index_dim d USING (symbol)
    GROUP BY symbol, sector
    ORDER BY avg_close DESC
"""
 
# DuckDB
t0 = time.perf_counter()
df_duck = db.execute(query_sql).df()
t_duck = time.perf_counter() - t0
 
# Pandas equivalent
t0 = time.perf_counter()
df_pandas = (ohlcv_pd.merge(dim_pd[["symbol", "sector"]], on="symbol")
             .groupby(["symbol", "sector"])["close"]
             .agg(["mean", "std", "count"])
             .reset_index()
             .sort_values("mean", ascending=False))
t_pandas = time.perf_counter() - t0
 
# Polars equivalent
t0 = time.perf_counter()
df_polars = (ohlcv_pl.join(dim_pl.select("symbol", "sector"), on="symbol")
             .group_by("symbol", "sector")
             .agg(pl.col("close").mean().alias("avg_close"),
                  pl.col("close").std().alias("std_close"),
                  pl.col("close").count().alias("days"))
             .sort("avg_close", descending=True))
t_polars = time.perf_counter() - t0
 
print(f"DuckDB:  {t_duck:.3f}s")
print(f"Pandas:  {t_pandas:.3f}s")
print(f"Polars:  {t_polars:.3f}s")

DuckDB: 0.007s Pandas: 0.011s Polars: 0.003s

Schema Inspection

# List tables
display(db.execute("SHOW TABLES").df())
 
# Describe a table
display(db.execute("DESCRIBE eurostoxx50_ohlcv").df())
 
# Table sizes
display(db.execute("""
    SELECT table_name, estimated_size, column_count
    FROM duckdb_tables()
    ORDER BY estimated_size DESC
""").df())

	name
0	eurostoxx50_ohlcv
1	index_dim
2	scores_daily
3	v_stock_summary

	column_name	column_type	null	key	default	extra
0	id	BIGINT	YES	None	None	None
1	symbol	VARCHAR	YES	None	None	None
2	date	DATE	YES	None	None	None
3	open	DOUBLE	YES	None	None	None
4	high	DOUBLE	YES	None	None	None
5	low	DOUBLE	YES	None	None	None
6	close	DOUBLE	YES	None	None	None
7	adj_close	DOUBLE	YES	None	None	None
8	volume	BIGINT	YES	None	None	None
9	dividends	DOUBLE	YES	None	None	None
10	stock_splits	DOUBLE	YES	None	None	None
11	is_filled	BOOLEAN	YES	None	None	None

	table_name	estimated_size	column_count
0	eurostoxx50_ohlcv	66355	12
1	scores_daily	466	36
2	index_dim	169	26

# Cleanup temp files
shutil.rmtree(TMP, ignore_errors=True)
db.execute("DROP VIEW IF EXISTS v_stock_summary")
db.execute("DROP MACRO IF EXISTS sma")
print("DuckDB cleanup done")

DuckDB cleanup done

DuckDB Summary

Feature	DuckDB
Setup	duckdb.connect() (in-memory) or duckdb.connect('file.duckdb')
Query DataFrames	db.execute('SELECT * FROM df_variable') — zero-copy
Read Parquet/CSV	read_parquet('path'), read_csv_auto('path') — no import step
Glob files	read_parquet('data/*.parquet')
Window functions	Full support: RANK, LAG, ROWS BETWEEN, QUALIFY
CTEs	WITH ... AS, recursive CTEs
PIVOT/UNPIVOT	Native PIVOT syntax
Views	CREATE VIEW
Macros	CREATE MACRO name(args) AS (expr)
JSON	json_object(), json_extract()
Parameters	$1 positional, $name named
Export	COPY ... TO 'file' (FORMAT PARQUET/CSV/JSON)
Result to Pandas	.df()
Result to Polars	.pl()
Result to Arrow	.arrow()
Persistent storage	duckdb.connect('file.duckdb')
vs Pandas	Often 2–10x faster for analytical queries
vs Polars	Comparable speed; DuckDB wins on complex SQL, Polars on expression API

Part 1 Summary

Feature	Polars SQL	DuckDB
Setup	pl.SQLContext()	duckdb.connect()
Input	Polars DataFrames	Pandas/Polars/Arrow
Output	LazyFrame	Pandas DataFrame
SQL dialect	Standard SQL	PostgreSQL-like

---

Part 2: SQL Server Integration

Connect Pandas and Polars directly to SQL Server tables for reading, writing, and querying.

Connection Setup

Never hardcode credentials in connection strings

Use environment variables (os.environ.get()) or a secret manager. The .env file should be in .gitignore and never committed. See environment-variables for secure credential handling patterns.

Store credentials in environment variables

Load credentials at runtime via os.environ.get("DB_PASSWORD") or python-dotenv. Add .env to .gitignore and never commit it. For production, use a secret manager (GCP Secret Manager, Azure Key Vault) and inject the value as an environment variable.

TrustServerCertificate=yes disables certificate validation

Acceptable for local development. In production, use a valid TLS certificate and remove this flag — otherwise connections are vulnerable to man-in-the-middle attacks.

Use a valid TLS certificate in production

Install a trusted certificate on the SQL Server instance and remove TrustServerCertificate=yes from the connection string. Verify with openssl s_client -connect <host>:1433 before deploying.

pyodbc and SQLAlchemy Connection

load_dotenv(dotenv_path="../.env")
 
# Connection parameters
SERVER = "localhost,1434"
DATABASE = "stoxx"
USER = "sa"
PASSWORD = os.environ.get("STOXX_SA_PASSWORD", "")  # set via: $env:STOXX_SA_PASSWORD="..."
 
# pyodbc connection string
PYODBC_CONN = (
    f"DRIVER={{ODBC Driver 18 for SQL Server}};"
    f"SERVER={SERVER};"
    f"DATABASE={DATABASE};"
    f"UID={USER};"
    f"PWD={PASSWORD};"
    f"Encrypt=yes;TrustServerCertificate=yes;"
)
 
# SQLAlchemy engine (used by Pandas)
ENGINE = sa.create_engine(
    f"mssql+pyodbc:///?odbc_connect={quote_plus(PYODBC_CONN)}"
)
 
 
# Test connection
with pyodbc.connect(PYODBC_CONN) as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT @@VERSION")
    version = cursor.fetchone()
    if version:
        print(version[0][:80])
print("Connection OK")

Microsoft SQL Server 2022 (RTM-CU23) (KB5078297) - 16.0.4236.2 (X64) Jan 22 20 Connection OK

Reading Tables

pd.read_sql() loads the entire result

pd.read_sql() loads the entire result set into memory SELECT * FROM table on a 10M row table allocates the full DataFrame in RAM. For large tables, use chunksize= to iterate in batches, or add a WHERE clause to limit rows. Polars pl.read_database() has the same issue — neither library supports server-side cursors by default.

Limit rows or use chunked reads

Add a WHERE clause or TOP N / LIMIT N to bound the result set. For full-table loads, iterate with chunksize=: for chunk in pd.read_sql(q, engine, chunksize=50_000). In Polars use pl.read_database(q, conn, iter_batches=True, batch_size=50_000).

SQL injection risk with string

SQL injection risk with string formatting in queries Never use f-strings for user input: f"WHERE symbol = '{user_input}'" is injectable. Use parameterized queries: pd.read_sql("SELECT * FROM t WHERE symbol = ?", engine, params=["ASML"]).

Always use parameterized queries

Pass user-supplied values as parameters, never via string formatting: pd.read_sql("SELECT * FROM t WHERE symbol = ?", engine, params=[symbol]). In SQLAlchemy use bound parameters: text("SELECT * FROM t WHERE symbol = :s") with conn.execute(stmt, {"s": symbol}).

Reading with pd.read_sql() and pl.read_database()

Pandas | pd.read_sql()

Reads the top-5 rows from bronze.eurostoxx50_ohlcv into a Pandas DataFrame via a raw SQL string, printing column dtypes — confirming datetime64[ns] for _ingested_at and object for the date column before explicit parsing.

# Read entire table
df = pd.read_sql("SELECT TOP 5 * FROM bronze.eurostoxx50_ohlcv", ENGINE)
display(df)
print(f"dtypes:\n{df.dtypes}")

	id	_ingested_at	symbol	date	open	high	low	close	adj_close	volume	dividends	stock_splits
0	66728	2026-03-12 12:45:00.017366	ASML.AS	2026-03-12	1194.8	1202.20	1187.8	1190.80	1190.80	128223	0.0	0.0
1	66732	2026-03-12 12:45:00.017366	MC.PA	2026-03-12	495.3	497.40	491.6	494.35	494.35	171997	0.0	0.0
2	66736	2026-03-12 12:45:00.017366	RMS.PA	2026-03-12	1900.0	1918.50	1894.0	1906.00	1906.00	18681	0.0	0.0
3	66740	2026-03-12 12:45:00.017366	OR.PA	2026-03-12	361.1	362.30	357.8	360.80	360.80	82621	0.0	0.0
4	66744	2026-03-12 12:45:00.017366	SAP.DE	2026-03-12	163.0	166.74	162.8	166.52	166.52	806722	0.0	0.0

dtypes: id int64 _ingested_at datetime64[ns] symbol object date object open float64 high float64 low float64 close float64 adj_close float64 volume int64 dividends float64 stock_splits float64 dtype: object

# Parameterized query
symbol = "ASML.AS"
df = pd.read_sql(
    sa.text("SELECT [date], [close], volume FROM bronze.eurostoxx50_ohlcv WHERE symbol = :sym ORDER BY [date] DESC"),
    ENGINE,
    params={"sym": symbol}
)
display(df.head(10))
print(f"Shape: {df.shape}")

	date	close	volume
0	2026-03-12	1190.8	128223

Shape: (1, 3)

# Read entire table (shorthand)
df = pd.read_sql_table("index_dim", ENGINE, schema="bronze")
print(f"Columns: \n{list(df.columns)}")
print(f"\nShape: \n{df.shape}")

Columns: [‘id’, ‘_index’, ‘_ingested_at’, ‘symbol’, ‘long_name’, ‘short_name’, ‘sector’, ‘sector_key’, ‘industry’, ‘industry_key’, ‘country’, ‘city’, ‘website’, ‘long_business_summary’, ‘exchange’, ‘full_exchange_name’, ‘exchange_timezone_name’, ‘exchange_timezone_short’, ‘currency’, ‘financial_currency’, ‘quote_type’, ‘market’, ‘range_start’, ‘price_data_start’]

Shape: (169, 24)

Polars | pl.read_database()

Reads the top-5 rows from bronze.eurostoxx50_ohlcv into a Polars DataFrame using a SQLAlchemy engine — returning natively typed columns including datetime[μs] for _ingested_at and Date for date, unlike Pandas which reads the date column as object.

# Polars with SQLAlchemy engine
df = pl.read_database("SELECT TOP 5 * FROM bronze.eurostoxx50_ohlcv", connection=ENGINE)
display(df)
print(f"Schema: {df.schema}")

id	_ingested_at	symbol	date	open	high	low	close	adj_close	volume	dividends	stock_splits
i64	datetime[μs]	str	date	f64	f64	f64	f64	f64	i64	f64	f64
66728	2026-03-12 12:45:00.017366	ASML.AS	2026-03-12	1194.8	1202.2	1187.8	1190.8	1190.8	128223	0.0	0.0
66732	2026-03-12 12:45:00.017366	MC.PA	2026-03-12	495.3	497.4	491.6	494.35	494.35	171997	0.0	0.0
66736	2026-03-12 12:45:00.017366	RMS.PA	2026-03-12	1900.0	1918.5	1894.0	1906.0	1906.0	18681	0.0	0.0
66740	2026-03-12 12:45:00.017366	OR.PA	2026-03-12	361.1	362.3	357.8	360.8	360.8	82621	0.0	0.0
66744	2026-03-12 12:45:00.017366	SAP.DE	2026-03-12	163.0	166.74	162.8	166.52	166.52	806722	0.0	0.0

Schema: Schema({‘id’: Int64, ‘_ingested_at’: Datetime(time_unit=‘us’, time_zone=None), ‘symbol’: String, ‘date’: Date, ‘open’: Float64, ‘high’: Float64, ‘low’: Float64, ‘close’: Float64, ‘adj_close’: Float64, ‘volume’: Int64, ‘dividends’: Float64, ‘stock_splits’: Float64})

# Filtered query
df = pl.read_database(
    "SELECT [date], symbol, [close], volume FROM bronze.eurostoxx50_ohlcv WHERE symbol = 'ASML.AS' ORDER BY [date] DESC",
    connection=ENGINE
)
display(df.head(10))
print(f"Shape: {df.shape}")

date	symbol	close	volume
date	str	f64	i64
2026-03-12	ASML.AS	1190.8	128223

Shape: (1, 4)

# Polars with SQLAlchemy engine
df = pl.read_database("SELECT TOP 5 * FROM bronze.index_dim", connection=ENGINE)
display(df)

id	_index	_ingested_at	symbol	long_name	short_name	sector	sector_key	industry	industry_key	country	city	website	long_business_summary	exchange	full_exchange_name	exchange_timezone_name	exchange_timezone_short	currency	financial_currency	quote_type	market	range_start	price_data_start
i64	str	datetime[μs]	str	str	str	str	str	str	str	str	str	str	str	str	str	str	str	str	str	str	str	date	date
198	stoxx_asia_50	2026-03-04 22:21:55.384964	7203.T	Toyota Motor Corporation	TOYOTA MOTOR CORP	Consumer Cyclical	consumer-cyclical	Auto Manufacturers	auto-manufacturers	Japan	Toyota	https://global.toyota/en	Toyota Motor Corporation desig…	JPX	Tokyo	Asia/Tokyo	JST	JPY	JPY	EQUITY	jp_market	1999-05-06	2021-01-01
199	stoxx_asia_50	2026-03-04 22:21:55.384964	BHP.AX	BHP Group Limited	BHP GROUP FPO [BHP]	Basic Materials	basic-materials	Other Industrial Metals & Mini…	other-industrial-metals-mining	Australia	Melbourne	https://www.bhp.com	BHP Group Limited operates as …	ASX	ASX	Australia/Sydney	AEDT	AUD	USD	EQUITY	au_market	1988-01-28	2021-01-01
200	stoxx_asia_50	2026-03-04 22:21:55.384964	6758.T	Sony Group Corporation	SONY GROUP CORPORATION	Technology	technology	Consumer Electronics	consumer-electronics	Japan	Tokyo	https://www.sony.com	Sony Group Corporation designs…	JPX	Tokyo	Asia/Tokyo	JST	JPY	JPY	EQUITY	jp_market	2000-01-04	2021-01-01
201	stoxx_asia_50	2026-03-04 22:21:55.384964	1299.HK	AIA Group Limited	AIA	Financial Services	financial-services	Insurance - Life	insurance-life	Hong Kong	Central	https://www.aia.com	AIA Group Limited, together wi…	HKG	HKSE	Asia/Hong_Kong	HKT	HKD	USD	EQUITY	hk_market	2010-10-29	2021-01-01
202	stoxx_asia_50	2026-03-04 22:21:55.384964	CBA.AX	Commonwealth Bank of Australia	CWLTH BANK FPO [CBA]	Financial Services	financial-services	Banks - Diversified	banks-diversified	Australia	Sydney	https://www.commbank.com.au	Commonwealth Bank of Australia…	ASX	ASX	Australia/Sydney	AEDT	AUD	AUD	EQUITY	au_market	1991-09-30	2021-01-01

Chunked Reading (Large Tables)

Chunked Reading

Pandas | pd.read_sql() with chunksize

Iterates through bronze.eurostoxx50_ohlcv in chunks of 10,000 rows using chunksize=10_000, accumulating total row count — demonstrating the iterator pattern for memory-bounded reads of large SQL result sets.

# Read in chunks for memory efficiency
total = 0
for chunk in pd.read_sql("SELECT * FROM bronze.eurostoxx50_ohlcv", ENGINE, chunksize=10_000):
    total += len(chunk)
print(f"Read {total:,} rows in chunks of 10,000")

Read 50 rows in chunks of 10,000

Polars | Batch reading with OFFSET/FETCH

Reads the first 10,000 rows from bronze.eurostoxx50_ohlcv using SQL Server’s OFFSET 0 ROWS FETCH NEXT 10000 ROWS ONLY syntax — showing the SQL pagination pattern used in place of Polars’ absent native batch iteration.

# Polars reads the full result but ConnectorX streams internally
# For very large tables, use a WHERE clause or OFFSET/FETCH
df = pl.read_database(
    "SELECT * FROM bronze.eurostoxx50_ohlcv ORDER BY id OFFSET 0 ROWS FETCH NEXT 10000 ROWS ONLY",
    connection=ENGINE
)
print(f"First batch: {df.shape}")

First batch: (50, 12)

Writing to SQL Server

df.to_sql() is extremely slow by default

df.to_sql() is extremely slow by default — ~100 rows/second Pandas inserts rows one at a time through SQLAlchemy. For bulk loading, use method="multi" (batches inserts) or fast_executemany=True on the engine:

engine = sa.create_engine(url, fast_executemany=True)
df.to_sql("table", engine, if_exists="append", index=False, method="multi")

For tables >100K rows, use bcp instead — it’s 10-50x faster than any ORM approach. See data-transfer for bcp patterns.

Enable fast_executemany or use bcp for bulk loads

Set fast_executemany=True on the SQLAlchemy engine to batch ODBC inserts and reach ~10K rows/second. For datasets >100K rows, export to CSV and bulk-load with bcp or BULK INSERT to reach millions of rows per minute.

if_exists="replace" drops the table

This destroys indexes, constraints, permissions, and foreign keys. Use if_exists="append" with a preceding DELETE for controlled replacement, or use MERGE/upsert patterns from merge-and-upsert.

Use if_exists="append" with a preceding DELETE

Truncate or delete the target rows before appending, preserving the table schema, indexes, and permissions: conn.execute(text("DELETE FROM schema.table")) then df.to_sql("table", engine, if_exists="append", index=False). For partial replacements, use a MERGE statement instead.

Writing DataFrames

Pandas | df.to_sql()

Creates a 2-row test DataFrame with symbol, score, and date columns, writes it to dbo._test_pandas with if_exists="replace", then demonstrates explicit SQL type mapping (NVARCHAR, Float, Date) and row-append behavior.

# Create a test DataFrame
test_df = pd.DataFrame({
    "symbol": ["TEST.XX", "TEST.YY"],
    "score": [0.42, 0.73],
    "date": pd.to_datetime(["2024-01-01", "2024-01-02"]),
})
 
# Write to SQL Server (replace if exists)
test_df.to_sql("_test_pandas", ENGINE, if_exists="replace", index=False)
print("Written to _test_pandas")
 
# Verify
display(pd.read_sql("SELECT * FROM dbo._test_pandas", ENGINE))

Written to _test_pandas

	symbol	score	date
0	TEST.XX	0.42	2024-01-01
1	TEST.YY	0.73	2024-01-02

# Writing options
# if_exists: "fail" (default), "replace" (DROP+CREATE), "append" (INSERT INTO)
# dtype: explicit SQL types
# method: "multi" for faster bulk insert, or callable for custom
 
test_df.to_sql("_test_pandas_typed", ENGINE, if_exists="replace", index=False,
               dtype={  # type: ignore[arg-type]
                   "symbol": sa.types.NVARCHAR(20),
                   "score": sa.types.Float,
                   "date": sa.types.Date,
               })
print("Written with explicit types")
 
# Check the SQL types
with ENGINE.connect() as conn:
    result = conn.execute(sa.text(
        "SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS "
        "WHERE TABLE_NAME = '_test_pandas_typed' AND TABLE_SCHEMA = 'dbo'"
    ))
    for row in result:
        print(f"  {row[0]:15s}: {row[1]}")

Written with explicit types symbol : nvarchar score : float date : date

# Append rows to existing table
new_rows = pd.DataFrame({
    "symbol": ["TEST.ZZ"],
    "score": [0.55],
    "date": pd.to_datetime(["2024-01-03"]),
})
new_rows.to_sql("_test_pandas", ENGINE, if_exists="append", index=False)
display(pd.read_sql("SELECT * FROM dbo._test_pandas", ENGINE))

	symbol	score	date
0	TEST.XX	0.42	2024-01-01
1	TEST.YY	0.73	2024-01-02
2	TEST.ZZ	0.55	2024-01-03
3	TEST.ZZ	0.55	2024-01-03

Polars | Write via Pandas bridge and pyodbc fast_executemany

Converts a 2-row Polars DataFrame to Pandas and writes it to dbo._test_polars via SQLAlchemy, then bulk-inserts the same rows directly into _test_bulk using pyodbc’s fast_executemany=True for higher-throughput ODBC batch inserts.

# Polars doesn't have a native write_sql yet — convert to Pandas first
test_pl = pl.DataFrame({
    "symbol": ["PL_TEST.XX", "PL_TEST.YY"],
    "score": [0.88, 0.91],
    "date": ["2024-01-01", "2024-01-02"],
})
 
test_pl.to_pandas().to_sql("_test_polars", ENGINE, if_exists="replace", index=False)
print("Polars → Pandas → SQL Server")
display(pl.read_database("SELECT * FROM dbo._test_polars", connection=ENGINE))

Polars → Pandas → SQL Server

symbol	score	date
str	f64	str
PL_TEST.XX	0.88	2024-01-01
PL_TEST.YY	0.91	2024-01-02

# For bulk inserts: use pyodbc executemany with fast_executemany
with pyodbc.connect(PYODBC_CONN) as conn:
    conn.autocommit = False
    cursor = conn.cursor()
    cursor.fast_executemany = True
 
    # Create table
    cursor.execute("IF OBJECT_ID('_test_bulk') IS NOT NULL DROP TABLE _test_bulk")
    cursor.execute("CREATE TABLE _test_bulk (symbol NVARCHAR(20), score FLOAT, dt DATE)")
 
    # Bulk insert from Polars
    rows = test_pl.select("symbol", "score", "date").rows()
    cursor.executemany("INSERT INTO _test_bulk (symbol, score, dt) VALUES (?, ?, ?)", rows)
    conn.commit()
 
print(f"Bulk inserted {len(rows)} rows")
display(pl.read_database("SELECT * FROM dbo._test_bulk", connection=ENGINE))

Bulk inserted 2 rows

symbol	score	dt
str	f64	date
PL_TEST.XX	0.88	2024-01-01
PL_TEST.YY	0.91	2024-01-02

Executing SQL Statements

# DDL and DML via SQLAlchemy
with ENGINE.begin() as conn:
    # Create/alter tables
    conn.execute(sa.text(
        "IF OBJECT_ID('_test_exec') IS NOT NULL DROP TABLE _test_exec"
    ))
    conn.execute(sa.text(
        "CREATE TABLE _test_exec (id INT IDENTITY PRIMARY KEY, name NVARCHAR(50), value FLOAT)"
    ))
    # Insert
    conn.execute(sa.text(
        "INSERT INTO _test_exec (name, value) VALUES (:name, :value)"
    ), [{"name": "alpha", "value": 1.1}, {"name": "beta", "value": 2.2}])
 
display(pd.read_sql("SELECT * FROM dbo._test_exec", ENGINE))

	id	name	value
0	1	alpha	1.1
1	2	beta	2.2

# DDL via pyodbc (for stored procedures, etc.)
with pyodbc.connect(PYODBC_CONN) as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT COUNT(*) as cnt FROM bronze.eurostoxx50_ohlcv")
    total = cursor.fetchone()
    print(f"Total rows: {total[0]:,}")  # type: ignore[index]
 
    # List all user tables
    cursor.execute(
        "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES "
        "WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME"
    )
    tables = [row[0] for row in cursor.fetchall()]
    print(f"Tables ({len(tables)}): {tables[:10]}...")

Total rows: 50 Tables (27): [‘_test_bulk’, ‘_test_exec’, ‘_test_pandas’, ‘_test_pandas_typed’, ‘_test_polars’, ‘dim_country’, ‘dim_index’, ‘eurostoxx50_ohlcv’, ‘eurostoxx50_ohlcv’, ‘index_dim’]…

Stored Procedures

Creating and Executing Stored Procedures

# Call stored procedures and read results
with pyodbc.connect(PYODBC_CONN) as conn:
    cursor = conn.cursor()
 
    # Create a test stored procedure
    cursor.execute("""
        IF OBJECT_ID('sp_test_top_stocks') IS NOT NULL DROP PROCEDURE sp_test_top_stocks
    """)
    cursor.execute("""
        CREATE PROCEDURE sp_test_top_stocks @top_n INT = 5
        AS
        SELECT TOP (@top_n) symbol, AVG([close]) as avg_close
        FROM bronze.eurostoxx50_ohlcv
        GROUP BY symbol
        ORDER BY avg_close DESC
    """)
    conn.commit()
 
# Execute stored procedure via Pandas
df = pd.read_sql("EXEC sp_test_top_stocks @top_n = 10", ENGINE)
display(Markdown("**Top 10 stocks by avg close (stored procedure):**"))
display(df)

Top 10 stocks by avg close (stored procedure)

	symbol	avg_close
0	RMS.PA	1906.00
1	RHM.DE	1551.50
2	ASML.AS	1190.80
3	ADYEN.AS	925.70
4	ARGX.BR	626.60
5	MUV2.DE	526.20
6	MC.PA	494.35
7	OR.PA	360.80
8	ALV.DE	348.70
9	SAF.PA	315.40

Schema Inspection

# List all tables with row counts
query = """
SELECT
    t.TABLE_NAME,
    p.rows as row_count
FROM INFORMATION_SCHEMA.TABLES t
JOIN sys.partitions p ON OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) = p.object_id
WHERE t.TABLE_TYPE = 'BASE TABLE' AND p.index_id IN (0, 1)
ORDER BY p.rows DESC
"""
display(pd.read_sql(query, ENGINE))

	TABLE_NAME	row_count
0	eurostoxx50_ohlcv	66355
1	stoxxusa50_ohlcv	65100
2	stoxxasia50_ohlcv	64045
3	trading_calendar	29335
4	oil20_ohlcv	24738
5	index_performance	5281
6	scores_daily	466
7	signals_daily	466
8	dim_country	212
9	signals_quarterly	177
10	scores_quarterly	170
11	index_dim	169
12	index_dim	169
13	signals_daily	169
14	signals_quarterly	169
15	eurostoxx50_ohlcv	50
16	stoxxusa50_ohlcv	50
17	stoxxasia50_ohlcv	50
18	pulse	40
19	pulse_tickers	40
20	oil20_ohlcv	19
21	dim_index	4
22	_test_pandas	4
23	_test_exec	2
24	_test_pandas_typed	2
25	_test_polars	2
26	_test_bulk	2

# Column details for a specific table
query = """
SELECT
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH,
    IS_NULLABLE,
    COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'eurostoxx50_ohlcv' AND TABLE_SCHEMA = 'bronze'
ORDER BY ORDINAL_POSITION
"""
display(pd.read_sql(query, ENGINE))

	COLUMN_NAME	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	IS_NULLABLE	COLUMN_DEFAULT
0	id	int	NaN	NO	None
1	_ingested_at	datetime2	NaN	NO	(sysutcdatetime())
2	symbol	varchar	20.0	NO	None
3	date	date	NaN	NO	None
4	open	float	NaN	YES	None
5	high	float	NaN	YES	None
6	low	float	NaN	YES	None
7	close	float	NaN	YES	None
8	adj_close	float	NaN	YES	None
9	volume	bigint	NaN	YES	None
10	dividends	float	NaN	YES	None
11	stock_splits	float	NaN	YES	None

Performance: SQLAlchemy vs pyodbc (Pandas vs Polars)

query = "SELECT * FROM bronze.eurostoxx50_ohlcv"
 
# SQLAlchemy (Polars)
t0 = time.perf_counter()
df_pl_sa = pl.read_database(query, connection=ENGINE)
t_pl_sa = time.perf_counter() - t0
 
# SQLAlchemy (Pandas)
t0 = time.perf_counter()
df_sa = pd.read_sql(query, ENGINE)
t_sa = time.perf_counter() - t0
 
# pyodbc via ENGINE.raw_connection()
t0 = time.perf_counter()
 
df_py = pd.read_sql(query, ENGINE)
t_py = time.perf_counter() - t0
 
print(f"SQLAlchemy (Polars): {t_pl_sa:.2f}s — {df_pl_sa.shape}")
print(f"SQLAlchemy (Pandas): {t_sa:.2f}s — {df_sa.shape}")
print(f"pyodbc     (Pandas): {t_py:.2f}s — {df_py.shape}")

SQLAlchemy (Polars): 0.00s — (50, 12) SQLAlchemy (Pandas): 0.00s — (50, 12) pyodbc (Pandas): 0.00s — (50, 12)

Cleanup Test Tables

# Drop test tables created during this notebook
with ENGINE.begin() as conn:
    for table in ["_test_pandas", "_test_pandas_typed", "_test_polars", "_test_bulk", "_test_exec"]:
        conn.execute(sa.text(f"IF OBJECT_ID('{table}') IS NOT NULL DROP TABLE {table}"))
    conn.execute(sa.text("IF OBJECT_ID('sp_test_top_stocks') IS NOT NULL DROP PROCEDURE sp_test_top_stocks"))
print("Test tables and procedures cleaned up")

Test tables and procedures cleaned up

Summary

Task	Pandas	Polars
Connection	sqlalchemy.create_engine()	SQLAlchemy engine
Read table	pd.read_sql(query, engine)	pl.read_database(query, uri)
Read with params	pd.read_sql(query, engine, params=[...])	Use f-string or ConnectorX params
Chunked read	pd.read_sql(query, engine, chunksize=N)	Use OFFSET/FETCH in SQL
Write table	df.to_sql(name, engine)	df.to_pandas().to_sql() or pyodbc bulk
Append rows	df.to_sql(name, engine, if_exists="append")	Same via Pandas
Bulk insert	to_sql(method="multi")	cursor.fast_executemany = True
Execute DDL	engine.execute(text(...))	cursor.execute(...) via pyodbc
Stored procs	pd.read_sql("EXEC sp_name", engine)	pl.read_database("EXEC sp_name", uri)
Speed	Moderate	Similar speed via SQLAlchemy/pyodbc

---

Common Traps and Safe Patterns

to_sql() defaults to row-by-row inserts

Pandas to_sql() issues inserts conservatively by default. On large DataFrames this becomes a throughput bottleneck very quickly, turning what should be a bulk load into minutes of row-by-row network chatter.

[!success] Batch writes or use bulk loaders

For moderate loads, enable method="multi" or fast_executemany=True. For genuinely large transfers, export and use bcp, BULK INSERT, or another database-native bulk path instead of ORM-style inserts.

String-built SQL is injectable

Queries assembled with f-strings or .format() splice raw values directly into SQL text. As soon as any value is user-controlled, the code becomes vulnerable to SQL injection and loses the plan-caching benefits of parameterization.

[!success] Bind every external value

Use ? parameters with pyodbc and named parameters like :symbol with SQLAlchemy text(). Query text should stay static while values travel separately through the driver parameter channel.

engine.execute() no longer exists

SQLAlchemy 2.x removed the old engine.execute(...) convenience path. Legacy examples that still call it fail with AttributeError, which is easy to misread as a driver or connection problem instead of an API change.

[!success] Execute through a connection

Open an explicit connection or transaction scope and call conn.execute(text(sql), params). That is the current SQLAlchemy execution model and the one new code should standardize on.

read_sql() can exhaust memory

pd.read_sql("SELECT * FROM large_table", engine) materializes the full result set into memory. On wide or high-row-count tables, that can consume all available RAM before any downstream filtering has a chance to reduce the data.

[!success] Limit or stream result sets

Add selective predicates to the query, project only needed columns, and use chunksize= for Pandas or batch iteration patterns for Polars when the full result must be processed. Bring the smallest useful result into memory, not the whole table by default.

Plain-text passwords leak secrets

Hardcoding passwords in connection strings leaves credentials in source control, notebooks, logs, stack traces, and shell history. Once a secret lands in code, it tends to spread far beyond the original file.

[!success] Load credentials from environment or vault

Read database secrets from environment variables, .env files kept out of version control, or a proper secret manager such as Azure Key Vault or GCP Secret Manager. Application code should assemble connection strings at runtime from those injected values.

Python Database and SQL Interface Recommendations

1. Use DuckDB for complex analytical SQL on DataFrames — CTEs, window functions, and set operations are more readable in SQL than in chained DataFrame expressions.
2. Use parameterized queries for all database operations — prevents SQL injection and improves query plan caching.
3. Enable fast_executemany for pyodbc bulk inserts — cursor.fast_executemany = True dramatically speeds up to_sql() and manual insert loops.
4. Use connection pooling — SQLAlchemy’s create_engine() pools connections by default. Do not create a new engine per query.
5. Validate schema after reading from database — column names, types, and nullability may differ from expectations due to database-side changes.
6. Prefer Parquet over database round-trips for intermediate data — if the data doesn’t need to persist in a database, Parquet reads are 10–50x faster than SQL queries.

Troubleshooting and failure modes

Symptom	Likely cause	Fix
OperationalError: ODBC driver not found	ODBC Driver for SQL Server not installed	Install ODBC Driver 18 for SQL Server from Microsoft
InterfaceError: connection refused	Wrong server name, port, or firewall rule	Verify server name, port (default 1433), and network access
to_sql() is extremely slow	Row-by-row insert mode	Set method="multi" or fast_executemany=True
ProgrammingError: table already exists	to_sql() with if_exists="fail" (default)	Use if_exists="append" or if_exists="replace"
DataError: string or binary data would be truncated	DataFrame string column exceeds the database column’s VARCHAR(N) limit	Increase the column size in the database, or truncate strings before insert
DuckDB query returns wrong types	DuckDB infers types independently from Polars/Pandas	Cast columns explicitly in the SQL query
read_sql() returns empty DataFrame	Query returns no rows, or wrong database/schema targeted	Run the query directly in the database client to verify