Is it possible to sync the foreign key attribute when setting a relation before committing, and the other way around?

[Maarten De Paepe]

Hi,

I've been looking around a bit, but can't seem to find any info on this.
We use "before flush/commit" events (but this problem applies to validators as well) to perform a number of things with an istance is persisted.

One of the issues we're facing a lot is when we create a new instance, and set a relation, the foreign key counterpart is still None, and the other way around; If I set the foreign key, the relation is None. Not having the counterpart set, influences how we write the event/validator, as we have to load the counterpart on the fly.

In itself, it's not really an issue, but it's making the code more dense everytime we have to add the code that loads.

I was wondering if there was an easy way to keep the foreign key and relation in sync, when setting one.

A small piece of code to illustrate the issue
​
class Teacher(Base):
    __tablename__ = 'teacher'
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String)
​
​
class Student(Base):
    __tablename__ = 'student'
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String)
​
    teacher_id = sa.Column(sa.Integer, sa.ForeignKey('teacher.id'))
    teacher = relationship('Teacher', backref='students')
​
​
teacher1 = Teacher(name='Mark')
teacher2 = Teacher(name='Eugene')
session.add_all((teacher1, teacher2))
session.commit()
​
student = Student(name='Louis')
student.teacher = teacher1
# assert student.teacher_id == 1  # Wanted behaviour
​
student.teacher_id = 2
# assert student.teacher == teacher2  # Wanted behaviour
​
student.teacher = teacher1
# assert student.teacher_id == 1  # Wanted behaviour
​
​I'm able to achieve this behaviour with validators that set it for me, but I don't want to write a validator everytime I have a fk/relation pair, plus I'm not sure this behavious is in the scope of validators.
I've managed to write something dynamic that introspects the class and registers "set" SQLA events, to then set the counterpart, but it's a very elaborate piece of code.

So, in conclusion, is there a built in way to keep the fk/relation of an instance in sync? Or am I going about this the wrong way.

Thanks for your time!

Kind regards,
Maarten

[Simon King]

I'm not aware of a built-in way to do this. The SQLAlchemy ORM leans
towards you just using the relationships and ignoring the foreign
keys.

The FAQ has an entry about this (in one direction at least):

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

...which points to a recipe for loading related objects when the
foreign key is set:

https://github.com/sqlalchemy/sqlalchemy/wiki/ExpireRelationshipOnFKChange

It's probably a similar idea to what you already wrote for
introspecting and registering event handlers.

Sorry, that's probably not the answer you were hoping for. Is there
any chance you could just use relationships and forget about the
foreign keys?

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/f8901192-e953-4360-8271-f0b4a6fc68ec%40googlegroups.com.

[Maarten De Paepe]

Thanks for your quick reply,

It's not entirely possible to do so, as we're using some 3rd party libs to expose our models as an API. Here you might set either your fk or relation depending on your call.

What is being set, is up the the person working with the API, so not entirely up to us. I could potentially limit it to only allow setting relations though, I'll look into that.

Thanks once more for your insights! This at least solidifies the fact that my idea was not so wierd after all :)

> To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.

[Mike Bayer]

these will be available if you session.flush().    It should be apparent why they aren't set ahead of time, if student hasn't been INSERTed yet, it may not even have a primary key value.   Similarly, if the FKs were set immediately, that doesn't accomodate for if the parent's primary key value were altered (would be very elaborate to track this at attribute set time) as well as it would be inefficient if student.teacher were applied multiple times and we really don't need this FK value until the object is flushed.

I'm able to achieve this behaviour with validators that set it for me, but I don't want to write a validator everytime I have a fk/relation pair, plus I'm not sure this behavious is in the scope of validators.

I've managed to write something dynamic that introspects the class and registers "set" SQLA events, to then set the counterpart, but it's a very elaborate piece of code.

So, in conclusion, is there a built in way to keep the fk/relation of an instance in sync? Or am I going about this the wrong way.

In general, I don't think it should be necessary to know or care about the foreign key column value ahead of time before SQL is being generated; similarly, if the concern is that an FK column might be un-set when it's supposed to be, that's first and foremost what NOT NULL at the schema level is designed to handle, and otherwise these FK values will be present in the mapper level hooks before_insert and before_update if you *really* need to get to them explicitly.

Thanks for your time!

Kind regards,

Maarten

--

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.