Can SQLAlchemy populate foreign key relationships without querying the database?

Question

I came from the .NET world where ORM NHibernate can populate foreign key relationships by loading an object by id. Loading an object simply returns the proxy of this object with the identifier you specified. This is useful for a filled relationship when I know that an identifier exists, since it does not need to query the database, thereby saving many rounds.

Here is my script. I have a client model that can be associated with one or more departments. These sections are statically stored in the database and can be associated with one or more clients, which requires me to model many-to-many relationships. There the route REST API in the form /api/v1/customers/123/divisionswhere I POST JSON data as a list of division identifiers {'division_ids': [1, 2, 3, 4]}. In this case (use in the real world could equal 20+ unit identifiers, I would have to issue a request to retrieve a client (id 123), as well as 4 requests to retrieve partitions.

Since I know the identifier of the partitions I want to insert, can I force SQLAlchemy to simply use the identifier rather than retrieve the objects?

Can SQLAlchemy do something like this?

Answer 1

If I understand your question correctly, yes, you can do this using lazy="dynamic"a relationship. The documents:

A key feature that allows you to manage a large collection is the so-called “dynamic” relationship. This is an optional form of relationship () that returns a Query object instead of a collection on access. filter (), as well as limits and offsets, either explicitly or through slices of the array:

Examples of tables:

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = relationship("Child", lazy="dynamic")

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))

When loading a parent object, children will not be loaded; instead, an object will be loaded instead Query, which can be used to access children if necessary.

p = session.query(Parent).first() # children will not be loaded here
print(type(p.children)) # <class 'sqlalchemy.orm.dynamic.AppenderQuery'>
all_children = p.children.all() # conduct a query on the children query object
print(all_children) # [<__main__.Child object at 0x10ba3f908>, <__main__.Child object at 0x10ba3f978>]