def test_group_concat_sqlite_one_arg(db_session):
"""
It should use SQLite's deafult arguments (comma delimiter)
"""
from sqlalchemy import literal_column
from occams_datastore.utils.sql import group_concat
if db_session.bind.url.drivername != 'sqlite':
pytest.skip('Not using SQLite')
data = (
db_session.query(
literal_column("'myitem'").label('name'),
literal_column("'foo'").label('value'))
.union(
db_session.query(
literal_column("'myitem'").label('name'),
literal_column("'bar'").label('value')))
.subquery())
query = (
db_session.query(group_concat(data.c.value))
.select_from(data)
.group_by(data.c.name))
result, = query.one()
assert sorted(['foo', 'bar']) == sorted(result.split(','))
def test_tuple_containment(self):
for test, exp in [
([("a", "b")], True),
([("a", "c")], False),
([("f", "q"), ("a", "b")], True),
([("f", "q"), ("a", "c")], False),
]:
eq_(
testing.db.execute(
select(
[
tuple_(
literal_column("'a'"), literal_column("'b'")
).in_(
[
tuple_(
*[
literal_column("'%s'" % letter)
for letter in elem
]
)
for elem in test
]
)
]
)
).scalar(),
exp,
)
def test_tuple_containment(self):
for test, exp in [
([('a', 'b')], True),
([('a', 'c')], False),
([('f', 'q'), ('a', 'b')], True),
([('f', 'q'), ('a', 'c')], False)
]:
eq_(
testing.db.execute(
select([
tuple_(
literal_column("'a'"),
literal_column("'b'")
).
in_([
tuple_(*[
literal_column("'%s'" % letter)
for letter in elem
]) for elem in test
])
])
).scalar(),
exp
)
def test_row_case_sensitive_unoptimized(self):
ins_db = engines.testing_engine(options={"case_sensitive": True})
row = ins_db.execute(
select([
literal_column("1").label("case_insensitive"),
literal_column("2").label("CaseSensitive"),
text("3 AS screw_up_the_cols")
])
).first()
eq_(
list(row.keys()),
["case_insensitive", "CaseSensitive", "screw_up_the_cols"])
in_("case_insensitive", row._keymap)
in_("CaseSensitive", row._keymap)
not_in_("casesensitive", row._keymap)
eq_(row["case_insensitive"], 1)
eq_(row["CaseSensitive"], 2)
eq_(row["screw_up_the_cols"], 3)
assert_raises(KeyError, lambda: row["Case_insensitive"])
assert_raises(KeyError, lambda: row["casesensitive"])
assert_raises(KeyError, lambda: row["screw_UP_the_cols"])
def test_group_concat_postgresql_invalid_args(db_session):
"""
It should only support at least two arguments in PostgreSQL
"""
from sqlalchemy import literal_column
from occams_datastore.utils.sql import group_concat
if db_session.bind.url.drivername != 'postgresql':
pytest.skip('Not using PostgreSQL')
data = (
db_session.query(
literal_column("'myitem'").label('name'),
literal_column("'foo'").label('value'))
.union(
db_session.query(
literal_column("'myitem'").label('name'),
literal_column("'bar'").label('value')))
.subquery())
query = (
db_session.query(group_concat(data.c.value))
.select_from(data)
.group_by(data.c.name))
with pytest.raises(TypeError):
result, = query.one()
def test_percent_sign_round_trip(self):
"""test that the DBAPI accommodates for escaped / nonescaped
percent signs in a way that matches the compiler
"""
m = self.metadata
t = Table('t', m, Column('data', String(50)))
t.create(config.db)
with config.db.begin() as conn:
conn.execute(t.insert(), dict(data="some % value"))
conn.execute(t.insert(), dict(data="some %% other value"))
eq_(
conn.scalar(
select([t.c.data]).where(
t.c.data == literal_column("'some % value'"))
),
"some % value"
)
eq_(
conn.scalar(
select([t.c.data]).where(
t.c.data == literal_column("'some %% other value'"))
), "some %% other value"
)
def test_row_case_sensitive(self):
row = testing.db.execute(
select([
literal_column("1").label("case_insensitive"),
literal_column("2").label("CaseSensitive")
])
).first()
eq_(list(row.keys()), ["case_insensitive", "CaseSensitive"])
in_("case_insensitive", row._keymap)
in_("CaseSensitive", row._keymap)
not_in_("casesensitive", row._keymap)
eq_(row["case_insensitive"], 1)
eq_(row["CaseSensitive"], 2)
assert_raises(
KeyError,
lambda: row["Case_insensitive"]
)
assert_raises(
KeyError,
lambda: row["casesensitive"]
)
def test_text_doesnt_explode(self):
for s in [
select(
[
case(
[
(
info_table.c.info == 'pk_4_data',
text("'yes'"))],
else_=text("'no'"))
]).order_by(info_table.c.info),
select(
[
case(
[
(
info_table.c.info == 'pk_4_data',
literal_column("'yes'"))],
else_=literal_column("'no'")
)]
).order_by(info_table.c.info),
]:
if testing.against("firebird"):
eq_(s.execute().fetchall(), [
('no ', ), ('no ', ), ('no ', ), ('yes', ),
('no ', ), ('no ', ),
])
else:
eq_(s.execute().fetchall(), [
('no', ), ('no', ), ('no', ), ('yes', ),
('no', ), ('no', ),
])
def execute(self, connection, filter_values):
max_date_query = sqlalchemy.select([
sqlalchemy.func.max(sqlalchemy.column('completed_on')).label('completed_on'),
sqlalchemy.column('case_id').label('case_id')
]).select_from(sqlalchemy.table(self.table_name))
if self.filters:
for filter in self.filters:
max_date_query.append_whereclause(filter.build_expression())
max_date_query.append_group_by(
sqlalchemy.column('case_id')
)
max_date_subquery = sqlalchemy.alias(max_date_query, 'max_date')
asha_table = self.get_asha_table_name()
checklist_query = sqlalchemy.select()
for column in self.columns:
checklist_query.append_column(column.build_column())
checklist_query = checklist_query.where(
sqlalchemy.literal_column('"{}".case_id'.format(asha_table)) == max_date_subquery.c.case_id
).where(
sqlalchemy.literal_column('"{}".completed_on'.format(asha_table)) == max_date_subquery.c.completed_on
).select_from(sqlalchemy.table(asha_table))
return connection.execute(checklist_query, **filter_values).fetchall()
def on_insert_also(self):
Foo2 = sql.t.Foo2
n_column = sqlalchemy.literal_column('1').label('n')
return (Foo2.insert().values(n=sqlalchemy.literal_column('new.id'),
foo=sqlalchemy.literal_column('new.description')),
sql.InsertFromSelect(Foo2, sqlalchemy.select([n_column])),
"select 42",
)
def test_select_composition_seven(self):
self.assert_compile(
select([
literal_column('col1'),
literal_column('col2')
], from_obj=table('tablename')).alias('myalias'),
"SELECT col1, col2 FROM tablename"
)
def test_select_composition_seven(self):
self.assert_compile(
select(
[literal_column("col1"), literal_column("col2")],
from_obj=table("tablename"),
).alias("myalias"),
"SELECT col1, col2 FROM tablename",
)
def _update_current_rev(self, old, new):
if old == new:
return
if new is None:
self.impl._exec(self._version.delete())
elif old is None:
self.impl._exec(self._version.insert().values(version_num=literal_column("'%s'" % new)))
else:
self.impl._exec(self._version.update().values(version_num=literal_column("'%s'" % new)))
def data(self,
use_choice_labels=False,
expand_collections=False,
ignore_private=True):
session = self.db_session
query = (
session.query(
models.Patient.id.label('id'),
models.Site.name.label('site'),
models.Patient.pid.label('pid'))
.join(models.Site))
# BBB 2014-02-20 (Marco): AEH needs Early Test
EarlyTest = aliased(models.Enrollment)
subquery = (
session.query(EarlyTest.patient_id, EarlyTest.reference_number)
.filter(EarlyTest.study.has(
models.Study.code.in_([literal_column("'ET'"),
literal_column("'LTW'"),
literal_column("'CVCT'")])))
.subquery())
query = (
query
.outerjoin(subquery, subquery.c.patient_id == models.Patient.id)
.add_column(subquery.c.reference_number.label('early_id')))
# Add every known reference number
for reftype in self.reftypes:
query = query.add_column(
session.query(
group_concat(
models.PatientReference.reference_number, ';'))
.filter(
models.PatientReference.patient_id == models.Patient.id)
.filter(
models.PatientReference.reference_type_id == reftype.id)
.group_by(models.PatientReference.patient_id)
.correlate(models.Patient)
.as_scalar()
.label(reftype.name))
CreateUser = aliased(datastore.User)
ModifyUser = aliased(datastore.User)
query = (
query
.join(CreateUser, models.Patient.create_user)
.join(ModifyUser, models.Patient.modify_user)
.add_columns(
models.Patient.create_date,
CreateUser.key.label('create_user'),
models.Patient.modify_date,
ModifyUser.key.label('modify_user'))
.order_by(models.Patient.id))
return query
def search_query(cls, tokens, weight_func=None, include_misses=False, ordered=True):
# Read the searchable columns from the table (strings)
columns = cls.__searchable_columns__
# Convert the columns from strings into column objects
columns = [getattr(cls, c) for c in columns]
# The model name that can be used to match search result to model
cls_name = literal_column("'{}'".format(cls.__name__))
# Filter out id: tokens for later
ids, tokens = process_id_option(tokens)
# If there are still tokens left after id: token filtering
if tokens:
# Generate the search weight expression from the
# searchable columns, tokens and patterns
if not weight_func:
weight_func = weight_expression
weight = weight_func(columns, tokens)
# If the search expression only included "special" tokens like id:
else:
weight = literal_column(str(1))
# Create an array of stringified detail columns
details = getattr(cls, "__search_detail_columns__", None)
if details:
details = [cast(getattr(cls, d), Unicode) for d in details]
else:
details = [literal_column("NULL")]
# Create a query object
query = db.session.query(
cls_name.label("model"),
cls.id.label("id"),
cls.name.label("name"),
array(details).label("details"),
weight.label("weight"),
)
# Filter out specific ids (optional)
if ids:
query = query.filter(cls.id.in_(ids))
# Filter out results that don't match the patterns at all (optional)
if not include_misses:
query = query.filter(weight > 0)
# Order by weight (optional)
if ordered:
query = query.order_by(desc(weight))
return query
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
def get_next_bus(mc, db, stop_id):
now_datetime = datetime.datetime.utcnow().replace(tzinfo=UTC)
now_datetime = LONDON.normalize(now_datetime.astimezone(LONDON))
now_day = now_datetime.weekday()
now_time = now_datetime.time()
mc_key = "V6:USERSTOP:" + str(stop_id) + "USERTIME:" + now_datetime.strftime("%w%H%M")
bus = mc.get(mc_key)
if not bus:
today_query = db.query(DepartureTimeDeref, literal_column("0").label("days_future")).\
filter_by(bus_stop_id=stop_id).\
filter(DepartureTimeDeref.time >= now_time).\
filter(DepartureTimeDeref.valid_days.contains(cast([DAYS[now_day]], postgresql.ARRAY(String)))).\
join(DepartureTimeDeref.timetable).\
filter(Timetable.valid_from <= now_datetime.date()).\
filter(Timetable.valid_to >= now_datetime.date())
single_day_delta = datetime.timedelta(days=1)
tomorrow_query = db.query(DepartureTimeDeref, literal_column("1").label("days_future")).\
filter_by(bus_stop_id=stop_id).\
filter(DepartureTimeDeref.valid_days.contains(cast([DAYS[now_day + 1]], postgresql.ARRAY(String)))).\
join(DepartureTimeDeref.timetable).\
filter(Timetable.valid_from <= now_datetime.date() + single_day_delta).\
filter(Timetable.valid_to >= now_datetime.date() + single_day_delta)
bus = today_query.union_all(tomorrow_query).\
options(joinedload(DepartureTimeDeref.timetable, Timetable.route)).\
order_by("days_future").\
order_by(DepartureTimeDeref.time).\
first()
if bus:
bus = {'departure': bus[0].to_JSON(), 'days_future': int(bus[1])}
mc.set(mc_key, bus, 30*24*60*60)
if bus:
departure = bus['departure']
# Create a day delta, is this departure time today or tomorrow?
day_delta = datetime.timedelta(days=bus['days_future'])
departure_day = now_datetime.date() + day_delta
departure_dt = datetime.datetime.combine(departure_day, departure['time'])
# Add timezone infomation. pytz will handle DST correctly
departure_dt = LONDON.localize(departure_dt)
departure['time'] = departure_dt
bus = departure
return bus
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
if agg_unit == 'quarter':
step = '3 months'
else:
step = '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')
# Create a CTE that grabs the number of records
# contained in each time bucket.
# Will only have rows for buckets with records.
where_filters = [t.c.point_date >= start,
t.c.point_date <= end]
if column_filters:
where_filters += column_filters
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
def _update_current_rev(self, old, new):
if old == new: # pragma: no cover
return
if new is None: # pragma: no cover
self.impl._exec(Version.__table__.delete().where(package=self.pkg_name))
elif old is None:
self.impl._exec(
Version.__table__.insert().values(package=self.pkg_name, version_num=sqla.literal_column("'%s'" % new))
)
else:
self.impl._exec(
Version.__table__.update().values(package=self.pkg_name, version_num=sqla.literal_column("'%s'" % new))
)
def test_select_composition_six(self):
# test that "auto-labeling of subquery columns"
# doesn't interfere with literal columns,
# exported columns don't get quoted
self.assert_compile(
select([
literal_column("column1 AS foobar"),
literal_column("column2 AS hoho"), table1.c.myid],
from_obj=[table1]).select(),
"SELECT column1 AS foobar, column2 AS hoho, myid FROM "
"(SELECT column1 AS foobar, column2 AS hoho, "
"mytable.myid AS myid FROM mytable)"
)
def test_select_composition_one(self):
self.assert_compile(select(
[
literal_column("foobar(a)"),
literal_column("pk_foo_bar(syslaal)")
],
text("a = 12"),
from_obj=[
text("foobar left outer join lala on foobar.foo = lala.foo")
]
),
"SELECT foobar(a), pk_foo_bar(syslaal) FROM foobar "
"left outer join lala on foobar.foo = lala.foo WHERE a = 12"
)
def test_query_one(self):
q = Session.query(User).\
filter(User.name == 'ed').\
options(joinedload(User.addresses))
q2 = serializer.loads(
serializer.dumps(q, -1),
users.metadata, Session)
def go():
eq_(q2.all(), [
User(name='ed', addresses=[Address(id=2),
Address(id=3), Address(id=4)])])
self.assert_sql_count(testing.db, go, 1)
eq_(q2.join(User.addresses).filter(Address.email
== '*****@*****.**').value(func.count(literal_column('*'))), 1)
u1 = Session.query(User).get(8)
q = Session.query(Address).filter(Address.user
== u1).order_by(desc(Address.email))
q2 = serializer.loads(serializer.dumps(q, -1), users.metadata,
Session)
eq_(q2.all(), [Address(email='*****@*****.**'),
Address(email='*****@*****.**'),
Address(email='*****@*****.**')])
def test_endswith_literal_mysql(self):
self.assert_compile(
column('x').endswith(literal_column('y')),
"x LIKE concat('%%', y)",
checkparams={},
dialect=mysql.dialect()
)
def migrate_claims(migrate_engine, metadata, buildrequests, objects,
buildrequest_claims):
# First, ensure there is an object row for each master
null_id = sa.null().label('id')
if migrate_engine.dialect.name == 'postgresql':
# postgres needs NULL cast to an integer:
null_id = sa.cast(null_id, sa.INTEGER)
new_objects = sa.select([
null_id,
buildrequests.c.claimed_by_name.label("name"),
sa.literal_column("'BuildMaster'").label("class_name"),
],
whereclause=buildrequests.c.claimed_by_name != NULL,
distinct=True)
# this doesn't seem to work without str() -- verified in sqla 0.6.0 - 0.7.1
migrate_engine.execute(
str(sautils.InsertFromSelect(objects, new_objects)))
# now make a buildrequest_claims row for each claimed build request
join = buildrequests.join(objects,
(buildrequests.c.claimed_by_name == objects.c.name)
# (have to use sa.text because str, below, doesn't work
# with placeholders)
& (objects.c.class_name == sa.text("'BuildMaster'")))
claims = sa.select([
buildrequests.c.id.label('brid'),
objects.c.id.label('objectid'),
buildrequests.c.claimed_at,
], from_obj=[join],
whereclause=buildrequests.c.claimed_by_name != NULL)
migrate_engine.execute(
str(sautils.InsertFromSelect(buildrequest_claims, claims)))
def test_startswith_literal_mysql(self):
self.assert_compile(
column("x").startswith(literal_column("y")),
"x LIKE concat(y, '%%')",
checkparams={},
dialect=mysql.dialect(),
)
def list_notifications(self, tenant_id, sort_by, offset, limit):
rows = []
with self._db_engine.connect() as conn:
nm = self.nm
select_nm_query = (select([nm])
.where(nm.c.tenant_id == bindparam('b_tenant_id')))
parms = {'b_tenant_id': tenant_id}
if sort_by is not None:
order_columns = [literal_column(col) for col in sort_by]
if 'id' not in sort_by:
order_columns.append(nm.c.id)
else:
order_columns = [nm.c.id]
select_nm_query = select_nm_query.order_by(*order_columns)
select_nm_query = (select_nm_query
.order_by(nm.c.id)
.limit(bindparam('b_limit')))
parms['b_limit'] = limit + 1
if offset:
select_nm_query = select_nm_query.offset(bindparam('b_offset'))
parms['b_offset'] = offset
rows = conn.execute(select_nm_query, parms).fetchall()
return [dict(row) for row in rows]
def update_market_history():
session = DBSession()
with transaction.manager:
last_tick = session.query(func.max(MarketHistoryElement.ticks)).scalar()
current_date = datetime.datetime.now(tzlocal())
current_tick = int((current_date - MarketHistoryElement.START_ERA).total_seconds() / MarketHistoryElement.TICK_SECONDS_LENGTH)
assert last_tick <= current_tick
if last_tick == current_tick:
logger.debug("Skipping update to market history: tick %d already saved.", current_tick)
return
origin_select = session.\
query(Item.data_id,
literal_column(str(current_tick)),
Item.buy_count,
Item.buy_price,
Item.sell_count,
Item.sell_price).\
filter(Item.buy_count > 0, Item.sell_count > 0)
i = insert(MarketHistoryElement).from_select([
MarketHistoryElement.item_id,
MarketHistoryElement.ticks,
MarketHistoryElement.buy_count,
MarketHistoryElement.buy_price,
MarketHistoryElement.sell_count,
MarketHistoryElement.sell_price
], origin_select)
logger.debug("Executing market history insert...")
i.execute()
logger.debug("Saved market data for tick %d.", current_tick)
def migrate_claims(migrate_engine, metadata, buildrequests, objects, buildrequest_claims):
# First, ensure there is an object row for each master
new_objects = sa.select(
[
sa.null().label("id"),
buildrequests.c.claimed_by_name.label("name"),
sa.literal_column("'BuildMaster'").label("class_name"),
],
whereclause=buildrequests.c.claimed_by_name != None,
distinct=True,
)
# this doesn't seem to work without str() -- verified in sqla 0.6.0 - 0.7.1
migrate_engine.execute(str(sautils.InsertFromSelect(objects, new_objects)))
# now make a buildrequest_claims row for each claimed build request
join = buildrequests.join(
objects,
(buildrequests.c.claimed_by_name == objects.c.name)
# (have to use sa.text because str, below, doesn't work
# with placeholders)
& (objects.c.class_name == sa.text("'BuildMaster'")),
)
claims = sa.select(
[buildrequests.c.id.label("brid"), objects.c.id.label("objectid"), buildrequests.c.claimed_at],
from_obj=[join],
whereclause=buildrequests.c.claimed_by_name != None,
)
migrate_engine.execute(str(sautils.InsertFromSelect(buildrequest_claims, claims)))
def test_contains_literal_concat(self):
self.assert_compile(
column('x').contains(literal_column('y')),
"x LIKE concat(concat('%%', y), '%%')",
checkparams={},
dialect=mysql.dialect()
)
def copr_cleanup(session, older_than):
session.log.debug('Cleaning up old copr projects')
interval = "now() - '{} month'::interval".format(older_than)
to_delete_ids = session.db.query(CoprRebuildRequest.id)\
.filter(CoprRebuildRequest.timestamp < literal_column(interval))\
.all_flat()
if to_delete_ids:
rebuilds = session.db.query(CoprRebuild)\
.filter(CoprRebuild.request_id.in_(to_delete_ids))\
.filter(CoprRebuild.copr_build_id != None)\
.all()
for rebuild in rebuilds:
try:
copr_client.delete_project(
username=get_config('copr.copr_owner'),
projectname=rebuild.copr_name,
)
except CoprException as e:
if 'does not exist' not in str(e):
session.log.warn("Cannot delete copr project {}: {}"
.format(rebuild.copr_name, e))
if rebuild.request_id in to_delete_ids:
to_delete_ids.remove(rebuild.request_id)
if to_delete_ids:
session.db.query(CoprRebuildRequest)\
.filter(CoprRebuildRequest.id.in_(to_delete_ids))\
.delete()
for request_id in to_delete_ids:
shutil.rmtree(get_request_cachedir(request_id), ignore_errors=True)
session.log_user_action(
"Cleanup: Deleted {} copr requests"
.format(len(to_delete_ids))
)
def test_startswith_sqlexpr(self):
col = self.tables.some_table.c.data
self._test(col.startswith(literal_column("'ab%c'")),
{1, 2, 3, 4, 5, 6, 7, 8, 9, 10})
def test_startswith_literal(self):
self.assert_compile(column('x').startswith(literal_column('y')),
"x LIKE y || '%%'",
checkparams={})
def test_endswith_literal(self):
self.assert_compile(column('x').endswith(literal_column('y')),
"x LIKE '%%' || y",
checkparams={})
def test_pg_time_expression_literal_1y_grain(self):
col = literal_column("COALESCE(a, b)")
expr = PostgresEngineSpec.get_timestamp_expr(col, None, "P1Y")
result = str(expr.compile(dialect=postgresql.dialect()))
self.assertEqual(result, "DATE_TRUNC('year', COALESCE(a, b))")
def _modify_query(query):
m1(query.column_descriptions[0]["entity"])
query = query.enable_assertions(False).filter(
literal_column("1") == 1
)
return query
def elevation(self, oid, segments=None, **params):
if segments is not None and segments.isdigit():
segments = int(segments)
if segments > 500 or segments <= 0:
segments = 500
else:
segments = 100
ret = OrderedDict()
ret['id'] = oid
r = cherrypy.request.app.config['DB']['map'].tables.routes.data
gen = sa.select([sa.func.generate_series(0, segments).label('i')
]).alias()
field = sa.func.ST_LineInterpolatePoint(r.c.geom,
gen.c.i / float(segments))
field = sa.func.ST_Collect(field)
sel = sa.select([field]).where(r.c.id == oid)\
.where(r.c.geom.ST_GeometryType() == 'ST_LineString')
res = cherrypy.request.db.execute(sel).first()
if res is not None and res[0] is not None:
geom = to_shape(res[0])
xcoord, ycoord = zip(*((p.x, p.y) for p in geom))
geomlen = LineString(geom).length
pos = [geomlen * i / float(segments) for i in range(segments)]
compute_elevation(((xcoord, ycoord, pos), ), geom.bounds, ret)
return ret
# special treatment for multilinestrings
sel = sa.select([r.c.geom,
sa.literal_column("""ST_Length2dSpheroid(ST_MakeLine(ARRAY[ST_Points(ST_Transform(geom,4326))]),
'SPHEROID[\"WGS 84\",6378137,298.257223563,AUTHORITY["EPSG",\"7030\"]]')"""),
r.c.geom.ST_NPoints()])\
.where(r.c.id == oid)
res = cherrypy.request.db.execute(sel).first()
if res is not None and res[0] is not None:
geom = to_shape(res[0])
if res[2] > 10000:
geom = geom.simplify(res[2] / 500, preserve_topology=False)
elif res[2] > 4000:
geom = geom.simplify(res[2] / 1000, preserve_topology=False)
segments = []
for seg in geom:
p = seg.coords[0]
xcoords = array('d', [p[0]])
ycoords = array('d', [p[1]])
pos = array('d')
if segments:
prev = segments[-1]
pos.append(prev[2][-1] + \
Point(prev[0][-1], prev[1][-1]).distance(Point(*p)))
else:
pos.append(0.0)
for p in seg.coords[1:]:
pos.append(
pos[-1] +
Point(xcoords[-1], ycoords[-1]).distance(Point(*p)))
xcoords.append(p[0])
ycoords.append(p[1])
segments.append((xcoords, ycoords, pos))
compute_elevation(segments, geom.bounds, ret)
ret['length'] = float(res[1])
return ret
raise cherrypy.NotFound()
def test_contains_literal_concat(self):
self.assert_compile(column('x').contains(literal_column('y')),
"x LIKE concat(concat('%%', y), '%%')",
checkparams={},
dialect=mysql.dialect())
def test_endswith_sqlexpr(self):
col = self.tables.some_table.c.data
self._test(col.endswith(literal_column("'e%fg'")),
{1, 2, 3, 4, 5, 6, 7, 8, 9})
def test_insert_w_newlines(self, connection):
from psycopg2 import extras
t = self.tables.data
ins = (t.insert().inline().values(
id=bindparam("id"),
x=select(literal_column("5")).select_from(
self.tables.data).scalar_subquery(),
y=bindparam("y"),
z=bindparam("z"),
))
# compiled SQL has a newline in it
eq_(
str(ins.compile(testing.db)),
"INSERT INTO data (id, x, y, z) VALUES (%(id)s, "
"(SELECT 5 \nFROM data), %(y)s, %(z)s)",
)
meth = extras.execute_values
with mock.patch.object(extras, "execute_values",
side_effect=meth) as mock_exec:
connection.execute(
ins,
[
{
"id": 1,
"y": "y1",
"z": 1
},
{
"id": 2,
"y": "y2",
"z": 2
},
{
"id": 3,
"y": "y3",
"z": 3
},
],
)
eq_(
mock_exec.mock_calls,
[
mock.call(
mock.ANY,
"INSERT INTO data (id, x, y, z) VALUES %s",
(
{
"id": 1,
"y": "y1",
"z": 1
},
{
"id": 2,
"y": "y2",
"z": 2
},
{
"id": 3,
"y": "y3",
"z": 3
},
),
template="(%(id)s, (SELECT 5 \nFROM data), %(y)s, %(z)s)",
fetch=False,
page_size=connection.dialect.executemany_values_page_size,
)
],
)
def define_tables(cls, metadata):
default_generator = cls.default_generator = {"x": 50}
def mydefault():
default_generator["x"] += 1
return default_generator["x"]
def myupdate_with_ctx(ctx):
conn = ctx.connection
return conn.execute(sa.select(sa.text("13"))).scalar()
def mydefault_using_connection(ctx):
conn = ctx.connection
return conn.execute(sa.select(sa.text("12"))).scalar()
use_function_defaults = testing.against("postgresql", "mssql")
is_oracle = testing.against("oracle")
class MyClass(object):
@classmethod
def gen_default(cls, ctx):
return "hi"
class MyType(TypeDecorator):
impl = String(50)
def process_bind_param(self, value, dialect):
if value is not None:
value = "BIND" + value
return value
cls.f = 6
cls.f2 = 11
with testing.db.connect() as conn:
currenttime = cls.currenttime = func.current_date(type_=sa.Date)
if is_oracle:
ts = conn.scalar(
sa.select(
func.trunc(
func.current_timestamp(),
sa.literal_column("'DAY'"),
type_=sa.Date,
)
)
)
currenttime = cls.currenttime = func.trunc(
currenttime, sa.literal_column("'DAY'"), type_=sa.Date
)
def1 = currenttime
def2 = func.trunc(
sa.text("current_timestamp"),
sa.literal_column("'DAY'"),
type_=sa.Date,
)
deftype = sa.Date
elif use_function_defaults:
def1 = currenttime
deftype = sa.Date
if testing.against("mssql"):
def2 = sa.text("getdate()")
else:
def2 = sa.text("current_date")
ts = conn.scalar(func.current_date())
else:
def1 = def2 = "3"
ts = 3
deftype = Integer
cls.ts = ts
Table(
"default_test",
metadata,
# python function
Column("col1", Integer, primary_key=True, default=mydefault),
# python literal
Column(
"col2",
String(20),
default="imthedefault",
onupdate="im the update",
),
# preexecute expression
Column(
"col3",
Integer,
default=func.length("abcdef"),
onupdate=func.length("abcdefghijk"),
),
# SQL-side default from sql expression
Column("col4", deftype, server_default=def1),
# SQL-side default from literal expression
Column("col5", deftype, server_default=def2),
# preexecute + update timestamp
Column("col6", sa.Date, default=currenttime, onupdate=currenttime),
Column("boolcol1", sa.Boolean, default=True),
Column("boolcol2", sa.Boolean, default=False),
# python function which uses ExecutionContext
Column(
"col7",
Integer,
default=mydefault_using_connection,
onupdate=myupdate_with_ctx,
),
# python builtin
Column(
"col8",
sa.Date,
default=datetime.date.today,
onupdate=datetime.date.today,
),
# combo
Column("col9", String(20), default="py", server_default="ddl"),
# python method w/ context
Column("col10", String(20), default=MyClass.gen_default),
# fixed default w/ type that has bound processor
Column("col11", MyType(), default="foo"),
)
def test_select_composition_seven(self):
self.assert_compile(
select([literal_column('col1'),
literal_column('col2')],
from_obj=table('tablename')).alias('myalias'),
"SELECT col1, col2 FROM tablename")
def test_contains_literal(self):
self.assert_compile(column('x').contains(literal_column('y')),
"x LIKE '%%' || y || '%%'",
checkparams={})
def connection_block(field: ASTNode, parent_name: typing.Optional[str]) -> Alias:
return_type = field.return_type
sqla_model = return_type.sqla_model
block_name = secure_random_string()
if parent_name is None:
join_conditions = [True]
else:
join_conditions = to_join_clause(field, parent_name)
filter_conditions = to_conditions_clause(field)
limit = to_limit(field)
has_total = check_has_total(field)
is_page_after = "after" in field.args
is_page_before = "before" in field.args
totalCount_alias = field.get_subfield_alias(["totalCount"])
edges_alias = field.get_subfield_alias(["edges"])
node_alias = field.get_subfield_alias(["edges", "node"])
cursor_alias = field.get_subfield_alias(["edges", "cursor"])
pageInfo_alias = field.get_subfield_alias(["pageInfo"])
hasNextPage_alias = field.get_subfield_alias(["pageInfo", "hasNextPage"])
hasPreviousPage_alias = field.get_subfield_alias(["pageInfo", "hasPreviousPage"])
startCursor_alias = field.get_subfield_alias(["pageInfo", "startCursor"])
endCursor_alias = field.get_subfield_alias(["pageInfo", "endCursor"])
# Apply Filters
core_model = sqla_model.__table__
core_model_ref = (
select(core_model.c)
.select_from(core_model)
.where(
and_(
# Join clause
*join_conditions,
# Conditions
*filter_conditions,
)
)
).alias(block_name)
new_edge_node_selects = []
new_relation_selects = []
for subfield in get_edge_node_fields(field):
# Does anything other than NodeID go here?
if subfield.return_type == ID:
# elem = select([to_node_id_sql(sqla_model, core_model_ref)]).label(subfield.alias)
elem = to_node_id_sql(sqla_model, core_model_ref).label(subfield.alias)
new_edge_node_selects.append(elem)
elif isinstance(subfield.return_type, (ScalarType, CompositeType, EnumType)):
col_name = field_name_to_column(sqla_model, subfield.name).name
elem = core_model_ref.c[col_name].label(subfield.alias)
new_edge_node_selects.append(elem)
else:
elem = build_relationship(subfield, block_name)
new_relation_selects.append(elem)
# Setup Pagination
args = field.args
after_cursor = args.get("after", None)
before_cursor = args.get("before", None)
first = args.get("first", None)
last = args.get("last", None)
if first is not None and last is not None:
raise ValueError('only one of "first" and "last" may be provided')
if after_cursor or before_cursor:
local_table_name = get_table_name(field.return_type.sqla_model)
cursor_table_name = before_cursor.table_name if before_cursor else after_cursor.table_name
cursor_values = before_cursor.values if before_cursor else after_cursor.values
if after_cursor is not None and before_cursor is not None:
raise ValueError('only one of "before" and "after" may be provided')
if after_cursor is not None and last is not None:
raise ValueError('"after" is not compatible with "last". Use "first"')
if before_cursor is not None and first is not None:
raise ValueError('"before" is not compatible with "first". Use "last"')
if cursor_table_name != local_table_name:
raise ValueError("Invalid cursor for entity type")
pkey_cols = get_primary_key_columns(sqla_model)
pagination_clause = tuple_(*[core_model_ref.c[col.name] for col in pkey_cols]).op(
">" if after_cursor is not None else "<"
)(tuple_(*[cursor_values[col.name] for col in pkey_cols]))
else:
pagination_clause = True
order_clause = [asc(core_model_ref.c[col.name]) for col in get_primary_key_columns(sqla_model)]
reverse_order_clause = [desc(core_model_ref.c[col.name]) for col in get_primary_key_columns(sqla_model)]
total_block = (
select([func.count(ONE).label("total_count")]).select_from(core_model_ref.alias()).where(has_total)
).alias(block_name + "_total")
node_id_sql = to_node_id_sql(sqla_model, core_model_ref)
cursor_sql = to_cursor_sql(sqla_model, core_model_ref)
# Select the right stuff
p1_block = (
select(
[
*new_edge_node_selects,
*new_relation_selects,
# For internal Use
node_id_sql.label("_nodeId"),
cursor_sql.label("_cursor"),
# For internal Use
func.row_number().over().label("_row_num"),
]
)
.select_from(core_model_ref)
.where(pagination_clause)
.order_by(*(reverse_order_clause if is_page_before else order_clause), *order_clause)
.limit(cast(limit + 1, Integer()))
).alias(block_name + "_p1")
# Drop maybe extra row
p2_block = (
select([*p1_block.c, (func.max(p1_block.c._row_num).over() > limit).label("_has_next_page")])
.select_from(p1_block)
.limit(limit)
).alias(block_name + "_p2")
ordering = desc(literal_column("_row_num")) if is_page_before else asc(literal_column("_row_num"))
p3_block = (select(p2_block.c).select_from(p2_block).order_by(ordering)).alias(block_name + "_p3")
final = (
select(
[
func.jsonb_build_object(
literal_string(totalCount_alias),
func.coalesce(func.min(total_block.c.total_count), ZERO) if has_total else None,
literal_string(pageInfo_alias),
func.jsonb_build_object(
literal_string(hasNextPage_alias),
func.coalesce(func.array_agg(p3_block.c._has_next_page)[ONE], FALSE),
literal_string(hasPreviousPage_alias),
TRUE if is_page_after else FALSE,
literal_string(startCursor_alias),
func.array_agg(p3_block.c._nodeId)[ONE],
literal_string(endCursor_alias),
func.array_agg(p3_block.c._nodeId)[func.array_upper(func.array_agg(p3_block.c._nodeId), ONE)],
),
literal_string(edges_alias),
func.coalesce(
func.jsonb_agg(
func.jsonb_build_object(
literal_string(cursor_alias),
p3_block.c._nodeId,
literal_string(node_alias),
func.cast(func.row_to_json(literal_column(p3_block.name)), JSONB()),
)
),
func.cast(literal("[]"), JSONB()),
),
).label("ret_json")
]
)
.select_from(p3_block)
.select_from(total_block if has_total else select([1]).alias())
).alias()
return final
def test_insert_modified_by_event(self, connection):
from psycopg2 import extras
t = self.tables.data
ins = (
t.insert()
.inline()
.values(
id=bindparam("id"),
x=select(literal_column("5"))
.select_from(self.tables.data)
.scalar_subquery(),
y=bindparam("y"),
z=bindparam("z"),
)
)
# compiled SQL has a newline in it
eq_(
str(ins.compile(testing.db)),
"INSERT INTO data (id, x, y, z) VALUES (%(id)s, "
"(SELECT 5 \nFROM data), %(y)s, %(z)s)",
)
meth = extras.execute_batch
with mock.patch.object(
extras, "execute_values"
) as mock_values, mock.patch.object(
extras, "execute_batch", side_effect=meth
) as mock_batch:
# create an event hook that will change the statement to
# something else, meaning the dialect has to detect that
# insert_single_values_expr is no longer useful
@event.listens_for(
connection, "before_cursor_execute", retval=True
)
def before_cursor_execute(
conn, cursor, statement, parameters, context, executemany
):
statement = (
"INSERT INTO data (id, y, z) VALUES "
"(%(id)s, %(y)s, %(z)s)"
)
return statement, parameters
connection.execute(
ins,
[
{"id": 1, "y": "y1", "z": 1},
{"id": 2, "y": "y2", "z": 2},
{"id": 3, "y": "y3", "z": 3},
],
)
eq_(mock_values.mock_calls, [])
if connection.dialect.executemany_mode & EXECUTEMANY_BATCH:
eq_(
mock_batch.mock_calls,
[
mock.call(
mock.ANY,
"INSERT INTO data (id, y, z) VALUES "
"(%(id)s, %(y)s, %(z)s)",
(
{"id": 1, "y": "y1", "z": 1},
{"id": 2, "y": "y2", "z": 2},
{"id": 3, "y": "y3", "z": 3},
),
)
],
)
else:
eq_(mock_batch.mock_calls, [])
def check_has_total(field: ASTNode) -> bool:
"Check if 'totalCount' is requested in the query result set"
return any(x.name in "totalCount" for x in field.fields)
def get_edge_node_fields(field):
"""Returns connection.edge.node fields"""
for cfield in field.fields:
if cfield.name == "edges":
for edge_field in cfield.fields:
if edge_field.name == "node":
return edge_field.fields
return []
ONE = literal_column("1")
ZERO = literal_column("0")
TRUE = literal_column("true")
FALSE = literal_column("false")
def connection_block(field: ASTNode, parent_name: typing.Optional[str]) -> Alias:
return_type = field.return_type
sqla_model = return_type.sqla_model
block_name = secure_random_string()
if parent_name is None:
join_conditions = [True]
else:
join_conditions = to_join_clause(field, parent_name)
def _insert_version(self, version):
assert version not in self.heads
self.heads.add(version)
self.context.impl._exec(self.context._version.insert().values(
version_num=literal_column("'%s'" % version)))
def setup_class(cls):
global t, f, f2, ts, currenttime, metadata, default_generator
db = testing.db
metadata = MetaData(db)
default_generator = {'x': 50}
def mydefault():
default_generator['x'] += 1
return default_generator['x']
def myupdate_with_ctx(ctx):
conn = ctx.connection
return conn.execute(sa.select([sa.text('13')])).scalar()
def mydefault_using_connection(ctx):
conn = ctx.connection
try:
return conn.execute(sa.select([sa.text('12')])).scalar()
finally:
# ensure a "close()" on this connection does nothing,
# since its a "branched" connection
conn.close()
use_function_defaults = testing.against('postgresql', 'mssql')
is_oracle = testing.against('oracle')
class MyClass(object):
@classmethod
def gen_default(cls, ctx):
return "hi"
# select "count(1)" returns different results on different DBs also
# correct for "current_date" compatible as column default, value
# differences
currenttime = func.current_date(type_=sa.Date, bind=db)
if is_oracle:
ts = db.scalar(
sa.select([
func.trunc(func.sysdate(),
sa.literal_column("'DAY'"),
type_=sa.Date).label('today')
]))
assert isinstance(
ts, datetime.date) and not isinstance(ts, datetime.datetime)
f = sa.select([func.length('abcdef')], bind=db).scalar()
f2 = sa.select([func.length('abcdefghijk')], bind=db).scalar()
# TODO: engine propigation across nested functions not working
currenttime = func.trunc(currenttime,
sa.literal_column("'DAY'"),
bind=db,
type_=sa.Date)
def1 = currenttime
def2 = func.trunc(sa.text("sysdate"),
sa.literal_column("'DAY'"),
type_=sa.Date)
deftype = sa.Date
elif use_function_defaults:
f = sa.select([func.length('abcdef')], bind=db).scalar()
f2 = sa.select([func.length('abcdefghijk')], bind=db).scalar()
def1 = currenttime
deftype = sa.Date
if testing.against('mssql'):
def2 = sa.text("getdate()")
else:
def2 = sa.text("current_date")
ts = db.scalar(func.current_date())
else:
f = len('abcdef')
f2 = len('abcdefghijk')
def1 = def2 = "3"
ts = 3
deftype = Integer
t = Table(
'default_test1',
metadata,
# python function
Column('col1', Integer, primary_key=True, default=mydefault),
# python literal
Column('col2',
String(20),
default="imthedefault",
onupdate="im the update"),
# preexecute expression
Column('col3',
Integer,
default=func.length('abcdef'),
onupdate=func.length('abcdefghijk')),
# SQL-side default from sql expression
Column('col4', deftype, server_default=def1),
# SQL-side default from literal expression
Column('col5', deftype, server_default=def2),
# preexecute + update timestamp
Column('col6', sa.Date, default=currenttime, onupdate=currenttime),
Column('boolcol1', sa.Boolean, default=True),
Column('boolcol2', sa.Boolean, default=False),
# python function which uses ExecutionContext
Column('col7',
Integer,
default=mydefault_using_connection,
onupdate=myupdate_with_ctx),
# python builtin
Column('col8',
sa.Date,
default=datetime.date.today,
onupdate=datetime.date.today),
# combo
Column('col9', String(20), default='py', server_default='ddl'),
# python method w/ context
Column('col10', String(20), default=MyClass.gen_default))
t.create()
def literal_string(text):
return literal_column(f"'{text}'")
ops.Power: fixed_arity(sa.func._ibis_sqlite_power, 2),
ops.Exp: unary(sa.func._ibis_sqlite_exp),
ops.Ln: unary(sa.func._ibis_sqlite_ln),
ops.Log: _log,
ops.Log10: unary(sa.func._ibis_sqlite_log10),
ops.Log2: unary(sa.func._ibis_sqlite_log2),
ops.Floor: unary(sa.func._ibis_sqlite_floor),
ops.Ceil: unary(sa.func._ibis_sqlite_ceil),
ops.Sign: unary(sa.func._ibis_sqlite_sign),
ops.FloorDivide: fixed_arity(sa.func._ibis_sqlite_floordiv, 2),
ops.Modulus: fixed_arity(sa.func._ibis_sqlite_mod, 2),
ops.Variance: _variance_reduction('_ibis_sqlite_var'),
ops.StandardDev: toolz.compose(
sa.func._ibis_sqlite_sqrt, _variance_reduction('_ibis_sqlite_var')
),
ops.RowID: lambda t, expr: sa.literal_column('rowid'),
}
)
def add_operation(op, translation_func):
_operation_registry[op] = translation_func
class SQLiteExprTranslator(alch.AlchemyExprTranslator):
_registry = _operation_registry
_rewrites = alch.AlchemyExprTranslator._rewrites.copy()
_type_map = alch.AlchemyExprTranslator._type_map.copy()
_type_map.update({dt.Double: sa.types.REAL, dt.Float: sa.types.REAL})
conn.scalar(raw_sql) # $ SPURIOUS: getSql=raw_sql
except sqlalchemy.exc.ObjectNotExecutableError:
pass
scalar_result = conn.scalar(text_sql) # $ getSql=text_sql
assert scalar_result == "FOO"
scalar_result = conn.scalar(statement=text_sql) # $ getSql=text_sql
assert scalar_result == "FOO"
# This is a contrived example
select = sqlalchemy.select(
sqlalchemy.text("'BAR'")) # $ constructedSql="'BAR'"
result = conn.execute(select) # $ getSql=select
assert result.fetchall() == [("BAR", )]
# This is a contrived example
select = sqlalchemy.select(sqlalchemy.literal_column("'BAZ'"))
result = conn.execute(select) # $ getSql=select
assert result.fetchall() == [("BAZ", )]
with future_engine.connect() as conn:
result = conn.execute(text_sql) # $ getSql=text_sql
assert result.fetchall() == [("FOO", )]
# `begin` returns a new Connection object with a transaction begun.
print("v2.0 engine.begin")
with engine.begin() as conn:
result = conn.execute(text_sql) # $ getSql=text_sql
assert result.fetchall() == [("FOO", )]
# construction by object
conn = sqlalchemy.future.Connection(engine)
def test_endswith_literal_mysql(self):
self.assert_compile(column('x').endswith(literal_column('y')),
"x LIKE concat('%%', y)",
checkparams={},
dialect=mysql.dialect())
async def test_operations_on_group_classifiers(
pg_engine: Engine, classifiers_bundle: Dict
):
# NOTE: mostly for TDD
async with pg_engine.acquire() as conn:
# creates a group
stmt = (
groups.insert()
.values(**random_group(name="MyGroup"))
.returning(groups.c.gid)
)
gid = await conn.scalar(stmt)
# adds classifiers to a group
stmt = (
group_classifiers.insert()
.values(bundle=classifiers_bundle, gid=gid)
.returning(literal_column("*"))
)
result = await conn.execute(stmt)
row = await result.first()
assert row
assert row[group_classifiers.c.gid] == gid
assert row[group_classifiers.c.bundle] == classifiers_bundle
# get bundle in one query
bundle = await conn.scalar(
sa.select([group_classifiers.c.bundle]).where(
group_classifiers.c.gid == gid
)
)
assert bundle
assert classifiers_bundle == bundle
# Cannot add more than one classifier's bundle to the same group
# pylint: disable=no-member
with pytest.raises(psycopg2.errors.UniqueViolation):
await conn.execute(group_classifiers.insert().values(bundle={}, gid=gid))
# deleting a group deletes the classifier
await conn.execute(groups.delete().where(groups.c.gid == gid))
groups_count = await conn.scalar(sa.select(func.count(groups.c.gid)))
classifiers_count = await conn.scalar(
sa.select([func.count()]).select_from(group_classifiers)
)
assert (
groups_count == 1
), "There should be only the Everyone group in the database!"
assert classifiers_count <= groups_count
assert classifiers_count == 0
# no bundle
bundle = await conn.scalar(
sa.select([group_classifiers.c.bundle]).where(
group_classifiers.c.gid == gid
)
)
assert bundle is None
import pytest
import sqlalchemy as sa
from db_mock import DBMockSync
from db_mock.utils import _normalize
QUERIES = [
"select * from users",
sa.select([sa.literal_column("*")]).select_from(sa.table("users")),
]
@pytest.fixture(params=QUERIES)
def query(request):
return request.param
def test_normalize(query):
import sqlalchemy.engine.default as default
assert _normalize(query, default.DefaultDialect()) == ("""SELECT *
FROM users""")
def test_blocking(query):
mock = (DBMockSync().expect_stmt(query).expect_returns([
(1, "User 1"), (2, "User 2")
]).expect_stmt(
"update users set password = '******' where id = 1;").expect_rowcount(
1).expect_stmt("select count(*) from users").expect_returns([
(5, )
def create_aux_pfamA_pfamseqid():
from sqlalchemy.orm import aliased
tablename = "pfamjoinpfamseq"
m = MetaData(engine)
if not engine.dialect.has_table(engine, tablename):
# t.drop(engine) # to delete/drop table
t = Table(tablename, m,
Column('pfamseq_id', String(40), primary_key=True),
Column('pfamA_acc', String(7), primary_key=True, index=True),
Column('pfamseq_acc', String(40)),
Column('has_pdb', Boolean, unique=False, default=False))
t.create(engine)
pfams = db.query(PfamA.pfamA_acc).all()
for pf in pfams:
# for pf in ["PF00451", "PF00452", "PF00453"]:
print pf[0]
pfam = pf[0]
# pfam = pf
query = db.query(
concat(Pfamseq.pfamseq_id, '/',
cast(PfamARegFullSignificant.seq_start,
types.Unicode), '-',
cast(PfamARegFullSignificant.seq_end,
types.Unicode)), PfamARegFullSignificant.pfamA_acc,
PfamARegFullSignificant.pfamseq_acc)
query = query.join(
PfamARegFullSignificant,
and_(
PfamARegFullSignificant.in_full == 1,
Pfamseq.pfamseq_acc == PfamARegFullSignificant.pfamseq_acc,
PfamARegFullSignificant.pfamA_acc == pfam))
### add column with pdb
subquery2 = db.query(PdbPfamAReg.pfamseq_acc)
subquery2 = subquery2.filter(
PdbPfamAReg.pfamA_acc == pfam).distinct().subquery()
query_pdb = query.filter(
PfamARegFullSignificant.pfamseq_acc == subquery2.c.pfamseq_acc)
subquery_pdb = query_pdb.subquery()
query = query.filter(
PfamARegFullSignificant.pfamseq_acc.notin_(subquery2))
# print query.add_columns(literal_column("0").label("has_pdb")).distinct().all()
# print "################"
# print "################"
# print query_pdb.add_columns(literal_column("1").label("has_pdb")).distinct().all()
query = query.add_columns(
literal_column("0").label("has_pdb")).distinct()
query_pdb = query_pdb.add_columns(
literal_column("1").label("has_pdb")).distinct()
query_union = query.union(query_pdb)
if query_union.first():
engine.execute(t.insert().values(tuple(query_union.all())))
return ["Succesfully created pfamjoinpfamseq"]
else:
pfamjoinpfamseq = Table(tablename, m, autoload=True)
mapper(Pfamjoinpfamseq, pfamjoinpfamseq)
return ["pfamjoinpfamseq table exists, creating mapper"]
def read_all(
isActive=None,
isFavourite=None,
isSandbox=None,
namesonly=None,
page=None,
page_size=None,
sort=None,
):
"""
This function responds to a request for /api/solutions
with the complete lists of solutions
:return: json string of list of solutions
"""
logger.debug("solution.read_all")
logger.debug(
"Parameters: isActive: %s, isFavourite: %s, isSandbox: %s, namesonly: %s, page: %s, page_size: %s, sort: %s",
isActive,
isFavourite,
isSandbox,
namesonly,
page,
page_size,
sort,
)
with db_session() as dbs:
# pre-process sort instructions
if sort is None:
solution_query = dbs.query(Solution).order_by(Solution.id)
else:
try:
sort_inst = [si.split(":") for si in sort]
orderby_arr = []
for si in sort_inst:
si1 = si[0]
if len(si) > 1:
si2 = si[1]
else:
si2 = "asc"
orderby_arr.append(f"{si1} {si2}")
# print("orderby: {}".format(orderby_arr))
solution_query = dbs.query(Solution).order_by(
literal_column(", ".join(orderby_arr)))
except SQLAlchemyError as e:
logger.warning("Exception: %s", e)
solution_query = dbs.query(Solution).order_by(Solution.id)
# Create the list of solutions from our data
solution_query = solution_query.filter(
(isActive is None or Solution.isActive == isActive),
(isFavourite is None or Solution.isFavourite == isFavourite),
(isSandbox is None or Solution.isSandbox == isSandbox),
)
# do limit and offset last
if page is None or page_size is None:
solutions = solution_query.all()
else:
solutions = solution_query.limit(page_size).offset(page *
page_size)
if namesonly is True:
# Serialize the data for the response
schema = SolutionNamesOnlySchema(many=True)
data = schema.dump(solutions)
else:
for sol in solutions:
sol = solution_extension.expand_solution(sol, dbsession=dbs)
schema = ExtendedSolutionSchema(many=True)
data = schema.dump(solutions)
logger.debug("read_all: %s", data)
return data, 200
