getting recarray into postgres using psycopg2

Question

Just would like to get a quick idea on the best, meaning least coding, way to get lots of data in recarray into postgres using psycopg2. I have seen some stuff using cast but really I thought it would be strait forward and I could find something good on the web.

Example have census data with 200 variables read in recarray with different data types for many columns. I want to just sweep through using column names and data types and input into postgres.

Also if there is anything better than psycopy2 I am open to suggestions.

This is what I found although it goes into sqlight and the wrong way.

elif driver=='sqlite3':
                    tups=cur.fetchall()
                    if len(tups)>0:
                            _cast = {types.BooleanType: numpy.bool,
                                    types.IntType: numpy.int32,
                                    types.LongType: numpy.int64,
                                    types.FloatType: numpy.float64,
                                    types.StringType: numpy.str,
                                    types.UnicodeType: numpy.str}
                            try:
                                    typelist=[_cast[type(tmp)] for tmp in tups[0]]
                            except KeyError:
                                    raise Exception("Unknown datatype")
                            res = numpy.core.records.array(tups)
                    else:
                            return None
            res=[res[tmp] for tmp in res.dtype.names]
    except BaseException:

score 2 · Answer 1 · answered May 11 '11 at 14:06

Psycopg can be extended using a new adapter (when you want to pass a Python object to Postgres) or a new typecaster (when you want to read from Postgres and get Python objects).

It looks like you have to register some of the existing adapters for the numpy types, for instance:

>>> psycopg2.extensions.register_adapter(numpy.int32, psycopg2._psycopg.AsIs)
>>> psycopg2.extensions.adapt(numpy.int32(42)).getquoted()
'42'

>>> psycopg2.extensions.register_adapter(numpy.str, psycopg2._psycopg.QuotedString)
>>> psycopg2.extensions.adapt(numpy.str("Hi 'quote'")).getquoted()
"'Hi ''quote'''"

With these registrations you can use numpy types directly as query parameters.

You may get more details and help on the Mailing List.

getting recarray into postgres using psycopg2

1 Answers1