class Genre(db.Model):
__ftcolumns__ = 'name, raw_desc'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(4096))
image = db.Column(db.String(4096))
desc = db.Column(db.Text)
raw_desc = db.Column(db.Text)
games = db.relationship('Game', secondary=game_genre_assoc, back_populates='genres')
direct_events = db.relationship('Event', secondary=event_genre_assoc)
indirect_events = db.relationship('Event',
viewonly=True,
secondary=join(game_genre_assoc, event_game_assoc,
game_genre_assoc.c.game_id == event_game_assoc.c.game_id).alias())
developers = db.relationship('Developer',
viewonly=True,
secondary=join(game_genre_assoc, game_developer_assoc,
game_genre_assoc.c.game_id == game_developer_assoc.c.game_id).alias())
def json(self):
return {'id': self.id,
'img': self.image,
'name': self.name,
'desc': self.desc,
'games': [(game.id, game.primary_name) for game in self.games],
'events': [(event.id, event.name) for event in self.events],
'developers': [(developer.id, developer.name) for developer in self.developers]
}
@hybrid_property
def events(self):
return self.direct_events or self.indirect_events
def _get_select_waypoints_for_routes():
waypoint_type = text('\'' + WAYPOINT_TYPE + '\'')
route_type = text('\'' + ROUTE_TYPE + '\'')
select_linked_waypoints = \
select([
Association.child_document_id.label('route_id'),
Association.parent_document_id.label('waypoint_id')
]). \
where(
and_(
Association.parent_document_type == waypoint_type,
Association.child_document_type == route_type)). \
cte('linked_waypoints')
select_waypoint_parents = \
select([
select_linked_waypoints.c.route_id,
Association.parent_document_id.label('waypoint_id')
]). \
select_from(join(
select_linked_waypoints,
Association,
and_(
Association.child_document_id ==
select_linked_waypoints.c.waypoint_id,
Association.parent_document_type == waypoint_type
))). \
cte('waypoint_parents')
select_waypoint_grandparents = \
select([
select_waypoint_parents.c.route_id,
Association.parent_document_id.label('waypoint_id')
]). \
select_from(join(
select_waypoint_parents,
Association,
and_(
Association.child_document_id ==
select_waypoint_parents.c.waypoint_id,
Association.parent_document_type == waypoint_type
))). \
cte('waypoint_grandparents')
return union(
select_linked_waypoints.select(),
select_waypoint_parents.select(),
select_waypoint_grandparents.select()
). \
cte('all_waypoints')
def _get_select_waypoints_for_routes():
waypoint_type = text('\'' + WAYPOINT_TYPE + '\'')
route_type = text('\'' + ROUTE_TYPE + '\'')
select_linked_waypoints = \
select([
Association.child_document_id.label('route_id'),
Association.parent_document_id.label('waypoint_id')
]). \
where(
and_(
Association.parent_document_type == waypoint_type,
Association.child_document_type == route_type)). \
cte('linked_waypoints')
select_waypoint_parents = \
select([
select_linked_waypoints.c.route_id,
Association.parent_document_id.label('waypoint_id')
]). \
select_from(join(
select_linked_waypoints,
Association,
and_(
Association.child_document_id ==
select_linked_waypoints.c.waypoint_id,
Association.parent_document_type == waypoint_type
))). \
cte('waypoint_parents')
select_waypoint_grandparents = \
select([
select_waypoint_parents.c.route_id,
Association.parent_document_id.label('waypoint_id')
]). \
select_from(join(
select_waypoint_parents,
Association,
and_(
Association.child_document_id ==
select_waypoint_parents.c.waypoint_id,
Association.parent_document_type == waypoint_type
))). \
cte('waypoint_grandparents')
return union(
select_linked_waypoints.select(),
select_waypoint_parents.select(),
select_waypoint_grandparents.select()
). \
cte('all_waypoints')
def _resource_consumption_stats_view():
time_difference_expr = func.sum(KojiTask.finished - KojiTask.started)
time_difference = extract('EPOCH', time_difference_expr)
time_difference_all = select([time_difference]).select_from(KojiTask)
return (select([
Package.name,
KojiTask.arch,
time_difference_expr.label('time'),
cast(time_difference / time_difference_all,
Float).label('time_percentage'),
]).select_from(
join(
join(Package, Build, Package.id == Build.package_id),
KojiTask,
)).group_by(Package.name, KojiTask.arch))
def _build_query(self, filter_values):
having = []
filter_cols = []
external_cols = _get_grouping(filter_values)
for fil in self.filters:
if fil.column_name not in ['group', 'gender', 'group_leadership', 'disaggregate_by', 'table_card_group_by']:
if fil.column_name not in external_cols and fil.column_name != 'maxmin':
filter_cols.append(fil.column_name)
having.append(fil.build_expression())
group_having = ''
having_group_by = []
if ('disaggregate_by' in filter_values and filter_values['disaggregate_by'] == 'group') or ('table_card_group_by' in filter_values and filter_values['table_card_group_by']):
group_having = "group_leadership=\'Y\'"
having_group_by.append('group_leadership')
elif 'group_leadership' in filter_values and filter_values['group_leadership']:
group_having = "(MAX(CAST(gender as int4)) + MIN(CAST(gender as int4))) = :group_leadership and group_leadership=\'Y\'"
having_group_by.append('group_leadership')
filter_cols.append('group_leadership')
elif 'gender' in filter_values and filter_values['gender']:
group_having = "(MAX(CAST(gender as int4)) + MIN(CAST(gender as int4))) = :gender"
table_card_group = []
if 'group_name' in self.group_by:
table_card_group.append('group_name')
s1 = alias(select(['doc_id', 'group_id', 'MAX(prop_value) + MIN(prop_value) as maxmin'] + filter_cols + external_cols,
from_obj='"fluff_FarmerRecordFluff"',
group_by=['doc_id', 'group_id'] + filter_cols + external_cols), name='x')
s2 = alias(select(['group_id', '(MAX(CAST(gender as int4)) + MIN(CAST(gender as int4))) as gender'] + table_card_group, from_obj='"fluff_FarmerRecordFluff"',
group_by=['group_id'] + table_card_group + having_group_by, having=group_having), name='y')
return select(['COUNT(x.doc_id) as %s' % self.key] + self.group_by,
group_by=['maxmin'] + filter_cols + self.group_by,
having=" and ".join(having),
from_obj=join(s1, s2, s1.c.group_id==s2.c.group_id)).params(filter_values)
def list_files(project_name):
files = db.session.query(File.name).select_from(join(
File, Project)).filter(Project.user_id == current_user.id,
Project.name == project_name).all()
file_names = [f[0] for f in files]
return jsonify(file_names)
def build_query_to_populate(self, query, full_table, aggregate_table):
insert_columns = [aggregate_table.c.join_key]
fk = Column(self.key, Integer)
geom = Column(self.geometry_column, Geometry())
bins_table = Table(self.table, full_table.metadata, fk, geom)
if self.join_custom_data:
extra_data = Table("extra_data", full_table.metadata,
Column("verified", Boolean),
Column("timestamp", DateTime),
Column("client_ip", Integer),
Column("server_ip", Integer),
Column("location", Geometry("Point", srid=4326)),
keep_existing = True)
joining = join(full_table, extra_data,
and_(extra_data.c.client_ip == full_table.c.client_ip,
extra_data.c.server_ip == full_table.c.server_ip,
extra_data.c.timestamp == full_table.c.time),
isouter = True)
query = query.select_from(joining)
location = case([(extra_data.c.verified, func.coalesce(extra_data.c.location, full_table.c.location))], else_ = full_table.c.location)
else:
location = full_table.c.location
select_query = (query.select_from(bins_table)
.where(ST_Intersects(location, geom))
.column(fk)
.group_by(fk))
return insert_columns, select_query
def build_query_to_populate(self, query, full_table, aggregate_table):
insert_columns = [aggregate_table.c.join_key]
fk = Column(self.key, Integer)
geom = Column(self.geometry_column, Geometry())
bins_table = Table(self.table, full_table.metadata, fk, geom)
if self.join_custom_data:
extra_data = Table("extra_data", full_table.metadata,
Column("timestamp", DateTime),
Column("verified", Boolean),
Column("bigquery_key", String),
Column("connection_type", String),
Column("advertised_download", Integer),
Column("actual_download", Float),
Column("advertised_upload", Integer),
Column("actual_upload", Float),
Column("min_rtt", Integer),
Column("location_type", String),
Column("cost_of_service", Integer),
Column("location", Geometry("Point", srid=4326)),
keep_existing = True)
joining = join(full_table, extra_data,
and_(extra_data.c.bigquery_key == full_table.c.bigquery_key),
isouter = True)
query = query.select_from(joining)
location = case([(extra_data.c.verified, func.coalesce(extra_data.c.location, full_table.c.location))], else_ = full_table.c.location)
else:
location = full_table.c.location
select_query = (query.select_from(bins_table)
.where(ST_Intersects(location, geom))
.column(fk)
.group_by(fk))
return insert_columns, select_query
class Developer(db.Model):
__ftcolumns__ = 'name, raw_desc, website'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(4096))
image = db.Column(db.String(4096))
desc = db.Column(db.Text)
raw_desc = db.Column(db.Text)
website = db.Column(db.String(4096))
games = db.relationship('Game', secondary=game_developer_assoc, back_populates='developers')
genres = db.relationship('Genre',
viewonly=True,
secondary=join(game_developer_assoc, game_genre_assoc,
game_developer_assoc.c.game_id == game_genre_assoc.c.game_id).alias())
def json(self):
return {'id': self.id,
'img': self.image,
'name': self.name,
'desc': self.desc,
'games': [(game.id, game.primary_name) for game in self.games],
'genres': [(genre.id, genre.name) for genre in self.genres],
'website': self.website
}
def _build_query(self, table, filter_values):
having = []
filter_cols = []
external_cols = _get_grouping(filter_values)
for fil in self.filters:
if isinstance(fil, ANDFilter):
filter_cols.append(fil.filters[0].column_name)
having.append(fil)
elif isinstance(fil, RawFilter):
having.append(fil)
elif fil.column_name not in ['group', 'gender', 'group_leadership', 'disaggregate_by',
'table_card_group_by']:
if fil.column_name not in external_cols and fil.column_name != 'maxmin':
filter_cols.append(fil.column_name)
having.append(fil)
group_having = ''
having_group_by = []
if ('disaggregate_by' in filter_values and filter_values['disaggregate_by'] == 'group') or \
(filter_values.get('table_card_group_by') == 'group_leadership'):
having_group_by.append('group_leadership')
elif 'group_leadership' in filter_values and filter_values['group_leadership']:
group_having = "(MAX(CAST(gender as int4)) + MIN(CAST(gender as int4))) " \
"= :group_leadership and group_leadership=\'Y\'"
having_group_by.append('group_leadership')
filter_cols.append('group_leadership')
elif 'gender' in filter_values and filter_values['gender']:
group_having = "(MAX(CAST(gender as int4)) + MIN(CAST(gender as int4))) = :gender"
table_card_group = []
if 'group_name' in self.group_by:
table_card_group.append('group_name')
s1 = alias(select([table.c.doc_id, table.c.group_case_id, table.c.group_name, table.c.group_id,
(sqlalchemy.func.max(table.c.prop_value) +
sqlalchemy.func.min(table.c.prop_value)).label('maxmin')] + filter_cols +
external_cols, from_obj=table,
group_by=([table.c.doc_id, table.c.group_case_id, table.c.group_name, table.c.group_id] +
filter_cols + external_cols)), name='x')
s2 = alias(
select(
[table.c.group_case_id,
sqlalchemy.cast(
cast(func.max(table.c.gender), Integer) + cast(func.min(table.c.gender), Integer), VARCHAR
).label('gender')] + table_card_group,
from_obj=table,
group_by=[table.c.group_case_id] + table_card_group + having_group_by, having=group_having
), name='y'
)
group_by = list(self.group_by)
if 'group_case_id' in group_by:
group_by[group_by.index('group_case_id')] = s1.c.group_case_id
group_by[group_by.index('group_name')] = s1.c.group_name
return select(
[sqlalchemy.func.count(s1.c.doc_id).label(self.key)] + group_by,
group_by=[s1.c.maxmin] + filter_cols + group_by,
having=AND(having).build_expression(s1),
from_obj=join(s1, s2, s1.c.group_case_id == s2.c.group_case_id)
).params(filter_values)
def rebuild_idnum_index(cls, session: SqlASession,
indexed_at_utc: Pendulum) -> None:
"""
Rebuilds the index entirely. Uses SQLAlchemy Core (not ORM) for speed.
Args:
session: an SQLAlchemy Session
indexed_at_utc: current time in UTC
"""
log.info("Rebuilding patient ID number index")
# noinspection PyUnresolvedReferences
indextable = PatientIdNumIndexEntry.__table__ # type: Table
indexcols = indextable.columns
# noinspection PyUnresolvedReferences
idnumtable = PatientIdNum.__table__ # type: Table
idnumcols = idnumtable.columns
# noinspection PyUnresolvedReferences
patienttable = Patient.__table__ # type: Table
patientcols = patienttable.columns
# Delete all entries
session.execute(
indextable.delete()
)
# Create new ones
# noinspection PyProtectedMember,PyPep8
session.execute(
indextable.insert().from_select(
# Target:
[indexcols.idnum_pk,
indexcols.indexed_at_utc,
indexcols.patient_pk,
indexcols.which_idnum,
indexcols.idnum_value],
# Source:
(
select([idnumcols._pk,
literal(indexed_at_utc),
patientcols._pk,
idnumcols.which_idnum,
idnumcols.idnum_value])
.select_from(
join(
idnumtable,
patienttable,
and_(
idnumcols._device_id == patientcols._device_id,
idnumcols._era == patientcols._era,
idnumcols.patient_id == patientcols.id,
)
)
)
.where(idnumcols._current == True)
.where(patientcols._current == True)
)
)
)
def upgrade():
"""Upgrade database schema and/or data, creating a new revision."""
# connection = op.get_bind()
src_dst_snapshots = join(relationships, snapshots,
relationships.c.destination_id == snapshots.c.id)
sel1 = select([relationships.c.id]).select_from(src_dst_snapshots)\
.where(and_(snapshots.c.child_type == relationships.c.source_type, # noqa
relationships.c.destination_type == "Snapshot"))
dst_src_snapshots = join(relationships, snapshots,
relationships.c.source_id == snapshots.c.id)
sel2 = select([relationships.c.id]).select_from(dst_src_snapshots)\
.where(and_(snapshots.c.child_type == relationships.c.destination_type, # noqa
relationships.c.source_type == "Snapshot"))
op.execute(relationships.delete().where(
relationships.c.id.in_(
sel1.union_all(sel2).alias("delete_it").select())))
def upgrade():
"""Upgrade database schema and/or data, creating a new revision."""
# connection = op.get_bind()
src_dst_snapshots = join(relationships, snapshots,
relationships.c.destination_id == snapshots.c.id)
sel1 = select([relationships.c.id]).select_from(src_dst_snapshots)\
.where(and_(snapshots.c.child_type == relationships.c.source_type, # noqa
relationships.c.destination_type == "Snapshot"))
dst_src_snapshots = join(relationships, snapshots,
relationships.c.source_id == snapshots.c.id)
sel2 = select([relationships.c.id]).select_from(dst_src_snapshots)\
.where(and_(snapshots.c.child_type == relationships.c.destination_type, # noqa
relationships.c.source_type == "Snapshot"))
op.execute(relationships.delete().where(relationships.c.id.in_(
sel1.union_all(sel2).alias("delete_it").select()
)))
def _last_build():
max_expr = select([func.max(Build.id).label('mx')])\
.group_by(Build.package_id).alias()
joined = select([Build]).select_from(join(Build, max_expr,
Build.id == max_expr.c.mx))\
.alias()
return relationship(mapper(Build, joined, non_primary=True),
uselist=False,
primaryjoin=(Package.id == joined.c.package_id))
def migrate_data(self):
join_expr = join(
proposalhistory_table, proposal_table,
proposalhistory_table.c.proposal_id == proposal_table.c.id)
statement = select(
[proposalhistory_table, proposal_table]).select_from(join_expr)
result_rows = self.execute(statement).fetchall()
for result_row in result_rows:
PropsosalHistoryEntryMigrator(result_row).run()
def migrate_data(self):
join_expr = join(
proposalhistory_table, proposal_table,
proposalhistory_table.c.proposal_id == proposal_table.c.id)
statement = select([proposalhistory_table,
proposal_table]).select_from(join_expr)
result_rows = self.execute(statement).fetchall()
for result_row in result_rows:
PropsosalHistoryEntryMigrator(result_row).run()
def _resource_consumption_stats_view():
time_difference_expr = func.sum(KojiTask.finished - KojiTask.started)
time_difference = extract('EPOCH', time_difference_expr)
time_difference_all = select([time_difference]).select_from(KojiTask)
return (
select([
Package.name,
KojiTask.arch,
time_difference_expr.label('time'),
cast(time_difference / time_difference_all, Float).label('time_percentage'),
])
.select_from(
join(
join(Package, Build, Package.id == Build.package_id),
KojiTask,
)
)
.group_by(Package.name, KojiTask.arch)
)
def unsubscribe(path):
path = str(path)
project_name, file_name = path.split('/', 1)
file_id = db.session.query(File.id).select_from(join(
File, Project)).filter(File.name == file_name,
Project.name == project_name,
Project.user_id == current_user.id).scalar()
if file_id:
leave_room("file:" + str(file_id), namespace="/files")
print('>User {} unsubscribed from {} ({})'.format(
current_user.username, path, "file:" + str(file_id)))
def get_projects(self):
data = join(db.user_projects, db.projects,
db.user_projects.c.project_id == db.projects.c.id
).select(use_labels=True).where(
db.user_projects.c.user_id == self.id
).execute()
rows = data.fetchall()
projects = []
for row in rows:
projects.append(Project.init(**row))
return projects
def get_all_for_project(cls, project_id):
data = join(db.user_projects, db.users,
db.user_projects.c.user_id == db.users.c.id
).select(use_labels=True).where(
db.user_projects.c.project_id == project_id
).execute()
rows = data.fetchall()
users = []
for row in rows:
users.append(User.init(**row))
return users
def access_list_paths(self,
member,
prefix=None,
include_owned=False,
include_containers=True):
"""Return the list of paths granted to member.
Keyword arguments:
prefix -- return only paths starting with prefix (default None)
include_owned -- return also paths owned by member (default False)
include_containers -- return also container paths owned by member
(default True)
"""
xfeatures_xfeaturevals = self.xfeatures.join(self.xfeaturevals)
selectable = (self.groups.c.owner + ':' + self.groups.c.name)
member_groups = select([selectable.label('value')],
self.groups.c.member == member)
members = select([literal(member).label('value')])
any = select([literal('*').label('value')])
u = union(member_groups, members, any).alias()
inner_join = join(xfeatures_xfeaturevals, u,
self.xfeaturevals.c.value == u.c.value)
s = select([self.xfeatures.c.path], from_obj=[inner_join]).distinct()
if prefix:
like = lambda p: self.xfeatures.c.path.like(
self.escape_like(p) + '%', escape=ESCAPE_CHAR)
s = s.where(
or_(*map(like,
self.access_inherit(prefix) or [prefix])))
r = self.conn.execute(s)
l = [row[0] for row in r.fetchall()]
r.close()
if include_owned:
container_nodes = select(
[self.nodes.c.node],
self.nodes.c.parent == self.node_lookup(member))
condition = self.nodes.c.parent.in_(container_nodes)
if include_containers:
condition = or_(condition,
self.nodes.c.node.in_(container_nodes))
s = select([self.nodes.c.path], condition)
r = self.conn.execute(s)
l += [row[0] for row in r.fetchall() if row[0] not in l]
r.close()
return l
def _collect_joins(self):
"""Collect joins and register joined tables. All tables used should be collected in this
function."""
self.logger.info("collecting joins and registering tables...")
self.tables = {}
self.expression = self.fact_table
self.tables[self.fact_name] = self.fact_table
if not self.cube.joins:
self.logger.info("no joins")
return
for join in self.cube.joins:
self.logger.debug("join: %s" % join)
# Get master and detail table names and their respective keys that will be used for join
master_name, master_key = self.split_field(join["master"])
if not master_name:
master_name = self.fact_name
detail_name, detail_key = self.split_field(join["detail"])
alias = join.get("alias")
if not detail_name or detail_name == self.fact_name:
raise ValueError(
"Detail table name should be present and should not be a fact table"
)
master_table = self.table(master_name)
detail_table = self.register_table(detail_name,
alias=alias,
schema=self.schema)
try:
master_column = master_table.c[master_key]
except:
raise Exception('Unable to find master key "%s"."%s" ' %
(master_name, master_key))
try:
detail_column = detail_table.c[detail_key]
except:
raise Exception('Unable to find master key "%s"."%s" ' %
(detail_name, detail_key))
onclause = master_column == detail_column
self.expression = expression.join(self.expression,
detail_table,
onclause=onclause)
def test_sql_expression_join(self):
table_1 = self._make_table('table_1',
Column('x', types.UInt32, primary_key=True))
table_2 = self._make_table('table_2',
Column('x', types.UInt32, primary_key=True))
join = expression.join(table_1,
table_2,
onclause=table_1.c.x == table_2.c.x,
isouter=False)
self.assertEqual(
self.compile(join),
'table_1 INNER JOIN table_2 ON table_1.x = table_2.x')
def associate(config):
engine = create_engine(config.database_uri)
metadata = MetaData()
records = config.make_cache_table(metadata)
extra_data = config.make_extra_data_table(metadata)
metadata.create_all(engine)
joining = join(records, extra_data, and_(extra_data.c.bigquery_key == records.c.bigquery_key))
query = select([joining, extra_data.c.id.label('extra_data_id')])
results = engine.execute(query)
p = re.compile('^(.*):[0-9]{4,5}\.[cs]2[cs]_snaplog\.gz$')
for result in results.fetchall():
test_id = p.match(result.test_id).group(1)
query = extra_data.update().where(extra_data.c.id == result.extra_data_id).values(bigquery_test_id = test_id)
engine.execute(query)
def associate(config):
engine = create_engine(config.database_uri)
metadata = MetaData()
records = config.make_cache_table(metadata)
extra_data = config.make_extra_data_table(metadata)
metadata.create_all(engine)
joining = join(records, extra_data, and_(extra_data.c.bigquery_key == records.c.bigquery_key))
query = select([joining, extra_data.c.id.label('extra_data_id')])
results = engine.execute(query)
p = re.compile('^(.*):[0-9]{4,5}\.[cs]2[cs]_snaplog\.gz$')
for result in results.fetchall():
test_id = p.match(result.test_id).group(1)
query = extra_data.update().where(extra_data.c.id == result.extra_data_id).values(bigquery_test_id = test_id)
engine.execute(query)
def access_list_paths(self, member, prefix=None, include_owned=False,
include_containers=True):
"""Return the list of paths granted to member.
Keyword arguments:
prefix -- return only paths starting with prefix (default None)
include_owned -- return also paths owned by member (default False)
include_containers -- return also container paths owned by member
(default True)
"""
xfeatures_xfeaturevals = self.xfeatures.join(self.xfeaturevals)
selectable = (self.groups.c.owner + ':' + self.groups.c.name)
member_groups = select([selectable.label('value')],
self.groups.c.member == member)
members = select([literal(member).label('value')])
any = select([literal('*').label('value')])
u = union(member_groups, members, any).alias()
inner_join = join(xfeatures_xfeaturevals, u,
self.xfeaturevals.c.value == u.c.value)
s = select([self.xfeatures.c.path], from_obj=[inner_join]).distinct()
if prefix:
like = lambda p: self.xfeatures.c.path.like(
self.escape_like(p) + '%', escape=ESCAPE_CHAR)
s = s.where(or_(*map(like,
self.access_inherit(prefix) or [prefix])))
r = self.conn.execute(s)
l = [row[0] for row in r.fetchall()]
r.close()
if include_owned:
container_nodes = select(
[self.nodes.c.node],
self.nodes.c.parent == self.node_lookup(member))
condition = self.nodes.c.parent.in_(container_nodes)
if include_containers:
condition = or_(condition,
self.nodes.c.node.in_(container_nodes))
s = select([self.nodes.c.path], condition)
r = self.conn.execute(s)
l += [row[0] for row in r.fetchall() if row[0] not in l]
r.close()
return l
def get_existing_id_lookup(self):
url_table = table(
"urls", column('id'), column('label'), column('contact_id'))
contact_table = table(
"contacts",
column('id'), column('former_contact_id'))
stmt = select([
url_table.c.id, url_table.c.label, url_table.c.contact_id,
contact_table.c.former_contact_id])
stmt = stmt.select_from(
join(url_table, contact_table,
url_table.c.contact_id == contact_table.c.id))
return {self.get_identifier(gever_row): gever_row.id
for gever_row in self.db_session.execute(stmt)}
def get_existing_id_lookup(self):
address_table = table("addresses", column('id'), column('label'),
column('contact_id'))
contact_table = table("contacts", column('id'),
column('former_contact_id'))
stmt = select([
address_table.c.id, address_table.c.label,
address_table.c.contact_id, contact_table.c.former_contact_id
])
stmt = stmt.select_from(
join(address_table, contact_table,
address_table.c.contact_id == contact_table.c.id))
return {
self.get_identifier(gever_row): gever_row.id
for gever_row in self.db_session.execute(stmt)
}
def _collect_joins(self):
"""Collect joins and register joined tables. All tables used should be collected in this
function."""
self.logger.info("collecting joins and registering tables...")
self.tables = {}
self.expression = self.fact_table
self.tables[self.fact_name] = self.fact_table
if not self.cube.joins:
self.logger.info("no joins")
return
for join in self.cube.joins:
self.logger.debug("join: %s" % join)
# Get master and detail table names and their respective keys that will be used for join
master_name, master_key = self.split_field(join["master"])
if not master_name:
master_name = self.fact_name
detail_name, detail_key = self.split_field(join["detail"])
alias = join.get("alias")
if not detail_name or detail_name == self.fact_name:
raise ValueError("Detail table name should be present and should not be a fact table")
master_table = self.table(master_name)
detail_table = self.register_table(detail_name, alias = alias, schema = self.schema)
try:
master_column = master_table.c[master_key]
except:
raise Exception('Unable to find master key "%s"."%s" ' % (master_name, master_key))
try:
detail_column = detail_table.c[detail_key]
except:
raise Exception('Unable to find master key "%s"."%s" ' % (detail_name, detail_key))
onclause = master_column == detail_column
self.expression = expression.join(self.expression, detail_table, onclause = onclause)
class Event(db.Model):
__ftcolumns__ = 'name, raw_desc, location, link'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(4096))
desc = db.Column(db.Text)
raw_desc = db.Column(db.Text)
location = db.Column(db.String(4096))
link = db.Column(db.String(4096))
time = db.Column(db.DateTime)
games = db.relationship('Game', secondary=event_game_assoc)
direct_genres = db.relationship('Genre', secondary=event_genre_assoc)
indirect_genres = db.relationship('Genre',
viewonly=True,
secondary=join(event_game_assoc, game_genre_assoc,
event_game_assoc.c.game_id == game_genre_assoc.c.game_id).alias())
def json(self):
image = 'https://cf.geekdo-images.com/images/pic1657689_t.jpg'
images = []
images.extend(game.image for game in self.games if game.image != image)
images.extend(genre.image for genre in self.genres if genre.image != image)
if images:
image = images[random.randrange(len(images))]
return {'id': self.id,
'name': self.name,
'img': image,
'desc': self.desc,
'location': self.location,
'time': self.time,
'games': [(game.id, game.primary_name) for game in self.games],
'genres': [(genre.id, genre.name) for genre in self.genres],
'link': self.link
}
@hybrid_property
def genres(self):
return self.direct_genres or self.indirect_genres
def build_query_to_populate(self, query, full_table, aggregate_table):
insert_columns = [aggregate_table.c.join_key]
fk = Column(self.key, Integer)
geom = Column(self.geometry_column, Geometry())
bins_table = Table(self.table, full_table.metadata, fk, geom)
if self.join_custom_data:
extra_data = Table("extra_data",
full_table.metadata,
Column("timestamp", DateTime),
Column("verified", Boolean),
Column("bigquery_key", String),
Column("bigquery_test_id", String),
Column("connection_type", String),
Column("advertised_download", Integer),
Column("actual_download", Float),
Column("advertised_upload", Integer),
Column("actual_upload", Float),
Column("min_rtt", Integer),
Column("location_type", String),
Column("cost_of_service", Integer),
Column("location", Geometry("Point",
srid=4326)),
keep_existing=True)
joining = join(full_table,
extra_data,
and_(extra_data.c.bigquery_test_id == func.left(
full_table.c.test_id,
func.length(extra_data.c.bigquery_test_id))),
isouter=True)
query = query.select_from(joining)
location = case([(extra_data.c.verified,
func.coalesce(extra_data.c.location,
full_table.c.location))],
else_=full_table.c.location)
else:
location = full_table.c.location
select_query = (query.select_from(bins_table).where(
ST_Intersects(location, geom)).column(fk).group_by(fk))
return insert_columns, select_query
def joined(mapped, other):
'''
Creates a joined mapped for the provided mapped class with other class. The joined mapped class will be cached on
the other class.
@param mapped: class
The mapped class to create the joined mapped class with.
@param other: class
The other class to create the joined mapping with.
@return: class
The joined mapped class.
'''
assert isclass(mapped), 'Invalid mapped class %s' % mapped
assert isclass(other), 'Invalid other class %s' % other
name = '%s%s' % (mapped.__name__, other.__name__)
try:
return getattr(mapped, name)
except AttributeError:
pass
properties = {}
mapping, omapping = mappingFor(mapped), mappingFor(other)
for cp in mapping.iterate_properties:
if not isinstance(cp, ColumnProperty) or not cp.key: continue
assert isinstance(cp, ColumnProperty)
properties['%s_%s' % (mapped.__name__, cp.key)] = column_property(
getattr(mapping.c, cp.key))
for cp in omapping.iterate_properties:
if not isinstance(cp, ColumnProperty) or not cp.key: continue
assert isinstance(cp, ColumnProperty)
properties['%s_%s' % (other.__name__, cp.key)] = column_property(
getattr(omapping.c, cp.key))
clazz = type(name, (object, ), {})
mapper(clazz,
join(tableFor(mapped), tableFor(other)),
properties=properties)
setattr(mapped, name, clazz)
return clazz
def _loadAttributes(self):
for row in self._connection.execute(
ex.select([
md.InventoryClasses.c.class_namespace,
md.InventoryClasses.c.class_name,
md.InventoryClassAttributes
]).select_from(
ex.join(
md.InventoryClassAttributes, md.InventoryClasses,
md.InventoryClassAttributes.c.class_id ==
md.InventoryClasses.c.class_id)).where(
and_(
md.InventoryClasses.c.class_namespace ==
self._namespace,
md.InventoryClasses.c.class_name ==
self._class_name))):
self._classId = row["class_id"]
self._attributes[row["attr_key"]] = {}
for i in [
"attr_name", "attr_type", "attr_default", "attr_mandatory"
]:
self._attributes[row["attr_key"]][i] = row[i]
def _build_query(self, table, filter_values):
having = []
filter_cols = []
external_cols = _get_grouping(filter_values)
for fil in self.filters:
if isinstance(fil, ANDFilter):
filter_cols.append(fil.filters[0].column_name)
having.append(fil)
elif fil.column_name not in ['group', 'gender', 'group_leadership', 'disaggregate_by',
'table_card_group_by']:
if fil.column_name not in external_cols and fil.column_name != 'maxmin':
filter_cols.append(fil.column_name)
having.append(fil)
group_having = ''
having_group_by = []
if ('disaggregate_by' in filter_values and filter_values['disaggregate_by'] == 'group') or ('table_card_group_by' in filter_values and filter_values['table_card_group_by']):
group_having = "group_leadership=\'Y\'"
having_group_by.append('group_leadership')
elif 'group_leadership' in filter_values and filter_values['group_leadership']:
group_having = "(MAX(CAST(gender as int4)) + MIN(CAST(gender as int4))) = :group_leadership and group_leadership=\'Y\'"
having_group_by.append('group_leadership')
filter_cols.append('group_leadership')
elif 'gender' in filter_values and filter_values['gender']:
group_having = "(MAX(CAST(gender as int4)) + MIN(CAST(gender as int4))) = :gender"
table_card_group = []
if 'group_name' in self.group_by:
table_card_group.append('group_name')
s1 = alias(select(['doc_id', 'group_id', 'MAX(prop_value) + MIN(prop_value) as maxmin'] + filter_cols + external_cols,
from_obj='"fluff_FarmerRecordFluff"',
group_by=['doc_id', 'group_id'] + filter_cols + external_cols), name='x')
s2 = alias(select(['group_id', '(MAX(CAST(gender as int4)) + MIN(CAST(gender as int4))) as gender'] + table_card_group, from_obj='"fluff_FarmerRecordFluff"',
group_by=['group_id'] + table_card_group + having_group_by, having=group_having), name='y')
return select(['COUNT(x.doc_id) as %s' % self.key] + self.group_by,
group_by=['maxmin'] + filter_cols + self.group_by,
having=AND(having).build_expression(table),
from_obj=join(s1, s2, s1.c.group_id==s2.c.group_id)).params(filter_values)
def get_trip_pk_to_path_map(route_pk):
"""
Get a map of trip PK to the path of the trip for every trip in a route.
The path here is a list of stop PKs.
:param route_pk: the route's PK
:return: map described above
"""
statement = (sql.select(
models.TripStopTime.trip_pk, models.TripStopTime.stop_pk).select_from(
sql.join(
models.TripStopTime,
models.Trip)).where(models.Trip.route_pk == route_pk).order_by(
models.TripStopTime.trip_pk,
models.TripStopTime.stop_sequence))
session = dbconnection.get_session()
trip_pk_to_stop_pks = {}
for trip_pk, stop_pk in session.execute(statement):
if trip_pk not in trip_pk_to_stop_pks:
trip_pk_to_stop_pks[trip_pk] = []
trip_pk_to_stop_pks[trip_pk].append(stop_pk)
return trip_pk_to_stop_pks
def _get_select_waypoints_for_outings_aggregated():
""" Returns a select which retrieves for every outing the ids for the
waypoints that are associated to routes associated to the outing. It
also returns the parent and grand-parent of waypoints, so that when
searching for outings for a waypoint, you also get the outings associated
to child waypoints.
E.g. when searching for outings in Calanques, you also get the outings
associated to sub-sectors.
"""
outing_type = text('\'' + OUTING_TYPE + '\'')
route_type = text('\'' + ROUTE_TYPE + '\'')
all_waypoints_for_routes = _get_select_waypoints_for_routes()
waypoints_for_outings = \
select([
Association.child_document_id.label('outing_id'),
all_waypoints_for_routes.c.waypoint_id
]). \
select_from(join(
Association,
all_waypoints_for_routes,
and_(
Association.parent_document_id ==
all_waypoints_for_routes.c.route_id,
Association.parent_document_type == route_type,
Association.child_document_type == outing_type
))). \
cte('waypoints_for_outings')
return \
select([
waypoints_for_outings.c.outing_id.label('outing_id'),
func.array_agg(
waypoints_for_outings.c.waypoint_id,
type_=postgresql.ARRAY(Integer)).label('waypoint_ids')
]). \
select_from(waypoints_for_outings). \
group_by(waypoints_for_outings.c.outing_id)
def _get_select_waypoints_for_outings_aggregated():
""" Returns a select which retrieves for every outing the ids for the
waypoints that are associated to routes associated to the outing. It
also returns the parent and grand-parent of waypoints, so that when
searching for outings for a waypoint, you also get the outings associated
to child waypoints.
E.g. when searching for outings in Calanques, you also get the outings
associated to sub-sectors.
"""
outing_type = text('\'' + OUTING_TYPE + '\'')
route_type = text('\'' + ROUTE_TYPE + '\'')
all_waypoints_for_routes = _get_select_waypoints_for_routes()
waypoints_for_outings = \
select([
Association.child_document_id.label('outing_id'),
all_waypoints_for_routes.c.waypoint_id
]). \
select_from(join(
Association,
all_waypoints_for_routes,
and_(
Association.parent_document_id ==
all_waypoints_for_routes.c.route_id,
Association.parent_document_type == route_type,
Association.child_document_type == outing_type
))). \
cte('waypoints_for_outings')
return \
select([
waypoints_for_outings.c.outing_id.label('outing_id'),
func.array_agg(
waypoints_for_outings.c.waypoint_id,
type_=postgresql.ARRAY(Integer)).label('waypoint_ids')
]). \
select_from(waypoints_for_outings). \
group_by(waypoints_for_outings.c.outing_id)
def joined(mapped, other):
'''
Creates a joined mapped for the provided mapped class with other class. The joined mapped class will be cached on
the other class.
@param mapped: class
The mapped class to create the joined mapped class with.
@param other: class
The other class to create the joined mapping with.
@return: class
The joined mapped class.
'''
assert isclass(mapped), 'Invalid mapped class %s' % mapped
assert isclass(other), 'Invalid other class %s' % other
name = '%s%s' % (mapped.__name__, other.__name__)
try: return getattr(mapped, name)
except AttributeError: pass
properties = {}
mapping, omapping = mappingFor(mapped), mappingFor(other)
for cp in mapping.iterate_properties:
if not isinstance(cp, ColumnProperty) or not cp.key: continue
assert isinstance(cp, ColumnProperty)
properties['%s_%s' % (mapped.__name__, cp.key)] = column_property(getattr(mapping.c, cp.key))
for cp in omapping.iterate_properties:
if not isinstance(cp, ColumnProperty) or not cp.key: continue
assert isinstance(cp, ColumnProperty)
properties['%s_%s' % (other.__name__, cp.key)] = column_property(getattr(omapping.c, cp.key))
clazz = type(name, (object,), {})
mapper(clazz, join(tableFor(mapped), tableFor(other)), properties=properties)
setattr(mapped, name, clazz)
return clazz
def _build_query(self, table, filter_values):
having = []
filter_cols = []
external_cols = _get_grouping(filter_values)
for fil in self.filters:
if isinstance(fil, ANDFilter):
filter_cols.append(fil.filters[0].column_name)
having.append(fil)
elif isinstance(fil, RawFilter):
having.append(fil)
elif fil.column_name not in [
'group', 'gender', 'group_leadership', 'disaggregate_by',
'table_card_group_by'
]:
if fil.column_name not in external_cols and fil.column_name != 'maxmin':
filter_cols.append(fil.column_name)
having.append(fil)
group_having = ''
having_group_by = []
if ('disaggregate_by' in filter_values and filter_values['disaggregate_by'] == 'group') or \
(filter_values.get('table_card_group_by') == 'group_leadership'):
having_group_by.append('group_leadership')
elif 'group_leadership' in filter_values and filter_values[
'group_leadership']:
group_having = "(MAX(CAST(gender as int4)) + MIN(CAST(gender as int4))) " \
"= :group_leadership and group_leadership=\'Y\'"
having_group_by.append('group_leadership')
filter_cols.append('group_leadership')
elif 'gender' in filter_values and filter_values['gender']:
group_having = "(MAX(CAST(gender as int4)) + MIN(CAST(gender as int4))) = :gender"
table_card_group = []
if 'group_name' in self.group_by:
table_card_group.append('group_name')
s1 = alias(select([
table.c.doc_id, table.c.group_case_id, table.c.group_name,
table.c.group_id,
(sqlalchemy.func.max(table.c.prop_value) +
sqlalchemy.func.min(table.c.prop_value)).label('maxmin')
] + filter_cols + external_cols,
from_obj=table,
group_by=([
table.c.doc_id, table.c.group_case_id,
table.c.group_name, table.c.group_id
] + filter_cols + external_cols)),
name='x')
s2 = alias(select([
table.c.group_case_id,
sqlalchemy.cast(
cast(func.max(table.c.gender), Integer) +
cast(func.min(table.c.gender), Integer),
VARCHAR).label('gender')
] + table_card_group,
from_obj=table,
group_by=[table.c.group_case_id] + table_card_group +
having_group_by,
having=group_having),
name='y')
group_by = list(self.group_by)
if 'group_case_id' in group_by:
group_by[group_by.index('group_case_id')] = s1.c.group_case_id
group_by[group_by.index('group_name')] = s1.c.group_name
return select([sqlalchemy.func.count(s1.c.doc_id).label(self.key)] +
group_by,
group_by=[s1.c.maxmin] + filter_cols + group_by,
having=AND(having).build_expression(s1),
from_obj=join(s1, s2, s1.c.group_case_id ==
s2.c.group_case_id)).params(filter_values)
def identity_join_r(cls):
return join(cls.identity_table, cls,
(cls.identity_table.c.id == cls.base_id) & (cls.tombstone_date == None))
def identity_join_r(cls):
return join(cls.identity_table, cls,
(cls.identity_table.c.id == cls.base_id) &
(cls.tombstone_date == None))
def _loadAttributes(self):
for row in self._connection.execute(ex.select([md.InventoryClasses.c.class_namespace, md.InventoryClasses.c.class_name, md.InventoryClassAttributes]).select_from(ex.join(md.InventoryClassAttributes, md.InventoryClasses, md.InventoryClassAttributes.c.class_id == md.InventoryClasses.c.class_id)).where(and_(md.InventoryClasses.c.class_namespace == self._namespace, md.InventoryClasses.c.class_name == self._class_name))):
self._classId = row["class_id"]
self._attributes[row["attr_key"]] = {}
for i in ["attr_name", "attr_type", "attr_default", "attr_mandatory"]:
self._attributes[row["attr_key"]][i] = row[i]
def generateCountsJs(self, target_fn):
n_markers_in = (self.session.query(
Species, func.count(distinct(
Sequence.id_ortholog))).join(Sequence).group_by(Species).all())
n_species = len(n_markers_in) # how many species are there?
out_str = "var species_key = [\n"
out_str += ',\n'.join([
"\t['%s', '%s', %d]" % (chr(64 + x[0].id), x[0].name, x[1])
for x in n_markers_in
])
out_str += "\n];\n\n"
out_str += "var marker_sets_input = [ "
# output single-species marker counts
out_str += "{sets: ['%s'], size: %d}" % (
chr(64 + n_markers_in[0][0].id), n_markers_in[0][1])
for rec in n_markers_in[1:]:
out_str += ",\n\t{sets: ['%s'], size: %d}" % (chr(64 + rec[0].id),
rec[1])
# determine overlaps
from sqlalchemy.sql.expression import alias, join, select
seq_tab = Base.metadata.tables['sequences']
aka = [alias(seq_tab) for n in range(n_species)]
for n_levels in range(2, n_species + 1):
cols = [
func.count(distinct(aka[0].c.id_ortholog)), aka[0].c.id_species
]
joins = aka[0]
# build selected columns and joins
for i in range(1, n_levels):
cols += [aka[i].c.id_species]
joins = join(joins, aka[i],
aka[0].c.id_ortholog == aka[i].c.id_ortholog)
# create select statement on columns and joins
stmt = select(cols).select_from(joins)
# add filtering clauses
for i in range(1, n_levels):
stmt = stmt.where(
aka[i - 1].c.id_species < aka[i].c.id_species)
# add grouping clauses
for i in range(n_levels):
stmt = stmt.group_by(aka[i].c.id_species)
# execute query statement
result = self.session.execute(stmt).fetchall()
for rec in result:
out_str += ",\n\t{sets: [%s], size: %d}" % (','.join(
["'%s'" % chr(64 + rec[i + 1])
for i in range(n_levels)]), rec[0])
out_str += "\n];\n\n"
# load number of markers found for each species
n_markers_out = (self.session.query(
Species.name,
func.count(distinct(PrimerSet.id_ortholog))).outerjoin(
PrimerSet, Species.primer_sets).group_by(Species).all())
out_str += "var species_markers_output = [\n"
out_str += "\t{name: '%s', value: %d}" % (n_markers_out[0][0],
n_markers_out[0][1])
for rec in n_markers_out[1:]:
out_str += ",\n\t{name: '%s', value: %d}" % (rec[0], rec[1])
out_str += "\n];\n"
with open(target_fn, 'wt') as outfile:
print(outfile.name)
outfile.write(out_str)
def generateCountsJs(self, target_fn):
n_markers_in = (self.session.query(
Species,
func.count(distinct(Sequence.id_ortholog)))
.join(Sequence)
.group_by(Species)
.all()
)
n_species = len(n_markers_in) # how many species are there?
out_str = "var species_key = [\n"
out_str += ',\n'.join(["\t['%s', '%s', %d]" % (chr(64+x[0].id), x[0].name, x[1]) for x in n_markers_in])
out_str += "\n];\n\n"
out_str += "var marker_sets_input = [ "
# output single-species marker counts
out_str += "{sets: ['%s'], size: %d}" % (chr(64+n_markers_in[0][0].id), n_markers_in[0][1])
for rec in n_markers_in[1:]:
out_str += ",\n\t{sets: ['%s'], size: %d}" % (chr(64+rec[0].id), rec[1])
# determine overlaps
from sqlalchemy.sql.expression import alias, join, select
seq_tab = Base.metadata.tables['sequences']
aka = [alias(seq_tab) for n in range(n_species)]
for n_levels in range(2,n_species+1):
cols = [func.count(distinct(aka[0].c.id_ortholog)), aka[0].c.id_species]
joins = aka[0]
# build selected columns and joins
for i in range(1,n_levels):
cols += [aka[i].c.id_species]
joins = join(joins, aka[i], aka[0].c.id_ortholog == aka[i].c.id_ortholog)
# create select statement on columns and joins
stmt = select(cols).select_from(joins)
# add filtering clauses
for i in range(1,n_levels):
stmt = stmt.where(aka[i-1].c.id_species < aka[i].c.id_species)
# add grouping clauses
for i in range(n_levels):
stmt = stmt.group_by(aka[i].c.id_species)
# execute query statement
result = self.session.execute(stmt).fetchall()
for rec in result:
out_str += ",\n\t{sets: [%s], size: %d}" % (','.join(["'%s'" % chr(64+rec[i+1]) for i in range(n_levels)]), rec[0])
out_str += "\n];\n\n"
# load number of markers found for each species
n_markers_out = (self.session.query(
Species.name,
func.count(distinct(PrimerSet.id_ortholog)))
.outerjoin(PrimerSet, Species.primer_sets)
.group_by(Species)
.all()
)
out_str += "var species_markers_output = [\n"
out_str += "\t{name: '%s', value: %d}" % (n_markers_out[0][0], n_markers_out[0][1])
for rec in n_markers_out[1:]:
out_str += ",\n\t{name: '%s', value: %d}" % (rec[0], rec[1])
out_str += "\n];\n"
with open(target_fn, 'wt') as outfile:
print(outfile.name)
outfile.write(out_str)
def join_(self, *args, **kwargs):
return join(*args, **kwargs)
ob, 'first_name'), getattr(ob, 'last_name'))
terms = []
for t in tdict.keys():
terms.append(
vocabulary.SimpleTerm(value=t, token=t, title=tdict[t]))
return vocabulary.SimpleVocabulary(terms)
# !+MODELS(mr, oct-2011) shouldn't this be elsewhere?
class PartyMembership(object):
pass
party_membership = sql.join(
schema.political_parties, schema.groups,
schema.political_parties.c.party_id == schema.groups.c.group_id).join(
schema.user_group_memberships,
schema.groups.c.group_id == schema.user_group_memberships.c.group_id)
mapper(PartyMembership, party_membership)
class PIAssignmentSource(SpecializedSource):
def constructQuery(self, context):
session = Session()
trusted = removeSecurityProxy(context)
parliament_id = self._get_parliament_id(context)
item_id = getattr(context, self.value_field, None)
trusted = removeSecurityProxy(context)
existing_item_ids = [assn.item_id for assn in trusted.values()]
if item_id:
def _build_query(self, filter_values):
having = []
filter_cols = []
external_cols = _get_grouping(filter_values)
for fil in self.filters:
if isinstance(fil, ANDFilter):
filter_cols.append(fil.filters[0].column_name)
having.append(fil.build_expression())
elif fil.column_name not in [
"group",
"gender",
"group_leadership",
"disaggregate_by",
"table_card_group_by",
]:
if fil.column_name not in external_cols and fil.column_name != "maxmin":
filter_cols.append(fil.column_name)
having.append(fil.build_expression())
group_having = ""
having_group_by = []
if ("disaggregate_by" in filter_values and filter_values["disaggregate_by"] == "group") or (
"table_card_group_by" in filter_values and filter_values["table_card_group_by"]
):
group_having = "group_leadership='Y'"
having_group_by.append("group_leadership")
elif "group_leadership" in filter_values and filter_values["group_leadership"]:
group_having = (
"(MAX(CAST(gender as int4)) + MIN(CAST(gender as int4))) = :group_leadership and group_leadership='Y'"
)
having_group_by.append("group_leadership")
filter_cols.append("group_leadership")
elif "gender" in filter_values and filter_values["gender"]:
group_having = "(MAX(CAST(gender as int4)) + MIN(CAST(gender as int4))) = :gender"
table_card_group = []
if "group_name" in self.group_by:
table_card_group.append("group_name")
s1 = alias(
select(
["doc_id", "group_id", "MAX(prop_value) + MIN(prop_value) as maxmin"] + filter_cols + external_cols,
from_obj='"fluff_FarmerRecordFluff"',
group_by=["doc_id", "group_id"] + filter_cols + external_cols,
),
name="x",
)
s2 = alias(
select(
["group_id", "(MAX(CAST(gender as int4)) + MIN(CAST(gender as int4))) as gender"] + table_card_group,
from_obj='"fluff_FarmerRecordFluff"',
group_by=["group_id"] + table_card_group + having_group_by,
having=group_having,
),
name="y",
)
return select(
["COUNT(x.doc_id) as %s" % self.key] + self.group_by,
group_by=["maxmin"] + filter_cols + self.group_by,
having=" and ".join(having),
from_obj=join(s1, s2, s1.c.group_id == s2.c.group_id),
).params(filter_values)
def get_data(project_name, file_name):
file = db.session.query(File).select_from(join(File, Project)).filter(
File.name == file_name, Project.name == project_name,
Project.user_id == current_user.id).one()
print(file)
return send_file(file.get_path())
def __init__(self,
db,
cohortClass,
dtcols=('feature_start_date', 'person_start_date',
'person_end_date'),
id_col='person_id',
time_col='feature_start_date',
feature_col='concept_name',
unique_id_col='example_id'):
self._db = db
self.cohortClass = cohortClass
self._dtcols = dtcols
self.id_col = id_col
self.time_col = time_col
self.feature_col = feature_col
self.unique_id_col = unique_id_col
self._temporal_features = []
self._nontemporal_features = []
self._temporal_feature_names = []
self._temporal_feature_names_set = set()
self._nontemporal_feature_names = []
self._nontemporal_feature_names_set = set()
self._spm_arr = []
self.id_map = None
self.id_map_rev = None
self.concept_map = None
self.concept_map_rev = None
self.time_map = None
self.time_map_rev = None
# Map InspectOMOP tables to names we were already using with our own class definitions.
ConditionOccurrence = db.inspector.tables['condition_occurrence']
ProcedureOccurrence = db.inspector.tables['procedure_occurrence']
DrugExposure = db.inspector.tables['drug_exposure']
VisitOccurrence = db.inspector.tables['visit_occurrence']
Provider = db.inspector.tables['provider']
Concept = db.inspector.tables['concept']
# SqlAlchemy feature definitions
condition_features = select([
self.cohortClass.example_id, ConditionOccurrence.person_id,
(cast(ConditionOccurrence.condition_concept_id, String) +
' - condition - ' +
case([(Concept.concept_name == None, 'no match')], else_ = Concept.concept_name)).label('concept_name'),
ConditionOccurrence.condition_start_datetime.label(dtcols[0]),
self.cohortClass.start_date.label(dtcols[1]),
self.cohortClass.end_date.label(dtcols[2])
])\
.select_from(
join(ConditionOccurrence, self.cohortClass, ConditionOccurrence.person_id == self.cohortClass.person_id)\
.join(Concept, Concept.concept_id == ConditionOccurrence.condition_concept_id)
)
procedure_features = select([
self.cohortClass.example_id, ProcedureOccurrence.person_id,
(cast(ProcedureOccurrence.procedure_concept_id, String) +
' - procedure - ' +
case([(Concept.concept_name == None, 'no match')], else_ = Concept.concept_name)).label('concept_name'),
ProcedureOccurrence.procedure_datetime.label(dtcols[0]),
self.cohortClass.start_date.label(dtcols[1]),
self.cohortClass.end_date.label(dtcols[2])
])\
.select_from(
join(ProcedureOccurrence, self.cohortClass, ProcedureOccurrence.person_id == self.cohortClass.person_id)\
.join(Concept, Concept.concept_id == ProcedureOccurrence.procedure_concept_id)
)
drug_features = select([
self.cohortClass.example_id, DrugExposure.person_id,
(cast(DrugExposure.drug_concept_id, String) +
' - drug - ' +
case([(Concept.concept_name == None, 'no match')], else_ = Concept.concept_name)).label('concept_name'),
DrugExposure.drug_exposure_start_datetime.label(dtcols[0]),
self.cohortClass.start_date.label(dtcols[1]),
self.cohortClass.end_date.label(dtcols[2])
])\
.select_from(
join(DrugExposure, self.cohortClass, DrugExposure.person_id == self.cohortClass.person_id)\
.join(Concept, Concept.concept_id == DrugExposure.drug_concept_id)
)
specialty_features = select([
self.cohortClass.example_id, VisitOccurrence.person_id,
(cast(Provider.specialty_concept_id, String) +
' - specialty - ' +
case([(Concept.concept_name == None, 'no match')], else_ = Concept.concept_name)).label('concept_name'),
VisitOccurrence.visit_start_date.label(dtcols[0]),
self.cohortClass.start_date.label(dtcols[1]),
self.cohortClass.end_date.label(dtcols[2])
])\
.select_from(
join(VisitOccurrence, self.cohortClass, VisitOccurrence.person_id == self.cohortClass.person_id)\
.join(Provider, VisitOccurrence.provider_id == Provider.provider_id)\
.join(Concept, Concept.concept_id == Provider.specialty_concept_id)
)
self.feature_dict = {
'Conditions': {
'sql': condition_features,
'is_temporal': True
},
'Procedures': {
'sql': procedure_features,
'is_temporal': True
},
'Drugs': {
'sql': drug_features,
'is_temporal': True
},
'Specialty': {
'sql': specialty_features,
'is_temporal': True
}
}
print('time table: {columns}'.format(columns=time.columns))
print('host table: {columns}'.format(columns=host.columns))
print('account table: {columns}'.format(columns=account.columns))
print('host_time table: {columns}'.format(columns=host_time.columns))
# Select all accounts.
print('Select all accounts:')
stmt = select([account.columns.id])
results = connection.execute(stmt).fetchall()
print('Number of accounts: {amnt}'.format(amnt=len(results)))
# Select all accounts and times & group by account.
user_accounts = account.columns.id.label('users')
total_seconds = func.sum(time.columns.seconds).label('seconds')
joined_on = join(
account, time,
account.columns.id == time.columns.account_id
)
stmt = select([user_accounts, total_seconds]) \
.select_from(joined_on) \
.group_by(user_accounts) \
.order_by(desc(total_seconds))
results = connection.execute(stmt).fetchall()
# Create Pandas DataFrame from results.
index_value = list(range(1, len(results) + 1))
columns = results[0].keys()
df = pd.DataFrame(
data=results,
index=index_value,
columns=columns
from sqlalchemy.sql.schema import Column
from sqlalchemy.sql.sqltypes import String
from blueshed.model_helpers.sqla_views import view
from sqlalchemy.sql.expression import select, join
from sqlalchemy.sql.functions import func
"""
Because the library requires attributes in Person and Permission
you can extend them like this:
"""
Person._token = Column(String(80))
'''
An example View
'''
q = select([Person.id.label('id'),
Person.email.label('email'),
func.count(Permission.id).label('permission_count')]).\
select_from(join(Person,
person_permissions_permission,
Person.id==person_permissions_permission.c.permissions_id).\
join(Permission,
Permission.id==person_permissions_permission.c.permission_id)).\
group_by(Permission.id)
PersonReport = view("person_report", Base.metadata, q)
def update_idnum_index_for_upload(
cls,
session: SqlASession,
indexed_at_utc: Pendulum,
tablechanges: UploadTableChanges) -> None:
"""
Updates the index for a device's upload.
- Deletes index entries for records that are on the way out.
- Creates index entries for records that are on the way in.
- Should be called after both the Patient and PatientIdNum tables are
committed; see special ordering in
:func:`camcops_server.cc_modules.client_api.commit_all`.
Args:
session:
an SQLAlchemy Session
indexed_at_utc:
current time in UTC
tablechanges:
a :class:`camcops_server.cc_modules.cc_client_api_core.UploadTableChanges`
object describing the changes to a table
""" # noqa
# noinspection PyUnresolvedReferences
indextable = PatientIdNumIndexEntry.__table__ # type: Table
indexcols = indextable.columns
# noinspection PyUnresolvedReferences
idnumtable = PatientIdNum.__table__ # type: Table
idnumcols = idnumtable.columns
# noinspection PyUnresolvedReferences
patienttable = Patient.__table__ # type: Table
patientcols = patienttable.columns
# Delete the old
removal_pks = tablechanges.idnum_delete_index_pks
if removal_pks:
log.debug("Deleting old ID number indexes: server PKs {}",
removal_pks)
session.execute(
indextable.delete()
.where(indextable.c.idnum_pk.in_(removal_pks))
)
# Create the new
addition_pks = tablechanges.idnum_add_index_pks
if addition_pks:
log.debug("Adding ID number indexes: server PKs {}", addition_pks)
# noinspection PyPep8,PyProtectedMember
session.execute(
indextable.insert().from_select(
# Target:
[indexcols.idnum_pk,
indexcols.indexed_at_utc,
indexcols.patient_pk,
indexcols.which_idnum,
indexcols.idnum_value],
# Source:
(
select([idnumcols._pk,
literal(indexed_at_utc),
patientcols._pk,
idnumcols.which_idnum,
idnumcols.idnum_value])
.select_from(
join(
idnumtable,
patienttable,
and_(
idnumcols._device_id == patientcols._device_id, # noqa
idnumcols._era == patientcols._era,
idnumcols.patient_id == patientcols.id,
)
)
)
.where(idnumcols._pk.in_(addition_pks))
.where(patientcols._current == True)
)
)
)
dump_query,
production_session,
LicensePool,
DataSource,
Edition,
PresentationCalculationPolicy,
)
# Find all books where the edition associated with the LicensePool has a
# different medium from the presentation edition.
_db = production_session()
# Find all the LicensePools that aren't books.
subq = select([LicensePool.id]).select_from(
join(LicensePool, Edition,
and_(LicensePool.data_source_id==Edition.data_source_id,
LicensePool.identifier_id==Edition.primary_identifier_id)
)
).where(Edition.medium != Edition.BOOK_MEDIUM)
# Of those LicensePools, find every LicensePool whose presentation
# edition says it _is_ a book.
qu = _db.query(LicensePool).join(
Edition, LicensePool.presentation_edition_id==Edition.id
).filter(LicensePool.id.in_(subq)).filter(Edition.medium == Edition.BOOK_MEDIUM)
print "Recalculating presentation edition for %d LicensePools." % qu.count()
for lp in qu:
# Recalculate that LicensePool's presentation edition, and then its
# work presentation.
lp.set_presentation_edition()
for t in tdict.keys():
terms.append(
vocabulary.SimpleTerm(
value = t,
token = t,
title = tdict[t]
))
return vocabulary.SimpleVocabulary(terms)
# !+MODELS(mr, oct-2011) shouldn't this be elsewhere?
class PartyMembership(object):
pass
party_membership = sql.join(schema.political_parties, schema.groups,
schema.political_parties.c.party_id == schema.groups.c.group_id
).join(schema.user_group_memberships,
schema.groups.c.group_id == schema.user_group_memberships.c.group_id)
mapper(PartyMembership, party_membership)
class PIAssignmentSource(SpecializedSource):
def constructQuery(self, context):
session= Session()
trusted=removeSecurityProxy(context)
parliament_id = self._get_parliament_id(context)
item_id = getattr(context, self.value_field, None)
trusted = removeSecurityProxy(context)
existing_item_ids = [assn.item_id for assn in trusted.values()]
if item_id:
Build.dependency_changes = relationship(
AppliedChange,
backref='build',
primaryjoin=(Build.id == AppliedChange.build_id),
order_by=AppliedChange.distance.nullslast(),
passive_deletes=True,
)
ResolutionChange.problems = relationship(
ResolutionProblem,
backref='result',
passive_deletes=True,
)
PackageGroup.package_count = column_property(
select([func.count()],
PackageGroupRelation.group_id == PackageGroup.id,
join(BasePackage, PackageGroupRelation,
PackageGroupRelation.base_id == BasePackage.id))
.where(~BasePackage.all_blocked)
.correlate(PackageGroup).as_scalar(),
deferred=True)
# pylint: disable=E1101
BasePackage.groups = relationship(
PackageGroup,
secondary=PackageGroupRelation.__table__,
secondaryjoin=(PackageGroup.id == PackageGroupRelation.group_id),
primaryjoin=(PackageGroupRelation.base_id == BasePackage.id),
order_by=PackageGroup.name,
passive_deletes=True,
)
Package.groups = relationship(
PackageGroup,
secondary=PackageGroupRelation.__table__,