前言
前面介绍了AI如何调用本地方法的功能，Function Calling，本文结合实际案例演示如何在业务中使用
场景说明
我想统计公司研发人员工时消耗情况，但是不想自己写各种SQL去统计，因此想借助AI理解我的话，并将结果直接返回给我
步骤分析
定义模型以及提示语
client = OpenAI(
# defaults to os.environ.get("OPENAI_API_KEY")
api_key=os.getenv("OPENAI_API_KEY"),
base_url=os.getenv("OPENAI_API_BASE")
)
messages = [
{"role": "system", "content": "基于mysql数据库表回答用户问题"},
]
def get_sql_completion(messages, model="gpt-4-1106-preview"):
response = client.chat.completions.create(
model=model,
messages=messages,
temperature=0,
tools=[{ # 摘自 OpenAI 官方示例 https://github.com/openai/openai-cookbook/blob/main/examples/How_to_call_functions_with_chat_models.ipynb
"type": "function",
"function": {
"name": "ask_database",
"description": "Use this function to answer user questions about business. \
Output should be a fully formed SQL query.",
"parameters": {
"type": "object",
"properties": {
"query": {
"type": "string",
"description": f"""
SQL query extracting info to answer the user's question.
SQL should be written using this database schema:
{禅道表结构.database_schema_string}
The query should be returned in plain text, not in JSON.
The query should only contain grammars supported by MYSQL.
""",
}
},
"required": ["query"],
}
}
}],
)
return response.choices[0].message
准备表结构说明
database_schema_string = """
--任务工时记录表
CREATE TABLE `zt_effort` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`objectType` varchar(30) NOT NULL COMMENT '对象类型',
`objectID` mediumint(8) unsigned NOT NULL COMMENT '对象ID',
`product` text NOT NULL COMMENT '产品',
`project` mediumint(8) unsigned NOT NULL COMMENT '项目',
`execution` mediumint(8) unsigned NOT NULL COMMENT '执行',
`account` varchar(30) NOT NULL COMMENT '账户',
`work` text COMMENT '工作内容',
`vision` varchar(10) NOT NULL DEFAULT 'rnd' COMMENT '视野',
`date` date NOT NULL COMMENT '日期',
`left` float NOT NULL COMMENT '剩余',
`consumed` float NOT NULL COMMENT '消耗',
`begin` smallint(4) unsigned zerofill NOT NULL COMMENT '开始',
`end` smallint(4) unsigned zerofill NOT NULL COMMENT '结束',
`extra` text NOT NULL COMMENT '额外信息',
`order` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
`deleted` enum('0','1') NOT NULL DEFAULT '0' COMMENT '删除状态'
);
--禅道任务表
CREATE TABLE `zt_task` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`project` mediumint(8) unsigned NOT NULL COMMENT '项目ID',
`parent` mediumint(8) NOT NULL DEFAULT '0' COMMENT '父任务ID',
`execution` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '执行ID',
`module` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '模块ID',
`design` mediumint(8) unsigned NOT NULL COMMENT '设计ID',
`story` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '需求ID',
`storyVersion` smallint(6) NOT NULL DEFAULT '1' COMMENT '需求版本',
`designVersion` smallint(6) unsigned NOT NULL COMMENT '设计版本',
`fromBug` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '来源Bug ID',
`fromIssue` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '来源问题ID',
`feedback` mediumint(8) unsigned NOT NULL COMMENT '反馈ID',
`name` varchar(255) NOT NULL COMMENT '任务名称',
`type` varchar(20) NOT NULL COMMENT '任务类型',
`mode` varchar(10) NOT NULL COMMENT '模式',
`pri` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '优先级',
`estimate` float unsigned NOT NULL COMMENT '预计',
`consumed` float unsigned NOT NULL COMMENT '已消耗',
`left` float unsigned NOT NULL COMMENT '剩余',
`deadline` date NOT NULL COMMENT '截止日期',
`status` enum('wait','doing','done','pause','cancel','closed') NOT NULL DEFAULT 'wait' COMMENT '状态',
`subStatus` varchar(30) NOT NULL DEFAULT '' COMMENT '子状态',
`color` char(7) NOT NULL COMMENT '颜色',
`mailto` text COMMENT '邮件通知列表',
`desc` mediumtext NOT NULL COMMENT '描述',
`version` smallint(6) NOT NULL COMMENT '版本',
`openedBy` varchar(30) NOT NULL COMMENT '创建者',
`openedDate` datetime NOT NULL COMMENT '创建日期',
`assignedTo` varchar(30) NOT NULL COMMENT '指派给',
`assignedDate` datetime NOT NULL COMMENT '指派日期',
`estStarted` date NOT NULL COMMENT '预计开始',
`realStarted` datetime NOT NULL COMMENT '实际开始',
`finishedBy` varchar(30) NOT NULL COMMENT '完成者',
`finishedDate` datetime NOT NULL COMMENT '完成日期',
`finishedList` text NOT NULL COMMENT '完成列表',
`canceledBy` varchar(30) NOT NULL COMMENT '取消者',
`canceledDate` datetime NOT NULL COMMENT '取消日期',
`closedBy` varchar(30) NOT NULL COMMENT '关闭者',
`closedDate` datetime NOT NULL COMMENT '关闭日期',
`planDuration` int(11) NOT NULL COMMENT '计划时长',
`realDuration` int(11) NOT NULL COMMENT '实际时长',
`closedReason` varchar(30) NOT NULL COMMENT '关闭原因',
`lastEditedBy` varchar(30) NOT NULL COMMENT '最后编辑者',
`lastEditedDate` datetime NOT NULL COMMENT '最后编辑日期',
`activatedDate` datetime NOT NULL COMMENT '激活日期',
`order` mediumint(8) NOT NULL DEFAULT '0' COMMENT '排序',
`repo` mediumint(8) unsigned NOT NULL COMMENT '代码仓库',
`mr` mediumint(8) unsigned NOT NULL COMMENT '合并请求',
`entry` varchar(255) NOT NULL COMMENT '条目',
`lines` varchar(10) NOT NULL COMMENT '行数',
`v1` varchar(40) NOT NULL COMMENT '版本1',
`v2` varchar(40) NOT NULL COMMENT '版本2',
`deleted` enum('0','1') NOT NULL DEFAULT '0' COMMENT '删除状态',
`vision` varchar(10) NOT NULL DEFAULT 'rnd' COMMENT '视野'
);
--禅道项目表
CREATE TABLE `zt_project` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`project` mediumint(8) NOT NULL DEFAULT '0' COMMENT '项目ID',
`model` char(30) NOT NULL COMMENT '模型',
`type` char(30) NOT NULL DEFAULT 'sprint' COMMENT '类型',
`lifetime` char(30) NOT NULL DEFAULT '' COMMENT '生命周期',
`budget` varchar(30) NOT NULL DEFAULT '0' COMMENT '预算',
`budgetUnit` char(30) NOT NULL DEFAULT 'CNY' COMMENT '预算单位',
`attribute` varchar(30) NOT NULL DEFAULT '' COMMENT '属性',
`percent` float unsigned NOT NULL DEFAULT '0' COMMENT '百分比',
`milestone` enum('0','1') NOT NULL DEFAULT '0' COMMENT '里程碑',
`output` text NOT NULL COMMENT '输出',
`auth` char(30) NOT NULL COMMENT '权限',
`parent` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '父项目ID',
`path` varchar(255) NOT NULL COMMENT '路径',
`grade` tinyint(3) unsigned NOT NULL COMMENT '等级',
`name` varchar(90) NOT NULL COMMENT '名称',
`code` varchar(45) NOT NULL COMMENT '代码',
`hasProduct` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '是否有产品',
`begin` date NOT NULL COMMENT '开始日期',
`end` date NOT NULL COMMENT '结束日期',
`realBegan` date NOT NULL COMMENT '实际开始日期',
`realEnd` date NOT NULL COMMENT '实际结束日期',
`days` smallint(5) unsigned NOT NULL COMMENT '天数',
`status` varchar(10) NOT NULL COMMENT '状态',
`subStatus` varchar(30) NOT NULL DEFAULT '' COMMENT '子状态',
`pri` enum('1','2','3','4') NOT NULL DEFAULT '1' COMMENT '优先级',
`desc` mediumtext NOT NULL COMMENT '描述',
`version` smallint(6) NOT NULL COMMENT '版本',
`parentVersion` smallint(6) NOT NULL COMMENT '父版本',
`planDuration` int(11) NOT NULL COMMENT '计划时长',
`realDuration` int(11) NOT NULL COMMENT '实际时长',
`openedBy` varchar(30) NOT NULL DEFAULT '' COMMENT '开启者',
`openedDate` datetime NOT NULL COMMENT '开启日期',
`openedVersion` varchar(20) NOT NULL COMMENT '开启版本',
`lastEditedBy` varchar(30) NOT NULL DEFAULT '' COMMENT '最后编辑者',
`lastEditedDate` datetime NOT NULL COMMENT '最后编辑日期',
`closedBy` varchar(30) NOT NULL DEFAULT '' COMMENT '关闭者',
`closedDate` datetime NOT NULL COMMENT '关闭日期',
`canceledBy` varchar(30) NOT NULL DEFAULT '' COMMENT '取消者',
`canceledDate` datetime NOT NULL COMMENT '取消日期',
`suspendedDate` date NOT NULL COMMENT '暂停日期',
`PO` varchar(30) NOT NULL DEFAULT '' COMMENT '产品负责人',
`PM` varchar(30) NOT NULL DEFAULT '' COMMENT '项目经理',
`QD` varchar(30) NOT NULL DEFAULT '' COMMENT '质量保证',
`RD` varchar(30) NOT NULL DEFAULT '' COMMENT '研发',
`team` varchar(90) NOT NULL COMMENT '团队',
`acl` char(30) NOT NULL DEFAULT 'open' COMMENT '访问控制列表',
`whitelist` text NOT NULL COMMENT '白名单',
`order` mediumint(8) unsigned NOT NULL COMMENT '排序',
`vision` varchar(10) NOT NULL DEFAULT 'rnd' COMMENT '视野',
`division` enum('0','1') NOT NULL DEFAULT '1' COMMENT '划分',
`displayCards` smallint(6) NOT NULL DEFAULT '0' COMMENT '显示卡片',
`fluidBoard` enum('0','1') NOT NULL DEFAULT '0' COMMENT '流动看板',
`multiple` enum('0','1') NOT NULL DEFAULT '1' COMMENT '多重',
`colWidth` smallint(4) NOT NULL DEFAULT '264' COMMENT '列宽',
`minColWidth` smallint(4) NOT NULL DEFAULT '200' COMMENT '最小列宽',
`maxColWidth` smallint(4) NOT NULL DEFAULT '384' COMMENT '最大列宽',
`deleted` enum('0','1') NOT NULL DEFAULT '0' COMMENT '删除状态'
)
-- 禅道用户表
CREATE TABLE `zt_user` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`company` mediumint(8) unsigned NOT NULL COMMENT '公司ID',
`type` char(30) NOT NULL DEFAULT 'inside' COMMENT '用户类型',
`dept` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '部门ID',
`account` char(30) NOT NULL DEFAULT '' COMMENT '账号',
`password` char(32) NOT NULL DEFAULT '' COMMENT '密码',
`role` char(10) NOT NULL DEFAULT '' COMMENT '角色',
`realname` varchar(100) NOT NULL DEFAULT '' COMMENT '真实姓名',
`pinyin` varchar(255) NOT NULL DEFAULT '' COMMENT '拼音',
`nickname` char(60) NOT NULL DEFAULT '' COMMENT '昵称',
`commiter` varchar(100) NOT NULL COMMENT '提交者',
`avatar` text NOT NULL COMMENT '头像',
`birthday` date NOT NULL DEFAULT '0000-00-00' COMMENT '生日',
`gender` enum('f','m') NOT NULL DEFAULT 'f' COMMENT '性别',
`email` char(90) NOT NULL DEFAULT '' COMMENT '电子邮件',
`skype` char(90) NOT NULL DEFAULT '' COMMENT 'Skype',
`qq` char(20) NOT NULL DEFAULT '' COMMENT 'QQ',
`mobile` char(11) NOT NULL DEFAULT '' COMMENT '手机',
`phone` char(20) NOT NULL DEFAULT '' COMMENT '电话',
`weixin` varchar(90) NOT NULL DEFAULT '' COMMENT '微信',
`dingding` varchar(90) NOT NULL DEFAULT '' COMMENT '钉钉',
`slack` varchar(90) NOT NULL DEFAULT '' COMMENT 'Slack',
`whatsapp` varchar(90) NOT NULL DEFAULT '' COMMENT 'WhatsApp',
`address` char(120) NOT NULL DEFAULT '' COMMENT '地址',
`zipcode` char(10) NOT NULL DEFAULT '' COMMENT '邮政编码',
`nature` text NOT NULL COMMENT '性格',
`analysis` text NOT NULL COMMENT '分析',
`strategy` text NOT NULL COMMENT '战略',
`join` date NOT NULL DEFAULT '0000-00-00' COMMENT '加入日期',
`visits` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '访问次数',
`visions` varchar(20) NOT NULL DEFAULT 'rnd,lite' COMMENT '视野',
`ip` char(15) NOT NULL DEFAULT '' COMMENT 'IP地址',
`last` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '最后登录时间',
`fails` tinyint(5) NOT NULL DEFAULT '0' COMMENT '失败尝试次数',
`locked` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '锁定时间',
`feedback` enum('0','1') NOT NULL DEFAULT '0' COMMENT '反馈',
`ranzhi` char(30) NOT NULL DEFAULT '' COMMENT '然之',
`ldap` char(30) NOT NULL COMMENT 'LDAP',
`score` int(11) NOT NULL DEFAULT '0' COMMENT '得分',
`scoreLevel` int(11) NOT NULL DEFAULT '0' COMMENT '得分等级',
`resetToken` varchar(50) NOT NULL COMMENT '重置令牌',
`deleted` enum('0','1') NOT NULL DEFAULT '0' COMMENT '删除状态',
`clientStatus` enum('online','away','busy','offline','meeting') NOT NULL DEFAULT 'offline' COMMENT '客户端状态',
`clientLang` varchar(10) NOT NULL DEFAULT 'zh-cn' COMMENT '客户端语言'
);
"""
将表结构告诉AI，让它基于这个结构生成查询SQL
获取查询SQL并执行
def local_gpt_query(question):
gpt_response = get_sql_completion(messages)
if gpt_response.content is None:
gpt_response.content = ""
messages.append(gpt_response)
print("====Function Calling====")
print_json(gpt_response)
result = None
call_id = None
if gpt_response.tool_calls is not None:
tool_call = gpt_response.tool_calls[0]
if tool_call.function.name == "ask_database":
arguments = tool_call.function.arguments
call_id = tool_call.id
args = json.loads(arguments)
print("====SQL====")
print(args["query"])
query = args["query"]
if query is not None:
result = ReporterDao().ask_database(sql=query)
print("====DB Records====")
print(result)
return result
结果拼接为自然语言
def db_to_llm(result):
# 将查询结果返回给gpt
messages.append({
"tool_call_id": call_id,
"role": "tool",
"name": "ask_database",
"content": str(result)
})
# 组织为自然语言
gpt_response = get_sql_completion(messages)
print("====最终回复====")
resultStr = gpt_response.content
print(resultStr)
return resultStr
MYSQL查询方法
def _query(self, query, params):
cursor = self.conn.cursor()
cursor.execute(query, params)
entries = cursor.fetchall()
print('执行SQL:%s' % cursor._executed)
return entries
def ask_database(self, sql):
return self._query(sql,())
提出问题
local_gpt_query("统计2024年人员在项目任务上消耗的工时情况")
执行结果
====Function Calling====
{
"content": "",
"role": "assistant",
"function_call": null,
"tool_calls": [
{
"id": "call_pQYaH7TGPHoJ2qXSxvRh3C0J",
"function": {
"arguments": "{\"query\":\"SELECT zt_user.realname, SUM(zt_effort.consumed) AS total_hours\\nFROM zt_effort\\nJOIN zt_user ON zt_effort.account = zt_user.account\\nJOIN zt_task ON zt_effort.objectID = zt_task.id\\nWHERE zt_effort.objectType = 'task' AND YEAR(zt_effort.date) = 2024\\nGROUP BY zt_effort.account\\nORDER BY total_hours DESC;\"}",
"name": "ask_database"
},
"type": "function"
}
]
}
====SQL====
SELECT zt_user.realname, SUM(zt_effort.consumed) AS total_hours
FROM zt_effort
JOIN zt_user ON zt_effort.account = zt_user.account
JOIN zt_task ON zt_effort.objectID = zt_task.id
WHERE zt_effort.objectType = 'task' AND YEAR(zt_effort.date) = 2024
GROUP BY zt_effort.account
ORDER BY total_hours DESC;
执行SQL:SELECT zt_user.realname, SUM(zt_effort.consumed) AS total_hours
FROM zt_effort
JOIN zt_user ON zt_effort.account = zt_user.account
JOIN zt_task ON zt_effort.objectID = zt_task.id
WHERE zt_effort.objectType = 'task' AND YEAR(zt_effort.date) = 2024
GROUP BY zt_effort.account
ORDER BY total_hours DESC;
====DB Records====
(('AA', 697.0), ('BB', 164.0), ('CC', 134.0), ('DD', 132.0), ('EE', 131.0), ('FF', 129.0), ('GG', 127.0), ('HH', 123.0), ('II', 116.0))
====最终回复====
在2024年，人员在项目任务上消耗的工时情况如下：
1. AA - 697.0小时
2. BB - 164.0小时
3. CC - 134.0小时
4. DD - 132.0小时
5. EE - 131.0小时
6. FF - 129.0小时
7. GG - 127.0小时
8. HH - 123.0小时
9. II - 116.0小时
以上是各个人员在2024年项目任务上的工时消耗统计。
510
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
