LangChain 使用文本描述的方式操作MySQL中的数据

一、LangChain 使用文本描述的方式操作MySQL中的数据

在 LangChain 中提供了 SQLDatabaseChain ，可以通过语义文本去操作 MySQL中的数据，例如在 MySQL 中有如下表数据：

用户表

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表';

写入测试数据：

INSERT INTO `langchain`.`user`(`id`, `name`, `age`) VALUES (1, '张三', 20);
INSERT INTO `langchain`.`user`(`id`, `name`, `age`) VALUES (2, '李四', 60);
INSERT INTO `langchain`.`user`(`id`, `name`, `age`) VALUES (3, '王五', 30);
INSERT INTO `langchain`.`user`(`id`, `name`, `age`) VALUES (4, '赵六', 31);
INSERT INTO `langchain`.`user`(`id`, `name`, `age`) VALUES (5, '小明', 35);
INSERT INTO `langchain`.`user`(`id`, `name`, `age`) VALUES (6, '小红', 25);
INSERT INTO `langchain`.`user`(`id`, `name`, `age`) VALUES (7, '小蓝', 40);

角色表

CREATE TABLE `role` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `role` varchar(255) DEFAULT NULL COMMENT '角色名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='角色表';

写入测试数据：

INSERT INTO `langchain`.`role`(`id`, `role`) VALUES (1, 'admin');
INSERT INTO `langchain`.`role`(`id`, `role`) VALUES (2, 'common');
INSERT INTO `langchain`.`role`(`id`, `role`) VALUES (3, 'role1');
INSERT INTO `langchain`.`role`(`id`, `role`) VALUES (4, 'role2');
INSERT INTO `langchain`.`role`(`id`, `role`) VALUES (5, 'role3');

工作组表：

CREATE TABLE `work_group` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `group` varchar(255) DEFAULT NULL COMMENT '工作组',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='工作组表';

写入测试数据：

INSERT INTO `langchain`.`work_group`(`id`, `group`) VALUES (1, 'A');
INSERT INTO `langchain`.`work_group`(`id`, `group`) VALUES (2, 'B');
INSERT INTO `langchain`.`work_group`(`id`, `group`) VALUES (3, 'C');
INSERT INTO `langchain`.`work_group`(`id`, `group`) VALUES (4, 'E');

用户角色关系表

CREATE TABLE `user_role_mapping` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` int DEFAULT NULL COMMENT '用户ID',
  `role_id` int DEFAULT NULL COMMENT '角色ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户角色关系表';

写入测试数据：

INSERT INTO `langchain`.`user_role_mapping`(`id`, `user_id`, `role_id`) VALUES (1, 1, 1);
INSERT INTO `langchain`.`user_role_mapping`(`id`, `user_id`, `role_id`) VALUES (2, 1, 2);
INSERT INTO `langchain`.`user_role_mapping`(`id`, `user_id`, `role_id`) VALUES (3, 1, 3);
INSERT INTO `langchain`.`user_role_mapping`(`id`, `user_id`, `role_id`) VALUES (4, 2, 4);
INSERT INTO `langchain`.`user_role_mapping`(`id`, `user_id`, `role_id`) VALUES (5, 2, 5);
INSERT INTO `langchain`.`user_role_mapping`(`id`, `user_id`, `role_id`) VALUES (6, 3, 2);
INSERT INTO `langchain`.`user_role_mapping`(`id`, `user_id`, `role_id`) VALUES (7, 4, 2);
INSERT INTO `langchain`.`user_role_mapping`(`id`, `user_id`, `role_id`) VALUES (8, 5, 2);
INSERT INTO `langchain`.`user_role_mapping`(`id`, `user_id`, `role_id`) VALUES (9, 6, 2);
INSERT INTO `langchain`.`user_role_mapping`(`id`, `user_id`, `role_id`) VALUES (10, 7, 5);

用户工作组关系表

CREATE TABLE `user_work_group_mapping` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` int DEFAULT NULL COMMENT '用户ID',
  `group_id` int DEFAULT NULL COMMENT '工作组ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户工作组关系表';

写入测试数据：

INSERT INTO `langchain`.`user_work_group_mapping`(`id`, `user_id`, `group_id`) VALUES (1, 1, 1);
INSERT INTO `langchain`.`user_work_group_mapping`(`id`, `user_id`, `group_id`) VALUES (2, 1, 2);
INSERT INTO `langchain`.`user_work_group_mapping`(`id`, `user_id`, `group_id`) VALUES (3, 1, 3);
INSERT INTO `langchain`.`user_work_group_mapping`(`id`, `user_id`, `group_id`) VALUES (4, 2, 1);
INSERT INTO `langchain`.`user_work_group_mapping`(`id`, `user_id`, `group_id`) VALUES (5, 2, 2);
INSERT INTO `langchain`.`user_work_group_mapping`(`id`, `user_id`, `group_id`) VALUES (6, 2, 3);
INSERT INTO `langchain`.`user_work_group_mapping`(`id`, `user_id`, `group_id`) VALUES (7, 3, 2);
INSERT INTO `langchain`.`user_work_group_mapping`(`id`, `user_id`, `group_id`) VALUES (8, 3, 3);
INSERT INTO `langchain`.`user_work_group_mapping`(`id`, `user_id`, `group_id`) VALUES (9, 4, 1);
INSERT INTO `langchain`.`user_work_group_mapping`(`id`, `user_id`, `group_id`) VALUES (10, 4, 2);
INSERT INTO `langchain`.`user_work_group_mapping`(`id`, `user_id`, `group_id`) VALUES (11, 5, 2);
INSERT INTO `langchain`.`user_work_group_mapping`(`id`, `user_id`, `group_id`) VALUES (12, 5, 4);
INSERT INTO `langchain`.`user_work_group_mapping`(`id`, `user_id`, `group_id`) VALUES (13, 6, 3);
INSERT INTO `langchain`.`user_work_group_mapping`(`id`, `user_id`, `group_id`) VALUES (14, 7, 2);

构建 SQLDatabaseChain

from langchain import OpenAI, SQLDatabase, SQLDatabaseChain
import os

db_user = "root"
db_password = "root"
db_host = "127.0.0.1"
db_name = "langchain"
db = SQLDatabase.from_uri(f"mysql+pymysql://{db_user}:{db_password}@{db_host}/{db_name}")

openai_api_key = os.environ["OPENAI_API_KEY"]
llm = OpenAI(temperature=0, openai_api_key=openai_api_key)
db_chain = SQLDatabaseChain(llm=llm, database=db, verbose=True)

使用文本描述操作数据：

questions = "有多少个用户"
res = db_chain.run(questions)
print("问题：", questions, "解答：", res)

questions = "统计30岁以下的人员信息"
res = db_chain.run(questions)
print("问题：", questions, "解答：", res)

questions = "统计每个工作组下的人员"
res = db_chain.run(questions)
print("问题：", questions, "解答：", res)

questions = "统计每个工作组下的人员数量"
res = db_chain.run(questions)
print("问题：", questions, "解答：", res)

questions = "统计每个校色下的人员数量"
res = db_chain.run(questions)
print("问题：", questions, "解答：", res)

questions = "统计30岁以下人员的工作组和角色"
res = db_chain.run(questions)
print("问题：", questions, "解答：", res)