mapping psql type oids to python types - with psycopg2

Question

Here is the story.

I have a bunch of stored procedures and all have their own argument types.
What I am looking to do is to create a bit of a type safety layer in python so I can make sure all values are of the correct type before hitting the database.

Of course I don't want to write up the whole schema again in python, so I thought I could auto generate this info on startup by fetching the argument names and types from the database.

So I proceed to hack up this query just for testing

SELECT proname, proargnames, proargtypes 
FROM pg_catalog.pg_namespace n
JOIN pg_catalog.pg_proc p
ON pronamespace = n.oid
WHERE nspname = 'public';

Then I run it from python and for 'proargtypes' I get a string like this for each result

'1043 23 1043'

My keen eye tells me these are the oids for the postgresql types, seperated by space, and this particular string means the function accepts varchar,integer,varchar. So in python speak, this should be

(unicode, int, unicode)

Now how can I get the python types from these numbers?

The ideal end result would be something like this

In [129]: get_python_type(23)
Out[129]: int

I've looked all through psycopg2 and the closest I've found is 'extensions.string_types' but that just maps oids to sql type names.

Answer 1

If you want the Python type classes, like you might get from a SQLALchemy column object, you'll need to build and maintain your own mapping. psycopg2 doesn't have one, even internally.

But if what you want is a way to get from an oid to a function that will convert raw values into Python instances, psycopg2.extensions.string_types is actually already what you need. It might look like it's just a mapping from oid to a name, but that's not quite true: its values aren't strings, they're instances of psycopg2._psycopg.type. Time to delve into a little code.

psycopg2 exposes an API for registering new type converters which we can use to trace back into the C code involved with typecasts; this centers around the typecastObject in typecast.c, which, unsurprisingly, maps to the psycopg2._psycopg.type we find in our old friend string_types. This object contains pointers to two functions, pcast (for Python casting function) and ccast (for C casting function), which would seem like what we want— just pick whichever one exists and call it, problem solved. Except they're not among the attributes exposed (name, which is just a label, and values, which is a list of oids). What the type does expose to Python is __call__, which, it turns out, just chooses between pcast and ccast for us. The documentation for this method is singularly unhelpful, but looking at the C code further shows that it takes two arguments: a string containing the raw value, and a cursor object.

>>> import psycopg2.extensions
>>> cur = something_that_gets_a_cursor_object()
>>> psycopg2.extensions.string_types[23]
<psycopg2._psycopg.type 'INTEGER' at 0xDEADBEEF>
>>> psycopg2.extensions.string_types[23]('100', cur)
100
>>> psycopg2.extensions.string_types[23]('10.0', cur)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ValueError: invalid literal for int() with base 10: '10.0'
>>> string_types[1114]
<psycopg2._psycopg.type 'DATETIME' at 0xDEADBEEF>
>>> string_types[1114]('2018-11-15 21:35:21', cur)
datetime.datetime(2018, 11, 15, 21, 35, 21)

The need for a cursor is unfortunate, and in fact, a cursor isn't always required:

>>> string_types[23]('100', None)
100

But anything having to with converting actual string (varchar, for example) types is dependent on the PostgreSQL server's locale, at least in a Python 3 compilation, and passing None to those casters doesn't just fail— it segfaults. The method you mention, cursor.cast(oid, raw), is essentially a wrapper around the casters in psycopg2.extensions.string_types and may be more convenient in some instances.

The only workaround for needing a cursor and connection that I can think of would be to build essentially a mock connection object. If it exposed all of the relevant environment information without connecting to an actual database, it could be attached to a cursor object and used with string_types[oid](raw, cur) or with cur.cast(oid, raw), but the mock would have be built in C and is left as an exercise to the reader.

Answer 2

The mapping of postgres types and python types is given here. Does that help?

Edit: When you read a record from a table, the postgres (or any database) driver will automatically map the record column types to Python types.

cur = con.cursor()
cur.execute("SELECT * FROM Writers")

row = cur.fetchone()

for index, val in enumerate(row):
    print "column {0} value {1} of type {2}".format(index, val, type(val))

Now, you just have to map Python types to MySQL types while writing your MySQL interface code. But, frankly, this is a roundabout way of mapping types from PostgreSQL types to MySQL types. I would just refer one of the numerous type mappings between these two databases like this