Spaces:
Runtime error
Runtime error
File size: 3,267 Bytes
91ede3b |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 |
# This page allows the user to view a list of members
# By default, the output will be a streamlit rendered pandas dataframe
# But there is also an option to render the output in raw HTML so that
# the member's profile URL can be visited by clicking on the
# "Profile" hyperlink that is only available in the raw HTML version.
import psycopg2
import os
import pandas as pd
import streamlit as st
# Obtain username/password saved as environment variables
user = 'project'
pwd = 'project'
# @st.cache prevents the streamlit app from executing redundant processes
# repeatedly that are expensive such as database connections or reading input
@st.cache(allow_output_mutation=True)
def get_query_results():
""" A function that returns a table of members.
Until streamlit provides a way to pageinate results,
maximum of 1000 rows will be returned.
Output can be either a streamlit rendered dataframe
or raw HTML version. Raw HTML version will provide
a hyperlink that will take you directly to the person's
company profile page. This can be used to double-check
that the profile URL has been correctly generated.
"""
# Connect to the PostgreSQL database server
with psycopg2.connect(host='127.0.0.1',
port='5433',
database='project',
user=user,
password=pwd) as conn:
sql = """
SELECT
*
FROM
public.basic_member_info
ORDER BY
last_name
LIMIT 1000
"""
# Execute query and return results as a pandas dataframe
df = pd.read_sql(sql, conn, index_col=None)
# Define a function to create a "Profile" hyperlink
def createProfileHref(url: str):
""" Function to create a new column that converts URL as HTML hyperlink """
value = '<a href="' + url + '"' + "/>Profile</a>"
return value
# Apply the function we created above and create our new hyperlink column
df['profile_href'] = df['web_url'].apply(createProfileHref)
# Change order of dataframe columns
df = df[['profile_href', 'first_name', 'last_name', 'web_id',
'web_url', 'is_ahm']]
return df
##
def write():
""" Writes content to the app """
st.title("Get Members Data from PostgreSQL")
# Check to see if checkbox was checked or not (boolean) and will be used to
# determine if the output should be a streamlit dataframe or raw HTML.
html = st.checkbox(
'OPTIONAL: Render output as raw html to access the \"Profile\" hyperlink. ' +
'Otherwise, just click on Execute botton.',
False)
# Define what happens when user clicks on the "Execute" button
if st.button("Execute"):
'''
### Query results:
'''
if html:
# Render or display the raw HTML version of the dataframe
st.write(get_query_results().to_html(escape=False, index=False), unsafe_allow_html=True)
else:
# Render or display the streamlit dataframe
st.dataframe(get_query_results())
if __name__ == "__main__":
write() |