sqlite single transaction without foreign key or triggers

[Rob Williscroft]

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/