association proxy example

example-association_proxy.py

# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
#
# Use this file to build your own SSCCE
# SSCCE = Short, Self Contained, Correct (Compatible) Example
# see http://sscce.org/
#
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
# Standard imports

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import deferred, class_mapper
from sqlalchemy import Integer, String, Text, Binary, Column, ForeignKey
from sqlalchemy import inspect
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import relationship
from sqlalchemy.ext.associationproxy import association_proxy


# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
# You probably don't need to overwrite this
Base = declarative_base()


# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
# Define some models that inherit from Base


class Bar(Base):
    __tablename__ = 'bar'
    id = Column(Integer, primary_key=True)
    qux = Column(String, primary_key=False)

class Foo(Base):
    __tablename__ = 'foo'
    id = Column(Integer, primary_key=True)
    id_bar = Column(Integer, ForeignKey("bar.id"), primary_key=True)
    
    bar = relationship(Bar, uselist=False, lazy='joined') 
    qux = association_proxy('bar', 'qux')




# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
# we'll use sqlite+memory to handle this
# if your issue is backend specific, then the engine will need to be different

engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)


# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
# you shouldn't change these 2 line

sessionFactory = sessionmaker(bind=engine)
s = sessionFactory()

# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
# Start your example here:

s.query(Foo).filter(Foo.qux == 'abc').first()

> SELECT foo.id AS foo_id, foo.id_bar AS foo_id_bar, bar_1.id AS bar_1_id, bar_1.qux AS bar_1_qux 
> FROM foo LEFT OUTER JOIN bar AS bar_1 ON bar_1.id = foo.id_bar 
> WHERE EXISTS (SELECT 1 FROM bar WHERE bar.id = foo.id_bar AND bar.qux = 'abc') LIMIT ? OFFSET ?