HackerNews – customFunctionsGeneral Part 2: Postgres Connection

news, daily newspaper, press-1172463.jpg

After creating the write_log function, we must connect to the Postgres database every time we execute our main file to retrieve HackerNews data. So, in customFunctionsGeneral, let’s create a function that initializes the Postres connection and another function that checks if the connection works successfully.

def initiatePostgresConnection():
    load_dotenv(".env")
    sv.user = os.environ['login']
    sv.password = os.environ['password']
    sv.dbname = os.environ['dbname']
    sv.host = os.environ['host']
    sv.port = os.environ['port']

The code above retrieves all the sensitive information from the .env file and assigns it the the variables from the sharedVars file accordingly.

def checkIfPostgresConnectionWorks():
    sv.engine = create_engine(
        (
            f"postgresql://{sv.user}:"
            f"{sv.password}"
            f"@{sv.host}:{sv.port}/{sv.dbname}"
        )
    )
    sv.connection = sv.engine.connect()

    sv.query = """
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = 'public';
    """

    try:
        pd.read_sql_query(sv.query, sv.connection)
        print('all good')

    except Exception as e:
        write_log("""I am in checkIfPostgresConnectionWorks exception.
                There is an issue with database connection""")
        sys.exit(1)

Now, after the sharedVars variables for the Postgres connection have been declared, we can create and connect an engine and query through the data in our database.