sqlite single transaction without foreign key or triggers
gert
gert.cuykens at gmail.com
Mon May 11 18:59:49 EDT 2009
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
More information about the Python-list
mailing list