On Sat, Mar 17, 2018 at 7:02 PM, Derek Lambert
<[email protected]> 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
The error I get (after swapping sqlite for postgresql) is:
sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked
autoflush; consider using a session.no_autoflush block if this flush
is occurring prematurely) (sqlite3.IntegrityError) NOT NULL constraint
failed: filter.directory_name [SQL: u'INSERT INTO filter
(category_name
, filter) VALUES (?, ?)'] [parameters: (u'category b', 'new filter')]
The reason this is happening is that accessing the "directory.filters"
attribute is causing an autoflush. new_filter is already in the
session at this point, so it gets flushed, but because it hasn't yet
been attached to the Directory, the directory_name attribute is still
None.
It's clear why this is happening in the second case. I wasn't sure why
it *wasn't* happening in the first instance, so I looked at the
bytecode:
50 0 LOAD_GLOBAL 0 (directory)
3 LOAD_ATTR 1 (filters)
6 LOAD_ATTR 2 (append)
9 LOAD_GLOBAL 3 (Filter)
12 LOAD_CONST 1 ('filter')
15 LOAD_CONST 2 ('test filter')
18 LOAD_CONST 3 ('category')
21 LOAD_GLOBAL 4 (category_a)
24 CALL_FUNCTION 512
27 CALL_FUNCTION 1
30 POP_TOP
It looks like Python evaluates the "directory.filters.append"
expression before evaluating the parameters to the append() function.
So in the first instance, "directory.filters" is loaded *before* the
Filter is constructed, so it never gets flushed in a
partially-constructed state. I *think* this is guaranteed by the
language (https://docs.python.org/2/reference/expressions.html#evaluation-order)
As the error message says, you can work around this by disabling
autoflush, perhaps like this:
with session.no_autoflush:
directory.filters.append(new_filter)
Or, if you don't actually want to load directory.filters at this point
in your code, use the other end of the relationship:
new_filter.directory = directory
Hope that helps,
Simon
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
