A coworker pointed out that this doesn't work:
>>> a=[]
>>> pg.DB().query_formatted("select 1 where 1 = any(%s)", [a] ).getresult()
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/usr/lib64/python2.6/site-packages/pg.py", line 1705, in query_formatted
command, parameters, types, inline))
File "/usr/lib64/python2.6/site-packages/pg.py", line 621, in format_query
append(add(value))
File "/usr/lib64/python2.6/site-packages/pg.py", line 268, in add
value = self.adapt(value, typ)
File "/usr/lib64/python2.6/site-packages/pg.py", line 477, in adapt
adapt = getattr(self, '_adapt_%s_array' % simple[:-2])
AttributeError: Adapter instance has no attribute '_adapt_None_array'
I noticed it works with pgdb:
>>> a=pgdb.connect().cursor()
>>> a.execute('SELECT 1 WHERE 1=ANY(%s)', [ [] ])
<pgdb.Cursor object at 0x7f04b81d9550>
>>> a.fetchall()
[]
.. and found a workaround:
>>> pg.DB().query_formatted('SELECT 1 WHERE 1=ANY(%s::integer[])', [ [] ],
>>> inline=True ).getresult()
[]
Note, this does not work:
>>> pg.DB().query_formatted('SELECT 1 WHERE 1=ANY(%s)', [ [] ], [list]
>>> ).getresult()
[...]
pg.DataError: ERROR: malformed array literal: "[]"
DETAIL: "[" must introduce explicitly-specified array dimensions.
For context, psycopg specifically allows that case:
http://initd.org/psycopg/docs/usage.html#lists-adaptation
|You can use a Python list as the argument of the IN operator using the
PostgreSQL ANY operator.
|[...]
|Furthermore ANY can also work with empty lists, whereas IN () is a SQL syntax
error.
I gather pg is doing:
def adapt(self, value, typ=None):
"""Adapt a value with known database type."""
if value is not None and not isinstance(value, Literal):
...
typ = simple = self.guess_simple_type(value) or 'text'
# None?
...
elif simple.endswith('[]'):
if isinstance(value, list):
adapt = getattr(self, '_adapt_%s_array' % simple[:-2])
value = adapt(value)
Thanks for your work on pygres.
Justin
_______________________________________________
PyGreSQL mailing list
[email protected]
https://mail.vex.net/mailman/listinfo.cgi/pygresql
