CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
kslnet
kslnet

Reputation: 574

SQLAlchemy with postgresql produces error "Could not locate column in row for column 'attype'"

I'm using SQLAlchemy to connect to a postgresql database. I do:

>>> my_engine = create_engine("postgresql://username@localhost:5432/dbname")
>>> my_metadata = MetaData(bind = my_engine)

(There's no password on the database.) These go through without any problems, but then I can't reflect the tables:

>>> my_metadata.reflect()

and get the error:

sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for column 'attype'"

What is causing this and how do I fix it? It looks like something on the schema level but I can't figure it out. I can connect to a MySQL database and reflect the tables without any problems.

EDIT: SqlAlchemy 0.9.2, psql server 9.2.5, psql client 8.4.13, psycopg2 2.5.1

EDIT: With echo=True

INFO:root:Creating music_engine postgresql://postgres@localhost/musicbrainz_db
INFO:sqlalchemy.engine.base.Engine:select version()
INFO:sqlalchemy.engine.base.Engine:{}
INFO:sqlalchemy.engine.base.Engine:select current_schema()
INFO:sqlalchemy.engine.base.Engine:{}
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:{}
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:{}
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:{}
INFO:sqlalchemy.engine.base.Engine:SELECT relname FROM pg_class c WHERE relkind = 'r' AND 'echonest' = (select nspname from pg_namespace n where n.oid = c.re
lnamespace)
INFO:sqlalchemy.engine.base.Engine:{}
INFO:sqlalchemy.engine.base.Engine:
            SELECT c.oid
            FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE (pg_catalog.pg_table_is_visible(c.oid))
            AND c.relname = %(table_name)s AND c.relkind in ('r','v')

INFO:sqlalchemy.engine.base.Engine:{'table_name': u'status'}
INFO:sqlalchemy.engine.base.Engine:
            SELECT a.attname,
              pg_catalog.format_type(a.atttypid, a.atttypmod),
              (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid)
                FROM pg_catalog.pg_attrdef d
               WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum
               AND a.atthasdef)
              AS DEFAULT,
              a.attnotnull, a.attnum, a.attrelid as table_oid
            FROM pg_catalog.pg_attribute a
            WHERE a.attrelid = %(table_oid)s
            AND a.attnum > 0 AND NOT a.attisdropped
            ORDER BY a.attnum

INFO:sqlalchemy.engine.base.Engine:{'table_oid': 19271}
INFO:sqlalchemy.engine.base.Engine:
            SELECT t.typname as "name",
               pg_catalog.format_type(t.typbasetype, t.typtypmod) as "attype",
               not t.typnotnull as "nullable",
               t.typdefault as "default",
               pg_catalog.pg_type_is_visible(t.oid) as "visible",
               n.nspname as "schema"
            FROM pg_catalog.pg_type t
               LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
            WHERE t.typtype = 'd'

INFO:sqlalchemy.engine.base.Engine:{}

Upvotes: 3

Views: 2487

Answers (1)

kslnet
kslnet

Reputation: 574

The problem was indeed related to versions, but in Python and psycopg2, not SQLAlchemy or PostgresQL.

I had installed SQLAlchemy in my python2.6 site-packages. I set my PYTHONPATH appropriately, so I figured it didn't matter whether I used python2.6 or python2.7. However, it DID matter because SQLAlchemy would always call psycopg2 for python2.6, and the native part of psycopg2 compiled for python2.6 is incompatible with python2.7.

Upvotes: 1

Related Questions