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

前言

前面介绍了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年项目任务上的工时消耗统计。