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
3
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")
3
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"
)
3
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"
)
3
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"
)
3
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"
)
3
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"
)
3
Example 7
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
)
3
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
)
3
Example 9
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))
3
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"
)
3
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"
)
3
Example 12
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)
0
Example 13
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
0
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')
0
Example 15
@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),
})
0
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')
0
Example 17
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
0
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