mysql 创建简单的临时表 tmp
create database test;
use test;
DROP TABLE IF EXISTS `tmp`;
CREATE TABLE `tmp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;
INSERT INTO `tmp` VALUES (1, 'klvchen');
INSERT INTO `tmp` VALUES (2, 'lily');
INSERT INTO `tmp` VALUES (3, 'lucy');
INSERT INTO `tmp` VALUES (4, 'james');
INSERT INTO `tmp` VALUES (5, 'jim');mysql 创建存储过程
delimiter $$ # 自定义 mysql 的分隔符
CREATE PROCEDURE p1(
in i1 int, # 仅用于传入参数用
in i2 int,
inout i3 int, # 既可以传入又可以当作返回值
out r1 int # 仅用于返回值用，外部传进来的值无用
)
BEGIN
DECLARE temp1 int;
DECLARE temp2 int default 0;
set temp1 = 1;
set r1 = i1 + i2 + temp1 + temp2;
set i3 = i3 + 100;
SELECT * FROM tmp;
end $$
delimiter ;
python 调用 mysql 存储过程
import pymysql
PY_MYSQL_CONN_DICT = {
"host" : '192.168.0.214',
"port" : 3306,
"user" : 'root',
"passwd" : '123456',
"db" : 'tmpdb'
}
conn = pymysql.connect(**PY_MYSQL_CONN_DICT)
cusor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 调用 p1 存储过程，传入4个参数
cusor.callproc('p1', args=(1, 2, 3, 4))
# 返回获得的集合，即存储函数中的 SELECT * FROM tmp; 结果
res1 = cusor.fetchall()
print(res1)
# 以 python 固定格式获取返回的值：@_存储过程名_0, 第一个返回值
cusor.execute("select @_p1_0, @_p1_1, @_p1_2, @_p1_3")
res2 = cusor.fetchall()
print(res2)
conn.commit()
cusor.close()
conn.close()内容来源于网络如有侵权请私信删除
- 还没有人评论，欢迎说说您的想法！
客服
