def create_roles_and_users(con: connection, db_user: str, db_password: str) -> None: """ Creates the roles and users. Args: con (object): a connection to the postgres database db_user: The username to connect to the database with. db_password: The password to connect to the database with. Raises: DatabaseError: An error occurred. """ con.set_isolation_level(ISOLATION_LEVEL_READ_COMMITTED) cur = get_cursor(con) sql_file = f"roles{SEP}app_role.sql" execute_sql_from_file(cur, sql_file, "create application role") sql_file = f"roles{SEP}appdbo_role.sql" execute_sql_from_file(cur, sql_file, "create appdbo role") sql_file = f"users{SEP}dbo.sql" execute_sql_from_file(cur, sql_file, "create dbo user") sql_file = f"users{SEP}appuser.sql" execute_sql_from_file(cur, sql_file, "create application user") sql_stmt = f"ALTER USER {db_user} WITH PASSWORD '{db_password}';" execute_sql(cur, sql_stmt, "set pw for application user") sql_stmt = f"ALTER USER dbo WITH PASSWORD '{db_password}';" execute_sql(cur, sql_stmt, "set pw for dbo user") con.commit() cur.close()
def test_tables(cur: psycopg2Ext.cursor, conn: psycopg2Ext.connection) -> None: """ Description: Test table status to make sure tables exists. Arguments: cur (psycopg2Ext.cursor): cursor object conn (psycopg2Ext.connection): connection object Returns: None """ print("\n==================== TEST -- table status ====================") for query in create_table_queries: tbl_name = query[query.find("EXISTS") + len("EXISTS"):query.find("(")].strip() query = f"""select exists(select * from information_schema.tables where table_name='{tbl_name}')""" try: cur.execute(query) except psycopg2.Error as e: msg = f"ERROR: Could not retrieve table info with query: {query}" logger.warning(msg, e) return conn.commit() try: tbl_status = cur.fetchone()[0] except psycopg2.Error as e: msg = f"ERROR: Could not fetch table status for table: {tbl_name}" logger.warning(msg, e) return print(f"Table '{tbl_name}' exists status: {tbl_status}.")
def create_dist_schema( DWH_DB_USER: str, schema_name: str, cur: psycopg2Ext.cursor, conn: psycopg2Ext.connection, ) -> None: """ Description: Create distribution schema and set search path to the schema name. Arguments: DWH_DB_USER (str): db user name to restrict authorization schema_name (str): schema cur (psycopg2Ext.cursor): cursor object conn (psycopg2Ext.connection): connection object Returns: None """ queries = [ f"DROP SCHEMA IF EXISTS {schema_name} CASCADE;", f"CREATE SCHEMA IF NOT EXISTS {schema_name} authorization {DWH_DB_USER };", f"SET search_path TO {schema_name};", ] for query in queries: try: cur.execute(query) except psycopg2.Error as e: msg = f"ERROR: Issue dropping/creating schema." logger.warning(msg, e) return conn.commit()
def _migrate(migrations: List[MigrationFile], conn: connection, schema: str = None): _prepare_meta_table(conn, schema) prev_mig = _fetch_previous_migrations(_get_schema_cursor(conn, schema)) prev_mig_iter = iter(prev_mig) current_mig = _next_or_none(prev_mig_iter) migrations.sort(key=lambda k: k.stamp) for migration in migrations: while migration.is_after(current_mig): log.info(f'IGNORE {current_mig.stamp} {current_mig.name}') current_mig = _next_or_none(prev_mig_iter) stamp_match = False try: if migration.is_equal(current_mig): log.info(f'SKIP {migration.stamp} {migration.name}') stamp_match = True except ValueError: log.warning( f'BADHASH {migration.stamp} {migration.name} Expected {current_mig.hash_} got {migration.hash_}' ) stamp_match = True if stamp_match: current_mig = _next_or_none(prev_mig_iter) else: curs = _get_schema_cursor(conn, schema) _execute_file(migration, curs) curs.close() conn.commit()
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 send_sync_reminders(conn: connection, slack_client, steps_data) -> None: reminder_users = queries.get_sync_reminder_users(conn) reminder_users_by_id = {user["id"]: user for user in reminder_users} for datum in steps_data: try: user_data = reminder_users_by_id[datum["gargling_id"]] except KeyError: continue msg = ( f"Du gikk {datum['amount']} skritt i går, by my preliminary calculations. " "Husk å synce hvis dette tallet er for lavt. " f"Denne reminderen kan skrus av <{config.server_name}/health|her>. Stay " "beautiful, doll-face!" ) try: resp = slack_client.chat_postMessage( text=msg, channel=user_data["slack_id"] ) if isinstance(resp, Future): # no test coverage # satisfy mypy raise Exception() if resp.data.get("ok") is True: queries.update_reminder_ts( conn, ts=resp.data["ts"], id=datum["gargling_id"] ) conn.commit() except Exception: # no test coverage log.error( f"Error sending sync reminder for user id: {user_data['slack_id']}", exc_info=True, )
def execute_operation(cur: cursor, conn: connection, commands: List[str], verbose: bool = True) -> None: """ Execute database operation. Parameters ---------- cur : psycopg2.extensions.cursor Database cursor. conn : psycopg2.extensions.connection Database connection. commands : list[str] Commands or queries to be executed in the database. verbose : bool, optional Whether to be verbose. """ for c in commands: if verbose: print(80 * '-') print(f'[{datetime.now()}] Running...') print(c) cur.execute(c) conn.commit() if verbose: print('Done!')
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 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 transfer_by_name(conn: connection, from_name: str, to_name: str, amount: Union[Decimal, int]): try: transfer_by_name_impl(conn, from_name, to_name, amount) conn.commit() except MoneyAmountError: conn.rollback() raise
def insert_metrics(conn: connection, metrics: List[dict]): if not metrics: return logger.info('Write messages ...') with conn.cursor() as cursor: extras.execute_batch(cursor, SQL_INSERT_METRICS, metrics) conn.commit()
def conn(postgresql: connection): populate_user_table(postgresql) populate_pics_table(postgresql) populate_quotes_table(postgresql) populate_congrats_table(postgresql) populate_health_table(postgresql) postgresql.cursor_factory = RealDictCursor postgresql.commit() yield postgresql
def create_comment( db_connection: connection, author_id: int, new_comment: NewComment, created_date: datetime, ) -> Comment: cursor = db_connection.cursor(cursor_factory=DictCursor) cursor.execute( """ INSERT INTO comments( article_id, content, parent_comment_id, author_id, created_date ) VALUES ( %s, %s, %s, %s, %s ) RETURNING id, article_id, author_id, content, parent_comment_id, created_date; """, ( new_comment.article_id, new_comment.content, new_comment.parent_comment_id, author_id, created_date, )) created_comment_row = cursor.fetchone() db_connection.commit() cursor.close() return Comment( id=created_comment_row['id'], article_id=created_comment_row['article_id'], author_id=created_comment_row['author_id'], content=created_comment_row['content'], parent_comment_id=created_comment_row['parent_comment_id'], created_date=created_comment_row['created_date'], )
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 archive_article(db_connection: connection, id: int): cursor = db_connection.cursor() cursor.execute( """ UPDATE articles SET status_id = 3 WHERE id = %s; """, (id, )) db_connection.commit() cursor.close()
def create_transaction(conn: connection, auto_commit: bool = False) -> cursor: cursor = conn.cursor() try: yield cursor except Exception: logging.debug('Rolling back') conn.rollback() raise else: if auto_commit: logging.debug('Committing') conn.commit() else: logging.debug('Rolling back') conn.rollback()
def conn(postgresql: connection): commands.queries.create_schema(postgresql) pictures.queries.create_schema(postgresql) quotes.forum_queries.create_schema(postgresql) quotes.msn_queries.create_schema(postgresql) greetings.queries.create_schema(postgresql) health.queries.create_schema(postgresql) journey.queries.create_schema(postgresql) populate_user_table(postgresql) populate_pics_table(postgresql) populate_quotes_table(postgresql) populate_congrats_table(postgresql) postgresql.cursor_factory = LoggingCursor postgresql.commit() yield postgresql
def execSql(conn:ext.connection, stmt:str, commit:bool=False, params:tuple=None) -> ext.cursor : if conn == None or conn.closed: raise _EXCEPTION_NoConnection cursor = conn.cursor() try: cursor.execute(stmt, params) if commit: conn.commit() return cursor except SyntaxError as cause: conn.rollback() raise cause except Exception as cause: conn.rollback() raise SqlExcutionError("Excution ERR[" + str(cause) +"](" + stmt +")", cause)
def delete_sync_reminders(conn: connection, slack_client) -> None: reminder_users = queries.get_sync_reminder_users(conn) log.info(reminder_users) for user in reminder_users: if user["last_sync_reminder_ts"] is None: # no test coverage continue try: slack_client.chat_delete( channel=user["slack_id"], ts=user["last_sync_reminder_ts"] ) except Exception: # no test coverage log.error( f"Error deleting sync reminder for user id: {user['id']}", exc_info=True, ) queries.update_reminder_ts(conn, ts=None, id=user["id"]) conn.commit()
def drop_tables(cur: psycopg2Ext.cursor, conn: psycopg2Ext.connection) -> None: """ Description: Drop each table using queries in `drop_table_queries` list. Arguments: cur (psycopg2Ext.cursor): cursor object conn (psycopg2Ext.connection): connection object Returns: None """ for query in drop_table_queries: try: cur.execute(query) except psycopg2.Error as e: msg = f"ERROR: Could not drop table with query: {query}" logger.warning(msg, e) return conn.commit()
def insert_salary(db_connection: connection, salary_date: PostSalaryReportData, created_date: datetime) -> None: cursor = db_connection.cursor(cursor_factory=DictCursor) cursor.execute( """ INSERT INTO salaries (position_id, city_id, technology_id, annual_salary, work_experience, created_date) VALUES (%s, %s, %s, %s, %s, %s); """, ( salary_date.position_id, salary_date.city_id, salary_date.technology_id, salary_date.annual_salary, salary_date.work_experience, created_date, )) db_connection.commit() cursor.close()
def migrate(dir: str, conn: connection, schema: str = None): _prepare_meta_table(conn, schema) pm = _fetch_previous_migrations(_get_schema_cursor(conn, schema)) pmi = iter(pm) try: cm = next(pmi) except StopIteration: cm = None for sql_file in _get_sql_files(dir): if sql_file.is_equal(cm): print('Skipping {}'.format(sql_file.path)) try: cm = next(pmi) except StopIteration: cm = None else: curs = _get_schema_cursor(conn, schema) _execute_file(sql_file, curs) curs.close() conn.commit()
def create_schema(con: connection) -> None: """ Pulls in 'schema\app.sql' and applies it to the database over the given {con}. Args: con: A connection to the database. Raises: DatabaseError: An error occurred. """ platform = os.environ[OS_ENVIRON_PLATFORM_KEY] _LOG.info(f"platform: {platform}") cur = get_cursor(con) if platform == 'AWS': sql_stmt = """SET SESSION AUTHORIZATION dbo;""" execute_sql(cur, sql_stmt, "auth dbo") sql_file = f"schema{SEP}app.sql" execute_sql_from_file(cur, sql_file, "create schema") cur.close() con.commit()
def load_table_from_csv( conn: connection, table: str, filep: str, **kwargs ): """Load records from a CSV file onto a table in PostgreSQL. Assume the CSV and the DB have coherent format. Raises exception otherwise. :param conn: (connection) Database connection (psycopg2) :param table: (str) Table to be populated :param filep: (str) CSV file path :param delim: (str) Delimiter used in the CSV. Default to ';' :param headers: (bool) True (default) is the CSV contains headers. If False, then columns must be provided. :param columns: (list) Column names in the CSV. Ignored if headers is True. """ delim = kwargs.get('delim', ';') headers = kwargs.get('headers', True) columns = kwargs.get('columns', None) freader = open(filep, 'rb') if headers: head = next(freader) cols = head.decode().strip().split(delim) else: cols = columns with conn.cursor() as cur: cur.copy_from( freader, table=table, sep=delim, null='', columns=cols) conn.commit()
def process_data(cur: cursor, conn: connection, filepath: str, func: Callable) -> None: """ will process each data file in a give filepath using func """ # get all files matching extension from directory all_files = [] for root, dirs, files in os.walk(filepath): files = glob.glob(os.path.join(root, "*.json")) for f in files: all_files.append(os.path.abspath(f)) # get total number of files found num_files = len(all_files) print(f"{num_files} files found in {filepath}") # iterate over files and process for i, datafile in enumerate(all_files, 1): func(cur, datafile) conn.commit() print(f"{i}/{num_files} files processed.")
def add_sentence(m: Mecab, db: connection, jpn: str) -> Tuple[int, int]: entries = m(jpn) with db.cursor() as cur: cur.execute('select coalesce(max(lvl)+1, 0) from added_sentences') new_level = cur.fetchone()[0] cur.execute( 'with known as (select id from features where feature in (' + ','.join(['%s'] * len(entries)) + ') order by id)' 'update sentence_words ' ' set lvl = %s ' ' where f_id in (select * from known) and lvl is null', (*[e.normalized() for e in entries], new_level), ) rowcount = cur.rowcount if rowcount > 0: cur.execute( 'insert into added_sentences(lvl, jpn) values(%s, %s)', (new_level, jpn), ) db.commit() print('Updated {} words with new level {}'.format(rowcount, new_level)) return rowcount, new_level
def write_db(conn: connection, data: List[EpisodeData], showname: str) -> int: c = conn.cursor() tuples = [(s.audio_url, s.title, s.description, showname, s.date) for s in data] log.info("Got %d episodes, let's see how many are already in the database", len(tuples)) numchange = 0 for t in tuples: c.execute(u"select count(*) from rrepisodes where audio_url = %s", (t[0], )) num = c.fetchone() if num[0] == 1: continue log.info("New episode on %s", t[4]) c.execute( """insert into rrepisodes (audio_url,title,description,show,date) values (%s,%s,%s,%s,%s) """, t, ) numchange = numchange + 1 conn.commit() return numchange
def steps(self, date: pendulum.Date, conn: connection) -> t.Optional[int]: log.info("Getting polar steps") trans = self._get_transaction() if trans is not None: activities = trans.list_activities()["activity-log"] log.info(f"number of activities: {len(activities)}") steps_by_date: dict[pendulum.Date, list] = defaultdict(list) for activity in activities: summary = trans.get_activity_summary(activity) log.info(summary) parsed = pendulum.parse(summary["date"]) assert isinstance(parsed, pendulum.DateTime) taken_at = parsed.date() created_at = pendulum.parse(summary["created"]) n_steps = summary["active-steps"] log.info(f"n steps {created_at}: {n_steps}") steps_by_date[taken_at].append( {"n_steps": n_steps, "created_at": created_at} ) not_past: list[dict] = [] for activity_date, activity_list in steps_by_date.items(): activity_list.sort(key=itemgetter("created_at")) last_synced = activity_list[-1] if activity_date < date: continue last_synced["gargling_id"] = self.gargling_id last_synced["taken_at"] = activity_date log.info(f"last_synced, {activity_date}: {last_synced}") not_past.append(last_synced) queries.upsert_steps(conn, not_past) conn.commit() trans.commit() todays_data = queries.cached_step_for_date(conn, date=date, id=self.gargling_id) steps = todays_data["n_steps"] if todays_data is not None else 0 return steps
def create_index( conn: connection, table: str, index: str, columns: list ): """Create an index in a PostgresSQL database table""" log.debug('Creating index on table %s, columns %s', table, columns) query = sql.SQL(""" create index {idx_name} on {table} ({cols}); """) idx_name = sql.Identifier(table + '_' + index) table = sql.Identifier(table) cols = sql.SQL(', ').join(map(sql.Identifier, columns)) with conn.cursor() as cur: cur.execute(query.format( idx_name=idx_name, table=table, cols=cols)) conn.commit() log.debug('Index created')
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()