pymysql与mysql各功能
pymysql
# 增删改操作
import pymysql
client=pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='egon123',
database='db2',
charset='utf8'
)
cursor=client.cursor()
# insert的三种方法：
# 方法一
sql='insert into t1 values(1,"egon"),(2,"lxx");' # 原生sql语句
try: # 下面省略异常捕捉
res=cursor.execute(sql)
print(res) # 返回的是执行表操作条数
client.commit() # 递交后才会改变数据
except Exception:
client.rollback() # 如果字段有误就能恢复修改前数据
#方法二
userinfo=[
(3,"alex"),
(4,"lxx"),
(5,"yxx")
]
for user in userinfo:
sql = ‘insert into t1 values(%s,”%s”);’ %(user[0],user[1])
cursor.execute(sql)
#方法三
sql = ‘insert into t1 values(%s,%s);’
cursor.executemany(sql,userinfo)
# 删除方法
cursor.execute(‘delete from t1 where id=3;’)
client.commit()
cursor.close()
client.close()
#查询操作
Import pymysql
client=pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='egon123',
database='db6',
charset='utf8'
)
cursor =client.cursor()
inp_user=input('输入账号名: ').strip()
inp_pwd=input('输入密码: ').strip()
sql='select id from user where name = "%s" and pwd = password("%s");' %(inp_user,inp_pwd)
rows = cursor.execute(sql)
# 这个方法可以防止注入问题
# sql = 'select id from t1 where name =%s and password = %s; ' # 注意：账号密码不用引号了
# rows = cursor.execute(sql,(inp_user,inp_pwd)) # 这句防注入问题
if rows:
print(‘登录成功’)
else:
print(‘用户名或密码错误’)
cursor.close()
client.close()
# 不做任何处理会产生注入问题，比如输入账号时输入alex "-- sadafas
或 sadgasd " or 1=1 – sfaf类似的可以直接登录
视图
强调:1.字段名不能重复
2.视图是为了简化查询的sql语句，不应该修改视图中的记录
create view emp2dep as select emp.*,dep.name as dep_name from emp inner join dep on
emp.dep_id = dep.id;
触发器
准备表
CREATE TABLE cmd (
id INT PRIMARY KEY auto_increment,
USER CHAR (32),
priv CHAR (10),
cmd CHAR (64),
sub_time datetime, #提交时间
success enum ('yes', 'no') #0代表执行失败
);
CREATE TABLE errlog (
id INT PRIMARY KEY auto_increment,
err_id int
);
delimiter $$ # 改变结束条件
CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW # 创建触发器
BEGIN
if NEW.success = 'no' then # if
insert into errlog(err_id) values(NEW.id);
end if;
END $$
delimiter ; # 必须改回结束条件
INSERT INTO cmd (
USER,
priv,
cmd,
sub_time,
success
)
VALUES
('egon','0755','ls -l /etc',NOW(),'yes'),
('egon','0755','cat /etc/passwd',NOW(),'no'),
('egon','0755','useradd xxx',NOW(),'no'),
('egon','0755','ps aux',NOW(),'yes');
#查询错误日志，发现有两条
#触发器可用于插入删除更新的前后
删除触发器：drop trigger tri_after_insert_cmd;
transaction:事务，交易
# 1、原子性：事务可以包含一系列的sql语句，事务的执行具有原子性
#2、回滚：包含多条sql语句要么都执行成功，要么都执行不成功
create table user(
id int primary key auto_increment,
name char(32),
balance int
);
insert into user(name,balance)
values
('wsb',1000),
('egon',1000),
('ysb',1000);
#出现异常，回滚到初始状态
start transaction;
update user set balance=900 where name='wsb'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
uppdate user set balance=1090 where name='ysb'; #卖家拿到90元,出现异常没有拿到
rollback;
commit;
存储过程
方案一：
应用程序：
mysql：编写存储过程
方案二：
应用程序：原生sql
mysql：
方案三：
应用程序：ORM(类/对象 --->原生sql)
mysql：
执行效率：
方案一 > 方案二 -> 方案三
开发效率：
方案一 > 方案三 -> 方案二
由于方案一所需的人员成本较高，我们一般使用方案三
# 无参
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
)
delimiter $$
create procedure p2()
BEGIN
declare n int default 1; #设置整型n的初始值为1
while (n < 100) do
insert into s1 values(n,concat(‘egon’,n),’male’,concat(‘egon’,n,’@163.com’));
set n = n+1 # 注意不能用 n+=1
end while;
END $$
delimiter;
# 有参
delimiter $$
create procedure p3(
in n int, # 传入的值
out res int # 传出的值
)
BEGIN
select * from blog where id >n;
set res = 0;
END $$
delimiter;
# 直接在mysql中调用：
mysql> set @x=111;
mysql> call p3(3,@x);
mysql> select @x;
可以查到@x的值变为了0
# 在python中调用：
cursor.callproc('p4',(3,111)) #set @_p4_0 = 3; set @_p4_1 = 111
print(cursor.fetchall())
cursor.execute('select @_p4_1;')
print(cursor.fetchone())
