AIGC开发 -- AI与数据库的互动示例

前言

前面介绍了AI如何调用本地方法的功能，Function Calling，本文结合实际案例演示如何在业务中使用

场景说明

我想统计公司研发人员工时消耗情况，但是不想自己写各种SQL去统计，因此想借助AI理解我的话，并将结果直接返回给我

步骤分析

#mermaid-svg-lIojzCa7my48NcQp {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-lIojzCa7my48NcQp .error-icon{fill:#552222;}#mermaid-svg-lIojzCa7my48NcQp .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-lIojzCa7my48NcQp .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-lIojzCa7my48NcQp .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-lIojzCa7my48NcQp .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-lIojzCa7my48NcQp .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-lIojzCa7my48NcQp .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-lIojzCa7my48NcQp .marker{fill:#333333;stroke:#333333;}#mermaid-svg-lIojzCa7my48NcQp .marker.cross{stroke:#333333;}#mermaid-svg-lIojzCa7my48NcQp svg{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-lIojzCa7my48NcQp .label{font-family:"trebuchet ms",verdana,arial,sans-serif;color:#333;}#mermaid-svg-lIojzCa7my48NcQp .cluster-label text{fill:#333;}#mermaid-svg-lIojzCa7my48NcQp .cluster-label span{color:#333;}#mermaid-svg-lIojzCa7my48NcQp .label text,#mermaid-svg-lIojzCa7my48NcQp span{fill:#333;color:#333;}#mermaid-svg-lIojzCa7my48NcQp .node rect,#mermaid-svg-lIojzCa7my48NcQp .node circle,#mermaid-svg-lIojzCa7my48NcQp .node ellipse,#mermaid-svg-lIojzCa7my48NcQp .node polygon,#mermaid-svg-lIojzCa7my48NcQp .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-lIojzCa7my48NcQp .node .label{text-align:center;}#mermaid-svg-lIojzCa7my48NcQp .node.clickable{cursor:pointer;}#mermaid-svg-lIojzCa7my48NcQp .arrowheadPath{fill:#333333;}#mermaid-svg-lIojzCa7my48NcQp .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-lIojzCa7my48NcQp .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-lIojzCa7my48NcQp .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-lIojzCa7my48NcQp .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-lIojzCa7my48NcQp .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-lIojzCa7my48NcQp .cluster text{fill:#333;}#mermaid-svg-lIojzCa7my48NcQp .cluster span{color:#333;}#mermaid-svg-lIojzCa7my48NcQp div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-lIojzCa7my48NcQp :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;}
调用

是

否

       用户指令LLM 
     

       OPEN API 
     

       调用工具 
     

       生成SQL 
     

       返回LLM结果 
     

       执行SQL 
     

       查询结果 
     

       结束

定义模型以及提示语

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数据库表回答用户问题"},]defget_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并执行

deflocal_gpt_query(question):
    gpt_response = get_sql_completion(messages)if gpt_response.content isNone:
        gpt_response.content =""
    messages.append(gpt_response)print("====Function Calling====")
    print_json(gpt_response)

    result =None
    call_id =Noneif gpt_response.tool_calls isnotNone:
        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 isnotNone:
                result = ReporterDao().ask_database(sql=query)print("====DB Records====")print(result)return result

结果拼接为自然语言

defdb_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

    defask_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年项目任务上的工时消耗统计。

标签： AIGC 人工智能数据库

本文转载自: https://blog.csdn.net/weixin_43871079/article/details/135948191
版权归原作者 _三石_ 所有，如有侵权，请联系我们删除。

AIGC开发 -- AI与数据库的互动示例

前言

场景说明

步骤分析

定义模型以及提示语

准备表结构说明

获取查询SQL并执行

结果拼接为自然语言

MYSQL查询方法

提出问题

执行结果

发表评论

“AIGC开发 -- AI与数据库的互动示例”的评论:

关于作者

overfit同步小助手

相关阅读

文章导航