import psycopg2

def query_postgresql(
    query: str, 
    database: str, 
    user: str, 
    password: str, 
    host: str,
    port: str,
    named_columns: bool=True
    ):
    
    conn = psycopg2.connect(
            database=database,
            user=user,
            password=password,
            host=host,
            port=port
        )
    
    cur = conn.cursor()
    cur.execute(query)
    rows = cur.fetchall()

    if named_columns:
        column_names = [desc[0] for desc in cur.description]
        return [ dict(zip(column_names, r)) for r in rows ]

    return rows

def query_postgresql_realvest(query: str, named_columns: bool=True):
    import streamlit as st
    POSTGRESQL_REALVEST_USER = st.secrets["POSTGRESQL_REALVEST_USER"]
    POSTGRESQL_REALVEST_PSWD = st.secrets["POSTGRESQL_REALVEST_PSWD"]

    return query_postgresql(
        query, 
        database="realvest", 
        user=POSTGRESQL_REALVEST_USER, 
        password=POSTGRESQL_REALVEST_PSWD, 
        host="realvest.cdb5lmqrlgu5.us-east-2.rds.amazonaws.com",
        port="5432",
        named_columns=named_columns
        )

if __name__ == "__main__":
    query = """
    SELECT *
    FROM main_products
    WHERE productid in ('2093075');
    """
    
    results = query_postgresql_realvest(query)
    print(results)