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
--
http://mail.python.org/mailman/listinfo/python-list
