Re: difference between statements

Actually I agree with everything that you say.

>> 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".

I never mess with this. Personally I like the fact that on inserts and
deletes that I have to commit them, just recently I was developing a
quick and dirty script to update a months of records in our production
database and it was great to be able to test my code without actually
modifying the data. :-) But I do tend to forget this from time to time,
and I "load a stare a", an old assembly language joke, it for awhile
until I remember that I forgot the commit. :-)

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

Again, I agree. However at times I have had issues with the nested
parens in a query, but as I look back on the offending code, it was
using the mx.ODBC.Windows driver, not pysqlite, sorry for the confusion.

>> 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.

I agree completely, the scripts in question are internal scripts that I
control the data on. If it is from the outside world I use the parameter
replacement stuff in my code, Java, perl, python, ASP.NET whatever...

Been doing this a long time, realized just recently that I have been a
web developer full or parttime for 10 years now, so none of this is new,
even though I am relatively new to python, been coding in it about 2.5
years. :-)

Thanks,

Greg Givler
Information Technology
Lockwood®
10 Valley Stream Parkway
Malvern, PA 19355
Phone: (484) 605-4826
Email: [email protected]


                                                                                                                                           
                      Martin Jenkins                                                                                                       
                      <mj-Vfh7fEhEWOlaa/[email protected]>               To:       "For users of the pysqlite database module." <pysqlite-IAPFreCvJWPBWskQ1e/[email protected]>       
                      Sent by:                     cc:                                                                                     
                      pysqlite-bounces@list        Subject:  Re: [pysqlite] difference between statements                                  
                      s.initd.org                                                                                                          
                                                                                                                                           
                                                                                                                                           
                      03/28/2007 09:32 AM                                                                                                  
                      Please respond to                                                                                                    
                      "For users of the                                                                                                    
                      pysqlite database                                                                                                    
                      module."                                                                                                             
                                                                                                                                           
                                                                                                                                           




[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
_______________________________________________
pysqlite mailing list
pysqlite-IAPFreCvJWPBWskQ1e/[email protected]
http://lists.initd.org/mailman/listinfo/pysqlite

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please call the help desk at ext 4850
______________________________________________________________________




______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
______________________________________________________________________