On Thu, Jul 06, 2017 at 02:23:37PM +0200, Christoph Zwerschke wrote:
> Am 05.07.2017 um 23:12 schrieb Justin Pryzby:
> >I'm hoping for some feedback on this - if the interface seems okay and the
> >implementation otherwise fine, I can clean up some by pulling
> >out the duplicated code from queryGet/Dict/Result and queryNext().
> Hi Justin,
Here's another revision; I haven't implemented named result iterators, and
still couldn't get len() to work.
Tomorrow I'll try to check if it saves us any RAM.
I "refactored" in 2 different ways; one issue is that queryNext() needs to take
zero arguments, but also needs col_types, so current reallocating/computing on
every iteration and every row populated in getresult()..
One possibility is if col_types was stored in the queryObject struct, and
either invalidated by query() and populated lazily by first call to result
function or (probably) just populated by query() for use by various result
functions. Is that reasoanble ?
Justin
Index: docs/contents/changelog.rst
===================================================================
--- docs/contents/changelog.rst (revision 901)
+++ docs/contents/changelog.rst (working copy)
@@ -57,7 +57,7 @@
- The classic interface got two new methods get_as_list() and get_as_dict()
returning a database table as a Python list or dict. The amount of data
returned can be controlled with various parameters.
- - A method upsert() has been added to the DB wrapper class that utilitses
+ - A method upsert() has been added to the DB wrapper class that utilizes
the "upsert" feature that is new in PostgreSQL 9.5. The new method nicely
complements the existing get/insert/update/delete() methods.
- When using insert/update/upsert(), you can now pass PostgreSQL arrays as
@@ -346,7 +346,7 @@
- Fixes to quoting function
- Add checks for valid database connection to methods
- Improved namespace support, handle `search_path` correctly
-- Removed old dust and unnessesary imports, added docstrings
+- Removed old dust and unnecessary imports, added docstrings
- Internal sql statements as one-liners, smoothed out ugly code
Version 3.6.2 (2005-02-23)
Index: docs/contents/pg/query.rst
===================================================================
--- docs/contents/pg/query.rst (revision 901)
+++ docs/contents/pg/query.rst (working copy)
@@ -48,6 +48,8 @@
Note that since PyGreSQL 5.0 this will return the values of array type
columns as Python lists.
+TODO: dictgetnext, namedgetnext
+
namedresult -- get query values as list of named tuples
-------------------------------------------------------
Index: pgmodule.c
===================================================================
--- pgmodule.c (revision 901)
+++ pgmodule.c (working copy)
@@ -176,6 +176,8 @@
connObject *pgcnx; /* parent connection object */
PGresult *result; /* result content */
int encoding; /* client encoding */
+ int current_row; /* current selected row */
+ int max_row; /* number of rows in
the result */
} queryObject;
#define is_queryObject(v) (PyType(v) == &queryType)
@@ -2369,6 +2371,8 @@
/* stores result and returns object */
Py_XINCREF(self);
npgobj->pgcnx = self;
+ npgobj->current_row = 0;
+ npgobj->max_row = PQntuples(result);
npgobj->result = result;
npgobj->encoding = encoding;
return (PyObject *) npgobj;
@@ -4510,7 +4514,7 @@
static PyObject *
queryNTuples(queryObject *self, PyObject *noargs)
{
- return PyInt_FromLong((long) PQntuples(self->result));
+ return PyInt_FromLong(self->max_row);
}
/* list fields names from query result */
@@ -4598,6 +4602,141 @@
return PyInt_FromLong(num);
}
+PyObject* queryGetIter(queryObject *self)
+{
+ Py_INCREF(self);
+ return (PyObject*)self;
+}
+
+/* Returns value of the given column */
+static PyObject *
+column_value(queryObject *self, int *col_types, int column)
+{
+ PyObject *val;
+
+ /* null case: */
+ if (PQgetisnull(self->result, self->current_row, column))
+ {
+ Py_INCREF(Py_None);
+ return Py_None;
+ }
+
+ /* not null case: */
+
+ /* get the string representation of the value */
+ /* note: this is always null-terminated text format */
+ char *s = PQgetvalue(self->result, self->current_row, column);
+ /* get the PyGreSQL type of the column */
+ int type = col_types[column];
+ int encoding = self->encoding;
+
+ if (type & PYGRES_ARRAY)
+ val = cast_array(s, PQgetlength(self->result,
self->current_row, column),
+ encoding, type, NULL, 0);
+ else if (type == PYGRES_BYTEA)
+ val = cast_bytea_text(s);
+ else if (type == PYGRES_OTHER)
+ val = cast_other(s,
+ PQgetlength(self->result, self->current_row, column),
encoding,
+ PQftype(self->result, column), self->pgcnx->cast_hook);
+ else if (type & PYGRES_TEXT)
+ val = cast_sized_text(s, PQgetlength(self->result,
self->current_row, column),
+ encoding, type);
+ else
+ val = cast_unsized_simple(s, type);
+
+ return val;
+}
+
+
+// doc ?
+static PyObject *
+queryNext(queryObject *self, PyObject *noargs)
+{
+ PyObject *res=NULL;
+ int j, *col_types;
+ int n = PQnfields(self->result);
+
+ if (self->current_row>=self->max_row) {
+ PyErr_SetNone(PyExc_StopIteration);
+ return NULL;
+ }
+
+ if (self->current_row < 0) {
+ PyErr_SetNone(PyExc_StopIteration); // XXX
+ return NULL;
+ }
+
+
+ if (!(res = PyTuple_New(n))) return NULL;
+ if (!(col_types = get_col_types(self->result, n))) {
+ Py_DECREF(res);
+ res = NULL;
+ goto exit;
+ }
+
+ for (j = 0; j<n; ++j)
+ {
+ PyObject *val;
+ if (NULL == (val = column_value(self, col_types, j)))
+ {
+ Py_DECREF(res);
+ res = NULL;
+ goto exit;
+ }
+
+ PyTuple_SET_ITEM(res, j, val);
+ }
+
+exit:
+ PyMem_Free(col_types);
+ if (res) ++self->current_row;
+ return res;
+}
+
+/* Update reference to current_row */
+static char queryMove__doc__[] =
+"queryMove([rows=1], [relative=True]) -- Move internal cursor-like pointer to
a different row";
+
+static PyObject *
+queryMove(queryObject *self, PyObject *args, PyObject *dict)
+{
+ static const char *kwlist[] = {"rows", "relative",};
+ int rows=1;
+ int relative=1; // True
+
+ if (!PyArg_ParseTupleAndKeywords(args, dict, "|ii", (char **) kwlist,
+ &rows, &relative))
+ return NULL;
+
+ if (relative) {
+ int new=rows + self->current_row;
+
+ if (new < 0) {
+ /* It's not really so bad, it'll just raise
StopIteration, but may as well take the opportunity to give a nice error */
+ PyErr_SetString(PyExc_ValueError, "Integer overflow");
+ return NULL;
+ }
+
+ if (new >= self->max_row) {
+ /* It's not really so bad, it'll just raise
StopIteration, but may as well take the opportunity to give a nice error */
+ PyErr_SetString(PyExc_ValueError, "Cannot advance
beyond the last row");
+ return NULL;
+ }
+
+ self->current_row = new;
+ } else {
+ if (rows<0) {
+ PyErr_SetString(PyExc_ValueError, "Cannot set negative
row number");
+ return NULL;
+ }
+ self->current_row = rows;
+ }
+
+ Py_INCREF(Py_None);
+ return Py_None;
+}
+
/* retrieves last result */
static char queryGetResult__doc__[] =
"getresult() -- Get the result of a query\n\n"
@@ -4608,80 +4747,84 @@
queryGetResult(queryObject *self, PyObject *noargs)
{
PyObject *reslist;
- int i, m, n, *col_types;
- int encoding = self->encoding;
+ int i;
/* stores result in tuple */
- m = PQntuples(self->result);
- n = PQnfields(self->result);
- if (!(reslist = PyList_New(m))) return NULL;
+ if (!(reslist = PyList_New(self->max_row))) return NULL;
- if (!(col_types = get_col_types(self->result, n))) return NULL;
-
- for (i = 0; i < m; ++i)
+ for (i = 0; i < self->max_row; ++i)
{
PyObject *rowtuple;
- int j;
- if (!(rowtuple = PyTuple_New(n)))
+ if (NULL==(rowtuple=queryNext(self, noargs)))
{
Py_DECREF(reslist);
- reslist = NULL;
- goto exit;
+ return NULL;
}
- for (j = 0; j < n; ++j)
- {
- PyObject * val;
+ PyList_SET_ITEM(reslist, i, rowtuple);
+ }
- if (PQgetisnull(self->result, i, j))
- {
- Py_INCREF(Py_None);
- val = Py_None;
- }
- else /* not null */
- {
- /* get the string representation of the value */
- /* note: this is always null-terminated text
format */
- char *s = PQgetvalue(self->result, i, j);
- /* get the PyGreSQL type of the column */
- int type = col_types[j];
+ /* returns list */
+ return reslist;
+}
- if (type & PYGRES_ARRAY)
- val = cast_array(s,
PQgetlength(self->result, i, j),
- encoding, type, NULL, 0);
- else if (type == PYGRES_BYTEA)
- val = cast_bytea_text(s);
- else if (type == PYGRES_OTHER)
- val = cast_other(s,
- PQgetlength(self->result, i,
j), encoding,
- PQftype(self->result, j),
self->pgcnx->cast_hook);
- else if (type & PYGRES_TEXT)
- val = cast_sized_text(s,
PQgetlength(self->result, i, j),
- encoding, type);
- else
- val = cast_unsized_simple(s, type);
- }
+static PyObject *
+queryDictNext(queryObject *self, PyObject *noargs)
+{
+ PyObject *dict;
+ int j,
+ n;
+ int *col_types;
- if (!val)
- {
- Py_DECREF(reslist);
- Py_DECREF(rowtuple);
- reslist = NULL;
- goto exit;
- }
+ if (self->current_row>=self->max_row) {
+ PyErr_SetNone(PyExc_StopIteration);
+ return NULL;
+ }
- PyTuple_SET_ITEM(rowtuple, j, val);
+ if (self->current_row < 0) {
+ PyErr_SetNone(PyExc_StopIteration); // XXX
+ return NULL;
+ }
+
+ n = PQnfields(self->result);
+ if (!(col_types = get_col_types(self->result, n))) return NULL;
+
+ if (!(dict = PyDict_New()))
+ {
+ goto exit;
+ }
+
+ for (j = 0; j<n; ++j)
+ {
+ PyObject *val;
+ if (NULL == (val = column_value(self, col_types, j)))
+ {
+ Py_DECREF(dict);
+ dict = NULL;
+ goto exit;
}
- PyList_SET_ITEM(reslist, i, rowtuple);
+ PyDict_SetItemString(dict, PQfname(self->result, j), val);
+ Py_DECREF(val);
}
exit:
PyMem_Free(col_types);
+ if (dict) ++self->current_row;
+ return dict;
+}
- /* returns list */
- return reslist;
+/* retrieves next row as a dictionary*/
+static char queryDictIter__doc__ [] =
+"dictnext() -- Return an iterator over query results\n\n"
+"The next row is returned as a dictionary with\n"
+"the field names used as its keys.\n";
+
+static PyObject *
+queryDictIter(PyObject *self, PyObject *noargs)
+{
+ return PyObject_GetIter(self);
}
/* retrieves last result as a list of dictionaries*/
@@ -4688,88 +4831,24 @@
static char queryDictResult__doc__[] =
"dictresult() -- Get the result of a query\n\n"
"The result is returned as a list of rows, each one a dictionary with\n"
-"the field names used as the labels.\n";
+"the field names used as its keys.\n";
static PyObject *
queryDictResult(queryObject *self, PyObject *noargs)
{
PyObject *reslist;
- int i,
- m,
- n,
- *col_types;
- int encoding = self->encoding;
+ int i;
/* stores result in list */
- m = PQntuples(self->result);
- n = PQnfields(self->result);
- if (!(reslist = PyList_New(m))) return NULL;
+ if (!(reslist = PyList_New(self->max_row))) return NULL;
- if (!(col_types = get_col_types(self->result, n))) return NULL;
-
- for (i = 0; i < m; ++i)
+ for (i = 0; i < self->max_row; ++i)
{
PyObject *dict;
- int j;
-
- if (!(dict = PyDict_New()))
- {
- Py_DECREF(reslist);
- reslist = NULL;
- goto exit;
- }
-
- for (j = 0; j < n; ++j)
- {
- PyObject * val;
-
- if (PQgetisnull(self->result, i, j))
- {
- Py_INCREF(Py_None);
- val = Py_None;
- }
- else /* not null */
- {
- /* get the string representation of the value */
- /* note: this is always null-terminated text
format */
- char *s = PQgetvalue(self->result, i, j);
- /* get the PyGreSQL type of the column */
- int type = col_types[j];
-
- if (type & PYGRES_ARRAY)
- val = cast_array(s,
PQgetlength(self->result, i, j),
- encoding, type, NULL, 0);
- else if (type == PYGRES_BYTEA)
- val = cast_bytea_text(s);
- else if (type == PYGRES_OTHER)
- val = cast_other(s,
- PQgetlength(self->result, i,
j), encoding,
- PQftype(self->result, j),
self->pgcnx->cast_hook);
- else if (type & PYGRES_TEXT)
- val = cast_sized_text(s,
PQgetlength(self->result, i, j),
- encoding, type);
- else
- val = cast_unsized_simple(s, type);
- }
-
- if (!val)
- {
- Py_DECREF(dict);
- Py_DECREF(reslist);
- reslist = NULL;
- goto exit;
- }
-
- PyDict_SetItemString(dict, PQfname(self->result, j),
val);
- Py_DECREF(val);
- }
-
+ dict=queryDictNext(self, noargs);
PyList_SET_ITEM(reslist, i, dict);
}
-exit:
- PyMem_Free(col_types);
-
/* returns list */
return reslist;
}
@@ -4918,10 +4997,17 @@
/* query object methods */
static struct PyMethodDef queryMethods[] = {
+ {"__len__", (PyCFunction) queryNTuples, METH_NOARGS, // XXX: doesn't
work: TypeError: object of type 'pg.Query' has no len()
+ queryNTuples__doc__},
+ {"move", (PyCFunction) queryMove, METH_VARARGS|METH_KEYWORDS,
+ queryMove__doc__},
{"getresult", (PyCFunction) queryGetResult, METH_NOARGS,
queryGetResult__doc__},
{"dictresult", (PyCFunction) queryDictResult, METH_NOARGS,
queryDictResult__doc__},
+ {"dictIter", (PyCFunction) queryDictIter, METH_NOARGS,
+ queryDictIter__doc__},
+
{"namedresult", (PyCFunction) queryNamedResult, METH_NOARGS,
queryNamedResult__doc__},
{"fieldname", (PyCFunction) queryFieldName, METH_VARARGS,
@@ -4957,17 +5043,18 @@
PyObject_GenericGetAttr, /* tp_getattro */
0, /*
tp_setattro */
0, /*
tp_as_buffer */
- Py_TPFLAGS_DEFAULT, /* tp_flags */
+ Py_TPFLAGS_DEFAULT|Py_TPFLAGS_HAVE_ITER, /*
tp_flags */
0, /*
tp_doc */
0, /*
tp_traverse */
0, /*
tp_clear */
0, /*
tp_richcompare */
0, /*
tp_weaklistoffset */
- 0, /*
tp_iter */
- 0, /*
tp_iternext */
- queryMethods, /* tp_methods */
+ (getiterfunc)queryGetIter, /*
tp_iter */
+ (iternextfunc)queryNext, /*
tp_iternext */
+ queryMethods, /*
tp_methods */
};
+
/* --------------------------------------------------------------------- */
/* MODULE FUNCTIONS */
Index: tests/test_classic_connection.py
===================================================================
--- tests/test_classic_connection.py (revision 901)
+++ tests/test_classic_connection.py (working copy)
@@ -289,6 +289,79 @@
self.assertEqual(r, s)
+class TestDictIteratorQueries(unittest.TestCase):
+ """Test cursor-like iterator around libpq results."""
+
+ def setUp(self):
+ self.c = connect()
+
+ def tearDown(self):
+ self.doCleanups()
+ self.c.close()
+
+ def testIterate(self):
+ r = self.c.query("SELECT i, i*i FROM generate_series(9,99)
i").dictIter()
+ for k,v in r:
+ self.assertEqual(k*k, v)
+ for v in r: # shouldn't happen
+ self.assertEqual(0, 1)
+# TODO: more tests
+
+class TestIteratorQueries(unittest.TestCase):
+ """Test cursor-like iterator around libpq results."""
+
+ def setUp(self):
+ self.c = connect()
+
+ def tearDown(self):
+ self.doCleanups()
+ self.c.close()
+
+ def testIterate(self):
+ r = self.c.query("SELECT * FROM generate_series(9,99) i")
+ for i, v in enumerate(r):
+ self.assertEqual(v[0], 9+i)
+ for i, v in r: # shouldn't happen
+ self.assertEqual(0, 1)
+
+ r.move(0, relative=False)
+ for i, v in enumerate(r):
+ self.assertEqual(v[0], 9+i)
+ r.move(-1)
+ for i, v in enumerate(r):
+ self.assertEqual(v[0], 99)
+
+ def testIteratePeek(self):
+ r = self.c.query("SELECT * FROM generate_series(9,99) i")
+ for i, v in enumerate(r):
+ self.assertEqual(v[0], 9+i)
+ break
+
+ # Peeked at the data, now iterate over it for real...
+ r.move(-1)
+ for i, v in enumerate(r):
+ self.assertEqual(v[0], 9+i)
+
+ def testIterateOverflow(self):
+ r = self.c.query("SELECT * FROM generate_series(9,99) i")
+ self.assertRaises(ValueError, r.move, 99)
+
+ def testIterateOverflow2(self):
+ r = self.c.query("SELECT * FROM generate_series(9,99) i")
+ for i, v in enumerate(r):
+ self.assertEqual(v[0], 9+i)
+ self.assertRaises(ValueError, r.move, 0)
+
+ def testIterateIntOverflow(self):
+ r = self.c.query("SELECT * FROM generate_series(9,99) i")
+ for i, v in enumerate(r):
+ self.assertEqual(v[0], 9+i)
+ self.assertRaisesRegexp(ValueError, 'Integer overflow', r.move,
(1<<31)-1) # this depends on size of machine's "ints" ?
+
+ def testIterateUnderflow(self):
+ r = self.c.query("SELECT * FROM generate_series(9,99) i")
+ self.assertRaises(ValueError, r.move, -1)
+
class TestSimpleQueries(unittest.TestCase):
"""Test simple queries via a basic pg connection."""
_______________________________________________
PyGreSQL mailing list
[email protected]
https://mail.vex.net/mailman/listinfo.cgi/pygresql
