def unique_sounds(conn: Connection, corpus_id: int) -> [str]:
sql = """
select distinct rhyme_sound
from phrases p
join corpora_sources cs
on p.source_id = cs.source_id
where corpus_id = :corpus_id
"""
result = conn.execute(sa.text(sql).params(corpus_id=corpus_id)).fetchall()
return list(filter(lambda r: r is not None, map(lambda r: r[0], result)))
def aggregate(self):
print "AGGREGATING...after ", self.delay
t = text(
"""
SELECT u.service_id, u.resource_owner, u.resource_id, u.location, um.metric_id, sum(um.value),
strftime('%Y-%m-%d', u.event_time)
FROM usage u, usagemetrics um
where um.usage_id = u.id
group by u.service_id, u.resource_owner, u.resource_id, u.location,
strftime('%Y-%m-%d', u.event_time)
""",
bind=self.engine,
)
conn = Connection(self.engine)
result = conn.execute(t)
Session = sessionmaker(bind=self.engine)
session = Session()
"""
blah = session.query(UsageMetric).group_by(
UsageMetric.service_id,
UsageMetric.resource_owner,
UsageMetric.resource_id,
UsageMetric.location,
UsageMetric.metric_id,
func.day(UsageMetric.event_time)).all()
for r in blah:
print r
"""
self.process_results(result, session)
conn.close()
session.close()
self.timer.cancel()
def insert_user_section_association(
connection: Connection,
ss_identifier: str,
source_system: str,
identifier: int,
user_identifier: int,
section_identifier: int,
):
connection.execute(
"SET IDENTITY_INSERT lms.LMSUserLMSSectionAssociation ON")
connection.execute(f"""
INSERT INTO [lms].[LMSUserLMSSectionAssociation]
([LMSUserLMSSectionAssociationIdentifier]
,[LMSSectionIdentifier]
,[LMSUserIdentifier]
,[SourceSystemIdentifier]
,[SourceSystem]
,[EnrollmentStatus]
,[SourceCreateDate]
,[SourceLastModifiedDate]
,[CreateDate]
,[LastModifiedDate]
,[DeletedAt])
VALUES
({identifier}
,{section_identifier}
,{user_identifier}
,N'{ss_identifier}'
,N'{source_system}'
,N'Active'
,NULL
,NULL
,CAST(N'2021-01-01 00:00:00' AS DateTime)
,CAST(N'2021-01-01 00:00:00' AS DateTime)
,NULL
)
""")
connection.execute(
"SET IDENTITY_INSERT lms.LMSUserLMSSectionAssociation OFF")
def insert_user(connection: Connection, ss_identifier: str, source_system: str,
identifier: int):
# insert a required user with LMSUserIdentifier = 1
connection.execute("SET IDENTITY_INSERT lms.LMSUser ON")
connection.execute(f"""
INSERT INTO [lms].[LMSUser]
([LMSUserIdentifier]
,[SourceSystemIdentifier]
,[SourceSystem]
,[UserRole]
,[SISUserIdentifier]
,[LocalUserIdentifier]
,[Name]
,[EmailAddress]
,[SourceCreateDate]
,[SourceLastModifiedDate]
,[CreateDate]
,[LastModifiedDate]
,[DeletedAt])
VALUES
({identifier}
,N'{ss_identifier}'
,N'{source_system}'
,N'student'
,N'{ss_identifier}'
,N'{ss_identifier}'
,N'{ss_identifier}'
,N'{ss_identifier}'
,NULL
,NULL
,CAST(N'2021-01-01 00:00:00' AS DateTime)
,CAST(N'2021-01-01 00:00:00' AS DateTime)
,NULL
)
""")
connection.execute("SET IDENTITY_INSERT lms.LMSUser OFF")
def get_table_names(self, connection: Connection, schema: str = None, **kw) -> List[str]:
query = 'SHOW TABLES'
if schema:
query = f'{query} FROM {self.identifier_preparer.quote_identifier(schema)}'
res = connection.execute(sql.text(query))
return [row.Table for row in res]
def get_schema_names(self, connection: Connection, **kw) -> List[str]:
query = 'SHOW SCHEMAS'
res = connection.execute(sql.text(query))
return [row.Schema for row in res]
def set_perm( # pylint: disable=unused-argument
self, mapper: Mapper, connection: Connection, target: "BaseDatasource"
) -> None:
"""
Set the datasource permissions.
:param mapper: The table mapper
:param connection: The DB-API connection
:param target: The mapped instance being persisted
"""
link_table = target.__table__
if target.perm != target.get_perm():
connection.execute(
link_table.update()
.where(link_table.c.id == target.id)
.values(perm=target.get_perm())
)
if (
hasattr(target, "schema_perm")
and target.schema_perm != target.get_schema_perm()
):
connection.execute(
link_table.update()
.where(link_table.c.id == target.id)
.values(schema_perm=target.get_schema_perm())
)
pvm_names = []
if target.__tablename__ in {"dbs", "clusters"}:
pvm_names.append(("database_access", target.get_perm()))
else:
pvm_names.append(("datasource_access", target.get_perm()))
if target.schema:
pvm_names.append(("schema_access", target.get_schema_perm()))
# TODO(bogdan): modify slice permissions as well.
for permission_name, view_menu_name in pvm_names:
permission = self.find_permission(permission_name)
view_menu = self.find_view_menu(view_menu_name)
pv = None
if not permission:
permission_table = (
self.permission_model.__table__ # pylint: disable=no-member
)
connection.execute(
permission_table.insert().values(name=permission_name)
)
permission = self.find_permission(permission_name)
if not view_menu:
view_menu_table = (
self.viewmenu_model.__table__ # pylint: disable=no-member
)
connection.execute(view_menu_table.insert().values(name=view_menu_name))
view_menu = self.find_view_menu(view_menu_name)
if permission and view_menu:
pv = (
self.get_session.query(self.permissionview_model)
.filter_by(permission=permission, view_menu=view_menu)
.first()
)
if not pv and permission and view_menu:
permission_view_table = (
self.permissionview_model.__table__ # pylint: disable=no-member
)
connection.execute(
permission_view_table.insert().values(
permission_id=permission.id, view_menu_id=view_menu.id
)
)
def create_user_to_interest(connection: Connection, user_id: int,
interest_id: int):
query = text("""
insert into user_to_interest (user, interest) values (:user_id, :interest_id)
""")
connection.execute(query, user_id=user_id, interest_id=interest_id)
def diff_tables(
connection: Connection,
master: Table,
copy: Table,
result_columns: Iterable[Column],
) -> Tuple[List[Tuple], List[Tuple], List[Tuple]]:
"""
Compute the differences in the contents of two tables with identical
columns.
The master table must have at least one :class:`PrimaryKeyConstraint` or
:class:`UniqueConstraint` with only non-null columns defined.
If there are multiple constraints defined the constraints that contains the
least number of columns are used.
:param connection: DB connection
:param master: Master table
:param copy: Copy of master table
:param result_columns: columns to return
:return: True, if the contents differ, otherwise False
"""
logger.debug('Calculating diff between "%s" and "%s"', master.name,
copy.name)
result_columns = tuple(result_columns)
unique_columns = min(
(constraint.columns for constraint in master.constraints
if isinstance(constraint, (UniqueConstraint, PrimaryKeyConstraint))
and constraint.columns and
not any(map(operator.attrgetter('nullable'), constraint.columns))),
key=len,
default=[])
if not unique_columns:
raise AssertionError("To diff table {} it must have at least one "
"PrimaryKeyConstraint/UniqueConstraint with only "
"NOT NULL columns defined on it.".format(
master.name))
unique_column_names = tuple(col.name for col in unique_columns)
other_column_names = tuple(col.name for col in master.c
if col.name not in unique_column_names)
on_clause = and_(
*(getattr(master.c, column_name) == getattr(copy.c, column_name)
for column_name in unique_column_names))
added = connection.execute(
select(result_columns).select_from(master.outerjoin(
copy, on_clause)).where(
or_(*(getattr(copy.c, column_name).is_(null())
for column_name in unique_column_names)))).fetchall()
deleted = connection.execute(
select(result_columns).select_from(copy.outerjoin(
master, on_clause)).where(
or_(*(getattr(master.c, column_name).is_(null())
for column_name in unique_column_names)))).fetchall()
modified = connection.execute(
select(result_columns).select_from(master.join(copy, on_clause)).where(
or_(*(
getattr(master.c, column_name) != getattr(copy.c, column_name)
for column_name in other_column_names
)))).fetchall() if other_column_names else []
logger.debug('Diff found %d added, %d deleted, and %d modified records',
len(added), len(deleted), len(modified))
return added, deleted, modified
def _upsert_sqlite(self: T, conn: Connection) -> None:
conn.execute(self.t.insert().values(**self._insert_values).prefix_with("OR REPLACE"))
def get_users_by_id(connection: Connection, user_id: int = 0) -> List[Tuple]:
query = text("""
select id, firstname, lastname from user where id > :user_id
""")
return connection.execute(query, user_id=user_id).fetchall()
def create_friend(connection: Connection, user_id: int, friend_id: int):
query = text("""
insert into friends (user_one, user_two) values (:user_id, :friend_id)
""")
connection.execute(query, user_id=user_id, friend_id=friend_id)
def populate(target: Table, connection: Connection, **kwargs):
connection.execute(target.insert(), *rows)
def delete_old_auth_attempts(connection: Connection, interval: timedelta):
"""Delete old authentication results from the ``radpostauth`` table."""
logger.debug('Deleting auth attempts in table "%s" older than "%s"',
radpostauth.name, interval)
connection.execute(radpostauth.delete().where(
and_(radpostauth.c.AuthDate < utcnow() - interval)))
def delete_old_sessions(connection: Connection, interval: timedelta):
"""Delete old session from the ``radacct`` table."""
logger.debug('Deleting sessions in table "%s" older than "%s"',
radacct.name, interval)
connection.execute(radacct.delete().where(
and_(radacct.c.AcctUpdateTime < utcnow() - interval)))
def _get_server_version_info(self, connection: Connection) -> Tuple[int, ...]:
query = 'SELECT version()'
res = connection.execute(sql.text(query)).scalar()
match = self.__version_pattern.match(res)
version = int(match.group(1)) if match else 0
return tuple([version])
def _upsert_postgres(self: T, conn: Connection) -> None:
conn.execute(
pg_insert(self.t)
.values(**self._insert_values)
.on_conflict_do_update(constraint=self.t.primary_key, set_=self._upsert_values)
)
def create_interests(connection: Connection, interests: List[str]):
query = text("""
insert into interest (name) values :data
""")
connection.execute(query, data=interests)
def _upsert_generic(self: T, conn: Connection):
conn.execute(self.t.delete().where(self._edit_identity))
conn.execute(self.t.insert().values(**self._insert_values))
def count_interests(connection: Connection) -> Tuple[int]:
query = text("""
select count(*) from interest;
""")
return connection.execute(query).fetchone()
