Re: difference between statements

[email protected] wrote:
> I very rarely work directly in sqlite, but Deletes and Inserts have to
> have a con.commit() after them.

That's not strictly true - unless you turn it off pysqlite will start 
transactions behind the scenes for you. have a look at the docs for 
"isolation_level".

> Also you may be having a problem which I have experienced, if a query
> contains parentheses then paremeter substitution fails. In other words
> if I have a query like this:
> 
> cur.execute("""select * from Products where ProductID = (select
> ProductID from Sponsor where SponsorID = ?)""", (12,))
> 
> This will fail, however if a do something like this:

It should work

 >>> c.execute("select rowid, * from t").fetchall()
[(1, u'abc'), (4, u'abc'), (5, u'abc'), (6, u'def'), (7, u'esj')]

 >>> c.execute("""select rowid, * from t where rowid = (select rowid 
from t where id = ?)""", ("def",)).fetchall()
[(1, u'abc')]

but as you can see it only returns one row. If you change the '=' to 
'in' then you'll get

 >>> c.execute("""select rowid, * from t where rowid in (select rowid 
from t where id = ?)""", ("abc",)).fetchall()
[(1, u'abc'), (4, u'abc'), (5, u'abc')]
 >>>

which is what I think you want.

> sql = """select * from Products where ProductID = (select ProductID from
> Sponsor where SponsorID = %d)""" % 12

In general, this sort of thing is frowned on because it can lead to SQL 
injection attacks, but you could argue that it only matters when values 
(like your 12) come from the web or user input.

Martin