File size: 5,068 Bytes
68a2af8 9a8f90f 73d59f1 68a2af8 923ec5b 9061f45 68a2af8 9061f45 68a2af8 9061f45 9a8f90f 9061f45 68a2af8 9061f45 68a2af8 9061f45 68a2af8 9a8f90f 68a2af8 9a8f90f 68a2af8 9a8f90f 68a2af8 0970e0c 9a8f90f 0970e0c 9061f45 0941bd4 68a2af8 9061f45 68a2af8 9061f45 68a2af8 7c06f81 68a2af8 7c06f81 68a2af8 9061f45 68a2af8 9061f45 68a2af8 9061f45 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 |
import streamlit as st
import sqlite3
import pandas as pd
import google.generativeai as genai
import os
# App UI
st.set_page_config(page_title="Zero SQL", layout="wide")
st.title("Zero SQL - Natural Language to SQL Query")
# Database initialization with caching
@st.cache_resource
def initialize_database():
conn = sqlite3.connect('database.db')
cursor = conn.cursor()
# Create tables
cursor.execute('''
CREATE TABLE IF NOT EXISTS Produkte (
ProduktID INTEGER PRIMARY KEY AUTOINCREMENT,
Produktname TEXT NOT NULL,
Preis REAL NOT NULL
)
''')
cursor.execute('''
CREATE TABLE IF NOT EXISTS Bestellungen (
BestellungID INTEGER PRIMARY KEY AUTOINCREMENT,
ProduktID INTEGER NOT NULL,
Menge INTEGER NOT NULL,
Bestelldatum TEXT NOT NULL,
Person TEXT NOT NULL,
FOREIGN KEY (ProduktID) REFERENCES Produkte(ProduktID)
)
''')
# Insert sample products
cursor.execute("SELECT COUNT(*) FROM Produkte")
if cursor.fetchone()[0] == 0:
products = [
('Laptop', 999.99),
('Smartphone', 699.99),
('Tablet', 399.99)
]
cursor.executemany("INSERT INTO Produkte (Produktname, Preis) VALUES (?, ?)", products)
# Insert sample orders
cursor.execute("SELECT COUNT(*) FROM Bestellungen")
if cursor.fetchone()[0] == 0:
orders = [
(1, 2, '2024-10-20', 'Max Mustermann'),
(2, 1, '2024-10-21', 'Erika Musterfrau'),
(3, 3, '2024-10-22', 'Hans Meier')
]
cursor.executemany("INSERT INTO Bestellungen (ProduktID, Menge, Bestelldatum, Person) VALUES (?, ?, ?, ?)", orders)
conn.commit()
conn.close()
# Initialize the database
initialize_database()
# Sidebar for API key
with st.sidebar:
st.header("Configuration")
api_key_iput = st.text_input("Gemini API Key", type="password")
st.markdown("---")
st.markdown("**Sample Questions:**")
st.markdown("- Show total sales per product")
st.markdown("- List orders from Max Mustermann")
st.markdown("- Find most popular product by quantity")
# Main form
with st.form("query_form"):
user_input = st.text_area(
"Enter your data request in natural language:",
placeholder="e.g. Show all orders over €500",
height=100
)
submitted = st.form_submit_button("🚀 Generate Query")
if submitted:
api_key = api_key_iput or os.getenv("GEMINI_API_KEY")
if not user_input:
st.error("📝 Please enter your data request!")
else:
try:
# Configure Gemini
genai.configure(api_key=api_key)
model = genai.GenerativeModel('gemini-2.0-flash')
# Schema context
system_context = """Given these SQL tables:
CREATE TABLE Produkte (
ProduktID INTEGER PRIMARY KEY,
Produktname TEXT NOT NULL,
Preis REAL NOT NULL
);
CREATE TABLE Bestellungen (
BestellungID INTEGER PRIMARY KEY,
ProduktID INTEGER,
Menge INTEGER,
Bestelldatum TEXT,
Person TEXT,
FOREIGN KEY (ProduktID) REFERENCES Produkte(ProduktID)
);
Generate ONLY the raw SQL query for the following request.
Output ONLY the pure SQL statement without any formatting,
explanations, or markdown blocks."""
# Combine context with user input
full_prompt = f"{system_context}\n\nUser Request: {user_input}"
# Generate SQL query
response = model.generate_content(
full_prompt,
generation_config={"temperature": 0.3}
)
# Clean up the response
sql_query = response.text.strip()
sql_query = sql_query.replace("```sql", "").replace("```", "").strip()
# Execute and display results
with sqlite3.connect('database.db') as conn:
cursor = conn.cursor()
cursor.execute(sql_query)
results = cursor.fetchall()
column_names = [desc[0] for desc in cursor.description]
st.subheader("Generated SQL")
st.code(sql_query, language="sql")
st.subheader("Results")
if results:
df = pd.DataFrame(results, columns=column_names)
st.dataframe(
data=df,
use_container_width=True,
hide_index=True
)
else:
st.info("No results found", icon="ℹ️")
except sqlite3.Error as e:
st.error(f"🚨 SQL Error: {str(e)}")
except Exception as e:
st.error(f"💥 Unexpected Error: {str(e)}") |