In 0.9.9, uuid columns are broken with postgresql+psycopg2

[Guido Winkelmann]

Starting with release 0.9.9, inserting a model with a primary key column of type uuid.uuid4, when running against postgresql with psycopg2, will raise this exception:

StatementError: 'UUID' object has no attribute 'replace' (original cause: AttributeError: 'UUID' object has no attribute 'replace')

For a real life example, see here: https://travis-ci.org/pyfarm/pyfarm-master/jobs/53941909

This only happens with postgresql as a backend. MySQL and SQLite are not affected.

Unfortunately, I haven't found time to produce a reduced test case yet.

[Mike Bayer]

this indicates that the as_uuid flag is False, yet you are passing a real Python UUID() object in. That's not the correct use, if you want to pass a UUID() object, the as_uuid flag must be set to True. You may have been relying upon the accidental workings of this earlier.

[Mike Bayer]

here's how to reproduce your case in 0.9.9, passes in 0.9.8 because SQLA isn't aware of the implicit translation going on:

from sqlalchemy import Column, Integer, create_engine
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import UUID
import uuid

# this is no longer needed
from psycopg2 import extras
extras.register_uuid(None, None)


Base = declarative_base()


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)

    # incorrect, will fail in 0.9.9
    data = Column(UUID(as_uuid=False))

    # correct for all versions
    # data = Column(UUID(as_uuid=True))

e = create_engine("postgresql://scott:tiger@localhost/test", echo='debug')
Base.metadata.create_all(e)

s = Session(e)
s.add(A(data=uuid.uuid4()))
s.commit()

[Guido Winkelmann]

Thanks, I fixed it on my end now by supplying as_uuid=True.

[Guido Winkelmann]

I just noticed that setting as_uuid=True will trigger that described bug with 0.9.8.

[Mike Bayer]

what bug?

[Guido Winkelmann]

I meant to say, if I define a column as Column(UUID(as_uuid=True)), the code will work with 0.9.9, but with 0.9.8 will produce the exception shown in the original discription of this issue.

[Mike Bayer]

the script I have above, as is, works in 0.9.8 with as_uuid=True. What would fail is if you leave the psycopg2 extensions turned on, and then do a SELECT. Because, "as_uuid=True" in 0.9.8 is not aware of the psycopg2 extension and assumes its getting strings back.

[Mike Bayer]

I'd probably have made this a 1.0 change if I knew people were using the psycopg2 extension directly, but it's too late now.

[Derek Miller]

Is there a way to set as_uuid=True when using automap?

[Mike Bayer]

@derek-miller so with automap, that's reflection, and you may have stumbled onto an issue here that Postgresql reflection should be setting this flag for you.

There's a way to get this now, let me write a demo:

from sqlalchemy.ext.automap import automap_base
from sqlalchemy import event
from sqlalchemy.schema import Table

@event.listens_for(Table, "column_reflect")
def _set_uuid_flag(inspector, table, column_info):
    if isinstance(column_info['type'], UUID):
        column_info['type'].as_uuid = True

B = automap_base()
B.prepare(e, reflect=True)

A = B.classes.a

s = Session(e)
s.add(A(data=uuid.uuid4()))
s.commit()

we're using the column_reflect event to update the flag as the column comes in.

[Derek Miller]

@zzzeek Works like a charm. Thanks!