def insert_multiple(application_id: str, account_name: str,
followers: Iterable, source: str, cursor: str,
conn: Connection) -> None:
"""Add followers for one account into the account relationships table"""
trans = conn.begin()
try:
account_id = select_one_id(application_id, account_name, source, conn)
for follower in followers:
follower_id = select_one_id(application_id, follower, source, conn)
if not follower_id:
follower_id = insert_one(application_id, follower, False,
source, conn)
stmt_relationship = insert(account_relationship,
values={
'account': account_id,
'follower': follower_id
})
conn.execute(stmt_relationship)
update_one_cursor(account_id, cursor, conn)
trans.commit()
except Exception as e:
trans.rollback()
def update(connection: Connection, data: dict):
"""
Update a survey (title, questions). You can also add or modify questions
here. Note that this creates a new survey (with new submissions, etc),
copying everything from the old survey. The old survey's title will be
changed to end with "(new version created on <time>)".
:param connection: a SQLAlchemy Connection
:param data: JSON containing the UUID of the survey and fields to update.
"""
survey_id = data['survey_id']
email = data['email']
existing_survey = survey_select(connection, survey_id, email=email)
if 'survey_metadata' not in data:
data['survey_metadata'] = existing_survey.survey_metadata
update_time = datetime.datetime.now()
with connection.begin():
new_title = '{} (new version created on {})'.format(
existing_survey.survey_title, update_time.isoformat())
executable = update_record(survey_table, 'survey_id', survey_id,
survey_title=new_title)
exc = [('survey_title_survey_owner_key',
SurveyAlreadyExistsError(new_title))]
execute_with_exceptions(connection, executable, exc)
new_survey_id = _create_survey(connection, data)
return get_one(connection, new_survey_id, email=email)
def test_delete_dummy_obj(pg_conn: Connection, test_conf: TestSettings,
app_fixture: TestClient) -> None:
with app_fixture as client:
put_result = client.put(
"/dummy_db_obj/",
json=test_conf.request_data["json"],
)
assert put_result.status_code == 200
with pg_conn.begin():
result = pg_conn.execute(DummyDBModel.select_query(
DummyDBModel.id)).first()
assert len(result) == 1
result = client.delete(f"/dummy_db_obj/{result[0]}")
assert result.status_code == 200
with pg_conn.begin():
result = pg_conn.execute(sa.func.count(DummyDBModel.id)).first()
assert result[0] == 0
def import_nivo(con: Connection, csv_file: ANivoCsv) -> None:
csv_file.normalize()
csv_file.find_and_replace_foreign_key_value()
with con.begin():
ins = insert(NivoRecordTable).values(
csv_file.cleaned_csv
) # .on_conflict_do_nothing(index_elements=['nss_name'])
con.execute(ins)
def refresh_and_diff_materialized_view(
connection: Connection, view: Table, copy: Table,
result_columns: Iterable[Column]
) -> Tuple[List[Tuple], List[Tuple], List[Tuple]]:
with connection.begin():
lock_table(connection, view)
create_temp_copy(connection, view, copy)
refresh_materialized_view(connection, view)
return diff_tables(connection, view, copy, result_columns)
def refresh_and_diff_materialized_view(
connection: Connection, view: Table, copy: Table,
result_columns: Iterable[Column]) -> Tuple[
List[Tuple], List[Tuple], List[Tuple]]:
with connection.begin():
lock_table(connection, view)
create_temp_copy(connection, view, copy)
refresh_materialized_view(connection, view)
return diff_tables(connection, view, copy, result_columns)
def save_ciks(conn: Connection = prelim_engine):
"""Loads the cik table from the csv file in the resources directory."""
df = pd.read_csv(
"resources/cik.csv",
converters={col.name: str
for col in prelim_cik_table.columns})
with conn.begin() as c:
c.execute('truncate table cik')
df.to_sql(prelim_cik_table.name, c, if_exists='append', index=False)
def delete(connection: Connection, survey_id: str):
"""
Delete the survey specified by the given survey_id
:param connection: a SQLAlchemy connection
:param survey_id: the UUID of the survey
"""
with connection.begin():
connection.execute(delete_record(survey_table, 'survey_id', survey_id))
return json_response('Survey deleted')
def create(connection: Connection, data: dict) -> dict:
"""
Create a survey with questions.
:param connection: a SQLAlchemy Connection
:param data: a JSON representation of the survey to be created
:return: a JSON representation of the created survey
"""
with connection.begin():
survey_id = _create_survey(connection, data)
return get_one(connection, survey_id, email=data['email'])
def upgrade(self, connection: Connection):
tx = connection.begin()
connection.execute("""create table villain_templates(
id int not null auto_increment,
name text not null,
face_image_url text not null,
primary key (id)
);""")
connection.execute("""
alter table villain_templates
add unique index `villain_template_name_idx` (`name`);
""")
tx.commit()
def upgrade(self, connection: Connection):
tx = connection.begin()
connection.execute("""create table players(
id int not null auto_increment,
username text not null,
password_salt text not null,
password_hash text not null,
location_name text not null,
primary key (id)
);""")
connection.execute("""
create index players_username_idx
on players (username);
""")
tx.commit()
def upgrade(self, connection: Connection):
tx = connection.begin()
connection.execute("""create table weathers(
`id` int not null auto_increment,
`location` text not null,
`temperature` float not null,
`phrase` text not null,
`on` datetime not null,
primary key (id)
);""")
# connection.execute("""
# alter table weathers
# add unique index `weathers_daily_idx` (`name`, `location`, `on`);
# """)
tx.commit()
def create_user(connection: Connection, data: dict) -> dict:
"""
Registers a new user account.
:param connection: a SQLAlchemy Connection
:param data: the user's e-mail
:return: a response containing the e-mail and whether it was created or
already exists in the database
"""
email = data['email']
try:
get_auth_user_by_email(connection, email)
except UserDoesNotExistError:
with connection.begin():
connection.execute(create_auth_user(email=email))
return json_response({'email': email, 'response': 'Created'})
return json_response({'email': email, 'response': 'Already exists'})
def update_channels_order(db: Connection, user_id: int, channels: List[int]) -> None:
"""
Update order of channels on dashboard for specified user.
"""
trans = db.begin()
try:
new_items = [dict(user_id=user_id, channel_id=channel_id, order=order)
for order, channel_id in enumerate(channels, 1)]
query = delete(CHANNELS_ORDER).where(CHANNELS_ORDER.c.user_id == user_id)
db.execute(query)
query = insert(CHANNELS_ORDER)
db.execute(query, new_items)
trans.commit()
except:
trans.rollback()
raise
def persist_bra(con: Connection, bra: List[Dict]):
"""
This function is dumb, because it does not take care of the case when bra already exist
"""
with con.begin():
for entities in bra:
for e, data in entities.items():
# https://docs.sqlalchemy.org/en/13/core/tutorial.html#executing-multiple-statements
if isinstance(data, Generator):
# execute is not capable of understanding a generator. But it understand list.
# behind the scene, the DBAPI `executemany` is called.
intermediate_data = list()
for x in data:
if x:
intermediate_data.append(x)
data = intermediate_data
# data can be null (generator yield None) then no need to execute
if data:
con.execute(insert(e), data)
def insert_class_objects(mm_conn: Connection, mm_meta, db_engine, db_meta,
class_name, class_map, attr_map, rel_map, obj_v_map,
obj_hash_map):
t1 = time.time()
trans: Transaction = mm_conn.begin()
try:
source_table: Table = db_meta.tables.get(class_name)
num_objs = db_engine.execute(source_table.count()).scalar()
q = source_table.select()
conn = db_engine.raw_connection()
cursor = conn.cursor()
cursor.execute(
str(
q.compile(dialect=db_engine.dialect,
compile_kwargs={"literal_binds": True})))
i = 0
obj = _get_dict_from_cursor(cursor)
with tqdm(desc='Objects', total=num_objs) as tpb:
while obj:
insert_object(mm_conn, obj, source_table, class_name,
class_map, attr_map, rel_map, obj_v_map,
obj_hash_map, mm_meta)
obj = _get_dict_from_cursor(cursor)
tpb.update(1)
i += 1
if i > 1000:
i = 0
try:
obj_v_map.sync()
obj_hash_map.sync()
except:
pass
trans.commit()
except:
trans.rollback()
raise
t2 = time.time()
time_diff = t2 - t1
def generate_token(connection: Connection, data: dict) -> dict:
"""
Generates a new API token for a user specified by e-mail address. You
can supply a duration in seconds.
:param connection: a SQLAlchemy Connection
:param data: the user's e-mail and an optional duration
:return: the generated token and the token's expiration time
"""
user = get_auth_user_by_email(connection, data['email'])
token = generate_api_token()
params = {'token': token,
'auth_user_id': user.auth_user_id}
if 'duration' in data:
duration = float(data['duration'])
if duration > 31536000:
raise TokenDurationTooLong(data['duration'])
params['expiration'] = timedelta(seconds=duration)
with connection.begin():
connection.execute(set_api_token(**params))
updated_user = get_auth_user_by_email(connection, data['email'])
return json_response(
{'token': token, 'expires_on': updated_user.expires_on.isoformat()})
def boot_db(dialect: str, engine: Connection):
with engine.begin() as conn:
creation_steps = BOOT_DB_REQUEST[dialect]
for step in creation_steps:
conn.execute(step)
