# See also https://docs.streamlit.io/knowledge-base/tutorials/build-conversational-apps
import json
openai_api = pq.connect('OpenAI')
st.title("AI Chatbot")
if "messages" not in st.session_state:
st.session_state.messages = []
else:
messages = [
{"role": m["role"], "content": m["content"]}
for m in st.session_state.messages
]
# Render full history again on page refresh
for message in st.session_state.messages:
with st.chat_message(message["role"]):
st.markdown(message["content"])
if prompt := st.chat_input("How can I help ?"):
messages.append({"role": "user", "content": prompt})
with st.chat_message("user"): # Show user icon (red)
st.markdown(prompt)
with st.chat_message("assistant"): # Show bot icon (yellow)
request = {
"model": "gpt-5",
"messages": messages,
"reasoning_effort": "minimal"
}
llm_answer = openai_api.get('completion_raw', request)
if not "choices" in llm_answer:
st.warning("Something went wrong. ")
st.write(llm_answer)
else:
message = llm_answer["choices"][0]["message"]
answer_content = message["content"]
st.write(answer_content)
st.session_state.messages = messages
# See also https://docs.streamlit.io/knowledge-base/tutorials/build-conversational-apps
import json
openai_conn = pq.connect('OpenAI')
dbconn = pq.dbconnect(pq.DW_NAME)
st.title("AI Chatbot for Finance")
if "messages" not in st.session_state:
st.session_state.messages = []
# Show message history
for message in st.session_state.messages:
with st.chat_message(message["role"]):
if not isinstance(message["content"], list):
st.markdown(message["content"])
else:
for query_to_run in message["content"]:
description = query_to_run["description"]
query = query_to_run["query"]
st.text(description)
# Handle new chat input from user
if question := st.chat_input("How can I help ?"):
with st.chat_message("user"):
st.markdown(question)
with st.chat_message("assistant"):
prompt = """
The user has a number of datasets, below is a list of all the tables with their columns.
Based on the question of the user, write SQL SELECT queries to find answers in these tables.
Use LIKE statements with % at the start and end, to find relevant rows.
Use LOWER() in the LIKE statements on both sides to make it case insensitive.
Your answer should be a JSON with an array of SQL SELECT queries. For each item in the array,
provide an object with 2 keys: description and query.
The description key explains what part of the question is answered.
The query key contains the SQL SELECT query to execute.
"""
all_tables = """
Table chargebee.invoices, columns: invoice_id, invoice_status, invoice_date, invoice_total, invoice_customer_id.
Table chargebee.customers, columns: customer_id, customer_company, customer_first_name, customer_last_name, customer_email.
"""
prompt = prompt + "\n\n List of tables from the user: \n" + all_tables
prompt = prompt + "\n\n The user question is: " + question
prompt = prompt.replace('\n', '. ')
messages = []
for m in st.session_state.messages:
if not isinstance(m["content"], list):
messages.append({"role": m["role"], "content": m["content"]})
else:
content = ""
for query_to_run in m["content"]:
content += query_to_run["description"] + ": " + query_to_run["query"] + ". "
messages.append({"role": m["role"], "content": content})
messages.append({"role": "user", "content": prompt})
st.session_state.messages.append({"role": "user", "content": question})
#response = openai_conn.get('completion', message = prompt, role = "user") # without history (context)
response = openai_conn.get('completion_raw', { "model": "gpt-3.5-turbo", "messages": messages, "temperature": 0.7 })
content = response["choices"][0]["message"]["content"]
content = content.replace('```json', '').replace('```', '') # rarely added by OpenAI in response
response_content = json.loads(content)
if "queries" in response_content:
queries_to_run = response_content["queries"]
else:
queries_to_run = response_content
#st.json(queries_to_run)
for query_to_run in queries_to_run:
description = query_to_run["description"]
query = query_to_run["query"]
df = dbconn.fetch(pq.DW_NAME, query = query.replace("\n", " "), df = True)
df = df.loc[:,~df.columns.duplicated()].copy() # remove duplicate column names
st.text(description)
#st.code(query, language = "SQL")
st.dataframe(df)
st.session_state.messages.append({"role": "assistant", "content": queries_to_run})
