Dan Farina, a developer at Heroku, recently clued me in to Multicorn, a PostgreSQL 9.1+ extension for writing foreign-data wrappers in Python. Foreign-data wrappers are a mechanism for exposing external data as PostgreSQL tables, allowing that data to be queried as if it were sitting in PostgreSQL just like any other table. That little nugget of information, and Josh’s Insanely Simple Python REST Script from earlier last week, inspired me to write my first Python script – a Multicorn foreign-data wrapper (FDW) for database.com (compatible with Force.com, of course, since they share the same API).

The README has full instructions, but, in summary, once you have PostgreSQL 9.1 or higher, Python and Multicorn all installed, you can create a Remote Access app in database.com/Force.com, build the database.com FDW, install it in the system-wide Python distribution and start using it. We’ll pick up the story here assuming you have completed the steps mentioned above. First, create the Multicore extension and a ‘foreign server’ in your database:

Now you can create a table. You can list any number of fields from a database.com sobject, but field names are (currently) case-sensitive and must be quoted:

At this point, you can query the foreign table as if it were any other PostgreSQL table. Note that FDWs are read-only, so you won’t be able to create, update or delete records.

Currently, the database.com FDW is very much prototype code. The main limitation right now is that the amount of data that you can read from database.com is somewhat limited, since the JSON response is parsed into a Python object in memory. If this looks interesting, then let me know in the comments and I’ll take a look at parsing the database.com response as a stream. Alternatively, if you’re a Pythonista and you’re interested in lending a hand, feel free to fork the project on Github and get to work!

UPDATE – the FDW now uses the yaml-py module to parse the JSON stream without reading it all into memory at once, and field names are no longer case sensitive.