On Sat, Jun 20, 2020 at 01:10:14AM +0200, Christoph Zwerschke wrote:
> Am 20.06.2020 um 00:54 schrieb Justin Pryzby:
> > I think these would be easy to add.
> >
> > https://github.com/PyGreSQL/PyGreSQL/issues/29
> > The pgquery type should have a method listtypes() #29
> > I sent a patch for this to the list last year
> >
> > https://github.com/PyGreSQL/PyGreSQL/issues/24
> > Allow inserttable() to take columns as parameter #24
> > This is simple enough I can probably put something together
>
> Ok, I will look into these two issues tomorrow.
1) This is actually possible if one passes a column list in the "table"
argument:
|d.inserttable('t(j)', ([2],))
This works by abusing the "table" argument, which isn't being escaped - That
should be addressed in a separate commit.
2) I think these should all be factored out of all their callers in the C
module: PyBytes_FromStringAndSize() / get_decoded_string().
3) I implemented an interface like:
|d.inserttable('t', ([2],), ['col1', 'col2'])
I'm attaching my WIP patch. It will needs to be cleaned up, since (1) and (2)
will surely cause rebase conflicts, so this is just a POC.
4) Note that this changelog item is under PGDB but should be under PG:
"The `types` parameter of `format_query` can now be passed as a string..."
--
Justin
>From 1dfd3ba134d43f3464ddee4b4cde37e06af2acb7 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Fri, 19 Jun 2020 19:51:58 -0500
Subject: [PATCH] WIP/POC: add an argument to inserttable() accepting optional
list of column names
---
docs/contents/changelog.rst | 10 ++++---
docs/contents/pg/connection.rst | 4 ++-
pgconn.c | 51 ++++++++++++++++++++++++++++----
tests/test_classic_connection.py | 13 ++++++++
4 files changed, 67 insertions(+), 11 deletions(-)
diff --git a/docs/contents/changelog.rst b/docs/contents/changelog.rst
index eaafb88..b821a89 100644
--- a/docs/contents/changelog.rst
+++ b/docs/contents/changelog.rst
@@ -14,16 +14,18 @@ Version 5.2 (to be released)
Additional connection parameter ``nowait``, and connection methods
`send_query()`, `poll()`, `set_non_blocking()`, `is_non_blocking()`.
Generously contributed by Patrick TJ McPhee (#19).
+ - The `types` parameter of `format_query` can now be passed as a string
+ that will be split on whitespace when values are passed as a sequence,
+ and the types can now also be specified using actual Python types
+ instead of type names. Suggested by Justin Pryzby (#38).
+ - The `inserttable()` now accepts an optional column list to be passed on
+ to the underlying SQL ``COPY`` command.
- Changes to the DB-API 2 module (pgdb):
- When using Python 2, errors are now derived from StandardError
instead of Exception, as required by the DB-API 2 compliance test.
- Connection arguments containing single quotes caused problems
(reported and fixed by Tyler Ramer and Jamie McAtamney).
- - The `types` parameter of `format_query` can now be passed as a string
- that will be split on whitespace when values are passed as a sequence,
- and the types can now also be specified using actual Python types
- instead of type names. Suggested by Justin Pryzby (#38).
Version 5.1.2 (2020-04-19)
--------------------------
diff --git a/docs/contents/pg/connection.rst b/docs/contents/pg/connection.rst
index f342c02..acf477c 100644
--- a/docs/contents/pg/connection.rst
+++ b/docs/contents/pg/connection.rst
@@ -487,12 +487,13 @@ first, otherwise :meth:`Connection.getnotify` will always return ``None``.
inserttable -- insert a list into a table
-----------------------------------------
-.. method:: Connection.inserttable(table, values)
+.. method:: Connection.inserttable(table, values, [columns])
Insert a Python list into a database table
:param str table: the table name
:param list values: list of rows values
+ :param list columns: list of column names
:rtype: None
:raises TypeError: invalid connection, bad argument type, or too many arguments
:raises MemoryError: insert buffer could not be allocated
@@ -503,6 +504,7 @@ It inserts the whole values list into the given table. Internally, it
uses the COPY command of the PostgreSQL database. The list is a list
of tuples/lists that define the values for each inserted row. The rows
values may contain string, integer, long or double (real) values.
+columns is an iterable over column names to be passed to the ``COPY`` command.
.. warning::
diff --git a/pgconn.c b/pgconn.c
index f8c81e2..b7452e4 100644
--- a/pgconn.c
+++ b/pgconn.c
@@ -686,13 +686,14 @@ conn_is_non_blocking(connObject *self, PyObject *args)
/* Insert table */
static char conn_inserttable__doc__[] =
-"inserttable(table, data) -- insert list into table\n\n"
-"The fields in the list must be in the same order as in the table.\n";
+"inserttable(table, data, [columns]) -- insert list into table\n\n"
+"The fields in the list must be in the same order as in the list of columns (if specified) or table.\n";
static PyObject *
conn_inserttable(connObject *self, PyObject *args)
{
PGresult *result;
+ PyObject *column_obj = NULL;
char *table, *buffer, *bufpt;
int encoding;
size_t bufsiz;
@@ -707,7 +708,7 @@ conn_inserttable(connObject *self, PyObject *args)
}
/* gets arguments */
- if (!PyArg_ParseTuple(args, "sO:filter", &table, &list)) {
+ if (!PyArg_ParseTuple(args, "sO|O:FiLtEr", &table, &list, &column_obj)) {
PyErr_SetString(
PyExc_TypeError,
"Method inserttable() expects a string and a list as arguments");
@@ -731,12 +732,52 @@ conn_inserttable(connObject *self, PyObject *args)
return NULL;
}
+ /* If column_obj is passed, ensure it's a non-empty tuple. */
+ if (column_obj) {
+ if (!PyList_Check(column_obj) &&
+ !PyTuple_Check(list)) {
+ PyErr_SetString(
+ PyExc_TypeError,
+ "Method inserttable() expects a list or a tuple"
+ " as third argument");
+ return NULL;
+ }
+
+ column_obj = PySequence_Fast(
+ column_obj, "Method inserttable() expects an iterable as third argument");
+ if (!column_obj) {
+ // XXX Py_XDECREF();
+ return NULL;
+ }
+ }
+
/* allocate buffer */
if (!(buffer = PyMem_Malloc(MAX_BUFFER_SIZE)))
return PyErr_NoMemory();
+ encoding = PQclientEncoding(self->cnx);
+
+ table = PQescapeIdentifier(self->cnx, table, strlen(table));
+
/* starts query */
- sprintf(buffer, "copy %s from stdin", table);
+ if (!column_obj)
+ sprintf(buffer, "copy %s from stdin", table);
+ else {
+ /* Adds a string like '({})'.format(', '.join(columns)) */
+ int len = (int) PySequence_Fast_GET_SIZE(column_obj);
+ sprintf(buffer, "copy %s(", table);
+ for (int i=0; i<len; ++i) {
+ // XXX: need to test this is a TUPLE or LIST (and not a STRING)
+ PyObject *obj = PySequence_Fast_GET_ITEM(column_obj, i);
+ PyObject *str_obj = get_encoded_string(obj, encoding);
+ ssize_t slen;
+ char *col;
+ PyBytes_AsStringAndSize(str_obj, &col, &slen);
+ col = PQescapeIdentifier(self->cnx, col, (size_t) slen);
+ sprintf(buffer+strlen(buffer), "%s%s", col, i<len-1 ? ", " : "");
+ }
+ sprintf(buffer+strlen(buffer), ") from stdin");
+ }
Py_BEGIN_ALLOW_THREADS
result = PQexec(self->cnx, buffer);
@@ -748,8 +789,6 @@ conn_inserttable(connObject *self, PyObject *args)
return NULL;
}
- encoding = PQclientEncoding(self->cnx);
-
PQclear(result);
n = 0; /* not strictly necessary but avoids warning */
diff --git a/tests/test_classic_connection.py b/tests/test_classic_connection.py
index c1f265e..935f2d1 100755
--- a/tests/test_classic_connection.py
+++ b/tests/test_classic_connection.py
@@ -1889,6 +1889,19 @@ class TestInserttable(unittest.TestCase):
self.c.inserttable('test', data)
self.assertEqual(self.get_back(), data)
+ def testInserttableMissingValues(self):
+ datain = [(11,)] * 100
+ dataout = [tuple([11 if i==2 else None for i in range(1,15)])] * 100
+ self.c.inserttable('test', datain, ['i4'])
+ self.assertEqual(self.get_back(), dataout)
+
+# This test passes if the table name is not escaped...
+ def testInserttableMissingValues2(self):
+ datain = [(11,)] * 100
+ dataout = [tuple([11 if i==2 else None for i in range(1,15)])] * 100
+ self.c.inserttable('test(i4)', datain)
+ self.assertEqual(self.get_back(), dataout)
+
def testInserttableMaxValues(self):
data = [(2 ** 15 - 1, int(2 ** 31 - 1), long(2 ** 31 - 1),
True, '2999-12-31', '11:59:59', 1e99,
--
2.17.0
_______________________________________________
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql
