One-Shot Query Interface

James William Pye x at
Thu Oct 4 23:56:21 UTC 2012

[I had been working on this e-mail for a while, but I'm afraid I still haven't given it the full attention it deserves.]
[feeling a fire. :~]

On Sep 11, 2012, at 7:11 PM, Jason Garber <boss at> wrote:
> I agree that we should build one in...  However, I think it needs to be "right" before releasing it, because of it being much harder to change after the fact.  


> I'd like to reply in more detail, but am short on time this evening.  Take a look at this Gist for some great usage examples:

Thanks. This is helpful.

>   We cache prepared statements on the connection object, which has *never* caused an issue.

"Query Libraries" were created to help here, but even I find their use a bit awkward, and I'm not sure how to solve that problem or if py-postgresql should attempt to solve it. Not to mention, they don't help in cases where you need to generate a query.

Also, my understanding is that caching statements on the connection object will create reference cycles.(?)
I understand that this can have an impact on (GC) performance. (can't remember if qlibs try to work around this)

> Execute - Just run a query

Yeah, notably with schema initialization code; db.execute() is nice.

> ValueList - Get a list of values from the first column.

Okay, I'm going to add .column() to 1.1.

> ValueSet - Get a set of values from the first column

set(ps.column()) in v1.1.?

> ValueDict - Get a dictionary of key=>value pairs from the first two columns


In cases where there are extra columns, the user should push down a filter.
(either a distinct query or a composition that selects the desired k/v columns)

> RowDict - Get a dict of rows -- key is the first column

I think this would be a reasonable addition, but I'm not sure I want to add it in v1.1 in order to give it more thought.

> RowList - Get a list of Rows

Yeah, our __call__() or list(ps.rows())

> TRowList - Get a list of TRows


> Bool - If one record found and first column is true, then True, else False

I'd argue this logic should be packed into the query. (For example: … UNION ALL SELECT FALSE AS foo)

> All of these functions have the same underlying signature:
> SQL is the SQL string with $FirstName style variables.

I've avoided this part on purpose. However, it's *not* a bad idea. I've wanted it a few times.

Rather, "$1-$n" is how *postgres* manages statement parameters. Currently there is no client side parsing at the "PG-API" level.

We do some conversion in the DB-API layer, but that's specifically because the DB-API specification doesn't support Postgres style parameters, and it's consistent with psycopg's "paramstyle".

> Note: The NotOneFound exception is very critical, because a lot of queries EXPECT a row of data and the process is a failure if they are not found or more than one is found.  A Python exception, while easy to catch, allows the programmer to assume all is well and not check every result for these mundane things.

> Row - Get a row -- fields accessed by .FieldName (kind of a named tuple).  Raise NotOneFound if len(result) != 1.
> TRow - Get a plain tuple. Raise NotOneFound if len(result) != 1.

> Value - Get the first column of a single row.  Raise NotOneFound if len(result) != 1.

next(ps.rows())/next(ps.column()) will raise StopIteration if there is no row to be found. Did you find this to be unattractive?

More information about the Python-general mailing list