This talk is about all the components to integrate Python with PostgreSQL (pg)
FDW is a pg component that allows you to use external resources (files, twitter messages, different databases, …) from SQL.
Python has a standard database interface, DB-API 2.0 (PEP0249). It uses 2 concepts: connections (to a database, handles transactions etc.) and cursors (queries).
Connections have methods like commit and rollback. Rollback can be done with a with: statement.
Cursors are created on a connections. Have methods like execute (query), fetch (results). execute() has special formatting primitives to insert things into the query.
psycopg is a DB-API 2.0 implementation that connects to pg databases.
peewee uses DB-API to implement an ORM, i.e. a mapping of tables on python classes.
SQLAlchemy is a complex ORM.
Alembic is a SQLAlchemy module to migrate a database to a new schema. Configured by an INI file. It rewrites the SQL and the corresponding python code. So it’s a kind of VCS for databases.
Multicorn is a pg foreign data wrapper extension that wraps python so it becomes available in SQL. It also wraps a lot of other things, e.g. LDAP, google searches. You can easily wrap more thing by creating some python code for it, e.g. Odoo (an ERP system) has a python multicorn module to be able to access it in pg.
PL/Python allows you to write python code in SQL. So you define an SQL function with python code, then apply it as a stored function in queries. But it’s difficult to maintain and debug, and it’s not sandboxed so not safe.