Spaces:
Sleeping
Sleeping
File size: 2,194 Bytes
5e468f2 | 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 | import sqlite3
def get_best_query(schema_create_statements, candidate_queries):
"""
Creates an in-memory database, builds the user's tables,
and tests the AI's generated queries to find the first valid one.
"""
# 1. Create a temporary database in RAM (disappears when the function ends)
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
# 2. Build the empty tables using the user's schema
try:
cursor.executescript(schema_create_statements)
except sqlite3.OperationalError as e:
return f"Error: The provided schema is invalid. ({e})"
# 3. Test the AI's candidate queries
for i, query in enumerate(candidate_queries):
try:
# We use EXPLAIN to check syntax without actually querying data
cursor.execute(f"EXPLAIN {query}")
conn.close()
return f"-- Selected Candidate #{i+1} (Syntax Valid)\n{query}"
except sqlite3.OperationalError as e:
# If there is a syntax error, we ignore it and try the next candidate
print(f"Candidate {i+1} failed syntax check: {e}")
continue
conn.close()
return "Error: All generated queries contained syntax errors."
# --- TESTING BLOCK ---
if __name__ == "__main__":
# The frontend will provide the raw CREATE statements
test_schema = """
CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, salary REAL);
CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT);
"""
# The AI generates these. Notice the first two have deliberate syntax errors.
ai_candidates = [
"SELECT SUMM(salary) FROM employees", # Fails: Bad function name
"SELECT sum(salary) FROM employees JOIN bad_table", # Fails: Table doesn't exist
"SELECT sum(salary) FROM employees" # Passes: Perfect SQLite syntax
]
print("Testing AI Candidates against In-Memory DB...\n")
final_output = get_best_query(test_schema, ai_candidates)
print("\nWinning Query to show the user:\n" + final_output)
|