Here are the examples of the python api sqlalchemy.select taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.
159 Examples
3
Example 1
Project: cubes Source File: test_expression.py
def assertExpressionEqual(self, left, right):
"""Asserts that the `left` and `right` statement expressions are equal
by pulling out the data from the table and testing whether the
returned sequences are equal."""
stmt = sa.select([left.label("value")], from_obj=self.table)
result = self.engine.execute(stmt)
left_result = [row["value"] for row in result]
stmt = sa.select([right.label("value")], from_obj=self.table)
result = self.engine.execute(stmt)
right_result = [row["value"] for row in result]
self.assertCountEqual(left_result, right_result)
3
Example 2
@db_schema.upgrade('digest')
def upgrade(ver, session):
if None is ver:
ver = 0
if ver == 0:
table = table_schema('digest_entries', session)
table_add_column(table, 'json', Unicode, session)
# Make sure we get the new schema with the added column
table = table_schema('digest_entries', session)
for row in session.execute(select([table.c.id, table.c.entry])):
try:
p = pickle.loads(row['entry'])
session.execute(table.update().where(table.c.id == row['id']).values(
json=json.dumps(p, encode_datetime=True)))
except KeyError as e:
log.error('Unable error upgrading backlog pickle object due to %s' % str(e))
ver = 1
return ver
3
Example 3
@dispatch(Projection, Selectable)
def compute_up(expr, data, **kwargs):
return compute(
expr,
sa.select([data]),
post_compute=False,
return_type='native',
)
3
Example 4
def test_count_distinct(self):
t = self.alltypes
sat = self.sa_alltypes.alias('t0')
cases = [
(t.int_col.nunique().name('nunique'),
sa.select([F.count(sat.c.int_col.distinct())
.label('nunique')])),
(t.group_by('string_col')
.aggregate(t.int_col.nunique().name('nunique')),
sa.select([sat.c.string_col,
F.count(sat.c.int_col.distinct())
.label('nunique')])
.group_by(sat.c.string_col)),
]
for case, ex in cases:
self._compare_sqla(case, ex)
3
Example 5
Project: odo Source File: test_sql.py
def test_copy_one_table_to_a_foreign_engine():
data = [(1, 1), (2, 4), (3, 9)]
ds = dshape('var * {x: int, y: int}')
with tmpfile('db') as fn1:
with tmpfile('db') as fn2:
src = into('sqlite:///%s::points' % fn1, data, dshape=ds)
tgt = into('sqlite:///%s::points' % fn2,
sa.select([src]), dshape=ds)
assert into(set, src) == into(set, tgt)
assert into(set, data) == into(set, tgt)
3
Example 6
Project: SickRage Source File: test_insert.py
def test_last_inserted_id_implicit_returning(self):
r = config.db.execute(
self.tables.autoinc_pk.insert(),
data="some data"
)
pk = config.db.scalar(select([self.tables.autoinc_pk.c.id]))
eq_(
r.inserted_primary_key,
[pk]
)
3
Example 7
def iteritems(self):
"""Iterate over table rows"""
query = select([self._table])
result = self._engine.execute(query)
for row in result:
key = row[0]
d = self._row_to_value(row)[1]
yield (key, d)
3
Example 8
def test_text_roundtrip(self):
text_table = self.tables.text_table
config.db.execute(
text_table.insert(),
{"text_data": 'some text'}
)
row = config.db.execute(
select([text_table.c.text_data])
).first()
eq_(row, ('some text',))
3
Example 9
def getRspec (self, sliver_urn):
s = select([slivers], slivers.c.sliver_urn==sliver_urn)
conn = self.connection()
result = conn.execute(s)
row = result.first()
return row[slivers.c.req_rspec]
3
Example 10
def test_round_trip(self):
date_table = self.tables.date_table
config.db.execute(
date_table.insert(),
{'date_data': self.data}
)
row = config.db.execute(
select([
date_table.c.date_data,
])
).first()
compare = self.compare or self.data
eq_(row,
(compare, ))
assert isinstance(row[0], type(compare))
3
Example 11
Project: ibis Source File: test_sqlalchemy.py
def test_where_uncorrelated_subquery(self):
expr = self._case_where_uncorrelated_subquery()
foo = self._to_sqla(self.foo).alias('t0')
bar = self._to_sqla(self.bar)
subq = sa.select([bar.c.job])
stmt = sa.select([foo]).where(foo.c.job.in_(subq))
self._compare_sqla(expr, stmt)
3
Example 12
Project: odo Source File: test_sql.py
def test_empty_select_to_empty_frame():
# data = [(1, 1), (2, 4), (3, 9)]
ds = dshape('var * {x: int, y: int}')
with tmpfile('db') as fn1:
points = resource('sqlite:///%s::points' % fn1, dshape=ds)
sel = sa.select([points])
df = odo(sel, pd.DataFrame)
assert df.empty
assert df.columns.tolist() == ['x', 'y']
3
Example 13
Project: SickRage Source File: test_insert.py
@requirements.fetch_rows_post_commit
def test_explicit_returning_pk_autocommit(self):
engine = config.db
table = self.tables.autoinc_pk
r = engine.execute(
table.insert().returning(
table.c.id),
data="some data"
)
pk = r.first()[0]
fetched_pk = config.db.scalar(select([table.c.id]))
eq_(fetched_pk, pk)
3
Example 14
def rows(self, table_name, columns=None):
"""Return an interable of rows from table `table_name`. If `columns`
is specified then yield only those columns."""
table = self.table(table_name)
if columns:
selection = [table.c[name] for name in columns]
else:
selection = table.columns
select = sa.select(selection)
return self.engine.execute(select)
3
Example 15
Project: SickGear Source File: test_select.py
def test_composed_int_desc(self):
table = self.tables.some_table
lx = (table.c.x + table.c.y).label('lx')
self._assert_result(
select([lx]).order_by(lx.desc()),
[(7, ), (5, ), (3, )]
)
3
Example 16
def __getitem__(self, key):
query = select([self._table], self._key_col == key)
row = self._engine.execute(query).fetchone()
if row is None:
raise KeyError(key)
return self._row_to_value(row)[1]
3
Example 17
Project: plenario Source File: point.py
def _null_malformed_geoms(existing):
# We decide to set the geom to NULL when the given lon/lat is (0,0)
# (off the coast of Africa).
upd = existing.update().values(geom=None).\
where(existing.c.geom == select([func.ST_SetSRID(func.ST_MakePoint(0, 0), 4326)]))
engine.execute(upd)
3
Example 18
@identity.require(turbogears.identity.not_anonymous())
@expose("json", allow_json=True)
def user_id(self):
people = {}
peoplesql = sqlalchemy.select([People.id, People.username])
persons = peoplesql.execute()
for person in persons:
people[person[0]] = person[1]
return dict(people=people)
3
Example 19
def test_plain(self):
table = self.tables.some_table
lx = table.c.x.label('lx')
self._assert_result(
select([lx]).order_by(lx),
[(1, ), (2, ), (3, )]
)
3
Example 20
def getManifest (self, sliver_urn):
s = select([slivers], and_(slivers.c.sliver_urn==sliver_urn, slivers.c.deleted==False))
conn = self.connection()
result = conn.execute(s)
row = result.first()
return row[slivers.c.manifest_rspec]
3
Example 21
def test_sql_select_to_csv(sql, csv):
sql = odo(csv, sql)
query = sa.select([sql.c.a])
with tmpfile('.csv') as fn:
csv = odo(query, fn)
assert odo(csv, list) == [(x,) for x, _ in data]
3
Example 22
Project: odo Source File: test_sql.py
def test_select_to_iterator():
engine, t = single_table_engine()
append(t, [('Alice', 100), ('Bob', 200)])
sel = sa.select([t.c.amount + 1])
assert convert(list, sel) == [(101,), (201,)]
assert convert(list, sel, dshape=dshape('var * int')) == [101, 201]
sel2 = sa.select([sa.sql.func.sum(t.c.amount)])
assert convert(int, sel2, dshape=dshape('int')) == 300
sel3 = sa.select([t])
result = convert(list, sel3, dshape=discover(t))
assert type(result[0]) is tuple
for res in result:
assert isinstance(res[0], string_types)
3
Example 23
Project: SickGear Source File: test_select.py
def test_composed_int(self):
table = self.tables.some_table
lx = (table.c.x + table.c.y).label('lx')
self._assert_result(
select([lx]).order_by(lx),
[(3, ), (5, ), (7, )]
)
3
Example 24
Project: odo Source File: test_sql.py
def test_select_to_series_retains_name():
data = [(1, 1), (2, 4), (3, 9)]
ds = dshape('var * {x: int, y: int}')
with tmpfile('db') as fn1:
points = odo(data, 'sqlite:///%s::points' % fn1, dshape=ds)
sel = sa.select([(points.c.x + 1).label('x')])
series = odo(sel, pd.Series)
assert series.name == 'x'
assert odo(series, list) == [x + 1 for x, _ in data]
3
Example 25
def test_null(self):
date_table = self.tables.date_table
config.db.execute(
date_table.insert(),
{'date_data': None}
)
row = config.db.execute(
select([
date_table.c.date_data,
])
).first()
eq_(row, (None,))
3
Example 26
def test_sort_by(self):
st = self.sa_star1.alias('t0')
cases = self._case_sort_by()
base = sa.select([st])
expected = [
base.order_by(st.c.f),
base.order_by(st.c.f.desc()),
base.order_by(st.c.c, st.c.f.desc()),
]
for case, ex_sqla in zip(cases, expected):
self._compare_sqla(case, ex_sqla)
3
Example 27
Project: ibis Source File: alchemy.py
def _table_column(t, expr):
op = expr.op()
ctx = t.context
table = op.table
sa_table = _get_sqla_table(ctx, table)
out_expr = getattr(sa_table.c, op.name)
# If the column does not originate from the table set in the current SELECT
# context, we should format as a subquery
if t.permit_subquery and ctx.is_foreign_expr(table):
return sa.select([out_expr])
return out_expr
3
Example 28
Project: SickRage Source File: test_insert.py
@requirements.dbapi_lastrowid
def test_native_lastrowid_autoinc(self):
r = config.db.execute(
self.tables.autoinc_pk.insert(),
data="some data"
)
lastrowid = r.lastrowid
pk = config.db.scalar(select([self.tables.autoinc_pk.c.id]))
eq_(
lastrowid, pk
)
3
Example 29
Project: ibis Source File: test_sqlalchemy.py
def test_array_distinct(self):
t = self.alltypes
sat = self.sa_alltypes.alias('t0')
cases = [
(t.string_col.distinct(),
sa.select([sat.c.string_col.distinct()]))
]
for case, ex in cases:
self._compare_sqla(case, ex)
3
Example 30
Project: SickGear Source File: test_select.py
def test_plain_desc(self):
table = self.tables.some_table
lx = table.c.x.label('lx')
self._assert_result(
select([lx]).order_by(lx.desc()),
[(3, ), (2, ), (1, )]
)
3
Example 31
Project: mindpark Source File: reader.py
def _collect_columns(self, engine, table):
result = engine.execute(sql.select([table]))
columns = np.array([x for x in result]).T
if not len(columns) or not columns.shape[1]:
return None
columns = Metric(
id=np.array([int(x, 16) for x in columns[0]]),
timestamp=columns[1],
step=columns[2].astype(int),
epoch=columns[3].astype(int),
training=columns[4].astype(bool),
episode=columns[5].astype(int),
data=columns[6:].T.astype(float))
columns = self._sort_columns(columns)
return columns
3
Example 32
Project: SickRage Source File: test_insert.py
def test_explicit_returning_pk_no_autocommit(self):
engine = config.db
table = self.tables.autoinc_pk
with engine.begin() as conn:
r = conn.execute(
table.insert().returning(
table.c.id),
data="some data"
)
pk = r.first()[0]
fetched_pk = config.db.scalar(select([table.c.id]))
eq_(fetched_pk, pk)
3
Example 33
def select(self, attrs, whereclause=None):
"""Returns a select statement from the star view"""
columns = [self.star.column(attr) for attr in attrs]
return sa.select(columns,
from_obj=self.context.star,
whereclause=whereclause)
3
Example 34
Project: pgcontents Source File: query.py
def list_remote_checkpoints(db, user_id, api_path):
db_path = from_api_filename(api_path)
fields = _remote_checkpoint_default_fields()
results = db.execute(
select(fields).where(
and_(
remote_checkpoints.c.user_id == user_id,
remote_checkpoints.c.path == db_path,
),
).order_by(
desc(remote_checkpoints.c.last_modified),
),
)
return [to_dict_no_content(fields, row) for row in results]
3
Example 35
Project: sqlakeyset Source File: test_paging.py
def do_core_tests(dburl):
spec = ['b', 'd', 'id', 'c']
cols = [column(each) for each in spec]
ob = [OC(x).uo for x in spec]
with S(dburl, echo=ECHO) as s:
selectable = select(
cols,
from_obj=[table('t_Book')],
whereclause=column('d') == 99,
order_by=ob)
check_paging(selectable=selectable, s=s)
3
Example 36
Project: sqlalchemy-redshift Source File: test_delete_stmt.py
def test_delete_stmt_on_subquerycomma():
del_stmt = sa.delete(
ham
).where(
ham.c.id.in_(
sa.select(
[hammy_spam.c.ham_id]
)
)
)
expected = """
DELETE FROM ham
WHERE ham.id IN
(SELECT "ham, spam".ham_id
FROM "ham, spam")"""
assert clean(compile_query(del_stmt)) == clean(expected)
3
Example 37
def __iter__(self):
"""Iterate over table index key values"""
query = select([self._key_col])
result = self._engine.execute(query)
for row in result:
key = row[0]
yield key
3
Example 38
def _test_empty_strings(self):
unicode_table = self.tables.unicode_table
config.db.execute(
unicode_table.insert(),
{"unicode_data": u('')}
)
row = config.db.execute(
select([unicode_table.c.unicode_data])
).first()
eq_(row, (u(''),))
3
Example 39
def pop(self, key):
query = select([self._table], self._key_col == key)
row = self._engine.execute(query).fetchone()
if row is None:
raise KeyError
query = delete(self._table, self._key_col == key)
self._engine.execute(query)
return row
3
Example 40
Project: plenario Source File: ShapeMetadata.py
def _get_num_shapes(self):
table = self.shape_table
# Arbitrarily select the first column of the table to count against
count_query = select([func.count(table.c.geom)])
# Should return only one row.
# And we want the 0th and only attribute of that row (the count).
return session.execute(count_query).fetchone()[0]
3
Example 41
@db_schema.upgrade('entry_list')
def upgrade(ver, session):
if None is ver:
ver = 0
if ver == 0:
table = table_schema('entry_list_entries', session)
table_add_column(table, 'json', Unicode, session)
# Make sure we get the new schema with the added column
table = table_schema('entry_list_entries', session)
for row in session.execute(select([table.c.id, table.c.entry])):
try:
p = pickle.loads(row['entry'])
session.execute(table.update().where(table.c.id == row['id']).values(
json=json.dumps(p, encode_datetime=True)))
except KeyError as e:
log.error('Unable error upgrading entry_list pickle object due to %s' % str(e))
ver = 1
return ver
3
Example 42
Project: pychess Source File: database.py
def get_movetext(self, gameno):
selection = select([game.c.movelist, game.c.comments],
game.c.id == self.games[gameno][0])
result = self.engine.execute(selection).first()
self.comments = result[1].split("|")
arr = array("H")
arr.fromstring(result[0])
return arr
3
Example 43
@db_schema.upgrade('input_cache')
def upgrade(ver, session):
if ver == 0:
table = table_schema('input_cache_entry', session)
table_add_column(table, 'json', Unicode, session)
# Make sure we get the new schema with the added column
table = table_schema('input_cache_entry', session)
for row in session.execute(select([table.c.id, table.c.entry])):
try:
p = pickle.loads(row['entry'])
session.execute(table.update().where(table.c.id == row['id']).values(
json=json.dumps(p, encode_datetime=True)))
except KeyError as e:
log.error('Unable error upgrading input_cache pickle object due to %s' % str(e))
ver = 1
return ver
3
Example 44
@dispatch(Summary, ClauseElement)
def compute_up(t, s, **kwargs):
scope = {t._child: s}
return sa.select(
compute(
value,
scope,
post_compute=None,
return_type='native',
).label(name)
for value, name in zip(t.values, t.fields)
)
3
Example 45
def test_text_empty_strings(self):
text_table = self.tables.text_table
config.db.execute(
text_table.insert(),
{"text_data": ''}
)
row = config.db.execute(
select([text_table.c.text_data])
).first()
eq_(row, ('',))
3
Example 46
def get_statistics(dimension_name=None):
ct = cube_table.table.alias('cube')
st = statistic_table.table.alias('statistic')
rt = reference_table.table.alias('reference')
tables = [ct, st, rt]
wheres = [
st.c.name==ct.c.statistic_name,
rt.c.cube_name==ct.c.name,
rt.c.dimension_name==dimension_name
]
q = select([st], and_(*wheres), tables, distinct=True)
return list(engine.query(q))
3
Example 47
Project: pymssql Source File: test_sqlalchemy.py
def test_pickle_type(self):
s = SAObj(name='foobar', data=['one'])
sess.add(s)
sess.commit()
res = sess.execute(sa.select([saotbl.c.data]))
row = res.fetchone()
eq_(row['data'], ['one'])
3
Example 48
Project: SickGear Source File: test_select.py
def test_composed_multiple(self):
table = self.tables.some_table
lx = (table.c.x + table.c.y).label('lx')
ly = (func.lower(table.c.q) + table.c.p).label('ly')
self._assert_result(
select([lx, ly]).order_by(lx, ly.desc()),
[(3, util.u('q1p3')), (5, util.u('q2p2')), (7, util.u('q3p1'))]
)
3
Example 49
Project: SickRage Source File: test_insert.py
def test_last_inserted_id(self):
r = config.db.execute(
self.tables.autoinc_pk.insert(),
data="some data"
)
pk = config.db.scalar(select([self.tables.autoinc_pk.c.id]))
eq_(
r.inserted_primary_key,
[pk]
)
3
Example 50
Project: ibis Source File: test_sqlalchemy.py
def test_join_just_materialized(self):
joined = self._case_join_just_materialized()
rt, nt, ct = self._sqla_tables(['tpch_region', 'tpch_nation',
'tpch_customer'])
nt = nt.alias('t0')
rt = rt.alias('t1')
ct = ct.alias('t2')
sqla_joined = (nt.join(rt, nt.c.n_regionkey == rt.c.r_regionkey)
.join(ct, nt.c.n_nationkey == ct.c.c_nationkey))
expected = sa.select([sqla_joined])
self._compare_sqla(joined, expected)