[DB-SIG] Test suite update for format conversion accuracy.

Daniele Varrazzo daniele.varrazzo at gmail.com
Mon May 20 16:12:16 CEST 2013


On Mon, May 20, 2013 at 12:51 PM, Vernon D. Cole <vernondcole at gmail.com> wrote:
> Stuart:
>
> I have just uploaded a branch for your consideration for
> https://code.launchpad.net/dbapi-compliance.  This version adds a second
> field to the second test table (barflies) and loads that field with the
> literal 'thi%s :may cau%(e)s? troub:1e'.  The value of this literal is a
> trap for unwary format conversion routines which fail to notice that they
> are re-formatting a literal value.

You are requiring the sql string to be parsed, literal string
identified and placeholders inside them not treated. This is not a
DBAPI2 requirement.

Without this requirement a driver can be implemented just passing the
string to the client library, an extremely reasonable way to implement
a client library wrapper. If you add it every driver will need to know
the parsing rule of the server (and of its version). This is not a
task as trivial as counting the quotes or applying a regexp: take a
look at the Postgres literals grammar:
<http://www.postgresql.org/docs/9.2/static/sql-syntax-lexical.html>.
Of course PG developers are free to complicate that as much as they
want: a client driver is expected to be agnostic and just forward the
query.

I don't think any currently implemented driver behaves as you wish.
Here is stdlib's sqlite3:

In [1]: import sqlite3

In [2]: cnn = sqlite3.connect(':memory:')

In [3]: cur = cnn.cursor()

In [4]: cur.execute("create table test (a text, b text)")
Out[4]: <sqlite3.Cursor at 0x2a4d500>

In [5]: cur.execute("insert into test values (?, ?)", ('hi', 'mum'))
Out[5]: <sqlite3.Cursor at 0x2a4d500>

In [6]: cur.execute("insert into test values (?, 'wat?')", ('hi'))
---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
/home/piro/src/qmarkpg/<ipython-input-6-b8a3105dc710> in <module>()
----> 1 cur.execute("insert into test values (?, 'wat?')", ('hi'))

ProgrammingError: Incorrect number of bindings supplied. The current
statement uses 1, and there are 2 supplied.

(for extra fun, I don't think sqlite3 even contemplates the
possibility to use ? into a literal and neither ?? nor \? appear to
work, see <http://www.sqlite.org/lang_expr.html#varparam>)


-- Daniele


More information about the DB-SIG mailing list