21.pyMySQL操作数据库

事务

事务可以包含一系列的sql语句，事务的执行具有原子性

包含多条sql语句要么都执行成功，要么都执行不成功

create table tt1(id int,name varchar(12));
insert into tt1 values(1,'dahai');

开启事务
只是插入到内存中,并没有写入硬盘

start transaction;
insert into tt1 values(1,'dahai');

提交事务

commit;

回滚

rollback;

pyMysql链接数据库

报错1
pymysql.err.OperationalError: (2003, “Can’t connect to MySQL server on ‘xxx.xxx.xxx.xxx’ ([WinError 10061] 由于目标计算机积极拒绝，无法连接。)”)

注意：一定要使虚拟机远程文件夹

pyMysql创建表

导入pymysql包

import pymysql

拿到套接字对象

client = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password = 'qwe123',
    database = 'mysql5',
    charset = 'utf8'
)

拿到游标 mysql>

cursor = client.cursor()
# print(cursor)
sql = '''
create table t1(
    id int not null,
    name varchar(10)
    );
'''
# execute 执行sql语句
try:
    cursor.execute(sql)
    print('创建成功')
except Exception as e:
    print('创建数据库失败%s'%e)
# 关闭游标连接# 相当与exit，关闭mysql
cursor.close()
# 关闭数据库连接# 回收资源
client.close()

pymysql之executemany

# 拿到套接字对象
client = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password = 'qwe123',
    database = 'mysql5',
    charset = 'utf8'
)
# 拿到游标 mysql>
cursor = client.cursor()
# 插入以下列表的数据

userinfo = [
    (3,'aaaaaaa'),
    (4,'bbbbbbb'),
    (5,'ccccccc')
]

# 使用executemany的第二个参数，放入占位符的内容,占位符不需要引号，它可以自动识别
sql = 'insert into t1 values (%s,%s);'
# 数据来源userinfo
res =cursor.executemany(sql,userinfo)
print(res)

sql的注入问题

注入问题导致登录时不用账号和密码可以登录
sql注入
用一些特殊符号来改变sql语句的运行逻辑，不需要账号和密码直接可以登录
– 注释
第一种 不输入密码可以登录
select id from user where name = “dahai” – dsadsadasdasff" and password = “%s”;
用户名输入 dahai" – dsadsadasdasff
密码不输入回车
登录成功
第二种
账号和密码都不需要
select id from user where name = “xxx” or 1=1 – dsadsafdgdgdsgds" and password = “%s”;
用户输入 xxx" or 1=1 – dsadsafdgdgdsgds
相当于
select id from user where name = “xxx” or 1=1
密码不输回车
登录成功

有些网站的账号密码不允许输入特殊符合，为了防止sql注入
前端（浏览器页面）可以筛选，
但是如果用爬虫是不是可以跳过前端，
所以后端也要检测

inp_user = input('输入账号名').strip()
inp_pwd = input('输入密码').strip()
sql = '''
select id from user where name = "%s" and password = "%s";
'''%(inp_user,inp_pwd)
res = cursor.execute(sql)

if res:
    print('登录成功')
else:
    print('用户或密码错误')
cursor.close()
client.close()

解决sql的注入

import pymysql
# 拿到套接字对象
client = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password = 'qwe123',
    database = 'mysql5',
    charset = 'utf8'
)
# 拿到游标 mysql>
cursor = client.cursor()
inp_user = input('输入账号名').strip()
inp_pwd = input('输入密码').strip()

sql = '''
select id from user where name = %s and password = %s;
'''

# 使用execute的第二个参数，放入占位符的内容,占位符不需要引号，它可以自动识别
res = cursor.execute(sql,(inp_user,inp_pwd))

if res:
    print('登录成功')
else:
    print('用户或密码错误')
cursor.close()
client.close()

pymysql的查询

import pymysql
# 拿到套接字对象
client = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password = 'qwe123',
    database = 'mysql5',
    charset = 'utf8'
)

cursor() 只会把每条记录放入小元组

#cursor = client.cursor()
#  显示成一个列表里面key是字段名 value是数据
cursor = client.cursor(pymysql.cursors.DictCursor)
sql = 'select * from user;'
rows = cursor.execute(sql)
# print(rows)
# fetchall一次性拿到所有
# print(cursor.fetchall())
# cursor()
# # 第二次没了返回空()
# cursor(pymysql.cursors.DictCursor)
# # 第二次没了返回空[]
# print(cursor.fetchall())
# 一次拿一条
# print(cursor.fetchone())
# print(cursor.fetchone())
# print(cursor.fetchone())
# # 没有呢返回None
# print(cursor.fetchone())
# 拿指定的条数
# print(cursor.fetchmany(2))
# print(cursor.fetchone())
# # 没有返回[]
# print(cursor.fetchmany(2))
# 读一行指针移动一行
# 可以移动指针
# # 绝对位置移动 scroll(行数,mode='absolute') absolute绝对位置
# cursor.scroll(0,mode='absolute')
# cursor.scroll(1,mode='absolute')
# print(cursor.fetchall())

print(cursor.fetchone())
# # 相对位置移动
cursor.scroll(1,mode='relative')
#
print(cursor.fetchone())

视图

视图是什么? *****
本质是一张虚拟的表
数据来自select语句，在内存里面，是一个临时数据
那么有没有办法把它保存
视图可以
但是它可以永久保存表的结构，数据来源与原始的表，
保存的是查询语句
每次查询都是那种select语句去原始表里面查

如何使用
创建视图
语法
create view 视图表的名字 as select语句
实例
create view test_view5 as select * from t1 where name = ‘xialuo’;
有什么用?
原表安全
案例: 在一公司中需要一张表保存所有人的薪资信息
这个表不是所有人都能全看到 老板 财务 可以
某一个员工 只能看到自己的信息
所以不能把整个表的信息开发给这个员工
工资保密协议
功能1,隐藏部分数据 开放指定的数据
insert into test_view5 values(7,‘xiaohai’);
视图有插入
但是查询
select * from test_view5;
还是 as 后面 select * from t1 where name = ‘xialuo’;
怎么证明
insert into test_view5 values(3,‘xialuo’);
同步到原表
select * from t1;
功能2,因为视图可以将查询结果保存特性 我可以用视图 来达到减少书写sql的次数
技术部门有那些员工
select * from emp1 join dep on emp1.dep_id = dep.id where dep.name = ‘技术’;
将查询结果作为一个视图 以后在使用到这个需求 就直接查看视图
注意:字段名不能重复
create view jishu6 as select emp1.*,dep.name as dep_name from emp1 join dep on emp1.dep_id = dep.id where dep.name = ‘技术’;

修改视图
语法
alter view 视图名称 as sql语句
实例
alter view test_view5 as select * from t1 where name = ‘xiaohai’;
删除视图
语法
drop view 视图名字
实例
drop view test_view5;
特点
1.每次对视图进行的查询 其实都是再次执行了 as 后面的查询语句
2.可以对视图进行修改 修改会同步到原表
3.视图是永久存储的 存储的不是数据 而就是一条 as sql语句
4.不要改视图，视图很多，只是用来查