Problem where SQLAlchemy emits a nonsense INSERT

[Joshua Leahy]

I've managed to create a situation where SQLAlchemy seems to generate queries that break foreign key constraints. As I'm not certain I'm not making a silly mistake I thought I'd post the example here so people could take a look rather than just reporting a bug.

Basically I have a table that looks like this:

class ReactionItem(Base):

    __tablename__ = 'reaction_items'

    __table_args__ = (

        PrimaryKeyConstraint('reaction_id', 'item_id'),

    )

    reaction_id = Column(Integer, ForeignKey('reactions.id'))

    item_id = Column(Integer, ForeignKey('items.id'))

And SQLAlchemy generates a query that looks like this:

INSERT INTO reaction_items (item_id) VALUES (?)

That's not going to work because it doesn't specify all elements of the primary key. It doesn't cause SQLite to error out, but MySQL does refuse to commit.

This is with the latest version of SQLAlchemy on PyPI and I've included a minimal example below.

Any help would be much appreciated (flames would be most welcome as they'd confirm I'm just doing something ridiculous).

Thanks,

Josh.

from sqlalchemy import Column, Integer, ForeignKey, create_engine, PrimaryKeyConstraint

from sqlalchemy.orm import relationship, sessionmaker

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Item(Base):

    __tablename__ = 'items'

    id = Column(Integer, primary_key=True)

    used_by = relationship('ReactionItem', cascade='all, delete-orphan', backref='item')

class Reaction(Base):

    __tablename__ = 'reactions'

    id = Column(Integer, primary_key=True)

    items = relationship('ReactionItem', cascade='all, delete-orphan', backref='reaction')

class ReactionItem(Base):

    __tablename__ = 'reaction_items'

    __table_args__ = (

        PrimaryKeyConstraint('reaction_id', 'item_id'),

    )

    reaction_id = Column(Integer, ForeignKey('reactions.id'))

    item_id = Column(Integer, ForeignKey('items.id'))

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

Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)

session = Session()

reaction = Reaction()

session.add(reaction)

session.query(Item).get(43)

item = Item(id=43)

session.add(item)

reaction.items.append(ReactionItem(item=item))

reaction.items.remove(reaction.items[0])

session.commit()

session.close()

[Michael Bayer]

On Oct 14, 2012, at 2:47 PM, Joshua Leahy wrote:

I've managed to create a situation where SQLAlchemy seems to generate queries that break foreign key constraints. As I'm not certain I'm not making a silly mistake I thought I'd post the example here so people could take a look rather than just reporting a bug.

Basically I have a table that looks like this:

class ReactionItem(Base):

    __tablename__ = 'reaction_items'

    __table_args__ = (

        PrimaryKeyConstraint('reaction_id', 'item_id'),

    )

    reaction_id = Column(Integer, ForeignKey('reactions.id'))

    item_id = Column(Integer, ForeignKey('items.id'))

And SQLAlchemy generates a query that looks like this:

INSERT INTO reaction_items (item_id) VALUES (?)

That's not going to work because it doesn't specify all elements of the primary key. It doesn't cause SQLite to error out, but MySQL does refuse to commit.

This is with the latest version of SQLAlchemy on PyPI and I've included a minimal example below.

Any help would be much appreciated (flames would be most welcome as they'd confirm I'm just doing something ridiculous).

There's a few ways this kind of thing can happen.   The usual reason is because ReactionItem(item=item) would be flushed when you say "reaction.items", which lazyloads, then autoflushes, then flushes ReactionItem(item=item) without yet being attached to Reaction, since we haven't yet loaded reaction.items.  Due to the specific order of operations in your example, this isn't *quite* happening, but I'd advise you create association objects such as ReactionItem as ReactionItem(item=item, reaction=reaction), or otherwise use the cascade_backrefs flag (see http://docs.sqlalchemy.org/en/latest/orm/session.html#backref-cascade).

In this case, you still have an incomplete ReactionItem present because "reaction.items.remove(reaction.items[0])" de-associates ReactionItem from the parent Reaction.    It then gets flushed, only associated with the Item, and there's no reaction_id to use, so you get the error.

SQLAlchemy doesn't try to jump in on that because primary key columns are often autogenerating, or via triggers, so it doesn't assume that just because the PK is composite, it should guess that maybe you don't mean to be doing what you're doing.  Because the database will report an error anyway, it avoids making a guess here.   SQLite for some reason is not emitting an error when a NULL primary key is being created, so if you're using SQLite maybe put "nullable=False" on those PK columns.

If the expectation here is that "delete-orphan" would take care of it, that is true, but the ReactionItem object has to be an orphan on all sides, which means detach it from Item as well:

ri = reaction.items[0]

reaction.items.remove(ri)

ri.item = None

[Joshua Leahy]

Thanks for your help Michael.

I've tried explicitly removing both parents and as you say it gets deleted, I was just misunderstanding the semantics when you have multiple parents and delete-orphan.

My other mistake was to assume that PrimaryKeyConstraint implied nullable=False, as primary_key=True seems to. I've added nullable=False to all my primary key columns now, that should help with debugging.

Joshua.

[Michael Bayer]

On Oct 15, 2012, at 1:28 PM, Joshua Leahy wrote:

>
> My other mistake was to assume that PrimaryKeyConstraint implied nullable=False, as primary_key=True seems to.

a PRIMARY KEY constraint absolutely does imply NOT NULL in most cases, I have a feeling SQlite with a composite PK is somehow letting it slide.