def establish_connection(bind: sa.engine.Engine) -> sa.engine.Engine:
for _ in range(100):
try:
bind.connect()
break
except exc.OperationalError:
time.sleep(0.05)
return bind
def project_id(user_id: int, postgres_db: sa.engine.Engine) -> Iterable[str]:
# inject project for user in db. This will give user_id, the full project's ownership
# pylint: disable=no-value-for-parameter
stmt = (projects.insert().values(**random_project(
prj_owner=user_id)).returning(projects.c.uuid))
print(str(stmt))
with postgres_db.connect() as conn:
result = conn.execute(stmt)
[prj_uuid] = result.fetchone()
yield prj_uuid
with postgres_db.connect() as conn:
conn.execute(projects.delete().where(projects.c.uuid == prj_uuid))
def get_send_off_diff(shots_df: pd.DataFrame,
engine: sqlalchemy.engine.Engine) -> pd.Series:
"""
Returns the player advantage based on the red cards given
:param shots_df: data frame of shots taken
:param engine: sqlalchemy engine
:return: the player differential at the time of each shot
"""
diffs = pd.Series(data=0, index=shots_df.index)
reds = []
with engine.connect() as cxn:
for league in shots_df['league'].unique(): # this is only 7 leagues
query = """SELECT *
FROM events_{}
WHERE
"1701" = True or
"1703" = True;""".format(league) # TAG: numbers
reds.append(pd.read_sql(query, cxn))
reds_df = pd.concat(reds)
for match in reds_df['matchId'].unique(): # 305 matches with red cards
for ind, row in shots_df[shots_df['matchId'] ==
match].iterrows(): # 50 max
same_team_slice = reds_df[(reds_df['eventSec'] < row['eventSec'])
& (reds_df['teamId'] == row['teamId'])]
other_team_slice = reds_df[(reds_df['eventSec'] < row['eventSec'])
& (reds_df['teamId'] != row['teamId'])]
reds_same = len(
same_team_slice[same_team_slice['matchId'] == match])
reds_other = len(
other_team_slice[other_team_slice['matchId'] == match])
diffs[ind] = reds_other - reds_same
return diffs
def cluster(
postgres_db: sa.engine.Engine, ) -> Iterator[Callable[..., Cluster]]:
created_cluster_ids: List[str] = []
def creator(user: Dict[str, Any], **cluster_kwargs) -> Cluster:
cluster_config = Cluster.Config.schema_extra["examples"][0]
cluster_config["owner"] = user["primary_gid"]
cluster_config.update(**cluster_kwargs)
new_cluster = Cluster.parse_obj(cluster_config)
assert new_cluster
with postgres_db.connect() as conn:
# insert basic cluster
created_cluster = conn.execute(
sa.insert(clusters).values(
new_cluster.to_clusters_db(only_update=False)).returning(
sa.literal_column("*"))).one()
created_cluster_ids.append(created_cluster.id)
if "access_rights" in cluster_kwargs:
for gid, rights in cluster_kwargs["access_rights"].items():
conn.execute(
pg_insert(cluster_to_groups).values(
cluster_id=created_cluster.id,
gid=gid,
**rights.dict()).on_conflict_do_update(
index_elements=["gid", "cluster_id"],
set_=rights.dict()))
access_rights_in_db = {}
for row in conn.execute(
sa.select([
cluster_to_groups.c.gid,
cluster_to_groups.c.read,
cluster_to_groups.c.write,
cluster_to_groups.c.delete,
]).select_from(clusters.join(cluster_to_groups)).where(
clusters.c.id == created_cluster.id)):
access_rights_in_db[row.gid] = {
"read": row[cluster_to_groups.c.read],
"write": row[cluster_to_groups.c.write],
"delete": row[cluster_to_groups.c.delete],
}
return Cluster.construct(
id=created_cluster.id,
name=created_cluster.name,
description=created_cluster.description,
type=created_cluster.type,
owner=created_cluster.owner,
endpoint=created_cluster.endpoint,
authentication=created_cluster.authentication,
access_rights=access_rights_in_db,
)
yield creator
# cleanup
with postgres_db.connect() as conn:
conn.execute(
# pylint: disable=no-value-for-parameter
clusters.delete().where(clusters.c.id.in_(created_cluster_ids)))
def project(postgres_db: sa.engine.Engine, user_db: Dict) -> Callable:
created_project_ids = []
def creator(**overrides) -> ProjectAtDB:
project_config = {
"uuid": uuid4(),
"name": "my test project",
"type": ProjectType.STANDARD.name,
"description": "my test description",
"prj_owner": user_db["id"],
"workbench": {},
}
project_config.update(**overrides)
with postgres_db.connect() as con:
result = con.execute(
projects.insert()
.values(**project_config)
.returning(literal_column("*"))
)
project = ProjectAtDB.parse_obj(result.first())
created_project_ids.append(project.uuid)
return project
yield creator
# cleanup
with postgres_db.connect() as con:
for pid in created_project_ids:
con.execute(projects.delete().where(projects.c.uuid == str(pid)))
def runs(
postgres_db: sa.engine.Engine
) -> Iterator[Callable[..., CompRunsAtDB]]:
created_run_ids: List[int] = []
def creator(user: Dict[str, Any], project: ProjectAtDB,
**run_kwargs) -> CompRunsAtDB:
run_config = {
"project_uuid": f"{project.uuid}",
"user_id": f"{user['id']}",
"iteration": 1,
"result": StateType.NOT_STARTED,
}
run_config.update(**run_kwargs)
with postgres_db.connect() as conn:
result = conn.execute(comp_runs.insert().values(
**run_config).returning(sa.literal_column("*")))
new_run = CompRunsAtDB.parse_obj(result.first())
created_run_ids.append(new_run.run_id)
return new_run
yield creator
# cleanup
with postgres_db.connect() as conn:
conn.execute(comp_runs.delete().where(
comp_runs.c.run_id.in_(created_run_ids)))
def write_iostream_to_database(engine: sqlalchemy.engine.Engine,
tablename: str,
dataframe: pd.DataFrame) -> None:
"""
More effective way of saving dataframe into database using io stream - StringIO.
:param sqlalchemy.engine.Engine engine: SQL Alchemy engine for connection to databse.
:param str tablename: Name of the table in database, where will be content of dataframe appended.
:param pd.Datafram dataframe: Dataframe, whose content will be appended to databse.
:return None: No return value.
"""
store = io.StringIO()
dataframe.to_csv(store, index=False, header=False)
store.seek(0)
conn = engine.connect().connection
cursor = conn.cursor()
cursor.copy_from(store,
tablename,
columns=dataframe.columns,
sep=',',
null='null')
conn.commit()
conn.close()
def task(postgres_db: sa.engine.Engine) -> Callable[..., str]:
created_task_ids: List[int] = []
def creator(project_id: str, node_uuid: str, **overrides) -> str:
task_config = {
"project_id": project_id,
"node_id": node_uuid,
}
task_config.update(**overrides)
with postgres_db.connect() as conn:
result = conn.execute(
comp_tasks.insert() # pylint: disable=no-value-for-parameter
.values(**task_config)
.returning(comp_tasks.c.task_id)
)
new_task_id = result.first()[comp_tasks.c.task_id]
created_task_ids.append(new_task_id)
return node_uuid
yield creator
# cleanup
with postgres_db.connect() as conn:
conn.execute(
comp_tasks.delete().where( # pylint: disable=no-value-for-parameter
comp_tasks.c.task_id.in_(created_task_ids)
)
)
def pipeline(
postgres_db: sa.engine.Engine,
) -> Iterable[Callable[..., CompPipelineAtDB]]:
created_pipeline_ids: List[str] = []
def creator(**overrides) -> CompPipelineAtDB:
pipeline_config = {
"project_id": f"{uuid4()}",
"dag_adjacency_list": {},
"state": StateType.NOT_STARTED,
}
pipeline_config.update(**overrides)
with postgres_db.connect() as conn:
result = conn.execute(comp_pipeline.insert().values(
**pipeline_config).returning(literal_column("*")))
new_pipeline = CompPipelineAtDB.parse_obj(result.first())
created_pipeline_ids.append(f"{new_pipeline.project_id}")
return new_pipeline
yield creator
# cleanup
with postgres_db.connect() as conn:
conn.execute(comp_pipeline.delete().where(
comp_pipeline.c.project_id.in_(created_pipeline_ids)))
def registered_user(postgres_db: sa.engine.Engine,
faker: Faker) -> Iterator[Callable[..., Dict]]:
created_user_ids = []
def creator(**user_kwargs) -> Dict[str, Any]:
with postgres_db.connect() as con:
# removes all users before continuing
user_config = {
"id": len(created_user_ids) + 1,
"name": faker.name(),
"email": faker.email(),
"password_hash": faker.password(),
"status": UserStatus.ACTIVE,
"role": UserRole.USER,
}
user_config.update(user_kwargs)
con.execute(users.insert().values(user_config).returning(
sa.literal_column("*")))
# this is needed to get the primary_gid correctly
result = con.execute(
sa.select([users]).where(users.c.id == user_config["id"]))
user = result.first()
assert user
created_user_ids.append(user["id"])
return dict(user)
yield creator
with postgres_db.connect() as con:
con.execute(users.delete().where(users.c.id.in_(created_user_ids)))
def cluster(
user_db: Dict,
postgres_db: sa.engine.Engine,
) -> Iterable[Callable[..., Cluster]]:
created_cluster_ids: List[str] = []
def creator(**overrides) -> Cluster:
cluster_config = Cluster.Config.schema_extra["examples"][0]
cluster_config["owner"] = user_db["primary_gid"]
cluster_config.update(**overrides)
new_cluster = Cluster.parse_obj(cluster_config)
assert new_cluster
with postgres_db.connect() as conn:
created_cluser_id = conn.scalar(
# pylint: disable=no-value-for-parameter
clusters.insert().values(
new_cluster.to_clusters_db(only_update=False)
).returning(clusters.c.id))
created_cluster_ids.append(created_cluser_id)
result = conn.execute(
sa.select([
clusters,
cluster_to_groups.c.gid,
cluster_to_groups.c.read,
cluster_to_groups.c.write,
cluster_to_groups.c.delete,
]).select_from(
clusters.join(
cluster_to_groups,
clusters.c.id == cluster_to_groups.c.cluster_id,
)).where(clusters.c.id == created_cluser_id))
row = result.fetchone()
assert row
return Cluster.construct(
id=row[clusters.c.id],
name=row[clusters.c.name],
description=row[clusters.c.description],
type=row[clusters.c.type],
owner=row[clusters.c.owner],
endpoint=row[clusters.c.endpoint],
authentication=row[clusters.c.authentication],
access_rights={
row[clusters.c.owner]: {
"read": row[cluster_to_groups.c.read],
"write": row[cluster_to_groups.c.write],
"delete": row[cluster_to_groups.c.delete],
}
},
)
yield creator
# cleanup
with postgres_db.connect() as conn:
conn.execute(
# pylint: disable=no-value-for-parameter
clusters.delete().where(clusters.c.id.in_(created_cluster_ids)))
def inject_tables(postgres_db: sa.engine.Engine):
stmt = text("""\
INSERT INTO "group_classifiers" ("id", "bundle", "created", "modified", "gid", "uses_scicrunch") VALUES
(2, '{"vcs_ref": "asdfasdf", "vcs_url": "https://foo.classifiers.git", "build_date": "2021-01-20T15:19:30Z", "classifiers": {"project::dak": {"url": null, "logo": null, "aliases": [], "related": [], "markdown": "", "released": null, "classifier": "project::dak", "created_by": "Nicolas Chavannes", "github_url": null, "display_name": "DAK", "wikipedia_url": null, "short_description": null}, "organization::zmt": {"url": "https://zmt.swiss/", "logo": null, "aliases": ["Zurich MedTech AG"], "related": [], "markdown": "Zurich MedTech AG (ZMT) offers tools and best practices for targeted life sciences applications to simulate, analyze, and predict complex and dynamic biological processes and interactions. ZMT is a member of Zurich43", "released": null, "classifier": "organization::zmt", "created_by": "crespo", "github_url": null, "display_name": "ZMT", "wikipedia_url": null, "short_description": "ZMT is a member of Zurich43"}}, "collections": {"jupyterlab-math": {"items": ["crespo/osparc-demo"], "markdown": "Curated collection of repositories with examples of notebooks to run in jupyter-python-octave-math service", "created_by": "crespo", "display_name": "jupyterlab-math"}}}', '2021-03-04 23:17:43.373258', '2021-03-04 23:17:43.373258', 1, '0');
""")
with postgres_db.connect() as conn:
conn.execute(stmt)
def tasks(
postgres_db: sa.engine.Engine
) -> Iterable[Callable[..., List[CompTaskAtDB]]]:
created_task_ids: List[int] = []
def creator(project: ProjectAtDB, **overrides) -> List[CompTaskAtDB]:
created_tasks: List[CompTaskAtDB] = []
for internal_id, (node_id,
node_data) in enumerate(project.workbench.items()):
task_config = {
"project_id":
f"{project.uuid}",
"node_id":
f"{node_id}",
"schema": {
"inputs": {},
"outputs": {}
},
"inputs": {
key:
json.loads(value.json(by_alias=True, exclude_unset=True))
if isinstance(value, BaseModel) else value
for key, value in node_data.inputs.items()
} if node_data.inputs else {},
"outputs": {
key:
json.loads(value.json(by_alias=True, exclude_unset=True))
if isinstance(value, BaseModel) else value
for key, value in node_data.outputs.items()
} if node_data.outputs else {},
"image":
Image(
name=node_data.key,
tag=node_data.version,
).dict(by_alias=True, exclude_unset=True),
"node_class":
to_node_class(node_data.key),
"internal_id":
internal_id + 1,
"submit":
datetime.utcnow(),
}
task_config.update(**overrides)
with postgres_db.connect() as conn:
result = conn.execute(comp_tasks.insert().values(
**task_config).returning(literal_column("*")))
new_task = CompTaskAtDB.parse_obj(result.first())
created_tasks.append(new_task)
created_task_ids.extend(
[t.task_id for t in created_tasks if t.task_id])
return created_tasks
yield creator
# cleanup
with postgres_db.connect() as conn:
conn.execute(comp_tasks.delete().where(
comp_tasks.c.task_id.in_(created_task_ids)))
def user_id(postgres_engine: sa.engine.Engine) -> Iterable[int]:
# inject user in db
# NOTE: Ideally this (and next fixture) should be done via webserver API but at this point
# in time, the webserver service would bring more dependencies to other services
# which would turn this test too complex.
# pylint: disable=no-value-for-parameter
stmt = users.insert().values(**random_user(name="test")).returning(users.c.id)
print(str(stmt))
with postgres_engine.connect() as conn:
result = conn.execute(stmt)
[usr_id] = result.fetchone()
yield usr_id
with postgres_engine.connect() as conn:
conn.execute(users.delete().where(users.c.id == usr_id))
def get_migration_ddl(engine: sqlalchemy.engine.Engine) -> [str]:
"""
Creates a diff between the defined SQLAlchemy models and the current database and applies it.
Prints the executed statements to stdout
"""
combined_meta_data = sqlalchemy.MetaData()
# merge all loaded SQL alchemy models into a single metadata object
for declarative_base in ([
obj for obj in gc.get_objects()
if isinstance(obj, sqlalchemy.ext.declarative.api.DeclarativeMeta)
]):
for (table_name, table) in declarative_base.metadata.tables.items():
combined_meta_data._add_table(table_name, table.schema, table)
with engine.connect() as connection:
output = io.StringIO()
ddl = []
diff_context = alembic.migration.MigrationContext(connection.dialect,
connection,
opts={})
autogen_context = alembic.autogenerate.api.AutogenContext(
diff_context,
opts={
'sqlalchemy_module_prefix': 'sqlalchemy.',
'alembic_module_prefix': 'executor.'
})
execution_context = alembic.migration.MigrationContext(
connection.dialect,
connection,
opts={
'output_buffer': output,
'as_sql': True
})
executor = alembic.operations.Operations(execution_context)
# Step 1: create a diff between the meta data and the data base
# operations is a list of MigrateOperation instances, e.g. a DropTableOp
operations = alembic.autogenerate.produce_migrations(
diff_context, combined_meta_data).upgrade_ops.ops
for operation in operations:
# Step 2: autogenerate a python statement from the operation, e.g. "executor.drop_table('bar')"
renderer = alembic.autogenerate.renderers.dispatch(operation)
statement = renderer(autogen_context, operation)
if isinstance(statement, list): statement = statement[0]
# Step 3: "execute" python statement and get sql from buffer, e.g. "DROP TABLE bar;"
eval(statement)
ddl.append(output.getvalue())
output.truncate(0)
output.seek(0)
return ddl
def _update_sequence(db_engine: sqlalchemy.engine.Engine, seq_name,
table_name):
""" Update the specified sequence's value to the maximum value of ID in the table.
Args:
seq_name (str): the name of the sequence to be updated.
table_name (str): the name of the table from which the maximum value is to be retrieved
"""
with db_engine.connect() as connection:
connection.execute(
sqlalchemy.text("""
SELECT setval('{seq_name}', max(id))
FROM {table_name}
""".format(seq_name=seq_name, table_name=table_name)))
def init_db(engine: sqlalchemy.engine.Engine):
"""
Create all required tables and insert prerequisite data.
"""
metadata.create_all(engine)
with engine.connect() as conn:
try:
conn.execute(account.insert().values(
id=settings.TOPUP_ACCOUNT_ID,
username="******",
password="",
))
except sqlalchemy.exc.IntegrityError:
pass
def query_db(engine: sqlalchemy.engine.Engine,
query: str = None) -> pd.DataFrame:
"""
Queries a data base with the string and returns the resulting dataframe
:param engine: sqlalchemy engine used to make a connection
:param query: sql query string, if not provide a default query will be used
:return: resulting data frame from the query
"""
if not query:
query = _DEFAULT_QUERY.format('England')
with engine.connect() as cxn:
data = pd.read_sql(query, cxn)
return data
def execute_queries(
postgres_engine: sa.engine.Engine,
sql_statements: List[str],
ignore_errors: bool = False,
) -> None:
"""runs the queries in the list in order"""
with postgres_engine.connect() as con:
for statement in sql_statements:
try:
con.execution_options(autocommit=True).execute(statement)
except Exception as e: # pylint: disable=broad-except
# when running tests initially the TEMPLATE_DB_TO_RESTORE dose not exist and will cause an error
# which can safely be ignored. The debug message is here to catch future errors which and
# avoid time wasting
log.debug("SQL error which can be ignored %s", str(e))
def raw_sqlalchemy_query_to_pandas_dataframe(e: sqlalchemy.engine.Engine,
sql: str) -> pd.DataFrame:
connection = e.connect()
rs = connection.execute(sql)
data = {}
keys = rs.keys()
for key in keys:
data[key] = []
for row in rs:
for key, value in zip(keys, row):
data[key].append(value)
return pd.DataFrame(data)
def user_db(postgres_db: sa.engine.Engine, user_id: PositiveInt) -> Dict:
with postgres_db.connect() as con:
result = con.execute(users.insert().values(
id=user_id,
name="test user",
email="*****@*****.**",
password_hash="testhash",
status=UserStatus.ACTIVE,
role=UserRole.USER,
).returning(literal_column("*")))
user = result.first()
yield dict(user)
con.execute(users.delete().where(users.c.id == user["id"]))
def watcher_thread(
conn_string: str,
engine: db.engine.Engine,
handlers_dict: MutableMapping[str, List[CallbackAfterCursor]],
dict_lock: threading.Lock,
watcher_thread_exit: threading.Event,
watcher_thread_started: threading.Event,
):
for notif in await_pg_notifications(
conn_string,
channels=[CHANNEL_NAME],
timeout=POLLING_CADENCE,
yield_on_timeout=True,
exit_event=watcher_thread_exit,
started_event=watcher_thread_started,
):
if notif is None:
if watcher_thread_exit.is_set():
break
else:
run_id, index_str = notif.payload.split("_")
with dict_lock:
if run_id not in handlers_dict:
continue
index = int(index_str)
with dict_lock:
handlers = handlers_dict.get(run_id, [])
with engine.connect() as conn:
cursor_res = conn.execute(
db.select([
SqlEventLogStorageTable.c.event
]).where(SqlEventLogStorageTable.c.id == index), )
dagster_event: EventLogEntry = deserialize_json_to_dagster_namedtuple(
cursor_res.scalar())
for callback_with_cursor in handlers:
if callback_with_cursor.start_cursor < index:
try:
callback_with_cursor.callback(dagster_event)
except Exception:
logging.exception(
"Exception in callback for event watch on run %s.",
run_id)
def pipeline(postgres_db: sa.engine.Engine) -> Callable[[str], str]:
created_pipeline_ids: List[str] = []
def creator(project_id: str) -> str:
with postgres_db.connect() as conn:
result = conn.execute(comp_pipeline.insert() # pylint: disable=no-value-for-parameter
.values(project_id=project_id).returning(
comp_pipeline.c.project_id))
new_pipeline_id = result.first()[comp_pipeline.c.project_id]
created_pipeline_ids.append(f"{new_pipeline_id}")
return new_pipeline_id
yield creator
# cleanup
with postgres_db.connect() as conn:
conn.execute(comp_pipeline.delete().where( # pylint: disable=no-value-for-parameter
comp_pipeline.c.project_id.in_(created_pipeline_ids)))
def setInfoKeys(db: sqlalchemy.engine.Engine,
keys: typing.Mapping[typing.Text, typing.Text]):
rowcount = 0
sqlite_keys = {
schemaconverter.convertToSqlite(SQLITE_TXT, key, True):
schemaconverter.convertToSqlite(SQLITE_OBJ, val, True)
for key, val in keys.items()
}
# if empty keys do nothing
if len(keys) > 0:
conn = db.connect()
res = conn.execute(info_table.insert(), [{
"key": key,
"value": value
} for key, value in sqlite_keys.items()])
rowcount = res.rowcount
conn.close()
return {"count": rowcount}
def user_db(postgres_db: sa.engine.Engine, user_id: PositiveInt) -> Dict:
with postgres_db.connect() as con:
# removes all users before continuing
con.execute(users.delete())
con.execute(users.insert().values(
id=user_id,
name="test user",
email="*****@*****.**",
password_hash="testhash",
status=UserStatus.ACTIVE,
role=UserRole.USER,
).returning(literal_column("*")))
# this is needed to get the primary_gid correctly
result = con.execute(select([users]).where(users.c.id == user_id))
user = result.first()
yield dict(user)
con.execute(users.delete().where(users.c.id == user_id))
def get_dominant_foot(shots_df: pd.DataFrame,
engine: sqlalchemy.engine.Engine) -> pd.Series:
"""
Cross-references the player database to see if the shot was taken with the player's dominant
foot. The values mapped are as follows:
-1 for non-dominant foot kick,
0 for head/body,
+1 for dominant foot kick
:param shots_df: data frame of shots taken
:param engine: sqlalchemy engine
:return: series for the shots taken of:
"""
query = """SELECT "wyId", "foot"
FROM players;"""
with engine.connect() as cxn:
player_foot = pd.read_sql(query, cxn)
player_foot_dict = pd.Series(player_foot['foot'],
index=player_foot['wyId']).to_dict()
def foot_mapping_func(row) -> int:
"""
mapping function because it was too confusing to use lambda
only one (left, right, or head) should trigger so to get the correct range we need weight
them appropriately, if none trigger, then not a dominant foot, so it should return -1
if head triggers, then it returns to 0
if left or right trigger then it returns +1
:param row: needs columns: '401', '402', '403', and 'playerId' to be in the row
:return: integer representation of whether it is a dominant foot
"""
left = row['401'] & (player_foot_dict.get(row['playerId'], 0)
in ['left', 'both'])
right = row['402'] & (player_foot_dict.get(row['playerId'], 0)
in ['right', 'both'])
head = row['403'] # TAG: numbers
return (2 * left) + (2 * right) + (1 * head) - 1
return shots_df.apply(foot_mapping_func, axis=1)
def wait_for_db(
engine: sa.engine.Engine,
num_retries=5,
linear_backoff_secs=2,
):
wait_secs = 0
while True:
num_retries -= 1
wait_secs += linear_backoff_secs
try:
with engine.connect():
return
except sa.exc.OperationalError:
if num_retries:
print(
f"Waiting for DB to accept connections, retrying {num_retries}"
f" more times in {wait_secs} seconds...")
time.sleep(wait_secs)
else:
raise
def test_encrypt_and_insert_data(capsys: pytest.CaptureFixture,
pool: sqlalchemy.engine.Engine,
env_aead: tink.aead.KmsEnvelopeAead) -> None:
encrypt_and_insert_data(pool, env_aead, table_name, "SPACES",
"*****@*****.**")
captured = capsys.readouterr()
decrypted_emails = []
with pool.connect() as conn:
results = conn.execute(
f"SELECT TOP(5) team, time_cast, voter_email FROM {table_name} "
"ORDER BY time_cast DESC").fetchall()
for row in results:
team = row[0]
email = env_aead.decrypt(row[2], team).decode()
decrypted_emails.append(email)
assert "Vote successfully cast for 'SPACES'" in captured.out
assert "*****@*****.**" in decrypted_emails
def insert_into_table(df: pd.DataFrame, table_name: str,
engine: sa.engine.Engine, schema: str) -> int:
"""
:param df: a dataframe with same column names as those in the database table
:param table_name: a table name as in util_function
:param engine: the sqlalchemy engine for the database
:param schema: a schema of interest - None if default schema of database is ok
:return: the number of records inserted
"""
# get the table
tbl = util_function(table_name, engine, schema)
# change all nan to None
groups = toolz.partition_all(
CHUNK_SIZE,
df.where(pd.notnull(df), None).to_dict(orient='records'))
# insert
count, last_successful_insert = 0, None
with engine.connect() as connection:
for group in groups:
try:
result = connection.execute(tbl.insert(), group)
last_successful_insert = group[-1]
count += result.rowcount
except exc.OperationalError as _:
"Try Again"
time.sleep(2)
try:
result = connection.execute(tbl.insert(), group)
last_successful_insert = group[-1]
count += result.rowcount
except exc.OperationalError as e:
raise OperationalError(
f"Failed to insert records. Last successful{last_successful_insert}",
e)
return count
def update_configuration(
postgres_engine: sa.engine.Engine,
project_id: str,
node_uuid: str,
new_configuration: Dict,
) -> None:
log.debug(
"Update configuration of pipeline %s, node %s",
project_id,
node_uuid,
)
with postgres_engine.connect() as conn:
conn.execute(comp_tasks.update() # pylint: disable=no-value-for-parameter
.where((comp_tasks.c.project_id == project_id)
& (comp_tasks.c.node_id == node_uuid)).values(
schema=new_configuration["schema"],
inputs=new_configuration["inputs"],
outputs=new_configuration["outputs"],
))
log.debug("Updated configuration")
