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

等等！