Author: cito
Date: Thu Jan 28 14:54:34 2016
New Revision: 792
Log:
Using ARRAY and ROW constructor in pgdb again
Using the special input syntax for quoting arrays and rows had some
advantages, but one big disadvantage, namely the missing type information.
Therefore, this change has been reverted, we now use ARRAY and ROW
constructor syntax again to quote lists and tuples. See comments.
The code has become simpler again and doesn't need the re module any more.
Modified:
trunk/pgdb.py
trunk/tests/test_dbapi20.py
Modified: trunk/pgdb.py
==============================================================================
--- trunk/pgdb.py Wed Jan 27 18:09:18 2016 (r791)
+++ trunk/pgdb.py Thu Jan 28 14:54:34 2016 (r792)
@@ -72,7 +72,6 @@
from decimal import Decimal
from math import isnan, isinf
from collections import namedtuple
-from re import compile as regex
from json import loads as jsondecode, dumps as jsonencode
try:
@@ -264,11 +263,6 @@
return lambda value: typecast(typ, value)
-_re_array_quote = regex(r'[{},"\\\s]|^[Nn][Uu][Ll][Ll]$')
-_re_record_quote = regex(r'[(,"\\]')
-_re_array_escape = _re_record_escape = regex(r'(["\\])')
-
-
class _quotedict(dict):
"""Dictionary with auto quoting of its items.
@@ -336,64 +330,27 @@
if isinstance(val, (int, long, Decimal)):
return val
if isinstance(val, list):
- return "'%s'" % self._quote_array(val)
+ # Quote value as an ARRAY constructor. This is better than using
+ # an array literal because it carries the information that this is
+ # an array and not a string. One issue with this syntax is that
+ # you need to add an explicit type cast when passing empty arrays.
+ # The ARRAY keyword is actually only necessary at the top level.
+ q = self._quote
+ return 'ARRAY[%s]' % ','.join(str(q(v)) for v in val)
if isinstance(val, tuple):
- return "'%s'" % self._quote_record(val)
+ # Quote as a ROW constructor. This is better than using a record
+ # literal because it carries the information that this is a record
+ # and not a string. We don't use the keyword ROW in order to make
+ # this usable with the IN synntax as well. It is only necessary
+ # when the records has a single column which is not really useful.
+ q = self._quote
+ return '(%s)' % ','.join(str(q(v)) for v in val)
try:
return val.__pg_repr__()
except AttributeError:
raise InterfaceError(
'do not know how to handle type %s' % type(val))
- def _quote_array(self, val):
- """Quote value as a literal constant for an array."""
- q = self._quote_array_element
- return '{%s}' % ','.join(q(v) for v in val)
-
- def _quote_array_element(self, val):
- """Quote value using the output syntax for arrays."""
- if isinstance(val, list):
- return self._quote_array(val)
- if val is None:
- return 'null'
- if isinstance(val, (int, long, float)):
- return str(val)
- if isinstance(val, bool):
- return 't' if val else 'f'
- if isinstance(val, tuple):
- val = self._quote_record(val)
- if isinstance(val, basestring):
- if not val:
- return '""'
- if _re_array_quote.search(val):
- return '"%s"' % _re_array_escape.sub(r'\\\1', val)
- return val
- raise InterfaceError(
- 'do not know how to handle base type %s' % type(val))
-
- def _quote_record(self, val):
- """Quote value as a literal constant for a record."""
- q = self._quote_record_element
- return '(%s)' % ','.join(q(v) for v in val)
-
- def _quote_record_element(self, val):
- """Quote value using the output syntax for records."""
- if val is None:
- return ''
- if isinstance(val, (int, long, float)):
- return str(val)
- if isinstance(val, bool):
- return 't' if val else 'f'
- if isinstance(val, list):
- val = self._quote_array(val)
- if isinstance(val, basestring):
- if not val:
- return '""'
- if _re_record_quote.search(val):
- return '"%s"' % _re_record_escape.sub(r'\\\1', val)
- return val
- raise InterfaceError(
- 'do not know how to handle component type %s' % type(val))
def _quoteparams(self, string, parameters):
"""Quote parameters.
Modified: trunk/tests/test_dbapi20.py
==============================================================================
--- trunk/tests/test_dbapi20.py Wed Jan 27 18:09:18 2016 (r791)
+++ trunk/tests/test_dbapi20.py Thu Jan 28 14:54:34 2016 (r792)
@@ -483,8 +483,10 @@
cur.execute("create table %s"
" (n smallint, i int[], t text[][])" % table)
params = [(n, v[0], v[1]) for n, v in enumerate(values)]
- cur.executemany(
- "insert into %s values (%%d,%%s,%%s)" % table, params)
+ # Note that we must explicit casts because we are inserting
+ # empty arrays. Otherwise this is not necessary.
+ cur.executemany("insert into %s values"
+ " (%%d,%%s::int[],%%s::text[][])" % table, params)
cur.execute("select i, t from %s order by n" % table)
self.assertEqual(cur.description[0].type_code, pgdb.ARRAY)
self.assertEqual(cur.description[0].type_code, pgdb.NUMBER)
@@ -540,16 +542,12 @@
self.assertEqual(rows[0].age, 61)
def test_select_record(self):
- values = (1, 25000, 2.5, 'hello', 'Hello World!', 'Hello, World!',
+ value = (1, 25000, 2.5, 'hello', 'Hello World!', 'Hello, World!',
'(test)', '(x,y)', ' x y ', 'null', None)
con = self._connect()
try:
cur = con.cursor()
- # Note that %s::record does not work on input unfortunately
- # ("input of anonymous composite types is not implemented").
- # so we need to resort to a row constructor instead.
- row = ','.join(["%s"] * len(values))
- cur.execute("select ROW(%s) as test_record" % row, values)
+ cur.execute("select %s as test_record", [value])
self.assertEqual(cur.description[0].name, 'test_record')
self.assertEqual(cur.description[0].type_code, 'record')
row = cur.fetchone()[0]
@@ -558,8 +556,8 @@
# Note that the element types get lost since we created an
# untyped record (an anonymous composite type). For the same
# reason this is also a normal tuple, not a named tuple.
- text_values = tuple(None if v is None else str(v) for v in values)
- self.assertEqual(row, text_values)
+ text_row = tuple(None if v is None else str(v) for v in value)
+ self.assertEqual(row, text_row)
def test_custom_type(self):
values = [3, 5, 65]
_______________________________________________
PyGreSQL mailing list
[email protected]
https://mail.vex.net/mailman/listinfo.cgi/pygresql
