sqlalchemy.select.alias

Here are the examples of the python api sqlalchemy.select.alias taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.

18 Examples 7

Example 1

Project: sqlalchemy Source File: test_compiler.py
    def test_noorderby_insubquery(self):
        """test that the ms-sql dialect removes ORDER BY clauses from
        subqueries"""

        table1 = table('mytable',
                       column('myid', Integer),
                       column('name', String),
                       column('description', String),
                       )

        q = select([table1.c.myid],
                   order_by=[table1.c.myid]).alias('foo')
        crit = q.c.myid == table1.c.myid
        self.assert_compile(select(['*'], crit),
                            "SELECT * FROM (SELECT mytable.myid AS "
                            "myid FROM mytable) AS foo, mytable WHERE "
                            "foo.myid = mytable.myid")

Example 2

Project: sqlalchemy Source File: test_rel_fn.py
    def test_join_targets_o2m_selfref(self):
        joincond = self._join_fixture_o2m_selfref()
        left = select([joincond.parent_selectable]).alias('pj')
        pj, sj, sec, adapter, ds = joincond.join_targets(
                                    left,
                                    joincond.child_selectable,
                                    True)
        self.assert_compile(
            pj, "pj.id = selfref.sid"
        )

        right = select([joincond.child_selectable]).alias('pj')
        pj, sj, sec, adapter, ds = joincond.join_targets(
                                    joincond.parent_selectable,
                                    right,
                                    True)
        self.assert_compile(
            pj, "selfref.id = pj.sid"
        )

Example 3

Project: sqlalchemy Source File: test_rel_fn.py
    def test_join_targets_o2m_left_aliased(self):
        joincond = self._join_fixture_o2m()
        left = select([joincond.parent_selectable]).alias('pj')
        pj, sj, sec, adapter, ds = joincond.join_targets(
                                    left,
                                    joincond.child_selectable,
                                    True)
        self.assert_compile(
            pj, "pj.id = rgt.lid"
        )

Example 4

Project: sqlalchemy Source File: test_rel_fn.py
    def test_join_targets_o2m_right_aliased(self):
        joincond = self._join_fixture_o2m()
        right = select([joincond.child_selectable]).alias('pj')
        pj, sj, sec, adapter, ds = joincond.join_targets(
                                    joincond.parent_selectable,
                                    right,
                                    True)
        self.assert_compile(
            pj, "lft.id = pj.lid"
        )

Example 5

Project: sqlalchemy Source File: test_rel_fn.py
    def test_join_targets_o2m_composite_selfref(self):
        joincond = self._join_fixture_o2m_composite_selfref()
        right = select([joincond.child_selectable]).alias('pj')
        pj, sj, sec, adapter, ds = joincond.join_targets(
                                    joincond.parent_selectable,
                                    right,
                                    True)
        self.assert_compile(
            pj,
            "pj.group_id = composite_selfref.group_id "
            "AND composite_selfref.id = pj.parent_id"
        )

Example 6

Project: sqlalchemy Source File: test_rel_fn.py
    def test_join_targets_m2o_composite_selfref(self):
        joincond = self._join_fixture_m2o_composite_selfref()
        right = select([joincond.child_selectable]).alias('pj')
        pj, sj, sec, adapter, ds = joincond.join_targets(
                                    joincond.parent_selectable,
                                    right,
                                    True)
        self.assert_compile(
            pj,
            "pj.group_id = composite_selfref.group_id "
            "AND pj.id = composite_selfref.parent_id"
        )

Example 7

Project: sqlalchemy Source File: test_selectable.py
Function: test_no_tables
    def test_no_tables(self):
        Subset = self.classes.Subset


        selectable = select([column("x"), column("y"), column("z")]).alias()
        mapper(Subset, selectable, primary_key=[selectable.c.x])

        self.assert_compile(
            Session().query(Subset),
            "SELECT anon_1.x AS anon_1_x, anon_1.y AS anon_1_y, "
            "anon_1.z AS anon_1_z FROM (SELECT x, y, z) AS anon_1",
            use_default_dialect=True
        )

Example 8

Project: sqlalchemy Source File: test_selectable.py
    def test_no_table_needs_pl(self):
        Subset = self.classes.Subset


        selectable = select([column("x"), column("y"), column("z")]).alias()
        assert_raises_message(
            sa.exc.ArgumentError,
            "could not assemble any primary key columns",
            mapper, Subset, selectable
        )

Example 9

Project: sqlalchemy Source File: test_selectable.py
Function: test_basic
    def test_basic(self):
        Subset, common = self.classes.Subset, self.tables.common

        subset_select = select([common.c.id, common.c.data]).alias()
        subset_mapper = mapper(Subset, subset_select)
        sess = Session(bind=testing.db)
        sess.add(Subset(data=1))
        sess.flush()
        sess.expunge_all()

        eq_(sess.query(Subset).all(), [Subset(data=1)])
        eq_(sess.query(Subset).filter(Subset.data==1).one(), Subset(data=1))
        eq_(sess.query(Subset).filter(Subset.data!=1).first(), None)

        subset_select = sa.orm.class_mapper(Subset).mapped_table
        eq_(sess.query(Subset).filter(subset_select.c.data==1).one(),
            Subset(data=1))

