SQLite is a relational database system for storing tables and running SQL queries against them. A table has named, typed columns and rows of values. For example, a games table can hold each game’s name, platform, release year, and genre. section 5.2.1
To load a games data set with SQLite 3:
- Check the installation with `sqlite3 --version`, then open a database with `sqlite3 games.db`. section 5.2.1
- Create a `games` table with columns suited to the data, such as text columns for names and genres and an integer column for release years. section 5.2.1
- Set CSV mode, import the data into `games`, and check the import with `SELECT count(*) FROM games;`. section 5.2.1
Once the data is loaded, a SQL query can answer a data-analysis question. A ready-to-run Google Colab notebook offers another way to try this: run its database-creation cell, replace the supplied SQL query, and run the query cell to see the result. This route avoids installing SQLite locally. section 2.4.2
Give a natural-language query interface the database structure
An interface that turns a question into SQL needs to know which tables and columns exist. SQLite’s `sqlite_master` schema table holds the SQL statements that created tables. Run `select sql from sqlite_master where type = 'table';` to retrieve those definitions, then include them in the language-model input so the generated query can refer to the current database. section 5.3.2
import sqlite3
def get_structure(data_path):
with sqlite3.connect(data_path) as connection: #1
cursor = connection.cursor() #2
cursor.execute("select sql from sqlite_master where type =
'table';") #3
table_rows = cursor.fetchall() #4
table_ddls = [r[0] for r in table_rows] #5
return '\n'.join(table_ddls) #6
The Python function connects to a database path, fetches table definitions from `sqlite_master`, and joins them into a description that can be supplied to a language model. section 5.3.2
