Hello,
First time here and looking for some help.
I have a web app in python and streamlit that I copied from a developer (here) and modified that queries a dataset from BigQuery. Using streamlit, my goal is to create some pre-defined sql queries in column one, have the user copy and paste a query into column two and submit, then the results would be presented in column three (first image). But I want to the results displayed in a table format.
When using Inspect in Chrome the results are shown as string-value, so no easy package to convert that I could find. But
I would like the results displayed from this:
to this:
Used df = query_res.result().to_dataframe() to get this output but from a different version of the app.
Here is my code:
import streamlit as st
from google.cloud import bigquery
from google.cloud.bigquery import dbapi
import google.cloud.storage
import pandas as pd
from pandas import DataFrame
import os
os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = '/home/mark/Documents/Data_Projects/web-app/web-app-341703-626adddc7d5a.json'
#DB management
st.set_page_config(layout="wide")
client = bigquery.Client()
conn = dbapi.Connection(client)
c = conn.cursor()
def sql_executor(raw_code):
c.execute(raw_code)
data = c.fetchall()
return data
def main():
menu = ["SQL Queries"]
with st.sidebar:
choice = st.selectbox("Menu",menu)
if choice == "SQL Queries":
st.subheader("SQL Queries")
col1, col2, col3 = st.columns([4,4,5])
with col1:
st.text("Click the copy button for one of the query selections below")
st.code("SELECT * FROM hosp_info.hospital_general_information")
st.code("SELECT hospital_name AS Hospital_Name, \ncity AS City, \nstate AS State, \nnumber_of_readmissions AS Number_of_Readmissions, \nFROM hospital_general_information, \nJOIN readmission_reduction, \nON hospital_general_information.provider_id = readmission_reduction.provider_id, \nWHERE number_of_readmissions > '50' \nGROUP BY state")
with col2:
with st.form(key='query_form'):
raw_code = st.text_area("Paste SQL query here")
submit_code = st.form_submit_button("Execute")
with col3:
if submit_code:
st.code(raw_code)
query_results = sql_executor(raw_code)
st.write(query_results) **this is where I am having trouble converting**
if __name__ == '__main__':
main()
Much obliged for any suggestions.