Example 10

Project: sqlalchemy Source File: test_text.py
    def test_order_by_outermost_label(self):
        # test [ticket:3335], assure that order_by("foo")
        # catches the label named "foo" in the columns clause only,
        # and not the label named "foo" in the FROM clause
        s1 = select([table1.c.myid.label("foo"), table1.c.name]).alias()
        stmt = select([s1.c.name, func.bar().label("foo")]).order_by("foo")

        self.assert_compile(
            stmt,
            "SELECT anon_1.name, bar() AS foo FROM "
            "(SELECT mytable.myid AS foo, mytable.name AS name "
            "FROM mytable) AS anon_1 ORDER BY foo"
        )

Example 11

Project: sqlalchemy Source File: test_text.py
    def test_group_by_subquery(self):
        stmt = select([table1]).alias()
        stmt = select([stmt]).apply_labels().group_by("myid")
        self.assert_compile(
            stmt,
            "SELECT anon_1.myid AS anon_1_myid, anon_1.name AS anon_1_name, "
            "anon_1.description AS anon_1_description FROM "
            "(SELECT mytable.myid AS myid, mytable.name AS name, "
            "mytable.description AS description FROM mytable) AS anon_1 "
            "GROUP BY anon_1.myid"
        )

Example 12

Project: glottolog3 Source File: check_db_consistency.py
Function: invalid_query
    def invalid_query(self, session, **kw):
        vs_rid = select([
            ValueSet.pk,
            func.unnest(func.regexp_matches(
                ValueSet.description, '\*\*(\d+)\*\*', 'g')).label('ref_id')]).alias()
        return session.query(ValueSet)\
            .filter(ValueSet.pk.in_(
                session.query(vs_rid.c.pk)
                .filter(~session.query(Ref).filter_by(id=vs_rid.c.ref_id).exists())))\
            .order_by(ValueSet.id)

Example 13

Project: plenario Source File: MetaTable.py
Function: time_series
    def timeseries(self, agg_unit, start, end, geom=None, column_filters=None):
        # Reading this blog post
        # http://no0p.github.io/postgresql/2014/05/08/timeseries-tips-pg.html
        # inspired this implementation.
        t = self.point_table

        # Special case for the 'quarter' unit of aggregation.
        step = '3 months' if agg_unit == 'quarter' else '1 ' + agg_unit

        # Create a CTE to represent every time bucket in the timeseries
        # with a default count of 0
        day_generator = func.generate_series(func.date_trunc(agg_unit, start),
                                             func.date_trunc(agg_unit, end),
                                             step)
        defaults = select([sa.literal_column("0").label('count'),
                           day_generator.label('time_bucket')]) \
            .alias('defaults')

        where_filters = [t.c.point_date >= start, t.c.point_date <= end]
        if column_filters is not None:
            # Column filters has to be iterable here, because the '+' operator
            # behaves differently for SQLAlchemy conditions. Instead of
            # combining the conditions together, it would try to build
            # something like :param1 + <column_filters> as a new condition.
            where_filters += [column_filters]

        # Create a CTE that grabs the number of records contained in each time
        # bucket. Will only have rows for buckets with records.
        actuals = select([func.count(t.c.hash).label('count'),
                          func.date_trunc(agg_unit, t.c.point_date).
                         label('time_bucket')]) \
            .where(sa.and_(*where_filters)) \
            .group_by('time_bucket')

        # Also filter by geometry if requested
        if geom:
            contains = func.ST_Within(t.c.geom, func.ST_GeomFromGeoJSON(geom))
            actuals = actuals.where(contains)

        # Need to alias to make it usable in a subexpression
        actuals = actuals.alias('actuals')

        # Outer join the default and observed values
        # to create the timeseries select statement.
        # If no observed value in a bucket, use the default.
        name = sa.literal_column("'{}'".format(self.dataset_name)) \
            .label('dataset_name')
        bucket = defaults.c.time_bucket.label('time_bucket')
        count = func.coalesce(actuals.c.count, defaults.c.count).label('count')
        ts = select([name, bucket, count]). \
            select_from(defaults.outerjoin(actuals, actuals.c.time_bucket == defaults.c.time_bucket))

        return ts

Example 14

