Displaying query results in table format

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:

web-app_2

to this:

web-app_3
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.

Hi @Mark_Moralls -

The BigQuery package supports a .to_dataframe() method:

Having the data returned as a dataframe, you can then call st.write and display as a table.

Best,
Randy

Thanks Randy for the response. I tried that, but the problem is the queries are being run through sql_executor (function?) and results returned in memory as string values. I did try iteration for a couple of columns and got what I was looking for but need to figure out how to include the column labels. Now I need to figure out how to apply iteration for each custom query result to be displayed. Also, can you help me with stacking two narrow columns on top of a wide column below them? I tried the container option but that didn’t work out.

Thanks again for your help!

Found the answer to stacking the columns.

This topic was automatically closed 365 days after the last reply. New replies are no longer allowed.