贵州网站设计公司,seo策略分析,网站建设20推广,全球军事新闻最新消息pymysql、视图、触发器、存储过程、函数、流程控制、数据库连接池 文章目录 pymysql、视图、触发器、存储过程、函数、流程控制、数据库连接池一、pymysql二、视图三、触发器四、存储过程五、函数六、流程控制七、数据库连接池 一、pymysql
可以使用pip install pymysql安装py…pymysql、视图、触发器、存储过程、函数、流程控制、数据库连接池 文章目录 pymysql、视图、触发器、存储过程、函数、流程控制、数据库连接池一、pymysql二、视图三、触发器四、存储过程五、函数六、流程控制七、数据库连接池 一、pymysql
可以使用pip install pymysql安装pymysql模块。
import pymysql#连接数据库host为ipport为端口database为库名user为用户名password为密码
conn pymysql.connect(host127.0.0.1,port3306,databaset,userroot,password123)
#创建游标对象cursorpymysql.cursors.DictCursor会返回字段:数据的字典默认则是以元组的方式显示
cursor conn.cursor(cursorpymysql.cursors.DictCursor)#执行sql语句返回结果为查询到记录的条数
rescursor.execute(select * from t;)
#返回sql语句的查询结果
rcursor.fetchall()
cursor.close()
conn.close()上方代码中直接传入sql语句的方式可能存在一定的安全隐患例如
#绕过密码查询
#sql中--后面的会被注释掉
import pymysql
conn pymysql.connect(host127.0.0.1,port3306,databaset,userroot,password123)
cursor conn.cursor(cursorpymysql.cursors.DictCursor)
sqlselect * from t1 where user%s and password%s;%(bb123--,)
rescursor.execute(sql)
rcursor.fetchall()
print(r)
cursor.close()
conn.close()[{id: 2, user: bb123, password: 123}]上述代码中传入的用户名后方添加了–也就相当于将查询语句中and password%s的代码全部注释了从而会出现绕过密码查询到用户的情况。
#不输入用户名登录
import pymysql
conn pymysql.connect(host127.0.0.1,port3306,databaset,userroot,password123)
cursor conn.cursor(cursorpymysql.cursors.DictCursor)
sqlselect * from t1 where user%s and password%s;%( or 11--,)
rescursor.execute(sql)
rcursor.fetchall()
print(r)
cursor.close()
conn.close()
[{id: 1, user: cc123, password: 123}, {id: 2, user: bb123, password: 123}]我们知道mysql查询语句是通过逻辑判断的方式进行查询的而11的结果永远为真这就会导致没有输入任何用户和密码的情况下依然可以查询到用户信息。 为了避免上述的结果我们可以让pymysql拼接字符串
import pymysql
conn pymysql.connect(host127.0.0.1,port3306,databaset,userroot,password123)
cursor conn.cursor(cursorpymysql.cursors.DictCursor)
#%s处千万不能加
sqlselect * from t1 where user%s and password%s;
rescursor.execute(sql,args( or 11--,))
rcursor.fetchall()
print(r)
cursor.close()
conn.close()
()pymysql的增改删操作如下
import pymysqlconn pymysql.connect(host127.0.0.1,port3306,databaset,userroot,password123)
cursor conn.cursor(cursorpymysql.cursors.DictCursor)sqlinsert into t1 values(%s,%s,%s);
#executemany可以一次操作多条记录
rescursor.executemany(sql,[(3,ee123,123),(4,ff123,123),(5,gg123,123)])
#res返回待插入的行数
print(res)#执行下面的语句后pymysql才会真正完成对数据库的增改删操作
conn.commit()
cursor.close()
conn.close()import pymysqlconn conn pymysql.connect(host127.0.0.1,port3306,databaset,userroot,password123)
cursor conn.cursor(cursorpymysql.cursors.DictCursor)sqlselect * from t1
rescursor.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 numxxx;#插入记录
insert teacher_view values(5,aaa,1234567);
#需要注意的是视图中的记录被修改时原始表中的记录也会被修改并且当创建视图涉及多个表时视图的记录可能根本无法修改。因此我们不应该修改视图中存放的记录。#修改视图存放的内容
alter view teacher_view as select * from course where id3;#删除视图
drop view teacher_view;三、触发器
使用触发器可以定制用户对表增删改操作时前后的行为。
# 插入记录前触发
delimiter //
create trigger tri_before_insert_t1 before insert on t1 for each row
begin#如果新插入的数据success字段值为noif 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字段值为noif 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()
BEGINselect * 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)
BEGINselect * from t1 where idn1 and idn3;set n2100;set n3200;
END //
delimiter ;#在mysql中调用
set res0;
set r5;
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
BEGINdeclare 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 ()
BEGINdeclare i int default 0;if i 1 THENSELECT 1;ELSEIF i 2 THENSELECT 2;ELSESELECT 7;END IF;
END //
delimiter ;#while循环
delimiter //
CREATE PROCEDURE proc_while ()
BEGINDECLARE num INT ;SET num 0 ;WHILE num 10 DOSELECT num ;SET num num 1 ;END WHILE ;
END //
delimiter ;#repeat循环
delimiter //
CREATE PROCEDURE proc_repeat ()
BEGINDECLARE i INT ;SET i 0 ;repeatselect i;set i i 1;until i 5end repeat;
END //
delimiter ;
七、数据库连接池
import pymysqlfrom DBUtils.PooledDB import PooledDB
POOL PooledDB(creatorpymysql, # 使用链接数据库的模块maxconnections6, # 连接池允许的最大连接数0和None表示不限制连接数mincached2, # 初始化时链接池中至少创建的空闲的链接0表示不创建maxcached5, # 链接池中最多闲置的链接0和None不限制maxshared3, # 链接池中最多共享的链接数量0和None表示全部共享。PS: 无用因为pymysql和MySQLdb等模块的 threadsafety都为1所有值无论设置为多少_maxcached永远为0所以永远是所有链接都共享。blockingTrue, # 连接池中如果没有可用连接后是否阻塞等待。True等待False不等待然后报错maxusageNone, # 一个链接最多被重复使用的次数None表示无限制setsession[], # 开始会话前执行的命令列表。ping0,# ping MySQL服务端检查是否服务可用。host127.0.0.1,port3306,userroot,password123,databaseyk,charsetutf8autocommitTrue #自动提交
)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()
