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, SQLDatabaseChainimport osdb_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)

来源地址：https://blog.csdn.net/qq_43692950/article/details/131378699