sqlite single transaction without foreign key or triggers

[gert]

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.

########### db.execute('BEGIN') #############
db.execute('UPDATE users SET uid=? WHERE uid=?',(v['uid'],s.UID))
db.execute('UPDATE sessions SET uid=? WHERE sid=?',(v['uid'],s.SID))
# 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))
########### db.execute('END') #####################

My tables are as follows

CREATE TABLE users (
uid VARCHAR(64) PRIMARY KEY,
name VARCHAR(64) DEFAULT '',
adress VARCHAR(64) DEFAULT '',
city VARCHAR(64) DEFAULT '',
country VARCHAR(64) DEFAULT '',
phone VARCHAR(64) DEFAULT '',
picture BLOB
);

CREATE TABLE groups (
gid VARCHAR(64),
uid VARCHAR(64),
PRIMARY KEY(gid,uid),
FOREIGN KEY(uid) REFERENCES users(uid) ON UPDATE CASCADE ON DELETE
CASCADE
);

CREATE TABLE sessions (
uid VARCHAR(64) UNIQUE,
pwd VARCHAR(64) DEFAULT '',
sid VARCHAR(64) PRIMARY KEY,
exp DATETIME,
FOREIGN KEY(uid) REFERENCES users(uid) ON UPDATE CASCADE ON DELETE
CASCADE
);

What is the python or sql way of doing this kind of things ?

 

[gert]

gert wrote in @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

 

[Aahz]

db.execute( '''
update "sessions" set "uid" = ?
where "uid" = ?
and exists(
select * from "users" where "uid" = ?
)
''',
(v['uid'],s.SID, v['uid'])
)

This will be more efficient if you do "select uid from users".

 

[Aahz]

Aahz wrote in news:[email protected] in comp.lang.python:

db.execute( '''
update "sessions" set "uid" = ?
where "uid" = ?
and exists(
select * from "users" where "uid" = ?
)
''',
(v['uid'],s.SID, v['uid'])
)

This will be more efficient if you do "select uid from users".

What will be more efficient ?

Do you mean the "select * ..." or do you want to take the exists
sub-query out and put it in a python if ?

"select uid" will be more efficient than "select *", although I suppose
I could be wrong about that given how little I know about current query
optimizers.

 

[John Machin]

Aahz wrote innews:[email protected] comp.lang.python:

db.execute( '''
        update "sessions" set "uid" = ?
        where "uid" = ?
        and exists(
                 select * from "users" where "uid" = ?
          )
   ''',
   (v['uid'],s.SID, v['uid'])
 )
This will be more efficient if you do "select uid from users".

What will be more efficient ?

Do you mean the "select * ..." or do you want to take the exists
sub-query out and put it in a python if ?

"select uid" will be more efficient than "select *", although I suppose
I could be wrong about that given how little I know about current query
optimizers.

My take is that it won't matter what you select if the optimiser is
smart enough; something that requires minimal resources to produce is
indicated in case the optimiser is dumb:

... exists (select 1 from ... )

 

[John Machin]

Aahz wrote incomp.lang.python:
db.execute( '''
update "sessions" set "uid" = ?
where "uid" = ?
and exists(
çˆelect * from "users" where "uid" > = ?
)
''',
(v['uid'],s.SID, v['uid'])
)
This will be more efficient if you do "select uid from users".
What will be more efficient ?
Do you mean the "select * ..." or do you want to take the exists
sub-query out and put it in a python if ?
"select uid" will be more efficient than "select *", although I
suppose I could be wrong about that given how little I know about
current query optimizers.

It seems the usual advice about premeture optimisation should apply,
namely write clear code (*), then optimise the bottlenecks when you
actualy find you need to.

_Deliberately_ writing * instead of some constant is premature potential
pessimisation and thus an utter nonsense. If you think 1 is obscure,
then * is likewise obscure; write e.g. 'any_old_constant' instead.

*) for some definition of "clear code".


I have to maintain some code writen by someone who thinks replacing
"*" in queries with "1" is always a good idea (you know just in case),
he wrote:

select count(1) from ...

of course it didn't do what he thought it did.

If you really have to maintain code that's been written by weird people
and not subsequently tested, you have my sympathy. However this is not
relevant. I am certainly not advocating bulk unthinking replacement of *
by 1 anywhere in a query.

Cheers,
John