Thank you, excellent explanation! Setting autoflush off fixed the issue.
In the end I created the Filter objects directly and set directory and
category on them, which generated a single INSERT. It was generating a ton
of SELECT / INSERT the way I was initially doing it.
On Saturday, March 17, 2018 at 2:02:00 PM UTC-5, Derek Lambert wrote:
>
> I'm probably overlooking something simple, looking for feedback before
> opening an issue.
>
> I have some objects with relationships defined between. When I create a
> new related object and pass it in the append() method of the collection
> everything works as expected, the foreign key is set. When I assign the new
> related object to a variable and pass that to the append() method, the
> foreign key isn't set and I get a 'null value in column "directory_name"
> violates not-null constraint'. This is with SQLAlchemy 1.2.5 and python 3.6.
>
> import sqlalchemy as sa
> import sqlalchemy.orm as orm
> from sqlalchemy.ext.declarative import declarative_base
>
>
> Base = declarative_base()
>
>
> class Directory(Base):
> name = sa.Column(sa.String, primary_key=True)
>
> __tablename__ = 'directory'
>
>
> class Category(Base):
> name = sa.Column(sa.String, primary_key=True)
>
> __tablename__ = 'category'
>
>
> class Filter(Base):
> directory_name = sa.Column(sa.String, sa.ForeignKey('directory.name'),
> primary_key=True)
> category_name = sa.Column(sa.String, sa.ForeignKey('category.name'),
> primary_key=True)
> filter = sa.Column(sa.String, primary_key=True)
>
> directory = orm.relationship('Directory',
> backref=orm.backref('filters', lazy='joined'), lazy='joined')
> category = orm.relationship('Category',
> backref=orm.backref('filters', lazy='joined'), lazy='joined')
>
> __tablename__ = 'filter'
>
>
> engine =
> sa.create_engine('postgresql+psycopg2://postgres@localhost/bug_test')
> Base.metadata.create_all(engine)
> session = orm.sessionmaker(bind=engine)()
>
> directory = Directory(name='test')
> category_a = Category(name='category a')
> category_b = Category(name='category b')
>
> session.add(directory)
> session.add(category_a)
> session.add(category_b)
> session.commit()
>
> assert len(session.new) == 0
>
> # Instantiate object in call to append - works
> directory.filters.append(Filter(filter='test filter', category=category_a))
> session.commit()
>
> assert len(session.new) == 0
>
> # Instantiate object before call to append - fails
> new_filter = Filter(filter='new filter', category=category_b)
> directory.filters.append(new_filter)
>
> session.commit()
>
> assert len(session.new) == 0
>
>
> Thanks,
> Derek
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
