sqlalchemy query example

gistfile1.txt

        filters = [
            TourneyList.tourney_id == Tourney.id ,
            ArchtypeList.id == TourneyList.archtype_id,
            Ship.archtype_id == ArchtypeList.id ,
            Ship.ship_pilot_id == ShipPilot.id ,
            ShipPilot.pilot_id == Pilot.id
        ]

        sql = session.query(
            sqlalchemy.extract('year', Tourney.tourney_date).label("year"),
            sqlalchemy.extract('month', Tourney.tourney_date).label("month"),
            sqlalchemy.extract('day', Tourney.tourney_date).label("day"),
            Tourney.tourney_type,
            ArchtypeList.faction,
            ShipPilot.ship_type,
            Pilot.name.label('pilot'),
            func.count(TourneyList.id).label("count")).\
            filter( and_(*filters)).\
            group_by( sqlalchemy.extract('year', Tourney.tourney_date),
                      sqlalchemy.extract('month', Tourney.tourney_date),
                      sqlalchemy.extract('day', Tourney.tourney_date),
                      ArchtypeList.faction,
                      ShipPilot.ship_type,
                      Pilot.name).\
            statement.compile(dialect=mysql.dialect())

        connection = self.pm.db_connector.get_engine().connect()
        time_series_data = connection.execute(sql)