controllers.py

from sqlalchemy.orm import sessionmaker

from shark import app
from settings import MSSQL_ENV_CONFIG
from datasource.engines import get_mssql_engine
from schemas.alchemy import BriefTable, LinkSubscriberBriefTable, SubscriberTable, SubscriberBriefProfileTable
from shark.interfaces import ContainsFieldValueSearch


engine = get_mssql_engine(MSSQL_ENV_CONFIG)
Session = sessionmaker(bind=engine)
db_session = Session()

def handle_search_call():
    # the query methods such as query(), filter(), and limit()
    # are mostly "generative", meaning they return another Query object (the "q" things)
    # that way, i can chain them together until I finally call all()
    # which I think fires off the query

    q = db_session.query(SubscriberTable.first_name,
                         SubscriberTable.last_name,
                         SubscriberTable.last_open,
                         SubscriberTable.email,
                         SubscriberTable.company).\
        join(SubscriberBriefProfileTable)

    search = ContainsFieldValueSearch(fields=request.json)

    for k, v in search.fields:
        q = q.filter(getattr(SubscriberTable, v).like("%%%s%%" % v))
    q = q.limit(20)  # todo raise limit
    # result set
    result_set = q.all()
    print result_set