ask your database下载 - ask your database源代码下载

使用 GPT 查询您的数据库

一个 CLI 工具，可让您询问有关任何 Postgres 数据库的 GPT 问题。只需提供您的连接详细信息，询问您的数据库就会自动加载架构、获取示例数据并为您运行查询。它与 GPT-4 配合使用效果最佳。

注意：这将直接在您提供的数据库上运行 GPT 提供的查询。除非它是只读连接，否则这意味着它可能会意外删除或更新数据。请谨慎使用并始终保留数据备份。

用法

首先，创建一个 JSON 配置文件：

{
  "openAIAPIKey" : " xxx " ,
  "openAIModel" : " gpt-4 " ,
  "dbTimeoutMs" : 20000 ,
  "apiTimeoutMs" : 30000 ,
  "postgresConnection" : {
    "host" : " localhost " ,
    "port" : 5432 ,
    "database" : " imdb " ,
    "user" : " imdb " ,
    "password" : " 1234 "
  }
}

然后，使用配置文件调用它，如下所示：

npx ask-your-database myConfig.json

它的作用

此示例使用 RyanMarcus/imdb_pg_dataset 中的示例 IMDB postgres 数据库。您可以通过下载此存档并使用pg_restore --database=db_name --user=user imdb_pg11来加载它。

首先，这将告诉您一些有关数据库的信息，并提示您提出一个初始问题：

 > npx [email protected] imdbConfig.json

You are connected to the database imdb. It has the following tables:

aka_name, aka_title, cast_info, char_name, comp_cast_type, company_name, company_type,
complete_cast, info_type, keyword, kind_type, link_type, movie_companies, movie_info,
movie_info_idx, movie_keyword, movie_link, name, person_info, role_type, title

? Ask me a question about this database, and I'll try to answer! (q to quit)

然后你可以提出一个问题，例如：

 > What movies did Keanu Reeves act in?

如果响应包含 SQL 查询，那么此工具将自动为您运行它并显示结果：

 To answer your question, we'll need to join the 'name' table to find Keanu Reeves' person_id,
and then join the 'cast_info' table to get the movie_ids in which Keanu Reeves appeared as an
actor, and finally join the 'title' table to get the titles of those movies.

Please run the following query:

SELECT title.title
FROM name
JOIN cast_info ON name.id = cast_info.person_id
JOIN title ON cast_info.movie_id = title.id
JOIN role_type ON cast_info.role_id = role_type.id
WHERE name.name ILIKE 'Reeves, Keanu' AND role_type.role = 'actor';

Returned 253 rows. Here are the first 101 rows:
┌─────────┬────────────────────────────────────────────────────────────────────────────┐
│ (index) │                                   title                                    │
├─────────┼────────────────────────────────────────────────────────────────────────────┤
│    0    │                        '101 Biggest Celebrity Oops'                        │
│    1    │                          '1992 MTV Movie Awards'                           │
│    2    │                          '1993 MTV Movie Awards'                           │
│    3    │                          '1995 MTV Movie Awards'                           │
│    4    │                          '1999 MTV Movie Awards'                           │
│    5    │                  '2000 Blockbuster Entertainment Awards'                   │
│    6    │                          '2003 MTV Movie Awards'                           │
│    7    │                      '2004 Taurus World Stunt Awards'                      │
│    8    │                          '2006 MTV Movie Awards'                           │
│    9    │                                 '47 Ronin'                                 │
│   10    │                              "A Man's Story"                               │
│   11    │                             'A Scanner Darkly'                             │

... some results truncated

当您将响应发送到 GPT 时，结果将包括：

 > This is great, but it includes things that aren't movies. Can you filter to only
movies, and also sort to show the most recent first?

使用新结果和过去的上下文，GPT 将运行更多查询并改进其结果：

 To filter only movies and sort them by the most recent first, we need to join the 'kind_type'
table to get the movie kind and filter by 'movie', and order the result by 'production_year'
in descending order. Please run the following query:

SELECT title.title, title.production_year
FROM name
JOIN cast_info ON name.id = cast_info.person_id
JOIN title ON cast_info.movie_id = title.id
JOIN role_type ON cast_info.role_id = role_type.id
JOIN kind_type ON title.kind_id = kind_type.id
WHERE name.name ILIKE 'Reeves, Keanu' AND role_type.role = 'actor' AND kind_type.kind = 'movie'
ORDER BY title.production_year DESC;

Returned 64 rows. Here are the first 51 rows:
┌─────────┬─────────────────────────────────────┬─────────────────┐
│ (index) │                title                │ production_year │
├─────────┼─────────────────────────────────────┼─────────────────┤
│    0    │             'John Wick'             │      null       │
│    1    │            'Passengers'             │      null       │
│    2    │              'Jekyll'               │      null       │
│    3    │              'Jekyll'               │      null       │
│    4    │          'Man of Tai Chi'           │      2013       │
│    5    │             '47 Ronin'              │      2013       │
│    6    │           'Side by Side'            │      2012       │
│    7    │         'Generation Um...'          │      2012       │
│    8    │           'Sunset Strip'            │      2012       │

... some results truncated

（我们的示例数据集仅包含截至 2013 年的数据）

然后，您可以继续细化结果：

 > This is great, but can you omit the ones with null production_year?

回应：

 To omit the ones with null production_year, add a condition in the WHERE clause. Please run the
following query:

SELECT title.title, title.production_year
FROM name
JOIN cast_info ON name.id = cast_info.person_id
JOIN title ON cast_info.movie_id = title.id
JOIN role_type ON cast_info.role_id = role_type.id
JOIN kind_type ON title.kind_id = kind_type.id
WHERE name.name ILIKE 'Reeves, Keanu' AND role_type.role = 'actor' AND kind_type.kind = 'movie' AND title.production_year IS NOT NULL
ORDER BY title.production_year DESC;

Returned 60 rows. Here are the first 51 rows:
┌─────────┬─────────────────────────────────────┬─────────────────┐
│ (index) │ title │ production_year │
├─────────┼─────────────────────────────────────┼─────────────────┤
│ 0 │ '47 Ronin' │ 2013 │
│ 1 │ 'Man of Tai Chi' │ 2013 │
│ 2 │ 'Sunset Strip' │ 2012 │
│ 3 │ 'Side by Side' │ 2012 │
│ 4 │ 'Generation Um...' │ 2012 │
│ 5 │ "A Man's Story" │ 2010 │
│ 6 │ "Henry's Crime" │ 2010 │
│ 7 │ 'The Private Lives of Pippa Lee' │ 2009 │
│ 8 │ 'Street Kings' │ 2008 │
│ 9 │ 'The Day the Earth Stood Still' │ 2008 │

... some results truncated

等等！