On Sun, Jun 27, 2021 at 10:14:49AM -0500, Justin Pryzby wrote:
> 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
Sorry, the work was accidentlly split across two commits.
Find attached my works in progress.
I can polish these and add docs and tests, but I'd like some feedback about the
future of inserttable and adaptation.
>From 06955b958260dee7b5b83ae64ff7c02c187239e4 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Sat, 26 Jun 2021 23:00:12 -0500
Subject: [PATCH 1/4] fix error checks in inserttable()
---
pgconn.c | 24 +++++++++++++++++-------
1 file changed, 17 insertions(+), 7 deletions(-)
diff --git a/pgconn.c b/pgconn.c
index 302dd29..1575df7 100644
--- a/pgconn.c
+++ b/pgconn.c
@@ -770,24 +770,32 @@ conn_inserttable(connObject *self, PyObject *args)
for (j = 0; j < n; ++j) {
PyObject *obj = getcolumn(columns, j);
ssize_t slen;
- char *col;
+ char *col, *newcol;
if (PyBytes_Check(obj)) {
PyBytes_AsStringAndSize(obj, &col, &slen);
- }
- else if (PyUnicode_Check(obj)) {
+ } else if (PyUnicode_Check(obj)) {
+ PyObject *oldobj = obj;
obj = get_encoded_string(obj, encoding);
- if (!obj) return NULL; /* pass the UnicodeEncodeError */
+ Py_DECREF(oldobj);
+ if (!obj) {
+ PyMem_Free(buffer);
+ return NULL; /* pass the UnicodeEncodeError */
+ }
PyBytes_AsStringAndSize(obj, &col, &slen);
Py_DECREF(obj);
} else {
PyErr_SetString(
PyExc_TypeError,
"The third argument must contain only strings");
+ PyMem_Free(buffer);
+ return NULL;
}
- col = PQescapeIdentifier(self->cnx, col, (size_t) slen);
+
+ newcol = PQescapeIdentifier(self->cnx, col, (size_t) slen);
bufpt += sprintf(bufpt, "%s%s", col, j == n - 1 ? ")" : ",");
- PQfreemem(col);
+ Py_DECREF(col);
+ PQfreemem(newcol);
}
}
sprintf(bufpt, " from stdin");
@@ -796,7 +804,7 @@ conn_inserttable(connObject *self, PyObject *args)
result = PQexec(self->cnx, buffer);
Py_END_ALLOW_THREADS
- if (!result) {
+ if (!result || PQresultStatus(result) != PGRES_COPY_IN) {
PyMem_Free(buffer);
PyErr_SetString(PyExc_ValueError, PQerrorMessage(self->cnx));
return NULL;
@@ -819,6 +827,7 @@ conn_inserttable(connObject *self, PyObject *args)
PyErr_SetString(
PyExc_TypeError,
"The second argument must contain a tuple or a list");
+ PQendcopy(self->cnx);
return NULL;
}
if (i) {
@@ -827,6 +836,7 @@ conn_inserttable(connObject *self, PyObject *args)
PyErr_SetString(
PyExc_TypeError,
"Arrays contained in second arg must have same size");
+ PQendcopy(self->cnx);
return NULL;
}
}
--
2.17.0
>From 35dfd0fc5c0be107ba3abf1009e50189a28cc6b7 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Sun, 27 Jun 2021 11:40:10 -0500
Subject: [PATCH 2/4] Allow connect().inserttable() to accept a
schema-qualified table
---
pgconn.c | 73 ++++++++++++++++++++++++++++++++++++++++++++++----------
1 file changed, 60 insertions(+), 13 deletions(-)
diff --git a/pgconn.c b/pgconn.c
index 1575df7..4be0706 100644
--- a/pgconn.c
+++ b/pgconn.c
@@ -693,6 +693,7 @@ conn_inserttable(connObject *self, PyObject *args)
char *table, *buffer, *bufpt;
int encoding;
size_t bufsiz;
+ PyObject *tableobj;
PyObject *list, *sublist, *item, *columns = NULL;
PyObject *(*getitem) (PyObject *, Py_ssize_t);
PyObject *(*getsubitem) (PyObject *, Py_ssize_t);
@@ -705,13 +706,70 @@ conn_inserttable(connObject *self, PyObject *args)
}
/* gets arguments */
- if (!PyArg_ParseTuple(args, "sO|O", &table, &list, &columns)) {
+ if (!PyArg_ParseTuple(args, "OO|O", &tableobj, &list, &columns)) {
PyErr_SetString(
PyExc_TypeError,
- "Method inserttable() expects a string and a list as arguments");
+ "Method inserttable() expects a string/list/tuple and a list as arguments");
return NULL;
}
+ encoding = PQclientEncoding(self->cnx);
+
+ /* allocate buffer */
+ if (!(bufpt = buffer = PyMem_Malloc(MAX_BUFFER_SIZE)))
+ return PyErr_NoMemory();
+
+ if (PyUnicode_Check(tableobj)) {
+ ssize_t len;
+ tableobj = get_encoded_string(tableobj, encoding);
+ if (!tableobj) return NULL; /* pass the UnicodeEncodeError */
+ // Py_DECREF(obj); //XXX
+ PyBytes_AsStringAndSize(tableobj, &table, &len);
+ table = PQescapeIdentifier(self->cnx, table, (size_t)len);
+ bufpt += sprintf(bufpt, "copy %s", table);
+ PQfreemem(table);
+ } else {
+ ssize_t length = 0;
+ if (PyList_Check(tableobj)) {
+ length = PyList_Size(tableobj);
+ getitem = PyList_GetItem;
+ } else if (PyTuple_Check(tableobj)) {
+ length = PyTuple_Size(tableobj);
+ getitem = PyTuple_GetItem;
+ }
+
+ if (length<0 || length>3) {
+ PyErr_SetString(
+ PyExc_TypeError,
+ "Method inserttable() expects a string or list/tuple of length 1, 2 or 3 as the first argument");
+ return NULL;
+ }
+
+ bufpt += sprintf(bufpt, "copy ");
+ for (i=0; i<length; ++i) {
+ PyObject *obj = getitem(tableobj, i);
+ char *tblpart;
+ ssize_t len;
+
+ if (PyBytes_Check(obj)) {
+ PyBytes_AsStringAndSize(obj, &tblpart, &len);
+ } else if (PyUnicode_Check(obj)) {
+ obj = get_encoded_string(obj, encoding);
+ if (!obj) return NULL; /* pass the UnicodeEncodeError */
+ PyBytes_AsStringAndSize(obj, &tblpart, &len);
+ Py_DECREF(obj);
+ } else {
+ PyErr_SetString(
+ PyExc_TypeError,
+ "The first argument must contain only strings");
+ return NULL;
+ }
+ tblpart = PQescapeIdentifier(self->cnx, tblpart, (size_t)len);
+ bufpt += sprintf(bufpt, "%s%s", tblpart, i==length-1 ? "" : ".");
+ PQfreemem(tblpart);
+ }
+ }
+
/* checks list type */
if (PyList_Check(list)) {
m = PyList_Size(list);
@@ -753,17 +811,6 @@ conn_inserttable(connObject *self, PyObject *args)
}
}
- /* allocate buffer */
- if (!(buffer = PyMem_Malloc(MAX_BUFFER_SIZE)))
- return PyErr_NoMemory();
-
- encoding = PQclientEncoding(self->cnx);
-
- /* starts query */
- bufpt = buffer;
- table = PQescapeIdentifier(self->cnx, table, strlen(table));
- bufpt += sprintf(bufpt, "copy %s", table);
- PQfreemem(table);
if (columns) {
/* adds a string like f" ({','.join(columns)})" */
bufpt += sprintf(bufpt, " (");
--
2.17.0
>From 5cdf2c632be60dd1decdb4522a9c4fcb731d490e Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Sun, 27 Jun 2021 01:11:03 -0500
Subject: [PATCH 3/4] WIP: rewrite inserttable in pg.py
WIP: reimplement inserttable() as a DB() method
---
pg.py | 43 ++++++++++++++++++++++++++++++++
pgconn.c | 4 +--
tests/test_classic_connection.py | 9 ++++---
3 files changed, 51 insertions(+), 5 deletions(-)
diff --git a/pg.py b/pg.py
index 6c1bd35..cf6896f 100644
--- a/pg.py
+++ b/pg.py
@@ -1759,6 +1759,49 @@ class DB:
"""Encode a JSON string for use within SQL."""
return jsonencode(d)
+ def inserttable(self, table, rows, columns=None):
+ # PQescapeIdentifier
+ 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])')
+ 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, 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)
+ toput = '\t'.join(toput) + '\n'
+ self.putline(toput)
+
def close(self):
"""Close the database connection."""
# Wraps shared library function so we can track state.
diff --git a/pgconn.c b/pgconn.c
index 4be0706..36ea1b3 100644
--- a/pgconn.c
+++ b/pgconn.c
@@ -1677,8 +1677,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):
--
2.17.0
>From 8d5436aeaf25b61c425916b6b0ea2781ba6f6d27 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Sun, 27 Jun 2021 10:27:29 -0500
Subject: [PATCH 4/4] WIP: inserttable: support schemas
---
pg.py | 13 ++++++++++++-
1 file changed, 12 insertions(+), 1 deletion(-)
diff --git a/pg.py b/pg.py
index cf6896f..25362e3 100644
--- a/pg.py
+++ b/pg.py
@@ -1764,7 +1764,18 @@ class DB:
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 isinstance(table, (str,unicode)):
+ table = self.escape_identifier(table)
+ elif isinstance(table, (list,tuple)) and\
+ len(table) <= 3 and len(table) > 0:
+ # Can be database.schema.table or schema.table or table
+ # The components must be separately escaped.
+ table = '.'.join(self.escape_identifier(i) for i in table)
+ else:
+ raise TypeError("table must be a string or a tuple of length 1, 2, or 3")
+
+ sql = 'copy %s' % table
+
if columns == []:
return
if columns is not None:
--
2.17.0
_______________________________________________
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql
