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()
