On Fri, Jul 19, 2019 at 07:44:21PM -0500, Justin Pryzby wrote:
> 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().
>
> I threw this together, I guess we should do a layer of processing, like what's
> done by pgdb.cursor._make_description.
Should the .description property exactly match the pgdb one ?
Or maybe it should (also?) return the raw, unmanipulated values from libpq ?
I don't know if we can return a namedtuple or highlevel type easily, since it
has to be a method of C query_obj, and not of DB class.
Actually, query_obj.description is good enough for our purposes...
I guess DbTypes should also include size.
PYTHONPATH=./build/lib.linux-x86_64-2.7/ python2.7 -c "import pg;
d=pg.DB('postgres'); q=d.query('SELECT 1 a'); print q.description; x={i[0]:
d.dbtypes.get(i[1]) for i in q.description}; print [ (k,v.__dict__) for k,v in
x.items()]"
(('a', 23, 4, -1),)
[('a', {'typtype': 'b', 'category': 'N', 'relid': 0, '_get_attnames': <bound
method DbTypes.get_attnames of {'int4': 'int', 23: 'int'}>, 'simple': 'int',
'pgtype': 'int4', 'typlen': 4, 'oid': 23, 'delim': ',', 'regtype': 'integer'})]
That doesn't resolve the question of how to expose typmod.
Also, if it were me, I think I'd populate _dbtypes in bulk, using SELECT * FROM
pg_types, and "refresh" it (possibly in bulk) in __missing__().
Please pardon my hand-edited diff. I have a month old copy of last year's
attempt to use server-side cursors. I'll hope to write about this soon..
Justin
Index: docs/contents/pg/db_types.rst
===================================================================
--- docs/contents/pg/db_types.rst (revision 1017)
+++ docs/contents/pg/db_types.rst (working copy)
@@ -13,9 +13,9 @@
These type names are strings which are equal to either the simple PyGreSQL
names or to the more fine-grained registered PostgreSQL type names if these
-have been enabled with :meth:`DB.use_regtypes`. Besides being strings, they
-carry additional information about the associated PostgreSQL type in the
-following attributes:
+have been enabled with :meth:`DB.use_regtypes`. Type names extend the string
+class with additional information about the associated PostgreSQL type in
+the following attributes:
- *oid* -- the PostgreSQL type OID
- *pgtype* -- the internal PostgreSQL data type name
@@ -26,6 +26,7 @@
- *delim* -- delimiter for array types
- *relid* -- corresponding table for composite types
- *attnames* -- attributes for composite types
+ - *typlen* -- a positive value for length in bytes, or -1 for a varlena value or -2 for a null-terminated C string (since 5.2)
For details, see the PostgreSQL documentation on `pg_type
<http://www.postgresql.org/docs/current/static/catalog-pg-type.html>`_.
Index: pg.py
===================================================================
--- pg.py (revision 1017)
+++ pg.py (working copy)
@@ -1160,6 +1160,7 @@
delim: delimiter for array types
relid: corresponding table for composite types
attnames: attributes for composite types
+ typlen: positive number of bytes or negative indicating varlena or null-terminated
"""
@property
@@ -1190,16 +1191,16 @@
# older remote databases (not officially supported)
self._query_pg_type = (
"SELECT oid, typname, typname::text::regtype,"
- " typtype, null as typcategory, typdelim, typrelid"
+ " typtype, null as typcategory, typdelim, typrelid, typlen"
" FROM pg_type WHERE oid=%s::regtype")
else:
self._query_pg_type = (
"SELECT oid, typname, typname::regtype,"
- " typtype, typcategory, typdelim, typrelid"
+ " typtype, typcategory, typdelim, typrelid, typlen"
" FROM pg_type WHERE oid=%s::regtype")
def add(self, oid, pgtype, regtype,
- typtype, category, delim, relid):
+ typtype, category, delim, relid, typlen):
"""Create a PostgreSQL type name with additional info."""
if oid in self:
return self[oid]
@@ -1213,15 +1214,20 @@
typ.category = category
typ.delim = delim
typ.relid = relid
+ typ.typlen = typlen
typ._get_attnames = self.get_attnames
return typ
def __missing__(self, key):
"""Get the type info from the database if it is not cached."""
+ print('looking up type',key)
try:
q = self._query_pg_type % (_quote_if_unqualified('$1', key),)
res = self._db.query(q, (key,)).getresult()
except ProgrammingError:
+ # Ideally we would not catch prg_error, and instead use
+ # to_regtype() rather than ::regtype, but it's only been around
+ # since PG9.4.
res = None
if not res:
raise KeyError('Type %s could not be found' % key)
@@ -1588,6 +1594,9 @@
attrs.update(dir(self.db))
return sorted(attrs)
+ def __str__(self):
+ return 'DB'
+
# Context manager methods
def __enter__(self):
Index: pgquery.c
===================================================================
--- pgquery.c (revision 1017)
+++ pgquery.c (working copy)
@@ -217,6 +250,7 @@
static char query_fieldnum__doc__[] =
"fieldnum(name) -- return position in query for field from its name";
+// Retire these or rewrite based on fieldinfo ?
static PyObject *
query_fieldnum(queryObject *self, PyObject *args)
{
@@ -239,25 +273,61 @@
return PyInt_FromLong(num);
}
+/* Get field info for last result. */
+static char query_fieldinfo__doc__[] =
+"fieldinfo(name) -- return information about query result";
+
+/* Return information on field types */
+/* XXX: this should either be raw values from libpq, or should match pgdb.description, but should not be ambiguous! */
+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;
+
+// XXX: type = self->col_types[column];
+ 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 +335,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)
{
@@ -707,6 +774,7 @@
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 +782,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 +819,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
