Extending SQL Databases with Python
summary
Shows how Python actually runs inside three databases—SQLite, DuckDB, PostgreSQL—by registering functions with sqlite3.create_function, building custom aggregates in DuckDB, and using PL/Python or embedding CPython in a PostgreSQL extension. Demonstrations include a Python moving‑average implemented as an SQL function, a correlation aggregate that calls SciPy, utilities like pgpdf and pdjq, and notes that Python3U is unsafe, virtualenvs are flaky, and the C+Python embedding approach works but adds complexity.
AI-written summary · about
chapters (22)
- 0:00Introduction
- 0:27Why SQL meets Python matters
- 1:53ACID compliance and databases
- 3:19Procedural vs relational
- 4:23Pandas limits vs SQL benefits
- 5:25Why SQL works
- 6:30What extensions are
- 7:34Custom functions and types
- 8:37Powerful SQL extensions
- 10:08Extending PostgreSQL with Python
- 12:17PL/Python metadata and optimizer
- 13:00PL/Python examples
- 14:50itertools in SQL with Python
- 16:16Calling C from Python in PostgreSQL
- 17:44PL/Python considerations
- 18:26SQLite vs PostgreSQL for extensions
- 19:08Extending SQLite with Python
- 21:13DuckDB Python extensions
- 22:00Custom aggregates in DuckDB
- 23:04Correlation matrix example
- 25:15Advice for Python devs
- 27:21Q&A
related talks
24mDeveloping DB Extensions in Python: Examples for SQLite, DuckDB & PostgreSQL
39mPython and SQL: Better Together, Powered by DuckDB
43mPL/Python -- Python inside the PostgreSQL RDBMS
51mPostgres with Python inside and out: PL/Python
32mBecause SQL is everywhere but you don't want to use it
30mDuckDB: Bringing analytical SQL directly to your Python shell
20mSiuba and duckdb: Analyzing Everything Everywhere All at Once
25mProcessing Biggish Data with DuckDB and Python