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)