CODEWITHSUNDEEP
pythonsqlflaskflask-sqlalchemy
hasdrubal
hasdrubal

Reputation: 1148

Query over foreign keys in SQLAlchemy

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.

Upvotes: 0

Views: 133

Answers (1)

Igor Hatarist
Igor Hatarist

Reputation: 5442

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]

Upvotes: 1

Related Questions