Project: sqlalchemy Source File: test_selects.py
    def test_map_to_select(self):
        Base, Child = self.classes.Base, self.classes.Child
        base, child = self.tables.base, self.tables.child

        base_select = select([base]).alias()
        mapper(Base, base_select, polymorphic_on=base_select.c.type,
                        polymorphic_identity='base')
        mapper(Child, child, inherits=Base,
                        polymorphic_identity='child')

        sess = Session()

        # 2. use an id other than "1" here so can't rely on
        # the two inserts having the same id
        c1 = Child(id=12, name='c1')
        sess.add(c1)

        sess.commit()
        sess.close()

        c1 = sess.query(Child).one()
        eq_(c1.name, 'c1')

Example 15

Project: sqlalchemy Source File: test_composites.py
Function: setup_mappers
    @classmethod
    def setup_mappers(cls):
        values, descriptions = cls.tables.values, cls.tables.descriptions

        class Descriptions(cls.Comparable):
            pass

        class Values(cls.Comparable):
            pass

        class CustomValues(cls.Comparable, list):
            def __init__(self, *args):
                self.extend(args)

            def __composite_values__(self):
                return self

        desc_values = select(
            [values, descriptions.c.d1, descriptions.c.d2],
            descriptions.c.id == values.c.description_id
        ).alias('descriptions_values')

        mapper(Descriptions, descriptions, properties={
            'values': relationship(Values, lazy='dynamic'),
            'custom_descriptions': composite(
                                CustomValues,
                                        descriptions.c.d1,
                                        descriptions.c.d2),

        })

        mapper(Values, desc_values, properties={
            'custom_values': composite(CustomValues,
                                            desc_values.c.v1,
                                            desc_values.c.v2),

        })

Example 16

Project: sqlalchemy Source File: test_deferred.py
    def test_map_selectable_wo_deferred(self):
        """test mapping to a selectable with deferred cols,
        the selectable doesn't include the deferred col.

        """

        Order, orders = self.classes.Order, self.tables.orders


        order_select = sa.select([
                        orders.c.id,
                        orders.c.user_id,
                        orders.c.address_id,
                        orders.c.description,
                        orders.c.isopen]).alias()
        mapper(Order, order_select, properties={
            'description':deferred(order_select.c.description)
        })

        sess = Session()
        o1 = sess.query(Order).order_by(Order.id).first()
        assert 'description' not in o1.__dict__
        eq_(o1.description, 'order 1')

Example 17

Project: glottolog3 Source File: fix_consistency.py
Function: main
def main(args):
    source_pattern = re.compile('__Source_(?P<id>[0-9]+)__')
    ref_in_markup_pattern = re.compile('\*\*(?P<id>[0-9]+)\*\*')

    with transaction.manager:
        redirect_map = RedirectMap()
        for key, value in DBSession.query(Config.key, Config.value):
            m = source_pattern.match(key)
            if m and value != '__gone__':
                redirect_map[m.group('id')] = value

        for cfg in DBSession.query(Config)\
                .filter(Config.key.startswith('__Source_'))\
                .filter(Config.value.in_(list(redirect_map.keys()))):
            try:
                new = redirect_map.get_final(cfg.value)
            except ValueError:
                args.log.error('invalid redirect loop: %s' % (cfg.value,))
                new = cfg.value

            if new != cfg.value:
                args.log.info('fixed redirect: %s %s' % (cfg.value, new))
                cfg.value = new

        def repl(m):
            try:
                new = redirect_map.get_final(m.group('id'))
            except ValueError:
                new = m.group('id')
            return '**%s**' % new

        vs_rid = select([
            ValueSet.pk,
            func.unnest(func.regexp_matches(
                ValueSet.description, '\*\*(\d+)\*\*', 'g')).label('ref_id')]).alias()
        for vs in DBSession.query(ValueSet) \
                .filter(ValueSet.pk.in_(
                    DBSession.query(vs_rid.c.pk)
                        .filter(
                            ~DBSession.query(Ref)
                                .filter_by(id=vs_rid.c.ref_id).exists()))) \
                .order_by(ValueSet.id):
            new = ref_in_markup_pattern.sub(repl, vs.description)
            if new != vs.description:
                args.log.info(
                    'fixed obsolete ref id in markup: %s %s' % (vs.description, new))
                vs.description = new

Example 18

Project: sqlalchemy-json-api Source File: query_builder.py
    def build_select(
        self,
        fields=None,
        include=None,
        sort=None,
        limit=None,
        offset=None,
        links=None,
        multiple=True,
        ids_only=False,
        as_text=False
    ):
        self.validate_field_keys(fields)
        if fields is None:
            fields = {}

        params = Parameters(
            fields=fields,
            include=include,
            sort=sort,
            limit=limit,
            offset=offset
        )
        from_args = self._get_from_args(
            params,
            multiple,
            ids_only,
            links
        )

        main_json_query = sa.select(from_args).alias('main_json_query')

        expr = sa.func.row_to_json(sa.text('main_json_query.*'))
        if as_text:
            expr = sa.cast(expr, sa.Text)

        query = sa.select(
            [expr],
            from_obj=main_json_query
        )
        return query