Behaviour when setting a foreign key column.

[João Miguel Neves]

Hi all,

I'm sorry, as I feel like this is kind of a beginner question. I'd expect that when I set a column attribute the respective relationship field would change or at least be marked as changed (so a posterior access can be loaded later on another access), but I've found a case where it doesn't. I've tried to find a bit of code to represent what I'm struggling with, hopefully it will be clear enough.

# --- START ---

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

from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base


engine = create_engine("sqlite://", echo=True)
Session = sessionmaker(engine)

Base = declarative_base()


class Company(Base):
    __tablename__ = "company"

    company_id = Column(Integer, primary_key=True)
    name = Column(String(50))


class Project(Base):
    __tablename__ = "project"

    project_id = Column(Integer, primary_key=True)
    name = Column(String(50))
    company_id = Column(Integer, ForeignKey("company.company_id"), index=True)
    company = relationship("Company")


Base.metadata.create_all(engine)

session = Session()

c = Company(name="First Company")

p = Project(name="First Project")

session.add(c)
session.add(p)

assert p.company is None
assert p.company_id is None

# Case 1: update the _id doesn't seem to reflect
p.company_id = c.company_id

assert p.company is None
assert p.company_id == c.company_id

session.flush()

# Wasn't expecting the None here
assert p.company is None
assert p.company_id is None

session.refresh(p)

# Wasn't expecting the None here
assert p.company is None
assert p.company_id is None

# Case 2: update the relation works as expected
p.company = c

assert p.company == c
assert p.company_id is None

session.flush()

assert p.company == c
assert p.company_id == c.company_id

session.refresh(p)

assert p.company == c
assert p.company_id == c.company_id

# --- END ---

Sorry for bothering, but I'm really puzzled/stuck and feel like I've missed something important,

João

[Simon King]

You have missed something important, but I don't know if it will clear
up all your questions :-)

In your example, c.company_id doesn't get populated until the first

flush. Until then, c.company_id is None. So when you wrote:

# Case 1: update the _id doesn't seem to reflect
p.company_id = c.company_id

You were actually writing:

p.company_id = None

This explains why p.company and p.company_id are None all the way
through Case 1.

However, even if you added a session.flush() after adding the objects
to the session, setting a foreign key attribute directly does not
cause the associated relationship to be updated immediately:

https://docs.sqlalchemy.org/en/13/faq/sessions.html#i-set-the-foo-id-attribute-on-my-instance-to-7-but-the-foo-attribute-is-still-none-shouldn-t-it-have-loaded-foo-with-id-7

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 sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAJGrhWZq%2Biw_X-7B9%2Bc0sdxj2fAebpMb-O1erXnfhYmDsWZ%3D4Q%40mail.gmail.com.

[João Miguel Neves]

Hi Simon,

Thank you very much. And this also explains why the problem appeared in tests.

Best regards,

João

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFHwexePGmkOqqiTcZ8gEiUYM9b0Fmc4i3SeHGEEkX2Q-dMGzg%40mail.gmail.com.