New Python bindings for PostgreSQL - Mailing list pgsql-announce
From | Cristian Gafton |
---|---|
Subject | New Python bindings for PostgreSQL |
Date | |
Msg-id | Pine.LNX.4.61.0609040247550.4420@alienpad.rpath.com Whole thread Raw |
List | pgsql-announce |
A few days ago I started off trying to improve the PyGreSQL bindings by adding support for prepared statements, bind parameters and server side cursors. As I progressed through this task, the changes I have been making to PyGreSQL became more and more extensive, modifying the original's behavior in incompatible ways. Things I changed from PyGreSQL 3.8.1: - compatible only with PostgreSQL 8.0+ and Python 2.3+. - added support bind parameters. The bind parameters are sent to the server separate from the query, not injected into the sql query body, so there is no need for expensive and error-prone escaping. - added support for prepared statements through an extension of the DB API 2.0 - db.prepare(sql). SQL statements that get executed multiple times with varying parameters can benefit from this. - support server side cursors, useful for looping over large datasets one or more row(s) at a time instead of downloading the entire dataset in the client memory after each query (which is the default behavior). This is implemented through an extension of the DB API 2.0 by calling db.itercursor() instead of db.cursor() to instantiate a cursor. - most common data types (numbers, strings, BYTEAs blobs, booleans, etc) are automatically converted to the right Python types as results are fetched in the C module. One notable exception are the datetime types, for which I have not had time to implement the conversion.[1] The datetime types are returned in Python as strings, where you can convert them as you see fit. - complex dataypes (arrays, geometric types, composite types) are not supported yet - but those can be probably added once I figure out a priority (better yet: patches welcomed! :-). Those are also returned to the python caller as server-produced strings. Preserving the old behavior for existing users of PyGreSQL is a challenge, seeing how the native PostgreSQL bind parameters syntax ($1, $2, etc) is very different from what PyGreSQL simulated (python-style %s or %(var)s). I don't have an interest in maintaining string-based argument-escaping functions... Based on all that, it feels like it is better to release this as a new project. I'd like to acknowledge and thank the PyGreSQL team for providing my starting point. So, my python-pgsql bindings can be found (for now) here: http://people.rpath.com/~gafton/pgsql/ I have tried to document the core functionality in the README file. There is also a demo.py file that shows you the code in action. Take a look at the code in demo.py - it explains things quicker and faster for those who can grok python. Please try them out and let me know what you think. Cristian [1] We do not use native database datetime types on fields we use in the application. We attempt to support multiple SQL backends - and it seems every one has different kinds of datetime fields which behave differently, making this a real pain. Thus, we represent datetime stuff as Unix time integers in our schema, in a case of "the best solution is the simplest" that has worked very well for us so far. YMMV. -- Cristian Gafton rPath, Inc.
pgsql-announce by date: