mysql数据库（六）pymysql、视图、触发器、存储过程、函数、流程控制、数据库连接池

🕗 发布于 2024-11-21 08:47 数据库 mysql

pymysql、视图、触发器、存储过程、函数、流程控制、数据库连接池

文章目录

pymysql、视图、触发器、存储过程、函数、流程控制、数据库连接池
一、pymysql
二、视图
三、触发器
四、存储过程
五、函数
六、流程控制
七、数据库连接池

一、pymysql

可以使用pip install pymysql安装pymysql模块。

import pymysql

#连接数据库，host为ip，port为端口，database为库名，user为用户名，password为密码
conn =pymysql.connect(host="127.0.0.1",port=3306,database='t',user="root",password="123")
#创建游标对象，cursor=pymysql.cursors.DictCursor会返回字段:数据的字典，默认则是以元组的方式显示
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

#执行sql语句，返回结果为查询到记录的条数
res=cursor.execute("select * from t;")
#返回sql语句的查询结果
r=cursor.fetchall()
cursor.close()
conn.close()

上方代码中直接传入sql语句的方式可能存在一定的安全隐患，例如：

#绕过密码查询
#sql中--后面的会被注释掉
import pymysql
conn =pymysql.connect(host="127.0.0.1",port=3306,database='t',user="root",password="123")
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql="select * from t1 where user=%s and password=%s;"%('"bb123"--','')
res=cursor.execute(sql)
r=cursor.fetchall()
print(r)
cursor.close()
conn.close()

<<<[{'id': 2, 'user': 'bb123', 'password': '123'}]

上述代码中传入的用户名后方添加了–也就相当于将查询语句中and password=%s的代码全部注释了，从而会出现绕过密码查询到用户的情况。

#不输入用户名登录
import pymysql
conn =pymysql.connect(host="127.0.0.1",port=3306,database='t',user="root",password="123")
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql="select * from t1 where user=%s and password=%s;"%('"" or 1=1--','')
res=cursor.execute(sql)
r=cursor.fetchall()
print(r)
cursor.close()
conn.close()
<<<[{'id': 1, 'user': 'cc123', 'password': '123'}, {'id': 2, 'user': 'bb123', 'password': '123'}]

我们知道mysql查询语句是通过逻辑判断的方式进行查询的，而1=1的结果永远为真，这就会导致没有输入任何用户和密码的情况下依然可以查询到用户信息。
为了避免上述的结果，我们可以让pymysql拼接字符串：

import pymysql
conn =pymysql.connect(host="127.0.0.1",port=3306,database='t',user="root",password="123")
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
#%s处千万不能加""
sql="select * from t1 where user=%s and password=%s;"
res=cursor.execute(sql,args=('"" or 1=1--',''))
r=cursor.fetchall()
print(r)
cursor.close()
conn.close()
<<<()

pymysql的增改删操作如下：

import pymysql

conn =pymysql.connect(host="127.0.0.1",port=3306,database='t',user="root",password="123")
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

sql='insert into t1 values(%s,%s,%s);'
#executemany可以一次操作多条记录
res=cursor.executemany(sql,[(3,'ee123','123'),(4,'ff123','123'),(5,'gg123','123')])
#res返回待插入的行数
print(res)

#执行下面的语句后pymysql才会真正完成对数据库的增改删操作
conn.commit()
cursor.close()
conn.close()

import pymysql

conn = conn =pymysql.connect(host="127.0.0.1",port=3306,database='t',user="root",password="123")
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

sql='select * from t1'
res=cursor.execute(sql)
#fetchone表示一次取出一条记录
print(cursor.fetchone())
#fetchmany表示一次取出多条记录
print(cursor.fetchmany(2))
#scroll可以控制取记录的指针移动
#正右移负左移，relative表示当前位置，absolute表示起始位置
cursor.scroll(-2,'relative')
print(cursor.fetchmany(2))
cursor.scroll(2,'absolute')
print(cursor.fetchall())
cursor.close()
conn.close()

<<<{'id': 1, 'user': 'cc123', 'password': '123'}
<<<[{'id': 2, 'user': 'bb123', 'password': '123'}, {'id': 3, 'user': 'ee123', 'password': '123'}]
<<<[{'id': 2, 'user': 'bb123', 'password': '123'}, {'id': 3, 'user': 'ee123', 'password': '123'}]
<<<[{'id': 3, 'user': 'ee123', 'password': '123'}, {'id': 4, 'user': 'ff123', 'password': '123'}, {'id': 5, 'user': 'gg123', 'password': '123'}]

上方的代码中第一次fetchone取出了查询到数据的第一条，指针向右移动一位，fetchmany(2)取出了第二第三条记录，指针再次向右移动两位，scroll(-2,‘relative’)以当前位置为基准让指针向左移动两位移回第二条记录的位置，因此后面的fetchmany(2)再次取出第二第三条记录，scroll(2,‘absolute’)以起始位置为基准，控制指针右移两位至第三条记录位置，最后的fetchall()则将第三条至最后一条记录之间的全部记录取出。

