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/
--
http://mail.python.org/mailman/listinfo/python-list
