psycopg2 dialect _python_UUID should accept UUID objects

[Mikko Ohtamaa]

PostgreSQL psycopg2 driver gets confused if it's handling an UUID object or a string. It can crash the query as demostrated below:

import sqlalchemy as sa
import sqlalchemy.dialects.postgresql as psql
from sqlalchemy import orm
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()


class User(Base):
    __tablename__ = "users"
    id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)


class UserCustomer(Base):

    __table__ = sa.Table('user_customer', Base.metadata,

        sa.Column('user_id',
                  sa.Integer,
                  sa.ForeignKey("users.id"),
                  primary_key=True),

        sa.Column('customer_id',
                  psql.UUID,
                  sa.ForeignKey("customer.id"),
                  primary_key=True)
    )

class Customer(Base):

    __tablename__ = "customer"

    #: Our id
    id = sa.Column(psql.UUID(as_uuid=True), primary_key=True, server_default=sa.text("uuid_generate_v4()"))

    users = orm.relationship(User,
                             secondary=UserCustomer.__table__,
                             backref=orm.backref("customers", lazy="dynamic"),
                             lazy="dynamic",
                             )


# Need to run  CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; on this
engine = sa.create_engine('postgresql://localhost/mydatabase')
Base.metadata.create_all(engine)

# create a configured "Session" class
Session = sessionmaker(bind=engine)

# create a Session
dbsession = Session()

u = User()
dbsession.add(u)
dbsession.flush()

c = Customer()
c.users.append(u)
dbsession.add(c)
dbsession.flush()

# sqlalchemy.exc.StatementError: (builtins.AttributeError) 'UUID' object has no attribute 'replace' [SQL: 'INSERT INTO user_customer (user_id, customer_id) VALUES (%(user_id)s, %(customer_id)s)'] [parameters: [{'customer_id': UUID('4b663bf0-75fe-43fe-ad35-095b0365fee5'), 'user_id': 1}]]

Specifically, psycopg2 passes around UUID object when it expects a string. I am not sure if it should pass UUID object or string in the first place. Full traceback:

Traceback (most recent call last):
  File "sabug.py", line 69, in <module>
    dbsession.flush()
  File "/Users/mikko/code/xxx/venv/src/sqlalchemy/lib/sqlalchemy/orm/session.py", line 2065, in flush
    self._flush(objects)
  File "/Users/mikko/code/xxx/venv/src/sqlalchemy/lib/sqlalchemy/orm/session.py", line 2183, in _flush
    transaction.rollback(_capture_exception=True)
  File "/Users/mikko/code/xxx/venv/src/sqlalchemy/lib/sqlalchemy/util/langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/Users/mikko/code/xxx/venv/src/sqlalchemy/lib/sqlalchemy/util/compat.py", line 186, in reraise
    raise value
  File "/Users/mikko/code/xxx/venv/src/sqlalchemy/lib/sqlalchemy/orm/session.py", line 2147, in _flush
    flush_context.execute()
  File "/Users/mikko/code/xxx/venv/src/sqlalchemy/lib/sqlalchemy/orm/unitofwork.py", line 386, in execute
    rec.execute(self)
  File "/Users/mikko/code/xxx/venv/src/sqlalchemy/lib/sqlalchemy/orm/unitofwork.py", line 500, in execute
    self.dependency_processor.process_saves(uow, states)
  File "/Users/mikko/code/xxx/venv/src/sqlalchemy/lib/sqlalchemy/orm/dependency.py", line 1092, in process_saves
    secondary_update, secondary_delete)
  File "/Users/mikko/code/xxx/venv/src/sqlalchemy/lib/sqlalchemy/orm/dependency.py", line 1136, in _run_crud
    connection.execute(statement, secondary_insert)
  File "/Users/mikko/code/xxx/venv/src/sqlalchemy/lib/sqlalchemy/engine/base.py", line 947, in execute
    return meth(self, multiparams, params)
  File "/Users/mikko/code/xxx/venv/src/sqlalchemy/lib/sqlalchemy/sql/elements.py", line 262, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/Users/mikko/code/xxx/venv/src/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1055, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/Users/mikko/code/xxx/venv/src/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1123, in _execute_context
    None, None)
  File "/Users/mikko/code/xxx/venv/src/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1386, in _handle_dbapi_exception
    exc_info
  File "/Users/mikko/code/xxx/venv/src/sqlalchemy/lib/sqlalchemy/util/compat.py", line 202, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/Users/mikko/code/xxx/venv/src/sqlalchemy/lib/sqlalchemy/util/compat.py", line 185, in reraise
    raise value.with_traceback(tb)
  File "/Users/mikko/code/xxx/venv/src/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1118, in _execute_context
    context = constructor(dialect, self, conn, *args)
  File "/Users/mikko/code/xxx/venv/src/sqlalchemy/lib/sqlalchemy/engine/default.py", line 640, in _init_compiled
    for key in compiled_params
  File "/Users/mikko/code/xxx/venv/src/sqlalchemy/lib/sqlalchemy/engine/default.py", line 640, in <genexpr>
    for key in compiled_params
  File "/Users/mikko/code/xxx/venv/src/sqlalchemy/lib/sqlalchemy/dialects/postgresql/psycopg2.py", line 414, in process
    value = _python_UUID(value)
  File "/usr/local/Cellar/python3/3.5.0/Frameworks/Python.framework/Versions/3.5/lib/python3.5/uuid.py", line 134, in __init__
    hex = hex.replace('urn:', '').replace('uuid:', '')
