db.query fails with "PRAGMA" on "?"

[Yoan Blanc]

SQLite fails using the db.query "vars" syntactic sugar when it succeed elsewhere.

>>> len(db.query("PRAGMA table_info('links')").list())
0.0 (2): PRAGMA table_info('links')
5
>>> len(db.query("PRAGMA table_info($links)", vars={'links':'links'}).list())
ERR: PRAGMA table_info('links')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python2.5/site-packages/web/db.py", line 739, in query
    out = DB.query(self, *a, **kw)
  File "/usr/lib/python2.5/site-packages/web/db.py", line 486, in query
    self._db_execute(db_cursor, sql_query)
  File "/usr/lib/python2.5/site-packages/web/db.py", line 436, in _db_execute
    out = cur.execute(sql_query.query(paramstyle), sql_query.values())
sqlite3.OperationalError: near "?": syntax error

[Yoan Blanc (greut)]

Of course it's on webpy.dev branch and not webpy.

Sorry, I'll get it right next time.

[Anand Chitipothu (anandology)]

Looks like this is a problem with sqlite3, not web.py.

>>> import sqlite3
>>> conn = sqlite3.connect('webpy.db')
>>> conn.execute("PRAGMA table_info('?')", ("person",))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 1 supplied.

[Yoan Blanc (greut)]

$ sqlite3 temp.db
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> CREATE TABLE persons (id INTEGER AUTO_INCREMENT, name VARCHAR);
sqlite> PRAGMA table_info('persons');
0|id|INTEGER AUTO_INCREMENT|0||0
1|name|VARCHAR|0||0

$ python
>>> import sqlite3
>>> conn = sqlite3.connect('temp.db')
>>> conn.execute("PRAGMA table_info('persons');").fetchall()
[(0, u'id', u'INTEGER AUTO_INCREMENT', 0, None, 0), (1, u'name', u'VARCHAR', 0, None, 0)]

it works for me?

>>> conn.execute("SELECT * FROM persons WHERE name=?;", ["greut"]).fetchall()
[(None, u'greut')]

but:

>>> conn.execute("SELECT * FROM '?' WHERE name='greut';", ["persons"]).fetchall()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: no such table: ?

? cannot be applied anywhere… I guess web.py don't have to find a workaround itself for this.