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.