lang2sql
1.0.0
該倉庫提供了一個分步指南和一個模板，用於使用OPNEAI API為SQL代碼生成器設置自然語言。
該教程也可以在Medium上獲得。
最後更新：2024年1月7日
自然語言模型的快速發展，尤其是大型語言模型（LLMS），為各個領域提供了許多可能性。最常見的應用之一是使用LLMS進行編碼。例如，OpenAI的Chatgpt和Meta的代碼Llama是LLM，可為代碼生成器提供最新的自然語言。一種潛在的用例是SQL代碼生成器的自然語言，該語言可以幫助非技術專業人員提出簡單的數據請求，並希望使數據團隊專注於更多數據密集型任務。本教程的重點是使用OpenAI API為SQL代碼生成器設置語言。
一個可能的應用程序是一個聊天機器人，可以用相關數據響應用戶查詢（圖1）。可以使用Python應用程序將聊天機器人與Slack通道集成，該應用程序執行以下步驟：
在本教程中，我們將構建一個分步Python應用程序，將用戶問題轉換為SQL查詢。
本教程提供了有關如何設置Python應用程序的分步指南，該應用程序使用OpenAI API將一般問題轉換為SQL查詢。其中包括以下功能：
下圖2描述了簡單語言對SQL代碼生成器的一般體系結構。
本教程的範圍和焦點是在綠色框上 - 構建以下功能：
提示的問題- 將問題轉換為及時格式：
API處理程序- 與OpenAI API一起使用的功能：
DB處理程序- 將SQL查詢發送到數據庫並返回所需數據的函數
本教程的主要先決條件是Python的基本知識。其中包括以下功能：
此外，需要SQL的基本知識和對OpenAI API的訪問。
雖然沒有必要，但擁有Docker的基本知識是有幫助的，因為該教程是使用VSCODE的DEV容器擴展程序在Dockerized環境中創建的。如果您沒有Docker或擴展名的經驗，則仍然可以通過創建虛擬環境並安裝所需的庫來運行教程（如下所述）。了解及時工程和OpenAI API的知識也是有益的。
我創建了一個有關使用VSCODE和DEV容器擴展的Python Dockerized環境的詳細教程：
https://github.com/ramikrispin/vscode-python
為了為SQL代碼生成設置自然語言，我們將使用以下Python庫：
pandas - 在整個過程中處理數據duckdb使用數據庫模擬工作openai與Openai API合作time和os加載CSV文件和格式字段該存儲庫包含必要的設置，以啟動具有VSCODE和DEV容器擴展的教程要求的Dockerized環境。下一節將提供更多詳細信息。
另外，您可以使用以下說明使用“使用虛擬環境”部分中的說明來設置虛擬環境並安裝教程要求。
該教程是在帶有VSCODE和DEV容器擴展的Dockerized環境中構建的。要使用VSCODE運行它，您將需要安裝Dev容器擴展程序並具有Docker Desktop（或等效）打開。環境的設置可在.devcontainer文件夾下可用：
.── .devcontainer
├── Dockerfile
├── Dockerfile.dev
├── devcontainer.json
├── install_dependencies_core.sh
├── install_dependencies_other.sh
├── install_quarto.sh
├── requirements_core.txt
├── requirements_openai.txt
└── requirements_transformers.txt
devcontainer.json具有此Dockerized環境的構建指令和VSCODE設置：
{
"name" : " lang2sql " ,
"build" : {
"dockerfile" : " Dockerfile " ,
"args" : {
"ENV_NAME" : " lang2sql " ,
"PYTHON_VER" : " 3.10 " ,
"METHOD" : " openai " ,
"QUARTO_VER" : " 1.3.450 "
},
"context" : " . "
},
"customizations" : {
"settings" : {
"python.defaultInterpreterPath" : " /opt/conda/envs/lang2sql/bin/python " ,
"python.selectInterpreter" : " /opt/conda/envs/lang2sql/bin/python "
},
"vscode" : {
"extensions" : [
" quarto.quarto " ,
" ms-azuretools.vscode-docker " ,
" ms-python.python " ,
" ms-vscode-remote.remote-containers " ,
" yzhang.markdown-all-in-one " ,
" redhat.vscode-yaml " ,
" ms-toolsai.jupyter "
]
}
},
"remoteEnv" : {
"OPENAI_KEY" : " ${localEnv:OPENAI_KEY} "
}
}
build參數定義了docker build方法並為構建設置參數。在這種情況下，我們將Python版本設置為3.10 ，將Conda虛擬環境設置為ang2sql 。 METHOD參數定義了環境類型 - 使用OpenAI API或transformers安裝本教程的要求openai ，以設置HuggingFaces API的環境（本教程的範圍不超出範圍）。
remoteEnv參數啟用設置環境變量。我們將使用它來設置OpenAI API密鑰。在這種情況下，我將本地設置為OPENAI_KEY ，並使用localEnv參數加載它。
如果您想了解有關使用Vscode和Docker設置Python開發環境的更多信息，請查看本教程。
如果您不使用教程Dockerized環境，則可以使用以下腳本從命令行創建本地虛擬環境：
ENV_NAME=openai_api
PYTHON_VER=3.10
conda create -y --name $ENV_NAME python= $PYTHON_VER
conda activate $ENV_NAME
pip3 install -r ./.devcontainer/requirements_core.txt
pip3 install -r ./.devcontainer/requirements_openai.txt
注意：我使用了conda ，它也應該與任何其他Virutal環境方法一起使用。
我們使用ENV_NAME和PYTHON_VER變量分別設置虛擬環境和Python版本。
要確認正確設置您的環境，請使用conda list確認安裝了所需的Python庫。您應該期望以下輸出：
(openai_api) root@0ca5b8000cd5:/workspaces/lang2sql# conda list
# packages in environment at /opt/conda/envs/openai_api:
#
# Name Version Build Channel
_libgcc_mutex 0.1 main
_openmp_mutex 5.1 51_gnu
aiohttp 3.9.0 pypi_0 pypi
aiosignal 1.3.1 pypi_0 pypi
asttokens 2.4.1 pypi_0 pypi
async-timeout 4.0.3 pypi_0 pypi
attrs 23.1.0 pypi_0 pypi
bzip2 1.0.8 hfd63f10_2
ca-certificates 2023.08.22 hd43f75c_0
certifi 2023.11.17 pypi_0 pypi
charset-normalizer 3.3.2 pypi_0 pypi
comm 0.2.0 pypi_0 pypi
contourpy 1.2.0 pypi_0 pypi
cycler 0.12.1 pypi_0 pypi
debugpy 1.8.0 pypi_0 pypi
decorator 5.1.1 pypi_0 pypi
duckdb 0.9.2 pypi_0 pypi
exceptiongroup 1.2.0 pypi_0 pypi
executing 2.0.1 pypi_0 pypi
fonttools 4.45.1 pypi_0 pypi
frozenlist 1.4.0 pypi_0 pypi
gensim 4.3.2 pypi_0 pypi
idna 3.5 pypi_0 pypi
ipykernel 6.26.0 pypi_0 pypi
ipython 8.18.0 pypi_0 pypi
jedi 0.19.1 pypi_0 pypi
joblib 1.3.2 pypi_0 pypi
jupyter-client 8.6.0 pypi_0 pypi
jupyter-core 5.5.0 pypi_0 pypi
kiwisolver 1.4.5 pypi_0 pypi
ld_impl_linux-aarch64 2.38 h8131f2d_1
libffi 3.4.4 h419075a_0
libgcc-ng 11.2.0 h1234567_1
libgomp 11.2.0 h1234567_1
libstdcxx-ng 11.2.0 h1234567_1
libuuid 1.41.5 h998d150_0
matplotlib 3.8.2 pypi_0 pypi
matplotlib-inline 0.1.6 pypi_0 pypi
multidict 6.0.4 pypi_0 pypi
ncurses 6.4 h419075a_0
nest-asyncio 1.5.8 pypi_0 pypi
numpy 1.26.2 pypi_0 pypi
openai 0.28.1 pypi_0 pypi
openssl 3.0.12 h2f4d8fa_0
packaging 23.2 pypi_0 pypi
pandas 2.0.0 pypi_0 pypi
parso 0.8.3 pypi_0 pypi
pexpect 4.8.0 pypi_0 pypi
pillow 10.1.0 pypi_0 pypi
pip 23.3.1 py310hd43f75c_0
platformdirs 4.0.0 pypi_0 pypi
prompt-toolkit 3.0.41 pypi_0 pypi
psutil 5.9.6 pypi_0 pypi
ptyprocess 0.7.0 pypi_0 pypi
pure-eval 0.2.2 pypi_0 pypi
pygments 2.17.2 pypi_0 pypi
pyparsing 3.1.1 pypi_0 pypi
python 3.10.13 h4bb2201_0
python-dateutil 2.8.2 pypi_0 pypi
pytz 2023.3.post1 pypi_0 pypi
pyzmq 25.1.1 pypi_0 pypi
readline 8.2 h998d150_0
requests 2.31.0 pypi_0 pypi
scikit-learn 1.3.2 pypi_0 pypi
scipy 1.11.4 pypi_0 pypi
setuptools 68.0.0 py310hd43f75c_0
six 1.16.0 pypi_0 pypi
smart-open 6.4.0 pypi_0 pypi
sqlite 3.41.2 h998d150_0
stack-data 0.6.3 pypi_0 pypi
threadpoolctl 3.2.0 pypi_0 pypi
tk 8.6.12 h241ca14_0
tornado 6.3.3 pypi_0 pypi
tqdm 4.66.1 pypi_0 pypi
traitlets 5.13.0 pypi_0 pypi
tzdata 2023.3 pypi_0 pypi
urllib3 2.1.0 pypi_0 pypi
wcwidth 0.2.12 pypi_0 pypi
wheel 0.41.2 py310hd43f75c_0
xz 5.4.2 h998d150_0
yarl 1.9.3 pypi_0 pypi
zlib 1.2.13 h998d150_0
我們將使用OpenAI API使用Text-Davinci-003引擎訪問CHATGPT。這需要一個主動的OpenAI帳戶和API密鑰。按照以下鏈接中的說明設置帳戶和API密鑰是很簡單的：
https://openai.com/product
設置了對API和密鑰的訪問後，我建議將密鑰作為環境變量添加到.zshrc文件（或您使用用於將環境變量存儲在Shell System上的任何其他格式）。我將API密鑰存儲在OPENAI_KEY環境變量下。由於令人信服的原因，我建議您使用相同的命名約定。
要在.zshrc文件（或同等）上設置變量，請在文件中添加以下行：
export OPENAI_KEY= " YOUR_API_KEY "如果使用VSCODE或從終端運行，則必須在將變量添加到.zshrc文件後重新啟動會話。
為了模擬數據庫功能，我們將使用芝加哥犯罪數據集。該數據集提供了有關自2001年以來記錄的芝加哥市記錄的犯罪的深入信息。數據集包含近800萬張記錄和22列，其中包括犯罪分類，位置，時間，結果等信息。數據可從芝加哥數據門戶中下載。由於我們將數據存儲在本地作為熊貓數據框架並使用duckDB模擬SQL查詢，因此我們將使用過去三年下載數據子集。
您可以從API中獲取數據或下載CSV文件。為了避免在每次運行腳本時調用API，請下載文件並將它們存儲在數據文件夾下。以下是按年份到數據集的鏈接：
要下載數據，請使用右上方的Export按鈕，選擇CSV選項，然後單擊Download按鈕，如圖4所示。
我使用以下命名約定-Chicago_crime_year.csv，並將文件保存在data文件夾中。每個文件大小接近50 MB。因此，我將它們添加到data文件夾下的git忽略文件中，並且在此存儲庫中不可用。下載文件並設置其名稱後，您應該在文件夾中具有以下文件：
| ── data
├── chicago_crime_2021.csv
├── chicago_crime_2022.csv
└── chicago_crime_2023.csv
注意：截至創建本教程時，2023的數據仍在更新中。因此，在以下部分運行一些查詢時，您可能會收到略有不同的結果。
讓我們繼續進行令人興奮的部分，該部分正在設置SQL代碼生成器。在本節中，我們將創建一個python函數，該功能會引入用戶的問題，關聯的SQL表和OpenAI API鍵，並輸出回答用戶問題的SQL查詢。
讓我們首先加載芝加哥犯罪數據集和所需的Python庫。
首先，讓我們加載所需的Python庫：
import pandas as pd
import duckdb
import openai
import time
import os我們將利用OS和時間庫加載CSV文件並重新格式化某些字段。數據將使用PANDAS庫處理，我們將使用DuckDB庫模擬SQL命令。最後，我們將使用OpenAI庫建立與OpenAI API的連接。
接下來，我們將從數據文件夾加載CSV文件。下面的代碼讀取數據文件夾中可用的所有CSV文件：
path = "./data"
files = [ x for x in os . listdir ( path = path ) if ".csv" in x ]如果您下載了2021年至2023年的相應文件並使用了相同的命名約定，則應期望以下輸出：
print ( files )
[ 'chicago_crime_2022.csv' , 'chicago_crime_2023.csv' , 'chicago_crime_2021.csv' ]接下來，我們將讀取和加載所有文件，並將它們附加到熊貓數據框架中：
chicago_crime = pd . concat (( pd . read_csv ( path + "/" + f ) for f in files ), ignore_index = True )
chicago_crime . head如果正確加載文件，則應期望以下輸出：
< bound method NDFrame . head of ID Case Number Date Block
0 12589893 JF109865 01 / 11 / 2022 03 : 00 : 00 PM 087 XX S KINGSTON AVE
1 12592454 JF113025 01 / 14 / 2022 03 : 55 : 00 PM 067 XX S MORGAN ST
2 12601676 JF124024 01 / 13 / 2022 04 : 00 : 00 PM 031 XX W AUGUSTA BLVD
3 12785595 JF346553 08 / 05 / 2022 09 : 00 : 00 PM 072 XX S UNIVERSITY AVE
4 12808281 JF373517 08 / 14 / 2022 02 : 00 : 00 PM 055 XX W ARDMORE AVE
... ... ... ... ...
648826 26461 JE455267 11 / 24 / 2021 12 : 51 : 00 AM 107 XX S LANGLEY AVE
648827 26041 JE281927 06 / 28 / 2021 01 : 12 : 00 AM 117 XX S LAFLIN ST
648828 26238 JE353715 08 / 29 / 2021 03 : 07 : 00 AM 010 XX N LAWNDALE AVE
648829 26479 JE465230 12 / 03 / 2021 08 : 37 : 00 PM 000 XX W 78 TH PL
648830 11138622 JA495186 05 / 21 / 2021 12 : 01 : 00 AM 019 XX N PULASKI RD
IUCR Primary Type
0 1565 SEX OFFENSE
1 2826 OTHER OFFENSE
2 1752 OFFENSE INVOLVING CHILDREN
3 1544 SEX OFFENSE
4 1562 SEX OFFENSE
... ... ...
648826 0110 HOMICIDE
648827 0110 HOMICIDE
648828 0110 HOMICIDE
648829 0110 HOMICIDE
648830 1752 OFFENSE INVOLVING CHILDREN
...
648828 41.899709 - 87.718893 ( 41.899709327 , - 87.718893208 )
648829 41.751832 - 87.626374 ( 41.751831742 , - 87.626373808 )
648830 41.915798 - 87.726524 ( 41.915798196 , - 87.726524412 ) 注意：創建本教程時，可以使用2023的部分數據。附加這三個文件將導致比所示的行還要多（648830行）。
在進入Python代碼之前，讓我們暫停並查看及時工程的工作原理以及如何幫助ChatGpt（以及通常任何LLM）生成最佳結果。我們將在本節中使用CHATGPT Web界面。
統計和機器學習模型的主要因素是，輸出質量取決於輸入質量。正如著名的短語所說 -垃圾，垃圾。同樣，LLM輸出的質量取決於提示的質量。
例如，假設我們想計算最終被捕的案件數量。
如果我們使用以下提示：
Create an SQL query that counts the number of records that ended up with an arrest.
這是chatgpt的輸出：
值得注意的是，Chatgpt提供了通用的響應。儘管通常是正確的，但在自動化過程中使用可能不切實際。首先，響應中的字段名稱與我們需要查詢的實際表中的字段名稱不匹配。其次，代表逮捕結果的領域是布爾值（ true或false ），而不是整數（ 0或1 ）。
從這個意義上講，chatgpt的行為就像人類。您不太可能通過在堆棧溢出或任何其他類似平台等編碼表上發布相同的問題來從人類那裡獲得更準確的答案。鑑於我們沒有提供有關表特徵的任何上下文或其他信息，因此期望chatgpt猜測字段名稱及其值是不合理的。在任何提示中，上下文都是至關重要的因素。為了說明這一點，讓我們看看chatgpt如何處理以下提示：
I have a table named chicago_crime with the crime records in Chicago City since 2021. The Arrest field defines if the case ended up with arrest or not, and it is a boolean (true or false).
I want to create an SQL query that counts the number of records that ended up with an arrest.
這是chatgpt的輸出：
這次，在添加上下文之後，chatgpt返回了我們可以按原樣使用的正確查詢。通常，使用文本生成器時，提示應包括兩個組件 - 上下文和請求。在上面的提示中，第一段表示提示的上下文：
I have a table named chicago_crime with the crime records in Chicago City since 2021. The Arrest field defines if the case ended up with arrest or not, and it is a boolean (true or false).
第二段代表請求：
I want to create an SQL query that counts the number of records that ended up with an arrest.
OpenAI API將上下文稱為system ，並作為user要求。
OpenAI API文檔在要求生成SQL代碼時在提示中如何設置system和user組件的建議提供了建議：
System
Given the following SQL tables, your job is to write queries given a user’s request.
CREATE TABLE Orders (
OrderID int,
CustomerID int,
OrderDate datetime,
OrderTime varchar(8),
PRIMARY KEY (OrderID)
);
CREATE TABLE OrderDetails (
OrderDetailID int,
OrderID int,
ProductID int,
Quantity int,
PRIMARY KEY (OrderDetailID)
);
CREATE TABLE Products (
ProductID int,
ProductName varchar(50),
Category varchar(50),
UnitPrice decimal(10, 2),
Stock int,
PRIMARY KEY (ProductID)
);
CREATE TABLE Customers (
CustomerID int,
FirstName varchar(50),
LastName varchar(50),
Email varchar(100),
Phone varchar(20),
PRIMARY KEY (CustomerID)
);
User
Write a SQL query which computes the average total order value for all orders on 2023-04-01.
在下一節中，我們將使用上述openai示例並將其推廣到通用模板中。
在上一節中，我們討論了及時工程的重要性，以及提供良好背景的如何提高LLM響應準確性。此外，我們看到了SQL代碼生成的OpenAI推薦的及時結構。在本節中，我們將重點介紹基於這些原則為SQL生成提示的過程。目標是構建一個接收表名和用戶問題的Python函數，並相應地創建提示。例如，對於我們之前加載的表chicago_crime表，以及我們在上一節中提出的問題，該功能應創建以下提示：
Given the following SQL table, your job is to write queries given a user’s request.
CREATE TABLE chicago_crime (ID BIGINT, Case Number VARCHAR, Date VARCHAR, Block VARCHAR, IUCR VARCHAR, Primary Type VARCHAR, Description VARCHAR, Location Description VARCHAR, Arrest BOOLEAN, Domestic BOOLEAN, Beat BIGINT, District BIGINT, Ward DOUBLE, Community Area BIGINT, FBI Code VARCHAR, X Coordinate DOUBLE, Y Coordinate DOUBLE, Year BIGINT, Updated On VARCHAR, Latitude DOUBLE, Longitude DOUBLE, Location VARCHAR)
Write a SQL query that returns - How many cases ended up with arrest?
讓我們從及時的結構開始。我們將採用OpenAI格式並使用以下模板：
system_template = """
Given the following SQL table, your job is to write queries given a user’s request. n
CREATE TABLE {} ({}) n
"""
user_template = "Write a SQL query that returns - {}" system_template收到兩個元素的位置：
對於本教程，我們將使用DuckDB庫來處理PANDAS的數據框架，因為它是SQL表，並使用duckdb.sql函數提取表的字段名稱和屬性。例如，讓我們使用DESCRIBE sql命令提取chicago_crime表字段信息：
duckdb . sql ( "DESCRIBE SELECT * FROM chicago_crime;" )應該返回下表：
┌──────────────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │ null │ key │ default │ extra │
│ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │
├──────────────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ ID │ BIGINT │ YES │ NULL │ NULL │ NULL │
│ Case Number │ VARCHAR │ YES │ NULL │ NULL │ NULL │
│ Date │ VARCHAR │ YES │ NULL │ NULL │ NULL │
│ Block │ VARCHAR │ YES │ NULL │ NULL │ NULL │
│ IUCR │ VARCHAR │ YES │ NULL │ NULL │ NULL │
│ Primary Type │ VARCHAR │ YES │ NULL │ NULL │ NULL │
│ Description │ VARCHAR │ YES │ NULL │ NULL │ NULL │
│ Location Description │ VARCHAR │ YES │ NULL │ NULL │ NULL │
│ Arrest │ BOOLEAN │ YES │ NULL │ NULL │ NULL │
│ Domestic │ BOOLEAN │ YES │ NULL │ NULL │ NULL │
│ Beat │ BIGINT │ YES │ NULL │ NULL │ NULL │
│ District │ BIGINT │ YES │ NULL │ NULL │ NULL │
│ Ward │ DOUBLE │ YES │ NULL │ NULL │ NULL │
│ Community Area │ BIGINT │ YES │ NULL │ NULL │ NULL │
│ FBI Code │ VARCHAR │ YES │ NULL │ NULL │ NULL │
│ X Coordinate │ DOUBLE │ YES │ NULL │ NULL │ NULL │
│ Y Coordinate │ DOUBLE │ YES │ NULL │ NULL │ NULL │
│ Year │ BIGINT │ YES │ NULL │ NULL │ NULL │
│ Updated On │ VARCHAR │ YES │ NULL │ NULL │ NULL │
│ Latitude │ DOUBLE │ YES │ NULL │ NULL │ NULL │
│ Longitude │ DOUBLE │ YES │ NULL │ NULL │ NULL │
│ Location │ VARCHAR │ YES │ NULL │ NULL │ NULL │
├──────────────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┤
│ 22 rows 6 columns │
└────────────────────────────────────────────────────────────────────────────┘注意：我們需要的信息 - 前兩列可用列名及其屬性可用。因此，我們將需要解析這些列並將它們組合到以下格式：
Column_Name Column_Attribute
例如， Case Number列應轉移到以下格式：
Case Number VARCHAR
下面的create_message函數協調了獲取表名和問題的過程，並使用上述邏輯來生成提示：
def create_message ( table_name , query ):
class message :
def __init__ ( message , system , user , column_names , column_attr ):
message . system = system
message . user = user
message . column_names = column_names
message . column_attr = column_attr
system_template = """
Given the following SQL table, your job is to write queries given a user’s request. n
CREATE TABLE {} ({}) n
"""
user_template = "Write a SQL query that returns - {}"
tbl_describe = duckdb . sql ( "DESCRIBE SELECT * FROM " + table_name + ";" )
col_attr = tbl_describe . df ()[[ "column_name" , "column_type" ]]
col_attr [ "column_joint" ] = col_attr [ "column_name" ] + " " + col_attr [ "column_type" ]
col_names = str ( list ( col_attr [ "column_joint" ]. values )). replace ( '[' , '' ). replace ( ']' , '' ). replace ( ' ' ' , '' )
system = system_template . format ( table_name , col_names )
user = user_template . format ( query )
m = message ( system = system , user = user , column_names = col_attr [ "column_name" ], column_attr = col_attr [ "column_type" ])
return m該功能創建提示模板並返回提示system和user組件以及列的名稱和屬性。例如，讓我們提出逮捕問題的數量：
query = "How many cases ended up with arrest?"
msg = create_message ( table_name = "chicago_crime" , query = query )這將返回：
print ( msg . system )
Given the following SQL table , your job is to write queries given a user ’ s request .
CREATE TABLE chicago_crime ( ID BIGINT , Case Number VARCHAR , Date VARCHAR , Block VARCHAR , IUCR VARCHAR , Primary Type VARCHAR , Description VARCHAR , Location Description VARCHAR , Arrest BOOLEAN , Domestic BOOLEAN , Beat BIGINT , District BIGINT , Ward DOUBLE , Community Area BIGINT , FBI Code VARCHAR , X Coordinate DOUBLE , Y Coordinate DOUBLE , Year BIGINT , Updated On VARCHAR , Latitude DOUBLE , Longitude DOUBLE , Location VARCHAR )
print ( msg . user )
Write a SQL query that returns - How many cases ended up with arrest ?
print ( msg . column_names )
0 ID
1 Case Number
2 Date
3 Block
4 IUCR
5 Primary Type
6 Description
7 Location Description
8 Arrest
9 Domestic
10 Beat
11 District
12 Ward
13 Community Area
14 FBI Code
15 X Coordinate
16 Y Coordinate
17 Year
18 Updated On
19 Latitude
20 Longitude
21 Location
Name : column_name , dtype : object
print ( msg . column_attr )
0 BIGINT
1 VARCHAR
2 VARCHAR
3 VARCHAR
4 VARCHAR
5 VARCHAR
6 VARCHAR
7 VARCHAR
8 BOOLEAN
9 BOOLEAN
10 BIGINT
11 BIGINT
12 DOUBLE
13 BIGINT
14 VARCHAR
15 DOUBLE
16 DOUBLE
17 BIGINT
18 VARCHAR
19 DOUBLE
20 DOUBLE
21 VARCHAR
Name : column_type , dtype : object create_message函數的輸出旨在符合OpenAI API ChatCompletion.create函數參數，我們將在下一節中進行審查。
本節重點介紹OpenAI Python庫功能。 OpenAI庫使無縫訪問OpenAI REST API。我們將使用庫連接到API並在我們的提示下發送獲取請求。
讓我們首先通過將我們的API饋送到openai.api_key函數來開始與API：
openai . api_key = os . getenv ( 'OPENAI_KEY' )注意：我們使用從os庫中使用getenv函數來加載OpenAI_KEY環境變量。另外，您可以直接餵食API密鑰：
openai . api_key = "YOUR_OPENAI_API_KEY"OpenAI API可訪問具有不同功能的各種LLM。您可以使用openai.model.list函數獲取可用模型的列表：
openai . Model . list ()要將其轉換為不錯的格式，您可以將其包裝在pandas數據框架中：
openai_api_models = pd . DataFrame ( openai . Model . list ()[ "data" ])
openai_api_models . head並應該期望以下輸出：
<bound method NDFrame.head of id object created owned_by
0 text-search-babbage-doc-001 model 1651172509 openai-dev
1 gpt-4 model 1687882411 openai
2 curie-search-query model 1651172509 openai-dev
3 text-davinci-003 model 1669599635 openai-internal
4 text-search-babbage-query-001 model 1651172509 openai-dev
.. ... ... ... ...
65 gpt-3.5-turbo-instruct-0914 model 1694122472 system
66 dall-e-2 model 1698798177 system
67 tts-1-1106 model 1699053241 system
68 tts-1-hd-1106 model 1699053533 system
69 gpt-3.5-turbo-16k model 1683758102 openai-internal
[70 rows x 4 columns]>
對於我們的用例，文本生成，我們將使用gpt-3.5-turbo模型，這是GPT3模型的改進。 gpt-3.5-turbo模型代表了一系列不斷更新的模型，默認情況下，如果未指定模型版本，API將指出最新的穩定版本。在創建本教程時，默認3.5型號為gpt-3.5-turbo-0613 ，使用4,096個令牌，並在2021年9月進行了數據培訓。
要在我們的提示下發送GET請求，我們將使用ChatCompletion.create函數。該功能有許多參數，我們將使用以下參數：
model - 要使用的模型ID，此處可用的完整列表messages - 到目前為止包括對話的消息列表（例如提示）temperature - 通過設置採樣溫度水平來管理過程輸出的隨機性或決定論。溫度水平接受0到2之間的值。當參數值更高時，輸出將變為隨機。相反，當參數值更接近0時，輸出將變得更加確定性（可重複）max_tokens完成中要生成的最大令牌數API Documentaiton上可用的函數參數的完整列表。
在下面的示例中，我們將使用與Chatgpt Web界面上使用的提示（IE，圖5），這次使用API。我們將使用create_message函數生成提示：
query = "How many cases ended up with arrest?"
prompt = create_message ( table_name = "chicago_crime" , query = query )讓我們將上述提示轉換為ChatCompletion.create的結構。創建函數messages參數：
message = [
{
"role" : "system" ,
"content" : prompt . system
},
{
"role" : "user" ,
"content" : prompt . user
}
]接下來，我們將使用ChatCompletion.create函數將提示（即， message對象）發送到API：
response = openai . ChatCompletion . create (
model = "gpt-3.5-turbo" ,
messages = message ,
temperature = 0 ,
max_tokens = 256 )我們將將temperature參數設置為0，以確保高可重複性並將文本完成中的令牌數限制為256。該功能返回具有文本完成，元數據和其他信息的JSON對象：
print ( response )
< OpenAIObject chat . completion id = chatcmpl - 8 PzomlbLrTOTx1uOZm4WQnGr4JwU7 at 0xffff4b0dcb80 > JSON : {
"id" : "chatcmpl-8PzomlbLrTOTx1uOZm4WQnGr4JwU7" ,
"object" : "chat.completion" ,
"created" : 1701206520 ,
"model" : "gpt-3.5-turbo-0613" ,
"choices" : [
{
"index" : 0 ,
"message" : {
"role" : "assistant" ,
"content" : "SELECT COUNT(*) FROM chicago_crime WHERE Arrest = true;"
},
"finish_reason" : "stop"
}
],
"usage" : {
"prompt_tokens" : 137 ,
"completion_tokens" : 12 ,
"total_tokens" : 149
}
}使用響應印度，我們可以提取SQL查詢：
sql = response [ "choices" ][ 0 ][ "message" ][ "content" ]
print ( sql ) ' SELECT COUNT(*) FROM chicago_crime WHERE Arrest = true; '使用duckdb.sql函數運行SQL代碼：
duckdb . sql ( sql ). show ()
┌──────────────┐
│ count_star () │
│ int64 │
├──────────────┤
│ 77635 │
└──────────────┘在下一部分中，我們將概括和功能化所有步驟。
在上一節中，我們介紹了提示格式，設置create_message函數，並審查了ChatCompletion.create函數的功能。在本節中，我們將它們拼接在一起。
關於從ChatCompletion.create返回的SQL代碼的一件事。當查詢中的變量名與兩個或多個單詞結合在一起時，這可能是一個問題。例如，在不使用引號的情況下，使用諸如Case Number或諸如案例號或Primary Type之chicago_crime的變量將導致錯誤。
如果返回的查詢沒有一個：
def add_quotes ( query , col_names ):
for i in col_names :
if i in query :
l = query . find ( i )
if query [ l - 1 ] != "'" and query [ l - 1 ] != '"' :
query = str ( query ). replace ( i , '"' + i + '"' )
return ( query )函數輸入是查詢和相應表的列名。它循環循環列名稱，並在查詢中找到匹配項，並添加引號。例如，我們可以使用從ChatCompletion.create函數輸出的SQL查詢運行它：
add_quotes ( query = sql , col_names = prompt . column_names )
'SELECT COUNT(*) FROM chicago_crime WHERE "Arrest" = true;'您可以注意到它在Arrest變量中添加了引號。
現在，我們可以介紹迄今為止我們介紹的三個函數的lang2sql函數 - create_message ， ChatCompletion.create和add_quotes將用戶問題轉換為SQL代碼：
def lang2sql ( api_key , table_name , query , model = "gpt-3.5-turbo" , temperature = 0 , max_tokens = 256 , frequency_penalty = 0 , presence_penalty = 0 ):
class response :
def __init__ ( output , message , response , sql ):
output . message = message
output . response = response
output . sql = sql
openai . api_key = api_key
m = create_message ( table_name = table_name , query = query )
message = [
{
"role" : "system" ,
"content" : m . system
},
{
"role" : "user" ,
"content" : m . user
}
]
openai_response = openai . ChatCompletion . create (
model = model ,
messages = message ,
temperature = temperature ,
max_tokens = max_tokens ,
frequency_penalty = frequency_penalty ,
presence_penalty = presence_penalty )
sql_query = add_quotes ( query = openai_response [ "choices" ][ 0 ][ "message" ][ "content" ], col_names = m . column_names )
output = response ( message = m , response = openai_response , sql = sql_query )
return output該函數作為輸入接收OpenAI API鍵，表名和ChatCompletion.create函數的核心參數，並使用提示，API響應和解析的查詢返回對象。例如，讓我們嘗試重新運行與上一節中使用lang2sql函數的相同查詢：
query = "How many cases ended up with arrest?"
response = lang2sql ( api_key = api_key , table_name = "chicago_crime" , query = query )我們可以從輸出對象提取SQL查詢：
print ( response . sql ) SELECT COUNT ( * ) FROM chicago_crime WHERE " Arrest " = true;我們可以根據上一節中收到的結果測試輸出：
duckdb . sql ( response . sql ). show ()
┌──────────────┐
│ count_star () │
│ int64 │
├──────────────┤
│ 77635 │
└──────────────┘現在，讓我們在問題中添加額外的複雜性，並詢問最終在2022年被捕的案件：
query = "How many cases ended up with arrest during 2022"
response = lang2sql ( api_key = api_key , table_name = "chicago_crime" , query = query )如您所見，該模型將相關字段正確識別為Year ，並生成了正確的查詢：
print ( response . sql )SQL代碼：
SELECT COUNT ( * ) FROM chicago_crime WHERE " Arrest " = TRUE AND " Year " = 2022 ;測試表中的查詢：
duckdb . sql ( response . sql ). show ()
┌──────────────┐
│ count_star () │
│ int64 │
├──────────────┤
│ 27805 │
└──────────────┘這是一個簡單問題的示例，需要特定變量進行分組：
query = "Summarize the cases by primary type"
response = lang2sql ( api_key = api_key , table_name = "chicago_crime" , query = query )
print ( response . sql )您可以從響應輸出中看到SQL代碼在這種情況下是正確的：
SELECT " Primary Type " , COUNT ( * ) as TotalCases
FROM chicago_crime
GROUP BY " Primary Type "這是查詢的輸出：
duckdb . sql ( response . sql ). show ()
┌───────────────────────────────────┬────────────┐
│ Primary Type │ TotalCases │
│ varchar │ int64 │
├───────────────────────────────────┼────────────┤
│ MOTOR VEHICLE THEFT │ 54934 │
│ ROBBERY │ 25082 │
│ WEAPONS VIOLATION │ 24672 │
│ INTERFERENCE WITH PUBLIC OFFICER │ 1161 │
│ OBSCENITY │ 127 │
│ STALKING │ 1206 │
│ BATTERY │ 115760 │
│ OFFENSE INVOLVING CHILDREN │ 5177 │
│ CRIMINAL TRESPASS │ 11255 │
│ PUBLIC PEACE VIOLATION │ 1980 │
│ · │ · │
│ · │ · │
│ · │ · │
│ ASSAULT │ 58685 │
│ CRIMINAL DAMAGE │ 75611 │
│ DECEPTIVE PRACTICE │ 46377 │
│ NARCOTICS │ 13931 │
│ BURGLARY │ 19898 │
...
├───────────────────────────────────┴────────────┤
│ 31 rows ( 20 shown ) 2 columns │
└────────────────────────────────────────────────┘最後但並非最不重要的一點是，LLM可以識別上下文（例如，哪個變量），即使我們提供了部分變量名稱：
query = "How many cases is the type of robbery?"
response = lang2sql ( api_key = api_key , table_name = "chicago_crime" , query = query )
print ( response . sql )它返回以下SQL代碼：
SELECT COUNT ( * ) FROM chicago_crime WHERE " Primary Type " = ' ROBBERY ' ;這是查詢的輸出：
duckdb . sql ( response . sql ). show ()
┌──────────────┐
│ count_star () │
│ int64 │
├──────────────┤
│ 25082 │
└──────────────┘在本教程中，我們演示瞭如何使用幾行Python代碼構建SQL代碼生成器並使用OpenAI API。我們已經看到，提示的質量對於由此產生的SQL代碼的成功至關重要。除提示提供的上下文外，字段名稱還應提供有關該字段特徵的信息，以幫助LLM確定該字段與用戶問題的相關性。
儘管本教程僅限於使用單個表（例如，在表之間沒有加入），但某些LLM（例如OpenAI上可用的LLM）可以處理更複雜的案例，包括使用多個表和確定正確的聯接操作。調整Lang2SQL功能來處理多個表可能是一個不錯的下一步。
本教程是在創意共享歸因非商業期4.0國際許可下獲得許可的。
