I may be missing something, but usually when I am in your situation I use
an update_or_insert web2py statement, rather than explicitly managing
transactions. Perhaps you could also consider encoding more of your logic
in web2py rather than postgres? Or perhaps I am being naive?
Luca
On Friday, August 9, 2013 9:53:22 AM UTC-7, Joe Barnhart wrote:
>
> I'm currently using Posgres as my database, but I'd like to preserve the
> portability that web2py gives me to change databases. I find that I need
> to add all sorts of "idioms" in my database code and I'm running dry when
> thinking of ways to abstract this and make a layer that allows me to use,
> say, SQLite in place of Postgres. Let me provide an example.
>
> When inserting data into Postgres which may collide with an existing row,
> and for which there is a unique index, I need to bracket the insert with
> code that sets a savepoint, tries the statement, and then either removes
> the savepoint or rolls back the transaction. It looks like this:
>
>
> def ignore_on_fail(token,stmt,*args,**kwargs):
> """Insert a row into the database but bracket the insert
> with a 'savepoint' and rollback to it if the statement fails"""
> from random import randint
> savepoint_name = '_'.join([token,str(randint())])
> ret = None
> db = current.db
> try:
> db.executesql('SAVEPOINT %s;'%savepoint_name)
> ret = stmt(*args,**kwargs)
> db.executesql('RELEASE %s;'%savepoint_name)
> except:
> db.executesql('ROLLBACK TO %;'%savepoint_name)
> return ret
>
>
> In SQLite, the table is built with a "ON CONFLICT IGNORE" constraint
> resolution, so the equivalent fucntion would simply be:
>
>
> def ignore_on_fail(token,stmt,*args,**kwargs):
> """Insert a row into the database but bracket the insert
> with a 'savepoint' and rollback to it if the statement fails"""
> ret = stmt(*args,**kwargs)
> return ret
>
>
> But where to put these functions? And how do I choose one set over the
> other, based on something that isn't known until the "db.py" file is read?
> I can put these in a module and import them, but the module name must be
> known at compile time (since these database commands are occurring in
> modules as well) and that isn't known because the db.py file has not yet
> been read. My head hurts!
>
> There must be some pattern I'm missing to extend my database code so that
> I can prevent it from being too tied to a single platform. What pattern do
> you use?
>
> -- Joe
>
--
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
