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_hoursFROM zt_effortJOIN zt_user ON zt_effort.account = zt_user.accountJOIN zt_task ON zt_effort.objectID = zt_task.idWHERE zt_effort.objectType = 'task' AND YEAR(zt_effort.date) = 2024GROUP BY zt_effort.accountORDER BY total_hours DESC;执行SQL:SELECT zt_user.realname, SUM(zt_effort.consumed) AS total_hoursFROM zt_effortJOIN zt_user ON zt_effort.account = zt_user.accountJOIN zt_task ON zt_effort.objectID = zt_task.idWHERE zt_effort.objectType = 'task' AND YEAR(zt_effort.date) = 2024GROUP BY zt_effort.accountORDER 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年项目任务上的工时消耗统计。