merge does not follow primary key with default value

Issue #4055 closed
pkyosx created an issue

merge will try to select first using primary key; however, a primary key with default value does not take effect on merge.

I have run the following example under sqlalchemy-1.1.13.

from sqlalchemy import create_engine, MetaData
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

engine = create_engine('sqlite:///:memory:', echo=True)

class User(Base):
    __tablename__ = 'users'
    family_id = Column(Integer, primary_key=True, autoincrement=False)
    member_id = Column(Integer, primary_key=True, autoincrement=False, default=1)
    name = Column(String)

Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)

session = Session()

john = User(family_id=1, name="john")
peter = User(family_id=1, name="peter")

session.merge(john)
session.commit()

session.merge(peter)

# a select does not issued for peter because missing a member_id in constructor
# however, member_id has default value setting...
# it supposed to take default value into account.

# A error like the following will happen.
# sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: users.family_id, users.member_id [SQL: u'INSERT INTO users (family_id, member_id, name) VALUES (?, ?, ?)'] [parameters: (1, 1, 'peter')]
session.commit()

Comments (5)

  1. Mike Bayer repo owner

    this isn't a bug because the 'default' on the column only takes effect during an INSERT statement, and is not part of the object's state until the INSERT is emitted and the object is synchronized with the row. Merge is explicitly looking for the primary key value that is already part of the object's state, as would have been assigned by the calling code.

    if you'd like the '1' to be a default at the Python level, do this:

    class User(Base):
        __tablename__ = 'users'
        family_id = Column(Integer, primary_key=True, autoincrement=False)
        member_id = Column(Integer, primary_key=True, autoincrement=False, default=1)
        name = Column(String)
    
        def __init__(self, **kw):
            kw.setdefault('member_id', 1)
            super(User, self).__init__(**kw)
    
  2. pkyosx reporter

    I would like to create a common base to do the following thing for me.

    class User(Base):
        __tablename__ = 'users'
        family_id = Column(Integer, primary_key=True, autoincrement=False)
        member_id = Column(Integer, primary_key=True, autoincrement=False, default=1)
        name = Column(String)
        def __init__(self, **kw):
            for k,v in self._sa_class_manager.iteritems():
                if v.default:
                    kw.setdefault(k, v.default.arg)
            super(User, self).__init__(**kw)
    

    I try to inherit Base but failed, it seems tablename and a primary key is needed. Is there an easy way to work around this?

    class CommonBase(Base):
        def __init__(self, **kw):
            for k,v in self._sa_class_manager.iteritems():
                if v.default:
                    kw.setdefault(k, v.default.arg)
            super(CommonBase, self).__init__(**kw)
    
    class User(CommonBase):
        __tablename__ = 'users'
        family_id = Column(Integer, primary_key=True, autoincrement=False)
        member_id = Column(Integer, primary_key=True, autoincrement=False, default=1)
        name = Column(String)
    
  3. Mike Bayer repo owner

    add "abstract = True" to CommonBase

    class CommonBase(Base):
        __abstract__ = True
    

    however if you're looking to generalize this, instead of using __init__ it might be cleaner to use the init event http://docs.sqlalchemy.org/en/latest/orm/events.html#sqlalchemy.orm.events.InstanceEvents.init

    (also never refer to private variables like _sa_class_manager, use inspect(). the mapper is preferred for looking at attributes)

    from sqlalchemy import event
    from sqlalchemy import inspect
    
    @event.listens_for(Base, "init", propagate=True)
    def init(target, args, kwargs):
        for column_attr in inspect(target).mapper.column_attrs:
                if column_attr.expression.default:
                    kwargs.setdefault(column_attr.key, column_attr.expression.default.arg)
    
  4. Log in to comment