sqlite single transaction without foreign key or triggers

Rob Williscroft rtw at freenet.co.uk
Mon May 11 17:07:42 EDT 2009

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


>  # 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:

  ... # somthing that errors up ...

catch sqlite3.DatabaseError:


More information about the Python-list mailing list