Text-to-SQL translates a question in everyday language into an SQL query. It can let someone explore data in a relational database without writing the query themselves. The generated query must still be run against the database to obtain an answer. section 1.4.2
A reusable prompt gives the language model three things:
- The database structure, including the table and column names the query may use. A CREATE TABLE statement can supply this description. section 5.2.4
- An explicit instruction to translate a question into an SQL query, with the target system identified as SQLite when appropriate. section 5.2.4
- The question itself, placed in a prompt-template slot so different questions can use the same database description. section 1.4.2
Count games in a database
For a database with a games table, asking “How many games are stored?” produces SELECT COUNT(*) FROM games;. Running that query in SQLite returns the count; in the demonstrated database, it is 16,599. Try questions about a publisher or genre next, and check the resulting SQL before relying on its answer. A model may add an unwanted output column, so specify the required result shape and ask for a correction if needed. section 5.2.6 section 2.4.2
def create_prompt(question):
parts = []
#1
parts += ['Database:']
parts += ['create table games(rank int, name text, platform text,']
parts += ['year int, genre text, publisher text, americasales numeric,']
parts += ['eusales numeric, japansales numeric, othersales numeric,']
parts += ['globalsales numeric);']
#2
parts += ['Translate this question into SQL query:']
parts += [question] #3
return '\n'.join(parts) #4
The Python prompt builder joins a fixed games-table definition, the translation instruction, and the supplied question. This keeps the initial translator simple, but changing databases requires changing its hardcoded schema; the initial command-line workflow also leaves query execution to the user. section 5.2.4 section 5.2.6
