Generic query in SQLAlchemy

Question

I have following code:

class ArchaeologicalRecord(Base, ObservableMixin, ConcurrentMixin):
    author_id = Column(Integer, ForeignKey('authors.id'))
    author = relationship('Author', backref=backref('record'))

    horizont_id = Column(Integer, ForeignKey('horizonts.id'))
    horizont = relationship('Horizont', backref=backref('record'))

    .....

    somefield_id = Column(Integer, ForeignKey('somefields.id'))
    somefield = relationship('SomeModel', backref=backref('record'))

At the moment I have one of entry (Author or Horizont or any other entry which related to arch.record). And I want to ensure that no one record has reference to this field. But I hate to write a lot of code for each case and want to do it most common way.

So, actually I have:

• instance of ArchaeologicalRecord
• instance of child entity, for example, Horizont
• (from previous) it's class definition.

How to check whether any ArchaeologicalRecord contains (or does not) reference to Horizont (or any other child entity) without writing great chunk of copy-pasted code?

Answer 1

Are you asking how to find orphaned authors, horzonts, somefields etc?

Assuming all your relations are many-to-one (ArchaelogicalRecord-to-Author), you could try something like:

from sqlalchemy.orm.properties import RelationshipProperty
from sqlalchemy.orm import class_mapper

session = ... # However you setup the session

# ArchaelogicalRecord will have various properties defined, 
# some of these are RelationshipProperties, which hold the info you want

for rp in class_mapper(ArchaeologicalRecord).iterate_properties:  
    if not isinstance(rp, RelationshipProperty):
        continue

    query = session.query(rp.mapper.class_)\
            .filter(~getattr(rp.mapper.class_, rp.backref[0]).any())

    orphans = query.all()
    if orphans:
        # Do something...
        print rp.mapper.class_
        print orphans

This will fail when rp.backref is None (i.e. where you've defined a relationship without a backref) - in this case you'd probably have to construct the query a bit more manually, but the RelationshipProperty, and it's .mapper and .mapper.class_ attributes should get you all the info you need to do this in a generic way.