Query over foreign keys in SQLAlchemy

Question

I am trying to query my database in flask-SQLAlchemy. I used the classical Object-Relational approach:

class User(object):

    query = db_session.query_property()

    def __init__(self):
        pass
    def __repr__(self):
        return '<User %i>' % (self.id)


class Context(object):

    query = db_session.query_property()

    def __init__(self, name=None, description=None, private=False):
        self.name = name
        self.description = description
        self.private = private

    def __repr__(self):
        return '<Context %r>' % (self.name)

class Beacon(object):

    query = db_session.query_property()

    def __init__(self, UUID, minor,major, context=None):
        self.UUID = UUID
        self.major = major
        self.minor = minor
        self.context = context

    def __repr__(self):
        return '<Beacon UUID: %r major: %r minor: %r>' 
        % ((self.UUID),(self.major), (self.minor))

users = Table('users', metadata, 
    Column('id',Integer, primary_key=True))   
mapper(User, users, properties={
    'beacons' : relationship(Beacon, backref='user'), 
    'contexts' : relationship(Context, backref='user')
    })

contexts = Table('contexts', metadata, 
    Column('id', Integer, primary_key=True), 
    Column('name', String(50), nullable=True), 
    Column('description', String(255)), 
    Column('private', Boolean, default=True), 
    Column('user_id', Integer, ForeignKey('users.id'), nullable=False),
    UniqueConstraint('name', 'user_id','private')) 
mapper(Context, contexts, properties={
    'beacons' : relationship(Beacon, backref='context')
    })

beacons = Table('beacons', metadata, 
    Column('id',Integer, primary_key=True),
    Column('UUID',String(32), nullable=True),
    Column('major', String(4), nullable=True),
    Column('minor', String(4), nullable=True),
    Column('user_id', Integer, ForeignKey('users.id')),
    Column('context_id', Integer, ForeignKey('contexts.id')),
    UniqueConstraint('UUID', 'major','minor'))

mapper(Beacon, beacons)

The question is simple: how do I query over the foreign keys? I would like e.g. to get all the Beacons owned by a certain user and whose context is set to private.

Igor Hatarist · Accepted Answer · 2015-02-13 09:38:50Z

1

There's an association proxy feature in SQLAlchemy.

Using it, you could get beacons like that:

[beacon for beacon in user.beacons if beacon.context.private == True]

answered Feb 13, 2015 at 9:38

Igor Hatarist

5,4672 gold badges35 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query over foreign keys in SQLAlchemy

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related