File size: 4,781 Bytes
68a2af8 9061f45 68a2af8 9061f45 68a2af8 9061f45 68a2af8 9061f45 68a2af8 9061f45 68a2af8 9061f45 68a2af8 9061f45 68a2af8 9061f45 68a2af8 9061f45 68a2af8 9061f45 68a2af8 9061f45 68a2af8 9061f45 68a2af8 9061f45 68a2af8 9061f45 68a2af8 9061f45 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 | import streamlit as st
from openai import OpenAI
import sqlite3
# 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()
# App UI
st.set_page_config(page_title="Zero SQL", layout="wide")
st.title("Zero SQL - Natural Language to SQL Query")
# Sidebar for API key
with st.sidebar:
st.header("Configuration")
api_key = st.text_input("OpenAI 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:
if not api_key:
st.error("🔑 API key is required!")
elif not user_input:
st.error("📝 Please enter your data request!")
else:
try:
client = OpenAI(api_key=api_key)
# System prompt with schema
system_context = """You are a SQL expert. 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 SQL query for the user's request. Output raw SQL without any formatting or explanations."""
# Generate SQL query
response = client.chat.completions.create(
model="gpt-4o",
messages=[
{"role": "system", "content": system_context},
{"role": "user", "content": user_input}
],
temperature=0.3
)
sql_query = response.choices[0].message.content.strip()
# Execute and display results
with sqlite3.connect('database.db') as conn:
cursor = conn.cursor()
cursor.execute(sql_query)
results = cursor.fetchall()
columns = [desc[0] for desc in cursor.description]
st.subheader("Generated SQL")
st.code(sql_query, language="sql")
st.subheader("Results")
if results:
st.dataframe(
data=results,
columns=columns,
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)}") |