ai4sqlite3

SQLite数据库的自然语言查询助手

使用本地的SQLite3数据库文件，通过命令行界面询问您的查询意图，使用OpenAI的ChatGPT API来制定满足这些意图的SQL，然后在您的数据库上运行SQL。请提供您自己的OpenAI API密钥（$ / 免费试用）。

该工具将您的数据库模式和书面查询意图发送到OpenAI。但不包括结果集或其他数据库的内容。数据库以只读模式打开，以便AI不会破坏它。

快速入门

$ export OPENAPI_API_KEY=xxx
$ pip3 install ai4sqlite3
$ wget https://github.com/lerocha/chinook-database/raw/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite
$ ai4sqlite3 Chinook_Sqlite.sqlite --yes
Analyzing schema of Chinook_Sqlite.sqlite in 4.9s 

This database models a digital music store. It includes tables for artists, albums,
tracks, genres, media types, invoices, customers, employees, playlists, and playlist
tracks. The tables are linked through foreign keys to form relationships, such as an
artist being associated with an album, an invoice being linked to a customer, and a
playlist being composed of multiple tracks. The database is designed to enable the store
to manage and track music sales, customer information, and employee records, as well as
organizing and categorizing the available music.

Please state the nature of the desired database query.
> top five customer countries by 2011 revenue (round to cents)

Generating SQL in 2.8s 

SELECT c.Country, ROUND(SUM(i.Total), 2) AS 'Revenue 2011'
FROM Customer c
JOIN Invoice i ON c.CustomerId = i.CustomerId
WHERE strftime('%Y', i.InvoiceDate) = '2011'
GROUP BY c.Country
ORDER BY SUM(i.Total) DESC
LIMIT 5;

Executing query in 0.1s 
+---------+--------------+
| Country | Revenue 2011 |
+---------+--------------+
|   USA   |    103.01    |
|  Canada |    55.44     |
| Germany |    48.57     |
|  France |    42.61     |
| Ireland |    32.75     |
+---------+--------------+

Next query?
> percentage of all revenue from sales to North American customers

Generating SQL in 3.3s 

SELECT 
    ROUND(SUM(i.Total) / (SELECT SUM(Total) FROM Invoice)*100, 2) AS "North American Revenue Percentage"
FROM 
    Invoice i
    INNER JOIN Customer c ON i.CustomerId = c.CustomerId
WHERE 
    c.Country = 'USA' OR c.Country = 'Canada';

Executing query in 0.1s 
+-----------------------------------+
| North American Revenue Percentage |
+-----------------------------------+
|               35.52               |
+-----------------------------------+

Next query?
>

建议

• 我们在下一个查询？提示之间重置AI内存，因此您不能隐式地回溯先前的查询。
• 但您可以使用上箭头键来回忆先前的输入以编辑或添加。
• 如果SQLite拒绝AI的SQL，我们将自动请求更正（最多--revisions尝试）。
• 您可以用类似的方式获得一般问题的答案：关于模式，每一张发票行项目指的是什么？
• 您可能会喜欢探索您的Firefox或Chrome浏览器历史数据库（如果您的浏览器已经打开它，可能需要复制文件）。

具有挑战性的示例

这里有一些通常由gpt-3.5-turbo生成错误答案的示例（但我们展示了精选的例子）。

> Considering sales to USA customers, find the top-grossing artist in each state.

Generating SQL in 13.4s 

WITH 
    -- Select only the sales to USA customers and
    -- join the necessary tables.
    usa_sales AS (
        SELECT il.*, c.State
        FROM InvoiceLine il
        INNER JOIN Invoice i ON il.InvoiceId=i.InvoiceId
        INNER JOIN Customer c ON i.CustomerId=c.CustomerId
        WHERE c.Country='USA'
    ),
 
    -- Calculate the total sale in dollars for each artist.
    artist_total_sales AS (
        SELECT ar.Name, SUM(usa_sales.UnitPrice*usa_sales.Quantity) AS total_sales
        FROM usa_sales
        INNER JOIN Track t ON usa_sales.TrackId=t.TrackId
        INNER JOIN Album al ON t.AlbumId=al.AlbumId
        INNER JOIN Artist ar ON al.ArtistId=ar.ArtistId
        GROUP BY ar.ArtistId
    ),
 
    -- Rank the artists by total sale in each state.
    -- If two or more artists share the top sale in a state,
    -- rank them alphabetically by artist name.
    ranked_artists AS (
        SELECT ar.Name, usa_sales.State,
               SUM(usa_sales.UnitPrice*usa_sales.Quantity) AS state_sales,
               RANK() OVER (
                    PARTITION BY usa_sales.State 
                    ORDER BY SUM(usa_sales.UnitPrice*usa_sales.Quantity) DESC, ar.Name
               ) AS rank_num
        FROM usa_sales
        INNER JOIN Track t ON usa_sales.TrackId=t.TrackId
        INNER JOIN Album al ON t.AlbumId=al.AlbumId
        INNER JOIN Artist ar ON al.ArtistId=ar.ArtistId
        GROUP BY ar.ArtistId, usa_sales.State
    )
 
-- Select only the top-ranked artist in each state.
SELECT State, Name AS Top_Grossing_Artist
FROM ranked_artists
WHERE rank_num=1
ORDER BY State;

Executing query in 0.1s 
+-------+--------------------------------+
| State |      Top_Grossing_Artist       |
+-------+--------------------------------+
|   AZ  |          Iron Maiden           |
|   CA  |          Iron Maiden           |
|   FL  |           Metallica            |
|   IL  |           The Office           |
|   MA  |       Milton Nascimento        |
|   NV  |          Cidade Negra          |
|   NY  |          Deep Purple           |
|   TX  |              Lost              |
|   UT  | Battlestar Galactica (Classic) |
|   WA  |           Metallica            |
|   WI  |           The Office           |
+-------+--------------------------------+

Next query?
> Col 1: employee name. Col 2: subordinate employee name. Subordinates include transitive as well as direct reports.

Generating SQL in 5.6s 

WITH RECURSIVE cte_subordinates AS (
  SELECT e1.FirstName || ' ' || e1.LastName AS EmployeeName, 
         e2.FirstName || ' ' || e2.LastName AS SubordinateName
  FROM Employee e1
  INNER JOIN Employee e2 ON e2.ReportsTo = e1.EmployeeId
  UNION
  SELECT cte.EmployeeName, e.FirstName || ' ' || e.LastName AS SubordinateName
  FROM cte_subordinates cte
  INNER JOIN Employee e ON e.ReportsTo = (
    SELECT EmployeeId FROM Employee WHERE FirstName || ' ' || LastName = cte.SubordinateName
  )
)
SELECT * FROM cte_subordinates ORDER BY EmployeeName, SubordinateName LIMIT 25;

Executing query in 0.1s 
+------------------+------------------+
|   EmployeeName   | SubordinateName  |
+------------------+------------------+
|   Andrew Adams   |   Jane Peacock   |
|   Andrew Adams   |  Laura Callahan  |
|   Andrew Adams   |  Margaret Park   |
|   Andrew Adams   | Michael Mitchell |
|   Andrew Adams   |  Nancy Edwards   |
|   Andrew Adams   |   Robert King    |
|   Andrew Adams   |  Steve Johnson   |
| Michael Mitchell |  Laura Callahan  |
| Michael Mitchell |   Robert King    |
|  Nancy Edwards   |   Jane Peacock   |
|  Nancy Edwards   |  Margaret Park   |
|  Nancy Edwards   |  Steve Johnson   |
+------------------+------------------+