app.py

from dotenv import load_dotenv
load_dotenv()

import streamlit as st
import os
import sqlite3
import google.generativeai as genai
import pandas as pd

#Configure API
genai.configure(api_key=os.getenv("google_api_key"))

#Load Google Gemini Pro and generate SQL query prompt
def getGemResponse(question, prompt):
    model = genai.GenerativeModel("gemini-pro")
    response = model.generate_content([prompt[0],question])
    return response.text

def getCodeDescription(question, prompt):
    model = genai.GenerativeModel("gemini-pro")
    response = model.generate_content([prompt[1],question])
    return response.text    

#Function to retrive query from SQL database
def readSQLQuery(sql,db):
    conn = sqlite3.connect(db)
    cursor = conn.cursor()
    cursor.execute(sql)
    rows=cursor.fetchall()
    conn.commit()
    conn.close()

    for row in rows:
        print(row)
    return rows


#Prompt
prompt = ["""You are an expert at converting english questions to a SQL query. 
          The SQL database that you'll be working on is called perfumes. The attribute description of the table is as follows: \n
            brand: The brand name of the perfume.
            title: The title or name of the perfume.
            type: The type of perfume (e.g., Eau de Parfum, Eau de Toilette).
            price: The price of the perfume.
            priceWithCurrency: The price of the perfume along with the currency.
            available: The quantity of the perfume available for sale.
            availableText: Text description of availability.
            sold: The quantity of the perfume sold.
            lastUpdated: The date and time when the information was last updated.
            city: The city associated with the perfume.
            state: The state associated with the perfume.
            country: The country associated with the perfume.
            Gender: The target gender for the perfume. The categories are Women and Men \n\n\n
          Some examples of the questions asked to you are as follows: 
          Example 1: Give me the brand, title and price for perfumes priced above $50. Then the SQL query will be: SELECT brand, title, price FROM your_table_name WHERE price > 50; \n\n
          Example 2: What are the average prices for each brand? Then the SQL query will be SELECT brand, AVG(price) AS avg_price FROM your_table_name GROUP BY brand; \n\n
          Example 3: Find the total revenue generated by perfume sales. Then the SQL query will be SELECT SUM(price * sold) AS total_revenue FROM your_table_name;
          Remember the sql code should not have ``` in beginning or end and the word sql in the output.
          Do not add ``` in beginning or end and the word sql in the output.
"""]


#Streamlit App

st.set_page_config(page_title="Aroma Archives", page_icon="⚱")
st.header("⚱ Aroma Archives: A Perfume Sales and Inventory Database")
st.markdown('''
Welcome to Aroma Archives! 🌸
Dive into the world of perfumes without needing to know a single line of SQL! 
            Simply type your question in the textbox below, and watch as the magic unfolds.  
            Our app will generate the perfect SQL query for you and fetch the results you need. 
            Effortless, efficient, and enchanting—just like a spritz of your favorite fragrance. Enjoy!🪄🔍''')
question = st.text_input("Enter your question:", key="input")
submit = st.button("Generate Results")

#if submit is clicked
if submit:
    response = getGemResponse(question,prompt)
    print(response)
    data= pd.DataFrame(readSQLQuery(response,"perfumeECommerce.db"))

    st.subheader("Generated SQL Query:")
    st.code(response, language="sql")

    st.subheader("Results:")
    st.write(data)