On Fri, Jun 07, 2019 at 09:06:26PM +0200, Christoph Zwerschke wrote:
> >pgdb has
> >|Cursor.coltypes
> >| The list of columns types of the current result set
> >|The values in this list are the same values as the type_code elements in
> >the Cursor.description attribute. Always use the latter if you want to
> >remain standard compliant.
> >
> >But I was surprised to find that pg doesn't expose anything similar. I
> >wondered whether we shouldn't add interfaces for PQftype and maybe PQfmod,
> >pehaps by exposing query_obj->col_types.
>
> Yes, that's really lacking. The interface has grown over time and I think
> originally, the focus was on the entity methods like get() and insert() and
> update().
> You would get the types via attnames().
I'm not sure if I understood the last sentence. Do you mean the types of
column in a table ? That's not the same as types of result columns, though.
It could be computed from columns of different types in multiple tables like
SELECT 1.0*a.incol1/b.intcol2, or not tied to table at all.
In the immediate case, I discovered/realized that our server application is
sending a list of types to the client, along with the result (which uses
iteration as of last month). In order to find the type of a column, it's
iterating over rows until it finds a non-null value, and testing if its type()
in [int,float,long]. However our reports might have many all-null columns (for
features the customer doesn't have enabled). So it's iterating over 1e6 rows a
few hundred times. An 7min query was being followed by 15min of looping over
iterable. I mitigated it for now by making it loop over rows first rather than
column, so we're calling into pygres to build each row once rather than once
per all-null row. It'd be much nicer if we could call ftype() and avoid
looping over rows for this at all :)
I threw this together, I guess we should do a layer of processing, like what's
done by pgdb.cursor._make_description.
Justin
Index: pgquery.c
===================================================================
--- pgquery.c (revision 1017)
+++ pgquery.c (working copy)
@@ -239,25 +273,59 @@
return PyInt_FromLong(num);
}
+/* Get field number from name in last result. */
+static char query_fieldinfo__doc__[] =
+"fieldinfo(name) -- return information about query result";
+
+/* Return information on field types */
+static PyObject *query_fieldinfo(PyObject *self, void *closure)
+{
+ queryObject *q=(queryObject *)self;
+ PyObject *result;
+ int i;
+
+ if (!(result = PyTuple_New(q->num_fields)))
+ return NULL;
+
+ for (i = 0; i < q->num_fields; ++i) {
+ PyObject *tup = PyTuple_New(4);
+ if (!tup)
+ return NULL;
+
+ PyTuple_SET_ITEM(result, i, tup);
+ // PyTuple_SET_ITEM(tup, 0, PyInt_FromLong(i));
+ PyTuple_SET_ITEM(tup, 0,
+ PyStr_FromString(PQfname(q->result, i)));
+ PyTuple_SET_ITEM(tup, 1,
+ PyInt_FromLong(PQftype(q->result, i)));
+ PyTuple_SET_ITEM(tup, 2,
+ PyInt_FromLong(PQfsize(q->result, i)));
+ PyTuple_SET_ITEM(tup, 3,
+ PyInt_FromLong(PQfmod(q->result, i)));
+ }
+
+ /* returns result */
+ return result;
+}
+
/* Retrieve one row from the result as a tuple. */
static char query_one__doc__[] =
"one() -- Get one row from the result of a query\n\n"
"Only one row from the result is returned as a tuple of fields.\n"
"This method can be called multiple times to return more rows.\n"
-"It returns None if the result does not contain one more row.\n";
+"It returns None if the result does not contain any more rows.\n";
static PyObject *
query_one(queryObject *self, PyObject *noargs)
{
@@ -265,14 +333,13 @@
"single() -- Get the result of a query as single row\n\n"
"The single row from the query result is returned as a tuple of fields.\n"
"This method returns the same single row when called multiple times.\n"
-"It raises an InvalidResultError if the result doesn't have exactly one row,\n"
-"which will be of type NoResultError or MultipleResultsError specifically.\n";
+"It raises an Exception if the result doesn't have exactly one row.\n"
+"The exception will be a subclass of InvalidResultError; specifically, either\n"
+"NoResultError or MultipleResultsError.\n";
static PyObject *
query_single(queryObject *self, PyObject *noargs)
{
/* Query sequence protocol methods */
@@ -707,6 +772,8 @@
METH_VARARGS, query_fieldnum__doc__},
{"listfields", (PyCFunction) query_listfields,
METH_NOARGS, query_listfields__doc__},
+ {"fieldinfo", (PyCFunction) query_fieldinfo,
+ METH_NOARGS, query_fieldinfo__doc__},
{"ntuples", (PyCFunction) query_ntuples,
METH_NOARGS, query_ntuples__doc__},
{NULL, NULL}
@@ -714,6 +781,11 @@
static char query__doc__[] = "PyGreSQL query object";
+static struct PyGetSetDef getset[]={
+ {"description", query_fieldinfo, NULL, query_fieldinfo__doc__, NULL, },
+ {NULL,}
+};
+
/* Query type definition */
static PyTypeObject queryType = {
PyVarObject_HEAD_INIT(NULL, 0)
@@ -746,4 +818,6 @@
(getiterfunc) query_iter, /* tp_iter */
(iternextfunc) query_next, /* tp_iternext */
query_methods, /* tp_methods */
+ 0, /* tp_members */
+ getset, /* tp_getset */
};
_______________________________________________
PyGreSQL mailing list
PyGreSQL@Vex.Net
https://mail.vex.net/mailman/listinfo/pygresql
