Lie Hetland book: Beginning Python..

[Magnus Lycka]

Vittorio wrote:
> Nonetheless, I was unable to find any documentation about such a 
> different behaviour between Pysqlite and Pysqlite2; from my beginner 
> point of view the Pysqlite (Magnus' version) paramstyle looks a better 
> and more pythonic choice and I don't grasp the Pysqlite2 developers' 
> intentions deviating from that way.

Please note that the DB-APIs let you use a foreign language, SQL,
in Python strings. Having SQL look Pythonic is hardly a virtue.
SQL should look SQLic! The SQL standards clearly state that '?'
is the correct symbol for dynamic SQL placeholders. For embedded
SQL (which is really moot for Python) it's ':NAME', but '%s' has
nothing to do with SQL. Pysqlite supports both '?' and ':NAME',
but no longer '%s', which is a blessing in my book.

Please note that while there is a rough correspondence between
the placeholders in SQL and %s and friends in Python strings,
they are far from the same. With SQL placeholders and separately
passed parameters, proper implementations of database servers will
prevent SQL injection attacks and provde a much better performance
than if you build an SQL string with Python's %-operator and %s etc
in the SQL string. Proper SQL parameter passing will also mean that
parameter quoting is handled for you. On the other hand, you can only
use placeholders in certain positions in SQL, so you might need %s
as well in SQL strings too, if you for instance need to determine
the table to search from in runtime.

Using the same symbol for both string substitutions and SQL placeholder
such as pysqlite 1 and the MySQL interface does, is not really a bright
idea in my opinion. Who thinks this is pretty?

sql = "SELECT %s FROM %s WHERE %s = %%s"
cur.execute(sql % (col,table,search_col), (param,))

I think it's less confusing with:

sql = "SELECT %s FROM %s WHERE %s = ?"
cur.execute(sql % (col,table,search_col), (param,))

With %s as placeholder, it's easy to do either...

   sql = "SELECT %s FROM %s WHERE %s = %s"
   cur.execute(sql % (col,table,search_col,param))

   If you do this, you won't have any help with quoting,
   you are suceptible to SQL injection attacks, and your
   performance won't improve if the same query is performed
   repeatedly with different values for param, since the
   database server will make a new query execution plan
   every time. :(

or...

   sql = "SELECT %s FROM %s WHERE %s = %s"
   cur.execute(sql, (col,table,search_col,param))

   If this works with your DB driver, it's likely to be really
   broken and just work as the previous example. In other
   words you don't have the benefits in performance, convenience
   or security that parameter passing provides in dynamic SQL.

Of course, the "proper" way, with %s-substitution for e.g. table
names and ? for parameters is also open for SQL injection attacks
if the values in the strings col, table and search_col above are
user input, but since they are plain SQL identifiers, they are much
easier to check than arbitrary search values. You'd probably have
a set of allowed values, and check that the input was in that
set. They are also less likely to come from an untrusted source.

The DB-API spec is available at http://python.org/peps/pep-0249.html
It's a good read. You could also look at:
http://www.thinkware.se/epc2004db/

> I would be very grateful if someone would cast a light over 
> Pysqlite/Pysqlite2 discrepancies.

I'm afraid I haven't seen that anywhere. Some of the more subtle
changes probably results from the difference between SQLite 2 and
SQLite 3, since these are the versions those Python libraries wrap.

As you can see in
http://initd.org/tracker/pysqlite/wiki/PysqliteVersions
you can use pysqlite 1.1 if you want to use the old pysqlite 1
API.

Pysqlite2 is documented here:
http://initd.org/pub/software/pysqlite/doc/usage-guide.html

It contains a fair amount of examples, but unfortunately no
direct comparision with pysqlite 1.