pg8000 not working with enums.

[Former user]

Hey guys,

I tried to get enums to work in pg8000 today, but it hates inserting them. Not sure what the deal is here, but here is a code example to reproduce it and the traceback. I tried pulling in the Enum stuff from the psycopg2 dialect, but it fails in the same manner.

cheers. -chris (percious)

Source:

from sqlalchemy import *
from sqlalchemy.orm import *
url = 'postgresql+pg8000://postgres@localhost/test8000'

engine = create_engine(url, encoding='utf-8')

metadata = MetaData(bind=engine)
metadata.drop_all()

metadata = MetaData(bind=engine)
some_table = Table(u'somes', metadata,
    Column(u'some_id', Integer, primary_key=True),
    Column(u'e', Enum('one', 'two', name=u'e'))
)

metadata.create_all()

class Some(object):pass

mapper(Some, some_table)

session = sessionmaker(bind=engine)()

s = Some()
setattr(s, 'e', 'one')

session.add(s)
session.flush()
session.commit()

Traceback:

$ python pg8000test.py 
Traceback (most recent call last):
  File "pg8000test.py", line 33, in <module>
    print session.query(Some).all()
  File "/Users/percious/clients/mvpss/mvp2.1/src/sqlalchemy_trunk/lib/sqlalchemy/orm/query.py", line 1217, in all
    return list(self)
  File "/Users/percious/clients/mvpss/mvp2.1/src/sqlalchemy_trunk/lib/sqlalchemy/orm/query.py", line 1311, in __iter__
    return self._execute_and_instances(context)
  File "/Users/percious/clients/mvpss/mvp2.1/src/sqlalchemy_trunk/lib/sqlalchemy/orm/query.py", line 1316, in _execute_and_instances
    mapper=self._mapper_zero_or_none())
  File "/Users/percious/clients/mvpss/mvp2.1/src/sqlalchemy_trunk/lib/sqlalchemy/orm/session.py", line 739, in execute
    clause, params or {})
  File "/Users/percious/clients/mvpss/mvp2.1/src/sqlalchemy_trunk/lib/sqlalchemy/engine/base.py", line 991, in execute
    return Connection.executors[c](c)(self, object, multiparams, params)
  File "/Users/percious/clients/mvpss/mvp2.1/src/sqlalchemy_trunk/lib/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
    return self.__execute_context(context)
  File "/Users/percious/clients/mvpss/mvp2.1/src/sqlalchemy_trunk/lib/sqlalchemy/engine/base.py", line 1076, in __execute_context
    self._cursor_execute(context.cursor, context.statement, context.parameters[0](0), context=context)
  File "/Users/percious/clients/mvpss/mvp2.1/src/sqlalchemy_trunk/lib/sqlalchemy/engine/base.py", line 1138, in _cursor_execute
    self._handle_dbapi_exception(e, statement, parameters, cursor, context)
  File "/Users/percious/clients/mvpss/mvp2.1/src/sqlalchemy_trunk/lib/sqlalchemy/engine/base.py", line 1136, in _cursor_execute
    self.dialect.do_execute(cursor, statement, parameters, context=context)
  File "/Users/percious/clients/mvpss/mvp2.1/src/sqlalchemy_trunk/lib/sqlalchemy/engine/default.py", line 207, in do_execute
    cursor.execute(statement, parameters)
  File "/Users/percious/clients/mvpss/mvp2.1/src/pg8000/pg8000/dbapi.py", line 243, in _fn
    return fn(self, *args, **kwargs)
  File "/Users/percious/clients/mvpss/mvp2.1/src/pg8000/pg8000/dbapi.py", line 312, in execute
    self._execute(operation, args)
  File "/Users/percious/clients/mvpss/mvp2.1/src/pg8000/pg8000/dbapi.py", line 317, in _execute
    self.cursor.execute(new_query, *new_args)
  File "/Users/percious/clients/mvpss/mvp2.1/src/pg8000/pg8000/interface.py", line 304, in execute
    self._stmt.execute(*args, **kwargs)
  File "/Users/percious/clients/mvpss/mvp2.1/src/pg8000/pg8000/interface.py", line 139, in execute
    self._row_desc, cmd = self.c.bind(self._portal_name, self._statement_name, args, self._parse_row_desc, kwargs.get("stream"))
  File "/Users/percious/clients/mvpss/mvp2.1/src/pg8000/pg8000/protocol.py", line 920, in _fn
    return fn(self, *args, **kwargs)
  File "/Users/percious/clients/mvpss/mvp2.1/src/pg8000/pg8000/protocol.py", line 1107, in bind
    output_fc = [self._record_field_names) for f in row_desc.fields](types.py_type_info(f,)
  File "/Users/percious/clients/mvpss/mvp2.1/src/pg8000/pg8000/types.py", line 166, in py_type_info
    raise NotSupportedError("type oid %r not mapped to py type" % type_oid)
sqlalchemy.exc.NotSupportedError: (NotSupportedError) type oid 2015924 not mapped to py type u'SELECT somes.some_id AS somes_some_id, somes.e AS somes_e \nFROM somes' [percious$ python pg8000test.py 
Traceback (most recent call last):
  File "pg8000test.py", line 28, in <module>
    session.flush()
  File "/Users/percious/clients/mvpss/mvp2.1/src/sqlalchemy_trunk/lib/sqlalchemy/orm/session.py", line 1307, in flush
    self._flush(objects)
  File "/Users/percious/clients/mvpss/mvp2.1/src/sqlalchemy_trunk/lib/sqlalchemy/orm/session.py", line 1385, in _flush
    flush_context.execute()
  File "/Users/percious/clients/mvpss/mvp2.1/src/sqlalchemy_trunk/lib/sqlalchemy/orm/unitofwork.py", line 261, in execute
    UOWExecutor().execute(self, tasks)
  File "/Users/percious/clients/mvpss/mvp2.1/src/sqlalchemy_trunk/lib/sqlalchemy/orm/unitofwork.py", line 753, in execute
    self.execute_save_steps(trans, task)
  File "/Users/percious/clients/mvpss/mvp2.1/src/sqlalchemy_trunk/lib/sqlalchemy/orm/unitofwork.py", line 768, in execute_save_steps
    self.save_objects(trans, task)
  File "/Users/percious/clients/mvpss/mvp2.1/src/sqlalchemy_trunk/lib/sqlalchemy/orm/unitofwork.py", line 759, in save_objects
    task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
  File "/Users/percious/clients/mvpss/mvp2.1/src/sqlalchemy_trunk/lib/sqlalchemy/orm/mapper.py", line 1420, in _save_obj
    c = connection.execute(statement.values(value_params), params)
  File "/Users/percious/clients/mvpss/mvp2.1/src/sqlalchemy_trunk/lib/sqlalchemy/engine/base.py", line 991, in execute
    return Connection.executors[c](]
(mvp2.1)percious-new-macbook:src)(self, object, multiparams, params)
  File "/Users/percious/clients/mvpss/mvp2.1/src/sqlalchemy_trunk/lib/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
    return self.__execute_context(context)
  File "/Users/percious/clients/mvpss/mvp2.1/src/sqlalchemy_trunk/lib/sqlalchemy/engine/base.py", line 1076, in __execute_context
    self._cursor_execute(context.cursor, context.statement, context.parameters[0](0), context=context)
  File "/Users/percious/clients/mvpss/mvp2.1/src/sqlalchemy_trunk/lib/sqlalchemy/engine/base.py", line 1138, in _cursor_execute
    self._handle_dbapi_exception(e, statement, parameters, cursor, context)
  File "/Users/percious/clients/mvpss/mvp2.1/src/sqlalchemy_trunk/lib/sqlalchemy/engine/base.py", line 1136, in _cursor_execute
    self.dialect.do_execute(cursor, statement, parameters, context=context)
  File "/Users/percious/clients/mvpss/mvp2.1/src/sqlalchemy_trunk/lib/sqlalchemy/engine/default.py", line 207, in do_execute
    cursor.execute(statement, parameters)
  File "/Users/percious/clients/mvpss/mvp2.1/src/pg8000/pg8000/dbapi.py", line 243, in _fn
    return fn(self, *args, **kwargs)
  File "/Users/percious/clients/mvpss/mvp2.1/src/pg8000/pg8000/dbapi.py", line 312, in execute
    self._execute(operation, args)
  File "/Users/percious/clients/mvpss/mvp2.1/src/pg8000/pg8000/dbapi.py", line 317, in _execute
    self.cursor.execute(new_query, *new_args)
  File "/Users/percious/clients/mvpss/mvp2.1/src/pg8000/pg8000/interface.py", line 303, in execute
    self._stmt = PreparedStatement(self.connection, query, statement_name="", *[type(x), "value": x} for x in args]({"type":))
  File "/Users/percious/clients/mvpss/mvp2.1/src/pg8000/pg8000/interface.py", line 108, in __init__
    self._parse_row_desc = self.c.parse(self._statement_name, statement, types)
  File "/Users/percious/clients/mvpss/mvp2.1/src/pg8000/pg8000/protocol.py", line 920, in _fn
    return fn(self, *args, **kwargs)
  File "/Users/percious/clients/mvpss/mvp2.1/src/pg8000/pg8000/protocol.py", line 1094, in parse
    return reader.handle_messages()
  File "/Users/percious/clients/mvpss/mvp2.1/src/pg8000/pg8000/protocol.py", line 906, in handle_messages
    raise exc
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('ERROR', '42804', 'column "e" is of type e but expression is of type text') u'INSERT INTO somes (e) VALUES (%s) RETURNING somes.some_id' ['one']('one')

[Former user]

reference this pg8000 ticket: http://github.com/mfenniak/pg8000/issues/#issue/2

[Mike Bayer]

are you saying that ENUM doesn't work with pyscopg2 ? ENUM is known not to work with pg8000 as of yet.

Also here is the actual ticket for pg8000, they are on launchpad:

https://bugs.launchpad.net/pg8000/+bug/412000

I would also suggest reporting pg8000 bugs using pure pg8000 code, which eliminates the possibility that SQLA's implementation is wrong.

[Former user]

oh no, enums work awesome with psycopg2. I'm just excited about pg8000 and starting to try it out on some of my clients' apps. Already have it going on one of them. I am pretty amazed that you have known about this for a month tho mike.

cheers. -chris

[Mike Bayer]

this is a pg8000 issue.

[Tony Locke]

This PG8000 bug is recorded at https://github.com/mfenniak/pg8000/issues/2. I've put up some code for adding enum support to PG8000 at https://github.com/tlocke/pg8000/tree/enum. This solution does need a cast, so the SQL that SA emits would need to look like:

INSERT INTO somes (e) VALUES (cast(%s as e)) RETURNING somes.some_id' ['one']('one')

Would that work?

[Mike Bayer]

that is doable now that we support column_expression and bind_expression: http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#types-sql-value-processing - what's "e" above, is that a CAST using a column name for the type?

[Tony Locke]

I've actually committed a fix to PG8000 that doesn't require any casting in the SQL https://github.com/mfenniak/pg8000/commit/41362eed49a271da05a079b953f7b513ad0483e2. So no change needed to SQLAlchemy!

Btw, to answer your question above about the 'e', I was following the @percious example which confusingly gave the same name 'e' to the column and the enum type:

    Column(u'e', Enum('one', 'two', name=u'e'))

Anyway, that's by the by.

[Mike Bayer]

great. not sure if you're aware, I created a fork of pg8000 that made extensive changes to the internals in the efforts of lots more speed and also extension capability. It was specific to a database I was contracted to work with called Akiban, but the thrust of the internal changes was to do away with most of the internal object creation pg8000 uses in order to represent postgres structures. I had emailed Mathieu about it but he did not respond. the fork is at https://github.com/zzzeek/pg8000/ , examples of major reductions in object creation can be seen at https://github.com/zzzeek/pg8000/commit/fffc22a5b92d849a23df8f46d1c54d3e98e9fba4, https://github.com/zzzeek/pg8000/commit/bc9127429b1a9df192d782198aee16488da0962f and https://github.com/zzzeek/pg8000/commit/b59841124f474fe14c69f7ff671e98f814447717 where I tackle that huge parameter formatting function, it looks like you might have addressed this as well. I have it supporting just the two formats that psycopg2 does instead of all of them.

[Tony Locke]

This is interesting. Looking at https://github.com/zzzeek/pg8000/compare/mfenniak:trunk...trunk it seems that @mfenniak did accept all your changes. I checked, and all your work is in the HEAD of 'trunk' on the mfenniak repo.

I too have been working on speeding up PG8000, but all of my stuff has been on the Python 3 version. The two branches were very different when I started, and now they've diverged even more. It's a shame that the two versions have divided our efforts. I've not sure what the accepted strategy is for maintaining Python 2 and 3 libraries, do you have any views?

Having worked on the speed for py3, I'm now focussing on fixing bugs. With bugs I've taken the approach of doing the fix to both trunk and py3 branches.

Btw, I had a similar thought to you about simplifying the paramstyles. I've put my suggestion for DBAPI 3 at:

https://github.com/tlocke/pg8000/tree/three

It removes the 'paramstyle' attribute entirely and just has parameter labels as :1, :2, :3, ... or the :name style. If it can cast the label to an int, it assumes the parameters argument is a sequence, otherwise it assumes it's a mapping. What do you think?

[Mike Bayer]

Hm I guess he merged it after I had done the extra work? I might have still been getting used to pull requests at that point.

for Py2/3, the only rational choice is an in-place codebase. all of my major libs/tools are now py2k/3k in place, no 2to3 needed - alembic, dogpile, mako, sqlalchemy as of 0.9. mako in fact supports from py2.4 all the way to py3.4, and is very heavy on unicode as well as python interpreter internals even. It's not very hard to make a pure-python codebase support between 2 and 3.

the two paramstyles I think are important, and the ones we've decided on the DB-SIG list would be the standard in a DBAPI 3, are qmark and named. These two formats are commonplace outside of the Python world.

my next choice would be format and pyformat. I'm not too keen on numbered since it is just another named style, just more awkward.

[Mike Bayer]

Removing milestone: 0.6.xx (automated comment)