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()
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/DC4YgcphzFcJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
