Maybe inserttable should be rewritten in python, to use all the adaptation
logic and flexibility that already exists there. I started this, and seems to
work fine. Obviously this break compatibility for anyone using
pg.connect().inserttable().
commit 94c8e81260b40ac87af6fda7a5368b220dbcb3ff
Author: Justin Pryzby <pryz...@telsasoft.com>
Date: Sun Jun 27 09:14:35 2021 -0500
WIP: reimplement inserttable() as a DB() method
diff --git a/pg.py b/pg.py
index d9ee6e1..bdded31 100644
--- a/pg.py
+++ b/pg.py
@@ -1761,26 +1761,46 @@ class DB:
def inserttable(self, table, rows, columns=None):
# PQescapeIdentifier
- sql = 'copy %s'%table
- if columns:
- sql += '( %s )' % ', '.join(columns) # escape
+ if not isinstance(rows, (list,tuple)):
+ raise TypeError('expects a list or a tuple as second argument')
+
+ sql = 'copy %s' % self.escape_identifier(table)
+ if columns == []:
+ return
+ if columns is not None:
+ sql += '( %s )' % ', '.join([self.escape_identifier(i) for i in
columns])
sql += ' from stdin'
self.query(sql)
+ try:
+ self._inserttable_guts(rows)
+ except Exception as e:
+ raise
+ finally:
+ self.endcopy()
+ def _inserttable_guts(self, rows):
import re
- bytesreg = re.compile(b'[\\\t\n]')
+ bytesreg = re.compile(b'([\\\t\n])')
+ strreg = re.compile('([\\\t\n])')
for row in rows:
+ if not isinstance(row, (list,tuple)):
+ raise TypeError('second argument must contain a tuple or a
list')
+
toput = []
for col in row:
if col is None:
toput.append('\\N')
elif isinstance(col, bytes):
- toput.append(re.sub(bytesreg, col, r'\1'))
- elif isinstance(col, (int,long)):
+ toput.append(re.sub(bytesreg, br'\\\g<1>', col).decode())
+ elif isinstance(col, (str,unicode)):
+ toput.append(re.sub(strreg, r'\\\g<1>', col))
+ #elif isinstance(col, (int,long)):
+ #toput.append(str(col))
+ else:
toput.append(str(col))
#pg_str = getattr(value, '__pg_str__', None)
- self.putline('\t'.join(toput))
- self.endcopy()
+ toput = '\t'.join(toput) + '\n'
+ self.putline(toput)
def close(self):
"""Close the database connection."""
diff --git a/pgconn.c b/pgconn.c
index 63433b9..7064ce4 100644
--- a/pgconn.c
+++ b/pgconn.c
@@ -1627,8 +1627,8 @@ static struct PyMethodDef conn_methods[] = {
METH_O, conn_set_notice_receiver__doc__},
{"getnotify", (PyCFunction) conn_get_notify,
METH_NOARGS, conn_get_notify__doc__},
- {"inserttable", (PyCFunction) conn_inserttable,
- METH_VARARGS, conn_inserttable__doc__},
+ // {"inserttable", (PyCFunction) conn_inserttable,
+ // METH_VARARGS, conn_inserttable__doc__},
{"transaction", (PyCFunction) conn_transaction,
METH_NOARGS, conn_transaction__doc__},
{"parameter", (PyCFunction) conn_parameter,
diff --git a/tests/test_classic_connection.py b/tests/test_classic_connection.py
index 4bb7336..10326df 100755
--- a/tests/test_classic_connection.py
+++ b/tests/test_classic_connection.py
@@ -1800,7 +1800,7 @@ class TestInserttable(unittest.TestCase):
def setUp(self):
self.assertTrue(self.cls_set_up)
- self.c = connect()
+ self.c = pg.DB(dbname, dbhost, dbport)
self.c.query("set client_encoding=utf8")
self.c.query("set datestyle='ISO,YMD'")
self.c.query("set lc_monetary='C'")
@@ -1831,6 +1831,8 @@ class TestInserttable(unittest.TestCase):
def get_back(self, encoding='utf-8'):
"""Convert boolean and decimal values back."""
data = []
+ pg.set_typecast('date', None)
+ pg.set_typecast('time', None)
for row in self.c.query("select * from test order by 1").getresult():
self.assertIsInstance(row, tuple)
row = list(row)
@@ -2083,7 +2085,7 @@ class TestDirectSocketAccess(unittest.TestCase):
@classmethod
def setUpClass(cls):
- c = connect()
+ c = pg.DB(dbname, dbhost, dbport)
c.query("drop table if exists test cascade")
c.query("create table test (i int, v varchar(16))")
c.close()
@@ -2097,7 +2099,8 @@ class TestDirectSocketAccess(unittest.TestCase):
def setUp(self):
self.assertTrue(self.cls_set_up)
- self.c = connect()
+ #self.c = connect()
+ self.c = pg.DB(dbname, dbhost, dbport)
self.c.query("set client_encoding=utf8")
def tearDown(self):
On Sat, Jun 19, 2021 at 12:36:30AM -0500, Justin Pryzby wrote:
> You'll see that I created another issue report.
> As you might guess, I'm starting to look at using inserttable() here :)
>
> Now, I have a question and possibly another issue with inserttable().
>
> I called Adapt().adapt() to handle None/strings/bytes/list, and pass a list of
> the adapted results to inserttable(). This seemed to work fine, except for
> timestamp values, which were failed during COPY since it was sending their
> repr():
>
> | sendto(3, "d\0\0\0005datetime.datetime(2021, 6, 19, 14, 57, 0,
> 58906)\nd\0\0\0\7\\.\nc\0\0\0\4", 67, MSG_NOSIGNAL, NULL, 0) = 67
> | recvfrom(3, "E\0\0\0\377SERROR\0VERROR\0C22007\0Minvalid input syntax for
> type timestamp with time zone: \"datetime.datetime(2021...
>
> The timestamp isn't being stringified during adaptation:
> | @classmethod
> | def _adapt_date(cls, v):
> | """Adapt a date parameter."""
> | if not v:
> | return None
> | if isinstance(v, basestring) and v.lower() in cls._date_literals:
> | return Literal(v)
> | return v
>
> Inserttable handles None, bytes, unicode, int, long, but then the timestamp
> values falls through and gets repr()d here:
>
> else {
> PyObject* s = PyObject_Repr(item);
> const char* t = PyStr_AsString(s);
>
> The docs say:
> | The rows values may contain string, integer, long or double (real) values.
>
> If it's really intended to only handle those, then I guess it should call
> PyFloat_Check() and error if the type isn't handled.
>
> Alternately, should inserttable have an additional branch for PyDate_Check() ?
> Or should its "else" branch use PyObject_Str instead of Repr ? When using
> query(), adapt() returns the value itself, which is then stringified when
> format_query() does command %= tuple(literals)
>
> So maybe inserttable should do the same ?
_______________________________________________
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql
