def close_connection_to_database(cursor: psycopg2.extensions.cursor, connection: psycopg2.extensions.connection): try: # closing database connection. if connection: cursor.close() connection.close() print("PostgreSQL connection is closed") except (Exception, psycopg2.Error) as error: print(f"Error while disconnection from PostgreSQL: {error}")
def create_table(sql_query: str, conn: psycopg2.extensions.connection, cur: psycopg2.extensions.cursor) -> None: try: # Execute the table creation query cur.execute(sql_query) except Exception as e: print(f"{type(e).__name__}: {e}") print(f"Query: {cur.query}") conn.rollback() cur.close() else: # To take effect, changes need be committed to the database conn.commit()
def run_query( sql_query: str, conn: psycopg2.extensions.connection, cur: psycopg2.extensions.cursor, ) -> None: try: cur.execute(sql_query) except Exception as e: print(f"{type(e).__name__}: {e}") print(f"Query: {cur.query}") conn.rollback() cur.close() else: conn.commit()
def insert_data(query: str, conn: psycopg2.extensions.connection, cur: psycopg2.extensions.cursor, df: pd.DataFrame, page_size: int) -> None: data_tuples = [tuple(row.to_numpy()) for index, row in df.iterrows()] try: psql_extras.execute_values(cur, query, data_tuples, page_size=page_size) print("Query:", cur.query) except Exception as error: print(f"{type(error).__name__}: {error}") print("Query:", cur.query) conn.rollback() cur.close() else: conn.commit()
def check_db(dbname: str = "dvf", secrets: str = "../secrets.yml", with_connection: bool = False, cur: psycopg2.extensions.cursor = None): """ Function to check the tables existing in a specific db """ if cur is not None: with_connection = True if not with_connection: conn = open_connection(dbname, secrets) cur = conn.cursor() cur.execute("""SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'""") elements = [element[0] for element in cur.fetchall()] if not with_connection: cur.close() conn.close() return elements
def db_close_cursor(cursor: psycopg2.extensions.cursor) -> None: if cursor is not None: cursor.close()