streamlit_app.py

# import streamlit as st
# import requests
# import json

# st.set_page_config(page_title="QueryMate: Text to SQL & CSV")

# st.markdown("# QueryMate: Text to SQL & CSV 💬🌐🗄️")
# st.description('''Welcome to QueryMate, your friendly assistant for converting natural language queries into SQL statements and CSV outputs!
#                Let's get started with your data queries!''')
# # Load chat history
# def load_chat_history():
#     try:
#         with open('chat_history.json', 'r') as f:
#             return json.load(f)
#     except FileNotFoundError:
#         return []

# def save_chat_history(history):
#     with open('chat_history.json', 'w') as f:
#         json.dump(history, f)

# chat_history = load_chat_history()

# # Data source selection
# data_source = st.radio("Select Data Source:", ('SQL Database', 'Employee CSV'))

# # Predefined queries
# predefined_queries = {
#     'SQL Database': [
#         'Print all students',
#         'Count total number of students',
#         'List students in Data Science class'
#     ],
#     'Employee CSV': [
#         'Print employees having the department id equal to 100',
#         'Count total number of employees',
#         'List Top 5 employees according to salary in descending order'
#     ]
# }

# st.markdown(f"### Predefined Queries for {data_source}")

# # Create buttons for predefined queries
# for query in predefined_queries[data_source]:
#     if st.button(query):
#         st.session_state.predefined_query = query

# st.markdown("### Enter Your Question")
# question = st.text_input("Input: ", key="input", value=st.session_state.get('predefined_query', ''))

# # Submit button
# submit = st.button("Submit")

# if submit:
#     # Send request to FastAPI backend
#     response = requests.post("http://localhost:8000/query", 
#                              json={"question": question, "data_source": data_source})
#     if response.status_code == 200:
#         data = response.json()
#         st.markdown(f"## Generated {'SQL' if data_source == 'SQL Database' else 'Pandas'} Query")
#         st.code(data['query'])
        
#         st.markdown("## Query Results")
#         st.write(data['result'])

#         if data_source == 'Employee CSV':
#             st.markdown("## Available CSV Columns")
#             st.write(data['columns'])

#         # Update chat history
#         chat_history.append(f"User ({data_source}): {question}")
#         chat_history.append(f"AI: {data['query']}")
#         save_chat_history(chat_history)
#     else:
#         st.error(f"Error processing your request: {response.text}")

#     # Clear the predefined query from session state
#     st.session_state.pop('predefined_query', None)

# # Display chat history
# st.markdown("## Chat History")
# for message in chat_history:
#     st.text(message)

# # Option to clear chat history
# if st.button("Clear Chat History"):
#     chat_history.clear()
#     save_chat_history(chat_history)
#     st.success("Chat history cleared!")





import streamlit as st
import requests
import json
import pandas as pd

st.set_page_config(page_title="QueryMate: Text to SQL & CSV")

st.markdown("# QueryMate: Text to SQL & CSV 💬🗄️")
st.markdown('''Welcome to QueryMate, your friendly assistant for converting natural language queries into SQL statements and CSV outputs!
               Let's get started with your data queries!''')

# Load chat history
def load_chat_history():
    try:
        with open('chat_history.json', 'r') as f:
            return json.load(f)
    except FileNotFoundError:
        return []

def save_chat_history(history):
    with open('chat_history.json', 'w') as f:
        json.dump(history, f)

chat_history = load_chat_history()

# Data source selection
data_source = st.radio("Select Data Source:", ('SQL Database', 'Employee CSV'))

# Predefined queries
predefined_queries = {
    'SQL Database': [
        'Print all students',
        'Count total number of students',
        'List students in Data Science class'
    ],
    'Employee CSV': [
        'Print employees having the department id equal to 100',
        'Count total number of employees',
        'List Top 5 employees according to salary in descending order'
    ]
}

st.markdown(f"### Predefined Queries for {data_source}")

# Create buttons for predefined queries
for query in predefined_queries[data_source]:
    if st.button(query):
        st.session_state.predefined_query = query

st.markdown("### Enter Your Question")
question = st.text_input("Input: ", key="input", value=st.session_state.get('predefined_query', ''))

# Submit button
submit = st.button("Submit")

if submit:
    # Send request to FastAPI backend
    response = requests.post("http://localhost:8000/query", 
                             json={"question": question, "data_source": data_source})
    if response.status_code == 200:
        data = response.json()
        st.markdown(f"## Generated {'SQL' if data_source == 'SQL Database' else 'Pandas'} Query")
        st.code(data['query'])
        
        st.markdown("## Query Results")
        result = data['result']
        
        if isinstance(result, list) and len(result) > 0:
            if isinstance(result[0], dict):
                # For CSV queries that return a list of dictionaries
                df = pd.DataFrame(result)
                st.dataframe(df)
            elif isinstance(result[0], list):
                # For SQL queries that return a list of lists
                df = pd.DataFrame(result)
                st.dataframe(df)
            else:
                # For single column results
                st.dataframe(pd.DataFrame(result, columns=['Result']))
        elif isinstance(result, dict):
            # For single row results
            st.table(result)
        else:
            # For scalar results or empty results
            st.write(result)

        if data_source == 'Employee CSV':
            st.markdown("## Available CSV Columns")
            st.write(data['columns'])

        # Update chat history
        chat_history.append(f"👨‍💻({data_source}): {question}")
        chat_history.append(f"🤖: {data['query']}")
        save_chat_history(chat_history)
    else:
        st.error(f"Error processing your request: {response.text}")

    # Clear the predefined query from session state
    st.session_state.pop('predefined_query', None)

# Display chat history
st.markdown("## Chat History")
for message in chat_history:
    st.text(message)

# Option to clear chat history
if st.button("Clear Chat History"):
    chat_history.clear()
    save_chat_history(chat_history)
    st.success("Chat history cleared!")