二、视图

视图是一个虚拟表，其本质是根据SQL语句获取动态的数据集，用户使用时只需使用名称即可获取结果集，可以将该结果集当做表来使用。
使用视图我们可以把查询过程中的临时表摘出来，用视图去实现，这样以后再想操作该临时表的数据时就无需重写复杂的sql了，直接去视图中查找即可，但视图有明显地效率问题，并且视图是存放在数据库中的，如果我们程序中使用的sql过分依赖数据库中的视图，这会使得扩展sql极为不便，因此视图不常使用。

#创建视图
create view teacher_view as select id,course,num from teacher where name='李平';
#从视图查询数据
select name from course where teacher_id = (select id from teacher_view);
#从视图中查询数据的效率非常低，甚至还不如写子查询语句查询的效率。

#修改视图记录
update teacher_view set num='xxx';

#插入记录
insert teacher_view values(5,'aaa','1234567');
#需要注意的是视图中的记录被修改时，原始表中的记录也会被修改，并且当创建视图涉及多个表时，视图的记录可能根本无法修改。因此我们不应该修改视图中存放的记录。

#修改视图存放的内容
alter view teacher_view as select * from course where id>3;

#删除视图
drop view teacher_view;

三、触发器

使用触发器可以定制用户对表增删改操作时前后的行为。

# 插入记录前触发
delimiter //
create trigger tri_before_insert_t1 before insert on t1 for each row
begin
#如果新插入的数据success字段值为no
    if NEW.success = 'no' 
    #向t2插入记录
    then insert t2 values(NEW.id,NEW.success) ; 
    end if ; 
end//
delimiter ;

# 插入后
delimiter //
create trigger tri_after_insert_t1 after insert on t1 for each row
begin
#NEW表示即将插入的记录
#如果新插入的数据success字段值为no
    if NEW.success = 'no' 
    #向t2插入记录
    then insert t2 values(NEW.id,NEW.success) ; 
    end if ; 
end//
delimiter ;

#OLD表示即将删除的记录
# 删除前
delimiter //
create trigger tri_before_delete_t1 before delete on t1 for each row
begin
......
end//
delimiter ;

# 删除后
delimiter //
create trigger tri_after_delete_t1 after delete on t1 for each row
begin
......
end//
delimiter ;

# 更新前
delimiter //
create trigger tri_before_update_t1 before update on t1 for each row
begin
......
end//
delimiter ;

# 更新后
delimiter //
create trigger tri_after_update_t1 after update on t1 for each row
begin
......
end//
delimiter ;

#删除触发器
drop trigger tri_after_insert_cmd;

四、存储过程

存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql。
存储过程的优点是：用于替代程序写的SQL语句，实现程序与sql解耦；基于网络传输，传别名的数据量小，而直接传sql数据量大。
存储过程的缺点是：不便于扩展。

#无参存储过程
delimiter //
create procedure p1()
BEGIN
    select * from t1;
    insert t1(name) values("aaa");
END //
delimiter ;

#在mysql中调用
call p1() 

#在python中调用
cursor.callproc('p1') 
print(cursor.fetchall())

#有参存储过程
delimiter //
#in声明的变量只能作为输入，out声明的变量只能作为输出，inout声明的变量可以作为输入或者输出
create procedure p1(in n1 int,out n2 int,inout n3 int)
BEGIN
    select * from t1 where id>n1 and id<n3;
    set n2=100;
    set n3=200;
END //
delimiter ;

#在mysql中调用
set @res=0;
set @r=5;
call p1(3,@res,@r);
select @res;
select @r;
#可以看到res和r的值变为了100和200，而存储过程查询到了id为4的记录。

#在python中调用
cursor.callproc('p1',(3,0,10))
#查看存储过程的查询结果
print(cursor.fetchall())
cursor.execute('select @_p1_0,@_p1_1,@_p1_2;')
#查看使用到的三个变量值
print(cursor.fetchall())

删除存储过程：drop procedure proc_name;

五、函数

mysql有许多内置函数，这里介绍一下date_format的使用方法。
date_format用于格式化时间。

select date_format('2009-10-04 22:23:00', '%Y-%m-%d %H:%i:%s');
<<<'2009-10-04 22:23:00'

自定义函数：

delimiter //
create function f1(
    i1 int,
    i2 int)
returns int
BEGIN
    declare num int;
    set num = i1 + i2;
    return(num);
END //
delimiter ;

函数中不能写sql语句，函数仅仅是一个功能的集合，如果向我在begin和end中间写sql语句要使用存储过程。

删除函数：drop function func_name;
使用函数：
select f1(1,2) into @res;
select @res;
在查询时使用：select f1(11,id) ,name from t2;

六、流程控制

#条件语句
delimiter //
CREATE PROCEDURE proc_if ()
BEGIN
    declare i int default 0;
    if i = 1 THEN
        SELECT 1;
    ELSEIF i = 2 THEN
        SELECT 2;
    ELSE
        SELECT 7;
    END IF;
