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国际许可下获得许可的。