sqlalchemy.exc.StatementError: (builtins.AttributeError) 'UUID' object has no attribute 'replace' [SQL: 'INSERT INTO user_customer (user_id, customer_id) VALUES (%(user_id)s, %(customer_id)s)'] [parameters: [{'customer_id': UUID('4b663bf0-75fe-43fe-ad35-095b0365fee5'), 'user_id': 1}]]

A workaround is a monkey patch: from uuid import UUID

def fixed_python_UUID(uuid):
    if isinstance(uuid, UUID):
        # Already objecfied
        return uuid
    else:
        # String-like
        return UUID(uuid)


def monkey_patch():
    from sqlalchemy.dialects.postgresql import psycopg2
    psycopg2._python_UUID = fixed_python_UUID

monkey_patch()

[Mike Bayer]

this mapping isn't valid because you are creating a relationship between two columns that are of essentially different types. the ORM doesn't know how to write between these two types.

Ideally, the relationship here could be declared as:

 users = orm.relationship(User,
                         secondary=UserCustomer.__table__,
                         secondaryjoin=lambda: sa.type_coerce(
                              orm.remote(orm.foreign(UserCustomer.customer_id)),
                              psql.UUID(as_uuid=True)) == Customer.id,
                         backref=orm.backref("customers", lazy="dynamic"),
                         lazy="dynamic",
                         )

but the "secondary" relationship is not working with that specific config right now (that can be fixed).

The "instance()" approach is not acceptable here because the "as_uuid" flag is specifically for type validation and we also avoid expensive "isinstance()" checks in type processors as much as possible. You are free to build a UUID type that accepts both strings and UUIDs using isinstance() using TypeDecorator.

Also, it's a bad idea to use a mapped class as the target of "secondary" without specifying viewonly=True; otherwise if you are inserting UserCustomer instances and also appending to Customer.users, you will get conflicting inserts.

[Mike Bayer]

this is the expected behavior when one mixes the UUID type that's converting to uuid with a UUID type thats converting to string.

[Mike Bayer]

really, the UserCustomer table shouldn't even specify the type, it's a ForeignKey so that's picked up from the target:

class UserCustomer(Base):

    __table__ = sa.Table('user_customer', Base.metadata,

        sa.Column('user_id',
                  sa.ForeignKey("users.id"),
                  primary_key=True),

        sa.Column('customer_id',
                  sa.ForeignKey("customer.id"),
                  primary_key=True)
    )

[Mikko Ohtamaa]

Thank you! Now I understand (as_uuid=True) better and how type interference should work here.