On 11 mei, 23:07, Rob Williscroft <r...@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 -- http://mail.python.org/mailman/listinfo/python-list