END //
delimiter ;

#while循环
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN
    DECLARE num INT ;
    SET num = 0 ;
    WHILE num < 10 DO
        SELECT num ;
        SET num = num + 1 ;
    END WHILE ;
END //
delimiter ;

#repeat循环
delimiter //
CREATE PROCEDURE proc_repeat ()
BEGIN
    DECLARE i INT ;
    SET i = 0 ;
    repeat
        select i;
        set i = i + 1;
        until i >= 5
    end repeat;
END //
delimiter ;

七、数据库连接池

import pymysql

from DBUtils.PooledDB import PooledDB
POOL = PooledDB(
    creator=pymysql,  # 使用链接数据库的模块
    maxconnections=6,  # 连接池允许的最大连接数，0和None表示不限制连接数
    mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
    maxcached=5,  # 链接池中最多闲置的链接，0和None不限制
    maxshared=3,  # 链接池中最多共享的链接数量，0和None表示全部共享。PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，所有值无论设置为多少，_maxcached永远为0，所以永远是所有链接都共享。
    blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
    maxusage=None,  # 一个链接最多被重复使用的次数，None表示无限制
    setsession=[],  # 开始会话前执行的命令列表。
    ping=0,
    # ping MySQL服务端，检查是否服务可用。
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='yk',
    charset='utf8'，
autocommit=True #自动提交
)


def func():
    # 检测当前正在运行连接数的是否小于最大链接数，如果不小于则：等待或报raise TooManyConnections异常
    # 否则
    # 则优先去初始化时创建的链接中获取链接 SteadyDBConnection。
    # 然后将SteadyDBConnection对象封装到PooledDedicatedDBConnection中并返回。
    # 如果最开始创建的链接没有链接，则去创建一个SteadyDBConnection对象，再封装到PooledDedicatedDBConnection中并返回。
    # 一旦关闭链接后，连接就返回到连接池让后续线程继续使用。
    conn = POOL.connection()

    print( '链接被拿走了', conn._con)
    print( '池子里目前有', POOL._idle_cache, '\r\n')

    cursor = conn.cursor()
    cursor.execute('select * from user')
    result = cursor.fetchall()
    print(result)
    conn.close()

if __name__ == '__main__':

    func()

原文地址：https://blog.csdn.net/m0_45491627/article/details/143921872

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：awk详解
下一篇：兼顾高性能与低成本，浅析 Apache Doris 异步物化视图原理及典型场景

论文阅读——Intrusion detection systems using longshort‑term memory (LSTM)
作者提出的 LSTM 模型能够有效区分正常网络流量和攻击流量。除此之外，模型结合主成分分析（PCA）和互信息作为降维方法。实验结果表明，基于 PCA 的模型（特别是使用2个主成分）在二分类和多分类任务
阅读更多2024-11-21
用源码编译虚幻引擎，并打包到安卓平台
本文详细介绍了如何用源码编译虚幻引擎，并将其打包到安卓平台。
阅读更多2024-11-21
Vue项目开发 element-UI 前端实现 1到10排列选择的按钮
在 Element UI 中，你可以通过来实现按钮的排列选择，例如让用户选择 1 到 10 之间的数字。为了实现这一功能，我们可以使用来动态生成 1 到 10 的按钮，并通过按钮点击事件来更新
阅读更多2024-11-21
Java EE 【知识改变命运】01计算机的一些知识点
计算机一些基础知识
阅读更多2024-11-21
04 —— Webpack打包CSS代码
加载器style-loader：把解析后的css代码插入到DOM。加载器css-loader ：解析css代码。直接引用，不用变量接收。
阅读更多2024-11-21
万能程序补丁工具 C# 源代码详解
万能程序补丁工具程序目的：搜索二进制可执行 EXE 或 DLL 文件分析的特征代码，替换特征代码，达到调试修正目标程序的功能。
阅读更多2024-11-21
【MySQL数据库】C#实现MySQL数据库最简单的查询和执行函数
C#和MySQL数据库是常见的数据交互，标准的查询和执行方法如下，做个记录。
阅读更多2024-11-21
单条推理转批量推理prompt
在每个线程中设置环境变量 CUDA_VISIBLE_DEVICES，以确保每个线程只使用指定的GPU。使用 concurrent.futures.ThreadPoolExecutor 来管理多线程任务
阅读更多2024-11-21
【AIGC】ChatGPT提示词Prompt解析：情感分析，分手后还可以做朋友吗？
【AIGC】在情感博弈中，最重要的是保持清醒的认知和优雅的态度。识别控制话术不是为了对抗，而是为了更好地保护自己的情感自由，实现真正的成长。
阅读更多2024-11-21
pycharm中配置pyqt5
PyQt和wxPython则提供了更多的控件和更强大的功能，适合于需要复杂用户界面的应用程序。pyQt生成的应用程序，引用图片通常是将资源文件装换为 python 文件，然后引用资源文件，而不能直接加
阅读更多2024-11-21