On 6/25/14, 5:35 PM, Jason Newton wrote: > > > > >> Hand crafted inserts use server side lo_create(-1) (in sql) which >> allocates a new large object and returning clause to get the oid >> in one go back to the client side. Then I start using the >> lobject api on the returned oid. > I'd like to see explicit SQL, preferably in the form of a psycopg2 > script that illustrates all the operations you wish to support and > specifically how they must interact with the database. > > > I've attached example usages. As I indicated in my prior email, right > now I only do inserts/selects. Here's my immediate thought about the INSERT (and the UPDATE) - the first way is using events, the second would attempt to move this system into something more native to the psycopg2 dialect:
Use the before_cursor_execute() and after_cursor_execute() events to get at this: http://docs.sqlalchemy.org/en/latest/core/events.html#sqlalchemy.events.ConnectionEvents.before_cursor_execute http://docs.sqlalchemy.org/en/latest/core/events.html#sqlalchemy.events.ConnectionEvents.after_cursor_execute 1. Create a new type called PGLOB or whatever you want. For starters, this can just be a blank UserDefinedType subclass. in before_cursor_execute: 2. determine if INSERT or UPDATE using context.isinsert, context.isupdate 3. examine the datatypes that SQLAlchemy is using here, by looking at context.compiled.binds. The values here are BindParameter objects, you want to look in those for the ones that are of type PGLOB. 4. From context.compiled.binds, you have the names of the bound params with the type. Search and replace the "statement" for occurrences of that bound parameter, replace with "lo_creat(-1)" or whatever you need there. 5. Also append to the RETURNING clause those cols you need. 6. the statement as a return value will be used, if you set up the event with retval=True (see the docs). in after_cursor_execute: 7. in after_cursor_execute - call fetchone() to get the RETURNING values. Get that OID you care about then do that work with conn.lobject and all that. hopefully this doesnt mess up the existing cursor state. 8. now the tricky part. SQLAlchemy needs that row if you're doing "implicit returning" to get at primary key values. psycopg2's cursor seems to have a scroll() method that works for client side cursors. I'd scroll it back one so that SQLA gets the state it expects. Alternative system, more plugged in: 1. We would be creating new features on sqlalchemy/dialects/postgresql/psycopg2.py -> PGExecutionContext_psycopg2. Similar hooks are available here which you can use to accomplish similar tasks; you'd want to look at the pre_exec(), post_exec() and possibly post_insert() methods, and maybe even _fetch_implicit_returning(). If SQLA were to support this more natively, things would be happening at this level. But again, I really wouldn't want all kinds of hardcoded expectations of LOB objects in this object taking up processing time for the vast majority of use cases that don't use LOBs, so the extra logic here should be contained within something that can easily be placed aside based on options or similar. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
