def test_handle_redirect_reregister( mock_jwt_required, mock_jwt_identity, client: testing.FlaskClient, conn: connection, ): user = conftest.users[0] h_user = register_user(user, conn) token = fake_token(user) service_user_id = queries.service_user_id_for_gargling_id( conn, gargling_id=user.id, token_gargling_table=f"{GooglefitService.name}_token_gargling", )["service_user_id"] mock_jwt_identity.return_value = h_user.gargling_id with patch("gargbot_3000.health.googlefit.GooglefitService.token") as mock_handler: mock_handler.return_value = None, token response = client.get("/googlefit/redirect", query_string={"code": "123"}) assert response.status_code == 200 with conn.cursor() as cursor: cursor.execute( "SELECT * FROM googlefit_token where id = %(fake_user_id)s", {"fake_user_id": service_user_id}, ) data = cursor.fetchone() assert data["access_token"] == token.token assert data["refresh_token"] == token.refresh_token assert pendulum.from_timestamp((data["expires_at"])) == token.expiry with conn.cursor() as cursor: cursor.execute( "SELECT gargling_id " f"FROM googlefit_token_gargling where service_user_id = %(fake_user_id)s", {"fake_user_id": service_user_id}, ) data = cursor.fetchone() assert data["gargling_id"] == h_user.gargling_id
def test_handle_redirect( mock_jwt_required, mock_jwt_identity, client: testing.FlaskClient, conn: connection, ): user = conftest.users[0] token = fake_token(user) fake_id = token["x_user_id"] mock_jwt_identity.return_value = user.id with patch("gargbot_3000.health.polar.PolarService.token") as mock_handler: mock_handler.return_value = fake_id, token response = client.get("/polar/redirect", query_string={"code": "123"}) assert response.status_code == 200 with conn.cursor() as cursor: cursor.execute( "SELECT * FROM polar_token where id = %(fake_user_id)s", {"fake_user_id": fake_id}, ) data = cursor.fetchone() assert data["id"] == fake_id assert data["access_token"] == token["access_token"] with conn.cursor() as cursor: cursor.execute( "SELECT * FROM polar_token_gargling where service_user_id = %(fake_user_id)s", {"fake_user_id": fake_id}, ) data = cursor.fetchone() assert data["gargling_id"] == user.id
def test_multiple_users_exclusive(conn: connection, dbx: conftest.MockDropbox) -> None: users = ["slack_nick2", "slack_nick3"] exclusive_pic = "test_pic4" # get seed that returns nonexclusive for seed in range(0, 20): with conn.cursor() as cursor: cursor.execute(f"select setseed(0.{seed})") url1, timestamp, description = pictures.get_pic(conn, dbx, arg_list=users) assert_valid_returns(url1, timestamp, description) if not url1.endswith(exclusive_pic): break else: # no test coverage raise Exception("could not find good seed") with conn.cursor() as cursor: cursor.execute(f"select setseed(0.{seed})") url2, timestamp, description = pictures.get_pic(conn, dbx, arg_list=["kun"] + users) assert_valid_returns(url2, timestamp, description) assert url2.endswith(exclusive_pic) for _ in range(10): url3, timestamp, description = pictures.get_pic(conn, dbx, arg_list=["kun"] + users) assert_valid_returns(url3, timestamp, description) pic = next(pic for pic in conftest.pics if url3.endswith(pic.path)) assert pic.faces == [2, 3], f"Wrong picture {pic}"
def transfer_by_name_tpc(from_connection: connection, from_name: str, to_connection: connection, to_name: str, amount: Union[Decimal, int]): with from_connection.cursor() as from_cursor, to_connection.cursor() as to_cursor: try: from_cursor.execute("UPDATE USERS SET current_amount = current_amount - %s WHERE name=%s", [amount, from_name]) to_cursor.execute("UPDATE USERS SET current_amount = current_amount + %s WHERE name=%s", [amount, to_name]) except IntegrityError: raise MoneyAmountError()
def create_table_from_csv( conn: connection, table: str, csv_file: str, delim: str = ';', indexes: dict = None ) -> bool: """Creates a table in PostgreSQL from a CSV header. If the table exists, it does nothing. :param conn: Psycopg2 connection to DB. :param table: (str) Name of the new table. :param csv_file: (str) Path of the CSV file. :param delim: (str) Delimiter used in the CSV. :param indexes: (dict) Indexes to create in format index_name -> [columns] :return: (bool) True if a new table was created. """ query0 = """ select exists ( select * from information_schema.tables where table_name = %s); """ query1 = sql.SQL('create table if not exists {} ();') query2 = sql.SQL('alter table {} add column {} %s;') with conn.cursor() as cur: cur.execute(query0, (table,)) exists = cur.fetchone()[0] if exists: return False columns = get_columns(csv_file, delim=delim) # because table and column names are variable # create first an empty table, then add columns # it may raise with conn.cursor() as cur: cur.execute(query1.format(sql.Identifier(table))) for key, val in columns.items(): col_name = key col_type = map_numpy_psql(val) cur.execute(query2.format( sql.Identifier(table), sql.Identifier(col_name)), (AsIs(col_type),) ) if isinstance(indexes, dict): for key, val in indexes.items(): create_index(conn, table, key, val) conn.commit() return True
def query_db_without_commit(conn: connection, query: str, query_args: Query_Args): """ Performs an operation on a database without committing the result. """ cursor = conn.cursor() cursor.execute(query, query_args)
def insert(conn: connection, user: User): with conn.cursor() as cursor: cursor.execute( "INSERT INTO USERS (name, min_amount, max_amount, current_amount) VALUES (%s, %s, %s, %s) RETURNING id", [user.name, user.min_, user.max_, user.current] ) user.id_ = cursor.fetchone()[0]
def create_article( db_connection: connection, article_input: ArticleInput, author_id: int, created_date: datetime, ) -> Article: cursor = db_connection.cursor(cursor_factory=DictCursor) cursor.execute( """ INSERT INTO articles( title, author_id, content, created_date ) VALUES ( %s, %s, %s, %s ) RETURNING id, title, author_id, content, status_id, created_date; """, ( article_input.title, author_id, article_input.content, created_date, )) created_article = cursor.fetchone() db_connection.commit() cursor.execute( """ SELECT article_statuses.name FROM article_statuses WHERE article_statuses.id = %s; """, (created_article['status_id'], )) article_status = cursor.fetchone() cursor.close() return Article( id=created_article['id'], title=created_article['title'], content=created_article['content'], author_id=created_article['author_id'], status=article_status['name'], created_date=created_article['created_date'], )
def garg(self, db: connection, args: List[str]): user = args[0] if args else None if user and user not in self.slack_nicks_to_db_ids: return f"Gargling not found: {user}. Husk å bruke slack nick" if user: user_filter = f"= {self.slack_nicks_to_db_ids[user]}" else: user_filter = "IN (2, 3, 5, 6, 7, 9, 10, 11)" sql = ( "SELECT db_id, post_text, post_time, post_id, bbcode_uid " f"FROM phpbb_posts WHERE db_id {user_filter} ORDER BY RANDOM() LIMIT 1" ) cursor = db.cursor() cursor.execute(sql) result = cursor.fetchone() db_id = result["db_id"] post_id = result["post_id"] user = user if user is not None else self.db_ids_to_slack_nicks[db_id] post = self._sanitize(result["post_text"], result["bbcode_uid"]) quote = ( f"{post}\n" "------\n" f"- {user}\n" f"http://eirik.stavestrand.no/gargen/viewtopic.php?p={post_id}#p{post_id}\n" ) return quote
def get_assets(connection: extensions.connection) -> Iterator[Asset]: """ Get assets. :param connection: A database connection. :return: The assets. """ sql = ''' select asset.asset_uid, is_sensor_type.avro_schema_name from asset, is_asset_assignment, is_asset_definition, is_sensor_type where asset.asset_uid = is_asset_assignment.asset_uid and is_asset_assignment.asset_definition_uuid = is_asset_definition.asset_definition_uuid and is_asset_definition.sensor_type_name = is_sensor_type.sensor_type_name and is_sensor_type.avro_schema_name is not null ''' with closing(connection.cursor()) as cursor: cursor.execute(sql) rows = cursor.fetchall() for row in rows: asset_id = row[0] asset_type = row[1] yield Asset(id=asset_id, type=asset_type)
def select_row_count(table_name,conn:DB_CON): query="SELECT count(*) FROM "+table_name+" " cur=conn.cursor() cur.execute(query) query_results=cur.fetchall() print(query_results) conn.close()
def download_and_import_google_image_index(conn: connection) -> None: """Função para baixar e importar o índice de imagens do Google para um banco de dados PostGres com extensão espacial Args: conn (connection): Conexão com o banco de dados onde os dados serão inseridos Returns: None """ _tmp_dir = temp.tempdir() landsat_file = os.path.join(_tmp_dir, "landsat.csv.gz") sentinel_file = os.path.join(_tmp_dir, "sentinel_index_l2.csv.gz") __download_file(GOOGLE_INDEX_LANDSAT, landsat_file) __download_file(GOOGLE_INDEX_SENTINEL_L2, sentinel_file) # Extraíndo os dados sentinel_file_extracted = __extract_gzfile(sentinel_file) landsat_file_extracted = __extract_gzfile(landsat_file) try: cur = conn.cursor() cur.copy_from(__remove_firstline_in_file(landsat_file_extracted), "landsat_index", sep=",") cur.copy_from(__remove_firstline_in_file(sentinel_file_extracted), "sentinel_index", sep=",") conn.commit() except BaseException as error: warnings.warn( f"Problemas ao inserir os dados no banco: \n {str(error)}") shutil.rmtree(_tmp_dir, ignore_errors=True)
def get_visitor_permissions_and_role( db_connection: connection, ) -> Optional[Dict[str, Any]]: cursor = db_connection.cursor( cursor_factory=DictCursor ) cursor.execute(""" SELECT user_roles.role AS role, ARRAY_AGG(permissions.name) AS permissions FROM user_roles LEFT JOIN user_roles_to_permissions ON user_roles.id = user_roles_to_permissions.user_role_id INNER JOIN permissions ON user_roles_to_permissions.permission_id = permissions.id WHERE user_roles.role = 'VISITOR' GROUP BY user_roles.role; """) visitor_permissions_and_role = cursor.fetchone() cursor.close() return visitor_permissions_and_role
def get_named_location_schema_name(connection: extensions.connection, named_location_id: int) -> Set[str]: """ Get the schema name for a named location. :param connection: The database connection. :param named_location_id: The named location name. :return: The schema name. """ sql = ''' select is_sensor_type.avro_schema_name from is_sensor_type, is_asset_definition, is_asset_assignment, is_asset_location, nam_locn where is_sensor_type.sensor_type_name = is_asset_definition.sensor_type_name and is_asset_definition.asset_definition_uuid = is_asset_assignment.asset_definition_uuid and is_asset_assignment.asset_uid = is_asset_location.asset_uid and is_asset_location.nam_locn_id = nam_locn.nam_locn_id and is_sensor_type.avro_schema_name is not null and nam_locn.nam_locn_id = %s ''' schema_names = set() with closing(connection.cursor()) as cursor: cursor.execute(sql, [named_location_id]) rows = cursor.fetchall() for row in rows: schema_name = row[0] schema_names.add(schema_name) return schema_names
def get_threshold_context(connection: extensions.connection, threshold_uuid: str) -> List[str]: """ Get all context codes for a threshold. :param connection: A database connection. :param threshold_uuid: The threshold UUID. :return: The context codes. """ context_codes: List[str] = [] with closing(connection.cursor()) as cursor: sql = ''' select context_code from threshold_context where threshold_uuid = %s ''' cursor.execute(sql, [threshold_uuid]) rows = cursor.fetchall() for row in rows: context_code = row[0] context_codes.append(context_code) return context_codes
def sentences_you_should_know( db: connection, offset: Tuple[int, int] = (0, 0), limit: Optional[int] = None, ) -> Tuple[int, List[Tuple[int, str, List[str]]]]: with db.cursor() as cur: cur.execute('select max(lvl) from sentences') (max_lvl_at_sentences, ) = cur.fetchone() # Let's update lvl in sentences for all the sentences that have words updated in the # levels from max_lvl_at_sentences: cur.execute( """ with to_update(id) as ( select s_id from sentence_words where lvl >= %s group by s_id order by s_id) update sentences set lvl = ( select max(lvl) from sentence_words where s_id=id group by s_id having count(case when lvl is null then 1 end) = 0) where lvl is null and id in (select * from to_update) """, (0 if max_lvl_at_sentences is None else max_lvl_at_sentences + 1, )) if cur.rowcount: db.commit() # Then we can do a fast query on only sentences: cur.execute( """ select lvl, id, jpn, translations from sentences where (lvl, id) >= (%s, %s) order by lvl, id """ + ('limit %s' if limit is not None else ''), offset + ((limit, ) if limit is not None else ()), ) return max_lvl_at_sentences, cur.fetchall()
def test_persist_token(conn: connection): user = conftest.users[0] register_user(user, conn) with conn.cursor() as cur: cur.execute("select * from googlefit_token") tokens = cur.fetchall() cur.execute("select * from googlefit_token_gargling") matched = cur.fetchall() assert len(tokens) == 1 token = dict(tokens[0]) id1 = token.pop("id") exp = { "access_token": "access_token2", "refresh_token": "refresh_token2", "expires_at": expiry, "enable_steps": False, "enable_weight": False, } assert token == exp assert len(matched) == 1 match = dict(matched[0]) id2 = match.pop("service_user_id") exp = {"gargling_id": 2} assert match == exp assert id1 == id2
def effectuate_table( connector: connection, delete_rows: List[int], update_rows: Dict[int, str], table: str, ): """ Send updated data back to the database. """ cursor = connector.cursor(cursor_factory=RealDictCursor) cursor.execute("BEGIN") if delete_rows: if len(delete_rows) == 1: delete_statement = f"WHERE id = {delete_rows[0]}" else: delete_statement = f"WHERE id IN {tuple(delete_rows)}" cursor.execute( f""" DELETE FROM {table} {delete_statement}; """, ) for id, virkning in update_rows.items(): if virkning._upper.year == 9999: virkning._upper = "infinity" # lol cursor.execute( f""" UPDATE {table} SET virkning.TimePeriod=%(virkning)s WHERE id = %(id)s; """, {"id": id, "virkning": virkning}, ) cursor.execute("COMMIT")
def producer_generator(target: Coroutine, *, table_name: str, pg_conn: _connection, storage: BaseStorage): cursor = pg_conn.cursor() cursor.arraysize = config.LIMIT state_manager = State(storage=storage) default_date = str(datetime(year=1700, month=1, day=1)) current_state = state_manager.state.get(table_name, default_date) sql = f""" SELECT id, updated_at FROM content.{table_name} WHERE updated_at >= %s ORDER BY updated_at """ cursor.execute(sql, (current_state,)) while True: batch_result = cursor.fetchmany() ids_list = [item['id'] for item in batch_result] if not ids_list: break target.send(ids_list) state_manager.set_state( key=table_name, value=str(batch_result[-1]['updated_at']))
def __init__(self, db: connection) -> None: cursor = db.cursor() self.years = self.get_years(cursor) self.topics = self.get_topics(cursor) self.users = self.get_users(cursor) self.possible_args = self.topics | self.years | set(self.users) self._connect_dbx()
def populate_quotes_table(db: connection) -> None: with db.cursor() as cursor: for quote in quotes: sql_command = """INSERT INTO phpbb_posts (db_id, post_id, post_time, post_text, bbcode_uid) VALUES (%(db_id)s, %(post_id)s, %(post_time)s, %(post_text)s, %(bbcode_uid)s);""" data = { "db_id": quote.db_id, "post_id": quote.post_id, "post_time": quote.post_time, "post_text": quote.post_text, "bbcode_uid": quote.bbcode_uid, } cursor.execute(sql_command, data) for message in messages: sql_command = """INSERT INTO msn_messages (session_id, msg_time, msg_color, from_user, msg_text, db_id) VALUES (%(session_id)s, %(msg_time)s, %(msg_color)s, %(from_user)s, %(msg_text)s, %(db_id)s);""" data = { "session_id": message.session_id, "msg_time": message.msg_time, "msg_color": message.msg_color, "from_user": message.from_user, "msg_text": message.msg_text, "db_id": message.db_id, } cursor.execute(sql_command, data)
def get_named_location_context(connection: extensions.connection, named_location_id: int) -> List[str]: """ Get context entries for a named location. :param connection: A database connection. :param named_location_id: The named location ID. :return: The context entries. """ sql = ''' select context_code, context_group_id from nam_locn_context where nam_locn_id = %s ''' contexts: List[str] = [] with closing(connection.cursor()) as cursor: cursor.execute(sql, [named_location_id]) rows = cursor.fetchall() for row in rows: context_code = row[0] group = row[1] if group is None: contexts.append(context_code) else: group_name = f'{context_code}-{str(group)}' contexts.append(group_name) return contexts
def get_comments_count(db_connection: connection, article_id: Optional[int] = None) -> int: cursor = db_connection.cursor(cursor_factory=DictCursor) has_article_id_filter = article_id is not None cursor.execute( """ SELECT count(*) AS comments_count FROM comments WHERE CASE -- Has article_id filter? WHEN %s THEN comments.article_id = %s ELSE TRUE END; """, ( has_article_id_filter, article_id, )) result = cursor.fetchone() cursor.close() return result['comments_count']
def delete_database(conn: connection, test_db: bool): """Delete the database.""" if test_db: database = TestingConfig.POSTGRES_DB else: database = current_app.config["POSTGRES_DB"] cursor = conn.cursor() cursor.execute("ROLLBACK") # Don't validate name for `police_data_test`. if database != TestingConfig.POSTGRES_DB: # Make sure we want to do this. click.echo(f"Are you sure you want to delete database {database!r}?") click.echo("Type in the database name '" + click.style(database, fg="red") + "' to confirm") confirmation = click.prompt("Database name") if database != confirmation: click.echo("The input does not match. " "The database will not be deleted.") return None try: cursor.execute(f"DROP DATABASE {database};") except psycopg2.errors.lookup("3D000"): click.echo(f"Database {database!r} does not exist.") cursor.execute("ROLLBACK") else: click.echo(f"Database {database!r} was deleted.")
def hyperparameter_optimization(a:Namespace,c:connection,task:str): dtype = torch.float device = torch.device("cuda" if torch.cuda.is_available() else "cpu") global cur cur = c.cursor() global conn conn = c global args args = a global ss global data_composition_key global model_key _,ss,data_composition_key,model_key=task.split(":") make_sure_table_exist(args, conn, cur, args.states_current_task_table_name) make_sure_table_exist(args, conn, cur, args.best_validation_results_table_name) make_sure_table_exist(args, conn, cur, args.best_test_results_table_name) range_lr = ax.RangeParameter(name="lr",lower=1e-7,upper=0.5,parameter_type=ax.ParameterType.FLOAT) range_weight_decay = ax.RangeParameter(name="weight_decay",lower=1e-8,upper=0.5,parameter_type=ax.ParameterType.FLOAT) choice_optimizer = ax.ChoiceParameter(name="optimizer", values=["Adadelta","Adagrad","Adam","AdamW","Adamax","ASGD","RMSprop","Rprop","SGD"], parameter_type=ax.ParameterType.STRING) choice_criterion = ax.ChoiceParameter(name="criterion",values=["BCELoss","MSELoss"],parameter_type=ax.ParameterType.STRING) search_space = ax.SearchSpace(parameters=[range_lr, range_weight_decay,choice_optimizer,choice_criterion]) experiment = ax.Experiment(name="experiment_building_blocks",search_space=search_space) sobol = ax.Models.SOBOL(search_space=experiment.search_space) generator_run = sobol.gen(1) return True
def exec_query(query:str,conn:DB_CON): cur=conn.cursor() cur.execute(query) query_results=cur.fetchall() print("query run successfull") conn.close() return query_results
def get_active_periods(connection: extensions.connection, named_location_id: int) -> List[ActivePeriod]: """ Get the active time periods for a named location. :param connection: A database connection. :param named_location_id: A named location ID. :return: The active periods. """ sql = ''' select start_date, end_date from active_period where named_location_id = %s ''' periods: List[ActivePeriod] = [] with closing(connection.cursor()) as cursor: cursor.execute(sql, [named_location_id]) rows = cursor.fetchall() for row in rows: start_date = row[0] if start_date is not None: end_date = row[1] periods.append( ActivePeriod(start_date=start_date, end_date=end_date)) return periods
def pg_get_version(pg_conn: connection) -> str: """Get PostgreSQL server version.""" query = "SELECT version() AS pg_version" cur = pg_conn.cursor() cur.execute(query) ret: Dict[str, str] = cur.fetchone() return ret["pg_version"]
def pg_get_version(pg_conn: connection) -> str: """Get PostgreSQL server version.""" query = queries.get("get_version") with pg_conn.cursor() as cur: cur.execute(query) ret: Dict[str, str] = cur.fetchone() return ret["pg_version"]
def create(conn: connection, author: int, title: str, content: str): with conn.cursor() as cursor: query = """ INSERT INTO posts (title, slug, author, content) VALUES (%s, %s, %s, %s) """ cursor.execute(query, (title, generate_slug(title), author, content))