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:
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.