On Mon, May 20, 2013 at 8:12 AM, Daniele Varrazzo < daniele.varra...@gmail.com> wrote:
> On Mon, May 20, 2013 at 12:51 PM, Vernon D. Cole <vernondc...@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. > Indeed, the DB API2 does not address parameter reformatting at all. We are just starting to play with that. I am merely making sure that a reformat operation does not step on the contents of an ANSI standard literal. And, yes, when I am reformatting a string I do have to parse it. How else would I find my format markers? > > 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 As a matter of fact, counting quotes handles this problem quite nicely. Actually, in my code I don't really count them. I do a .split("'") [that is using a single quote] and then only parse the odd-numbered tokens. Then do a "'".join(). > or applying a regexp: take a > look at the Postgres literals grammar: > <http://www.postgresql.org/docs/9.2/static/sql-syntax-lexical.html>. > v v v v v v v v 4.1.2.1. String Constants A string constant in SQL is an arbitrary sequence of characters bounded by single quotes ('), for example 'This is a string'. To include a single-quote character within a string constant, write two adjacent single quotes, e.g., 'Dianne''s horse'. Note that this is not the same as a double-quote character ("). ^ ^ ^ ^ ^ ^ That's what I am using for this test: an arbitrary sequence of characters bounded by single quotes. > 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. > Yes, that is the point: forward the query, not mess it up. > > I don't think any currently implemented driver behaves as you wish. > I happen to _know_ that at least _one_ does. ( My code is GPL, feel free to copy. I copied from yours yesterday morning.) > 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. > > That is, indeed, a programming error. "h" and "i" are two values. The line should have another comma: In [6]: cur.execute("insert into test values (?, 'wat?')", ('hi',)) I usually pass a single parameter in a list simply to avoid that pattern: In [6]: cur.execute("insert into test values (?, 'wat?')", ['hi']) (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>) > > Here is what the document you linked says: v v v v 4.1.2.1. String Constants A string constant in SQL is an arbitrary sequence of characters bounded by single quotes ('), for example 'This is a string'. To include a single-quote character within a string constant, write two adjacent single quotes, e.g., 'Dianne''s horse'. Note that this is not the same as a double-quote character ("). ^ ^ ^ ^ ^ Nothing in there about avoiding question marks. Now let's try it.... C:\Users\vernon\Projects\qmarkpg\tests>python Python 2.7.4 (default, Apr 6 2013, 19:55:15) [MSC v.1500 64 bit (AMD64)] on win32 Type "help", "copyright", "credits" or "license" for more information. >>> import adodbapi >>> cnn = adodbapi.connect( ... '%(prov_drv)s;Server=%(host)s;Database=%(database)s;uid=%(user)s;pwd=%(password)s;', ... 'adotestuser', '12345678', "25.223.161.222", 'adotest', ... prov_drv='Provider=MSDASQL;Driver={PostgreSQL Unicode(x64)}') >>> cnn.dbms_name u'PostgreSQL' >>> cnn.dbms_version u'9.1.9' >>> cur = cnn.cursor() >>> cur.execute("create table test (a text, b text)") >>> cur.paramstyle 'qmark' >>> cur.execute("insert into test values (?, ?)", ('hi', 'mum')) >>> cur.execute("insert into test values (?, 'wat?')", ('hi',)) >>> cur.paramstyle = 'format' >>> cur.execute("insert into test values (%s, '%sure')", ('hello',)) >>> cnn.commit() >>> cur.execute('select * from test') >>> for row in cur: ... print row ... ('hi', 'mum') ('hi', 'wat?') ('hello', '%sure') >>> Seems to me that counting quotes works. -- Vernon
_______________________________________________ DB-SIG maillist - DB-SIG@python.org http://mail.python.org/mailman/listinfo/db-sig
