def test_persist_token(conn: connection): user = conftest.users[0] register_user(user, conn) with conn.cursor() as cur: cur.execute("select * from withings_token") tokens = cur.fetchall() cur.execute("select * from withings_token_gargling") matched = cur.fetchall() assert len(tokens) == 1 token = dict(tokens[0]) exp = { "id": 1002, "access_token": "access_token2", "refresh_token": "refresh_token2", "expires_at": 1573921366, "enable_steps": False, "enable_weight": False, } assert token == exp assert len(matched) == 1 match = dict(matched[0]) exp = {"service_user_id": 1002, "gargling_id": 2} assert match == exp
def get_user_with_permissions_and_role_by_username( db_connection: connection, username: str) -> Optional[Dict[str, Any]]: cursor = db_connection.cursor(cursor_factory=DictCursor) cursor.execute( """ SELECT users.id, users.email, users.password, users.username, user_roles.role AS role, ARRAY_AGG(permissions.name) AS permissions FROM users INNER JOIN user_roles ON users.role_id = user_roles.id 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 users.username = %s OR users.email = %s GROUP BY users.id, user_roles.role; """, ( username, username, )) user = cursor.fetchone() cursor.close() return user
def populate_user_table(db: connection) -> None: with db.cursor() as cursor: print(cursor) for user in users: sql_command = """INSERT INTO faces (db_id, name) VALUES (%(db_id)s, %(name)s);""" data = {"db_id": user.db_id, "name": user.name} cursor.execute(sql_command, data) sql_command = """INSERT INTO user_ids (db_id, slack_id, slack_nick, first_name, bday) VALUES (%(db_id)s, %(slack_id)s, %(slack_nick)s, %(first_name)s, %(bday)s);""" data = { "db_id": user.db_id, "slack_id": user.slack_id, "slack_nick": user.slack_nick, "first_name": user.name, "bday": user.bday, } cursor.execute(sql_command, data)
def create_tables(connection: PostgresConnection) -> None: """Create empty tables in database.""" with connection.cursor() as cursor: for sql in get_sql_script("create_tables"): cursor.execute(sql) connection.commit()
def _execute_with_schema(conn: connection, schema: str, *args, **kwargs): with conn.cursor() as curs: # type: cursor if schema: curs.execute('SET search_path TO %s', [schema]) curs.execute(*args, **kwargs)
def add_user(conn: connection, user: User): insert_impl(conn, user) conn.commit() return user
def create_db(conn: connection): create_db_impl(conn) conn.commit()
def drop_db(conn: connection): with conn.cursor() as cursor: cursor.execute("DROP TABLE IF EXISTS USERS") cursor.execute("DROP SEQUENCE IF EXISTS public.users_seq")
def delete(conn: connection, id_: int): with conn.cursor() as cursor: cursor.execute("DELETE FROM USERS WHERE id=%s", [id_])
def create_tables(cur: cursor, conn: connection) -> None: conn, cur = get_connection() for query in create_table_queries: cur.execute(query) conn.commit()
def update_article( db_connection: connection, id: int, article_input: ArticleInput, ) -> Article: cursor = db_connection.cursor( cursor_factory=DictCursor ) cursor.execute( """ UPDATE articles SET title = %s, content = %s WHERE articles.id = %s RETURNING articles.id, articles.title, articles.author_id, articles.content, articles.status_id, articles.created_date; """, ( article_input.title, article_input.content, id, ), ) updated_article = cursor.fetchone() db_connection.commit() cursor.execute( """ SELECT article_statuses.name FROM article_statuses WHERE article_statuses.id = %s; """, ( updated_article['status_id'], ) ) article_status = cursor.fetchone() cursor.close() return Article( id=updated_article['id'], title=updated_article['title'], content=updated_article['content'], author_id=updated_article['author_id'], status=article_status['name'], created_date=updated_article['created_date'], )
def drop_tables(cur: cursor, conn: connection) -> None: for query in drop_table_queries: cur.execute(query) conn.commit()
def insert_many(table: str, data: List[dict], conn: connection, **kwargs): query, data = get_insert_query(table, data, **kwargs) with conn.cursor() as cur: _ = cur.executemany(query, data) return conn.commit()
def update(conn: connection, post_id: int, title: str, content: str): with conn.cursor() as cursor: query = "UPDATE posts SET title=%s, content=%s WHERE id=%s" cursor.execute(query, (title, content, post_id))
def get_article_by_id( db_connection: connection, id: int, are_draft_allowed: bool, are_draft_allowed_for_current_user_id: bool, current_user_id: int, are_published_allowed: bool, are_published_allowed_for_current_user_id: bool, are_archived_allowed: bool, are_archived_allowed_for_current_user_id: bool, ) -> Optional[Article]: cursor = db_connection.cursor( cursor_factory=DictCursor ) cursor.execute( """ SELECT * FROM ( SELECT articles.id, articles.title, articles.content, articles.author_id, article_statuses.name AS status, articles.created_date FROM articles INNER JOIN article_statuses ON articles.status_id = article_statuses.id WHERE CASE -- When DRAFT articles are allowed. WHEN article_statuses.name = 'DRAFT' AND %s THEN CASE -- Are articles allowed for a specific user id? WHEN %s THEN articles.author_id = %s ELSE TRUE END -- When PUBLISHED articles are allowed. WHEN article_statuses.name = 'PUBLISHED' AND %s THEN CASE -- Are articles allowed for a specific user id? WHEN %s THEN articles.author_id = %s ELSE TRUE END -- When ARCHIVED articles are allowed. WHEN article_statuses.name = 'ARCHIVED' AND %s THEN CASE -- Are articles allowed for a specific user id? WHEN %s THEN articles.author_id = %s ELSE TRUE END ELSE FALSE END ) AS allowed_articles WHERE allowed_articles.id = %s; """, ( are_draft_allowed, are_draft_allowed_for_current_user_id, current_user_id, are_published_allowed, are_published_allowed_for_current_user_id, current_user_id, are_archived_allowed, are_archived_allowed_for_current_user_id, current_user_id, id, ), ) article_row = cursor.fetchone() cursor.close() if article_row is None: return None else: return Article( id=article_row["id"], title=article_row["title"], content=article_row["content"], author_id=article_row["author_id"], status=article_row["status"], created_date=article_row["created_date"], )
def delete_by_name(conn: connection, name: str): with conn.cursor() as cursor: cursor.execute("DELETE FROM USERS WHERE name=%s", [name])
def fetch_user(name: str, conn: connection) -> dict: with conn.cursor(cursor_factory=RealDictCursor) as cursor: cursor.execute(SQL_SELECT_USER_BY_NAME, {'name': name}) user = cursor.fetchone() return user
def query(conn: connection, q: str, vals: tuple = ()) -> Iterator[tuple]: with conn: with conn.cursor() as cur: cur.execute(q, vals) for record in cur: yield record
def __init__(self, pg_conn: _connection): self.cursor = pg_conn.cursor() self._counter = 0
def db_disconnection(con: connection) -> None: try: con.close() print('PostgreSQL connection is closed') except (Exception, psycopg2.DatabaseError) as e: print('Error while disconnecting PostgreSQL:', e)
def drop_db(conn: connection): drop_db_impl(conn) conn.commit()
def get_thresholds(connection: extensions.connection) -> Iterator[Threshold]: sql = ''' select attr.column_name, threshold.term_name, threshold.threshold_uuid, nam_locn.nam_locn_name, condition.start_date, condition.end_date, condition.is_date_constrained, condition.start_day_of_year, condition.end_day_of_year, property.number_value, property.string_value from pdr.condition join pdr.threshold on condition.threshold_uuid = threshold.threshold_uuid join pdr.property on condition.condition_uuid = property.condition_uuid join pdr.attr on attr.attr_id = property.attr_id join pdr.nam_locn on pdr.nam_locn.nam_locn_id = pdr.condition.nam_locn_id where property.condition_uuid is not null order by nam_locn.nam_locn_name ''' with closing(connection.cursor()) as cursor: cursor.execute(sql) rows = cursor.fetchall() for row in rows: threshold_name = row[0] term_name = row[1] threshold_uuid = row[2] location_name = row[3] start_date = row[4] end_date = row[5] is_date_constrained = row[6] start_day_of_year = row[7] end_day_of_year = row[8] number_value = row[9] string_value = row[10] ''' Change term name for soil temp thresholds from 'soilPRTResistance' to 'temp'. The Avro schema for calibrated PRT data uses the term name 'temp' and data file term name must match threshold term name to apply QA/QC. ''' if term_name == 'soilPRTResistance': term_name = 'temp' if start_date is not None: start_date = date_formatter.to_string(start_date) if end_date is not None: end_date = date_formatter.to_string(end_date) if number_value is not None: number_value = float(number_value) context: List[str] = get_threshold_context(connection, threshold_uuid) threshold = Threshold(threshold_name=threshold_name, term_name=term_name, location_name=location_name, context=context, start_date=start_date, end_date=end_date, is_date_constrained=is_date_constrained, start_day_of_year=start_day_of_year, end_day_of_year=end_day_of_year, number_value=number_value, string_value=string_value) yield threshold
def transfer_by_name_tpc(from_connection: connection, from_name: str, to_connection: connection, to_name: str, amount: Union[Decimal, int]): from_connection.tpc_begin( from_connection.xid(42, 'transaction ID', 'connection 1')) to_connection.tpc_begin( to_connection.xid(42, 'transaction ID', 'connection 2')) try: transfer_by_name_tpc_impl(from_connection, from_name, to_connection, to_name, amount) from_connection.tpc_prepare() to_connection.tpc_prepare() except Error: from_connection.tpc_rollback() to_connection.tpc_rollback() raise except MoneyAmountError: from_connection.tpc_rollback() to_connection.tpc_rollback() raise else: from_connection.tpc_commit() to_connection.tpc_commit()
def drop_tables(cur: cursor, conn: connection) -> None: for table, query in drop_table_queries: print(f"Dropping {table} table") cur.execute(query) conn.commit()
def get_articles_count( db_connection: connection, are_draft_allowed: bool, are_draft_allowed_for_current_user_id: bool, current_user_id: int, are_published_allowed: bool, are_published_allowed_for_current_user_id: bool, are_archived_allowed: bool, are_archived_allowed_for_current_user_id: bool, status: Optional[str] = None, ) -> int: cursor = db_connection.cursor(cursor_factory=DictCursor) has_status_filter = status is not None cursor.execute( """ SELECT count(*) AS articles_count FROM ( SELECT articles.id, articles.title, articles.content, articles.author_id, article_statuses.name AS status, articles.created_date FROM articles INNER JOIN article_statuses ON articles.status_id = article_statuses.id WHERE CASE -- When DRAFT articles are allowed. WHEN article_statuses.name = 'DRAFT' AND %s THEN CASE -- Are articles allowed for a specific user id? WHEN %s THEN articles.author_id = %s ELSE TRUE END -- When PUBLISHED articles are allowed. WHEN article_statuses.name = 'PUBLISHED' AND %s THEN CASE -- Are articles allowed for a specific user id? WHEN %s THEN articles.author_id = %s ELSE TRUE END -- When ARCHIVED articles are allowed. WHEN article_statuses.name = 'ARCHIVED' AND %s THEN CASE -- Are articles allowed for a specific user id? WHEN %s THEN articles.author_id = %s ELSE TRUE END ELSE FALSE END ) AS allowed_articles WHERE CASE -- Has status filter? WHEN %s THEN allowed_articles.status = %s ELSE TRUE END """, ( are_draft_allowed, are_draft_allowed_for_current_user_id, current_user_id, are_published_allowed, are_published_allowed_for_current_user_id, current_user_id, are_archived_allowed, are_archived_allowed_for_current_user_id, current_user_id, has_status_filter, status, )) result = cursor.fetchone() cursor.close() return result['articles_count']
def create_tables(cur: cursor, conn: connection) -> None: for table, query in create_table_queries: print(f"Creating {table} table") cur.execute(query) conn.commit()
def _get_schema_cursor(conn: connection, schema: str = None) -> cursor: curs = conn.cursor() if schema: curs.execute('SET search_path TO %s', [schema]) return curs
def get_added_sentences(db: connection) -> Dict[int, str]: """ Return added sentences associated to its level """ with db.cursor() as cur: cur.execute('select lvl, jpn from added_sentences') return {lvl: jpn for lvl, jpn in cur.fetchall()}
def _create_users_table(conn: connection): with conn.cursor() as cursor: cursor.execute(SQL_CREATE_USERS_TABLE)
def get_articles( db_connection: connection, are_draft_allowed: bool, are_draft_allowed_for_current_user_id: bool, current_user_id: int, are_published_allowed: bool, are_published_allowed_for_current_user_id: bool, are_archived_allowed: bool, are_archived_allowed_for_current_user_id: bool, page: int, page_size: int, status: Optional[str] = None, ) -> List[Article]: cursor = db_connection.cursor( cursor_factory=DictCursor ) start_from = (page - 1) * page_size has_status_filter = status is not None cursor.execute(""" SELECT * FROM ( SELECT articles.id, articles.title, articles.content, articles.author_id, article_statuses.name AS status, articles.created_date FROM articles INNER JOIN article_statuses ON articles.status_id = article_statuses.id WHERE CASE -- When DRAFT articles are allowed. WHEN article_statuses.name = 'DRAFT' AND %s THEN CASE -- Are articles allowed for a specific user id? WHEN %s THEN articles.author_id = %s ELSE TRUE END -- When PUBLISHED articles are allowed. WHEN article_statuses.name = 'PUBLISHED' AND %s THEN CASE -- Are articles allowed for a specific user id? WHEN %s THEN articles.author_id = %s ELSE TRUE END -- When ARCHIVED articles are allowed. WHEN article_statuses.name = 'ARCHIVED' AND %s THEN CASE -- Are articles allowed for a specific user id? WHEN %s THEN articles.author_id = %s ELSE TRUE END ELSE FALSE END ) AS allowed_articles WHERE CASE -- Has status filter? WHEN %s THEN allowed_articles.status = %s ELSE TRUE END ORDER BY allowed_articles.created_date DESC LIMIT %s OFFSET %s; """, ( are_draft_allowed, are_draft_allowed_for_current_user_id, current_user_id, are_published_allowed, are_published_allowed_for_current_user_id, current_user_id, are_archived_allowed, are_archived_allowed_for_current_user_id, current_user_id, has_status_filter, status, page_size, start_from, )) article_rows = cursor.fetchall() cursor.close() return [ Article( id=article_row['id'], title=article_row['title'], content=article_row['content'], author_id=article_row['author_id'], status=article_row['status'], created_date=article_row['created_date'], ) for article_row in article_rows ]