Re: [sqlalchemy] Behaviour when setting a foreign key column.
Hi Simon,
Thank you very much. And this also explains why the problem appeared in
tests.
Best regards,
João
On Mon, 14 Dec 2020 at 14:51, Simon King wrote:
> 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
>
> On Mon, Dec 14, 2020 at 12:50 PM João Miguel Neves
> wrote:
> >
> > 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
> >
> > --
> > 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CAJGrhWZq%2Biw_X-7B9%2Bc0sdxj2fAebpMb-O1erXnfhYmDsWZ%3D4Q%40mail.gmail.com
> .
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CAFHwexePGmkOqqiTcZ8gEiUYM9b0Fmc4i3SeHGEEkX2Q-dMGzg%40mail.gmail.com
> .
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE:
Re: [sqlalchemy] Behaviour when setting a foreign key column.
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
On Mon, Dec 14, 2020 at 12:50 PM João Miguel Neves
wrote:
>
> 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
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CAJGrhWZq%2Biw_X-7B9%2Bc0sdxj2fAebpMb-O1erXnfhYmDsWZ%3D4Q%40mail.gmail.com.
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CAFHwexePGmkOqqiTcZ8gEiUYM9b0Fmc4i3SeHGEEkX2Q-dMGzg%40mail.gmail.com.
[sqlalchemy] Behaviour when setting a foreign key column.
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
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CAJGrhWZq%2Biw_X-7B9%2Bc0sdxj2fAebpMb-O1erXnfhYmDsWZ%3D4Q%40mail.gmail.com.
