Lie Hetland book: Beginning Python..

[Gerhard Häring]

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.

The reason why pysqlite 0.x/1.x used paramstyle "pyformat", based on 
Python string substitution for SQL parameters is that at the time 
pysqlite was started, SQLite 2.x did not have any support for parameter 
binding. So we had to "fake" it in Python, just like the MySQL interface 
does (for the same reasons).

Later SQLite 2.x versions and of course SQLite 3.x supported real bound 
parameters and pysqlite2 was developed from scratch to benefit from 
them. SQLite 3.x supports both qmark and named paramstyles, so you can 
use question marks *or* named parameters:

 >>> from pysqlite2 import dbapi2 as sqlite
 >>> con = sqlite.connect(":memory:")
 >>> cur = con.cursor()
 >>> cur.execute("select 2*?", (14,))
 >>> cur.fetchone()
(28,)
 >>>
 >>> cur.execute("select 2 * :x", {"x": 14})
 >>> cur.fetchone()
(28,)
 >>>
 >>> x = 14
 >>> cur.execute("select 2 * :x", locals())
 >>> cur.fetchone()
(28,)
 >>>

I've also once written a wrapper using pysqlite 2.x's hooks that allows 
you to use the "format" paramstyle with pysqlite 2.x, so you can reuse 
more code that was originally written against pysqlite 0.x/1.x:

from pysqlite2 import dbapi2 as sqlite

class PyFormatConnection(sqlite.Connection):
     def cursor(self):
         return sqlite.Connection.cursor(self, PyFormatCursor)

class PyFormatCursor(sqlite.Cursor):
     def execute(self, sql, args=None):
         if args:
             qmarks = ["?"] * len(args)
             sql = sql % tuple(qmarks)
             sqlite.Cursor.execute(self, sql, args)
         else:
             sqlite.Cursor.execute(self, sql)

con = sqlite.connect(":memory:", factory=PyFormatConnection)
cur = con.cursor()
cur.execute("create table test(a, b, c)")
cur.execute("insert into test(a, b, c) values (%s, %s, %s)", ('asdf', 4, 
5.2))
cur.execute("select a, b, c from test where c <> %s", (4.27,))
print cur.fetchone()
cur.close()
con.close()

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

I think about the only place I wrote a bit about the differences was in 
the pysqlite 2.0 final announcement:

http://lists.initd.org/pipermail/pysqlite/2005-May/000043.html

-- Gerhard