sqlite single transaction without foreign key or triggers

[gert]

On 11 mei, 23:07, Rob Williscroft <r... at freenet.co.uk> wrote:
> gert wrote in news:d7591495-4661-4243-ad7e-f142d8244e88
> @e24g2000vbe.googlegroups.com in comp.lang.python:
>
> > I am trying to do this in a single transaction, the 3 separate
> > statements work fine, but i am screwed if they are not executed
> > together.
>
> Well you're in luck, Python DBAPI 2 connections handle this
> for you, you do need to call commit() on the connection though.
>
> The default, for DBAPI 2 connections, is that all "work" occurs in a
> transaction (if the DB actually supports transactions) so you have to
> call commit() on the connection after doing updates.
>
>
>
> >  ########### db.execute('BEGIN') #############
> >  db.execute('UPDATE users SET uid=? WHERE uid=?',(v['uid'],s.UID))
>
> This is a fragile way to do it, your code won't work with a DB that
> has real foreign keys (and maybe sqlite will get them one day).
>
> A less fragile way of doing it is:
>
> db = connection.cursor()
>
> # First copy the row if it exists
>
> db.execute( '''
>         insert into "users"
>         select ?, "name", adress, city, country, phone, picture
>         from "users" where "uid" = ?        
>     ''', (v['uid'],s.UID)
>   )
>
> >  db.execute('UPDATE sessions SET uid=? WHERE sid=?',(v['uid'],s.SID))
>
> # Second update foriegn key tables to point to the new row
> # (but only if the new row exists )
>
> db.execute( '''
>         update "sessions" set "uid" = ?
>         where "uid" = ?
>         and exists(
>                 select * from "users" where "uid" = ?
>           )
>     ''',
>     (v['uid'],s.SID, v['uid'])
>   )
>
> #Do the same for the "groups" table, then
>
> # finally delete the original row (again only if the new row exists )
>
> db.execute( '''
>         delete from "users"
>         where "uid" = ?
>         and exists(
>                 select * from "users" where "uid" = ?
>           )
>     ''',
>     (s.SID, v['uid'])
>   )
>
> # Finally commit the transaction
>
> connection.commit()
>
> >  # only do this if there is no primary key conflict in the above
> >  if db.ERROR == None: db.execute('UPDATE groups SET uid=? WHERE uid=?',
> > (v['uid'],s.UID))
>
> Python reports errors by throwing exceptions, so if you needed somthing
> like this it would more likely be:
>
> try:
>
>   ... # somthing that errors up ...
>
> catch sqlite3.DatabaseError:
>   connection.rollback()
>
> Rob.
> --http://www.victim-prime.dsl.pipex.com/

ok go it, thanks