One-Shot Query Interface
James William Pye
x at jwp.io
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 gahooa.com> 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