Python and PostgreSQL, a wonderful wedding (Stephane Wirtel)

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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s