# 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!") import streamlit as st import requests 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!''') # Initialize chat history in session state if it doesn't exist if 'chat_history' not in st.session_state: st.session_state.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 in session state st.session_state.chat_history.append(f"👨‍💻({data_source}): {question}") st.session_state.chat_history.append(f"🤖: {data['query']}") 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 st.session_state.chat_history: st.text(message) # Option to clear chat history if st.button("Clear Chat History"): st.session_state.chat_history = [] st.success("Chat history cleared!")