欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Python全栈之学习MySQL(3)

程序员文章站 2022-03-08 18:13:15
目录1. pymysql的基本操作2. sql注入攻击3. sql增删改查4. mysql的数据恢复5. sql语句优化总结1. pymysql的基本操作# ### python 操作mysql im...

1. pymysql的基本操作

# ### python 操作mysql 
import pymysql
# ### 1.基本语法
"""
# (1) 创建连接对象 host user password database 这四个参数必写
conn = pymysql.connect( host="127.0.0.1" , user="root" , password="123456" , database="db003" , charset="utf8" , port=3306 )
# (2) 创建游标对象 (用来操作数据库的增删改查)
cursor = conn.cursor()
print(cursor)
# (3) 执行sql语句
sql = "select * from employee"
# 执行查询语句返回的总条数
res = cursor.execute(sql)
print(res) 
# (4) 获取数据 fetchone 获取一条数据
# 返回的是元组,里面包含的是第一条的完整数据
res = cursor.fetchone()
print(res)
res = cursor.fetchone()
print(res)
res = cursor.fetchone()
print(res)
# (5) 释放游标对象
cursor.close()
# (6) 释放连接对象
conn.close()
"""
# ### 2.创建/删除 表操作
# conn = pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db003")
# cursor = conn.cursor()
# 1.创建一张表
sql = """
create table t1(
id int unsigned primary key auto_increment,
first_name varchar(255) not null,
last_name varchar(255) not null,
sex tinyint not null,
age tinyint unsigned not null,
money float
);
"""
# res = cursor.execute(sql)
# print(res) # 无意义返回值
# 2.查询表结构
"""
sql = "desc t1"
res = cursor.execute(sql)
print(res) # 返回的是字段的个数
res = cursor.fetchone()
print(res)
res = cursor.fetchone()
print(res)
res = cursor.fetchone()
print(res)
"""
# 3.删除表
"""
try:
	sql = "drop table t1"
	res = cursor.execute(sql)
	print(res) # 无意义返回值
except:
	pass
"""
# ### 3.事务处理
"""pymysql 默认开启事务的,所有增删改的数据必须提交,否则默认回滚;rollback"""
conn = pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db003")
cursor = conn.cursor()
sql1 = "begin"
sql2 = "update employee set emp_name='程咬钻石' where id = 18 "
sql3 = "commit"
res1 = cursor.execute(sql1)
res1 = cursor.execute(sql2)
res1 = cursor.execute(sql3)
# 一般在查询的时候,通过fetchone来获取结果
res1 = cursor.fetchone()
print(res1)

cursor.close()
conn.close()

2. sql注入攻击

# ### sql 注入攻击
import pymysql
# (1) sql注入的现象
''' 现象:绕开账号密码登录成功 '''
'''
user = input("请输入您的用户名>>>")
pwd  = input("请输入您的密码>>>")
conn = pymysql.connect(host="127.0.0.1" , user="root" , password="123456",database="db005")
cursor = conn.cursor()
sql1 = """
create table usr_pwd(
id int unsigned primary key auto_increment,
username varchar(255) not null,
password varchar(255) not null
)
"""
sql2 = "select * from usr_pwd where username='%s' and password='%s' " % (user,pwd)
print(sql2)
res = cursor.execute(sql2)
print(res) # 1查到成功 0没查到失败
# res=cursor.fetchone()
"""
select * from usr_pwd where username='2222' or 4=4 -- aaa' and password='' 
相当于 : select * from usr_pwd where 10=10; 绕开了账户和密码的判断 -- 代表的是注释;
"""
if res:
	print("登录成功")
else:
	print("登录失败")
cursor.close()
conn.close()
'''
# (2) 预处理机制
""" 在执行sql语句之前,提前对sql语句中出现的字符进行过滤优化,避免sql注入攻击 """
""" execute( sql , (参数1,参数2,参数3 .... ) ) execute2个参数默认开启预处理机制 """
""" 填写 234234' or 100=100 -- sdfsdfsdfsdf  尝试攻击  """

user = input("请输入您的用户名>>>")
pwd  = input("请输入您的密码>>>")
conn = pymysql.connect(host="127.0.0.1" , user="root" , password="123456",database="db005")
cursor = conn.cursor()
sql = "select * from usr_pwd where username=%s and password=%s"
res = cursor.execute(sql , (user,pwd)  )
print(res)

print(    "登录成功"  if res else "登录失败"    )
cursor.close()
conn.close()

3. sql增删改查

# ### python 操作mysql 数据库 (增删改查)
import pymysql
"""
	python 操作mysql增删改时,默认是开启事务的,
	必须最后commit提交数据,才能产生变化
	提交数据: commit 
	默认回滚: rollback
"""
conn = pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db005")
# 默认获取查询结果时是元组,可以设置返回字典;  cursor=pymysql.cursors.dictcursor
cursor = conn.cursor(cursor=pymysql.cursors.dictcursor)
# 执行对mysql 的操作
# 1.增
"""
sql = "insert into t1(first_name,last_name,sex,age,money) values(%s,%s,%s,%s,%s)"
# (1) 一次插入一条
res = cursor.execute( sql , ("孙","健",0,15,20000)  )
print(res) # 1
# 获取最后插入这条数据的id号
print(cursor.lastrowid)
# (2) 一次插入多条
res = cursor.executemany(  sql , [  ("安","晓东",0,18,30000) , ("刘","玉波",1,20,50000) ,("张","光旭",0,80,60000) , ("李","是元",0,10,10) , ("高","大奥",1,20,80000)   ]   )
print(res) # 返回插入的条数
# 插入5条数据中的第一条数据的id
print(cursor.lastrowid)
# 获取最后一个数据的id
sql = "select id from t1 order by id desc limit 1"
res = cursor.execute(sql)
print(res)
# 获取结果,返回元组
res = cursor.fetchone()
print(res["id"])
# 默认元组 : (57, '高', '大奥', 1, 20, 80000.0)
# 返回字典 : {'id': 51, 'first_name': '高', 'last_name': '大奥', 'sex': 1, 'age': 20, 'money': 80000.0}
"""
# 2.删
"""
sql = "delete from t1 where id in (%s,%s,%s)"
res = cursor.execute(sql , (3,4,5) )
print(res) # 返回的是3,代表删除了3条
if res:
	print("删除成功")
else:
	print("删除失败")
"""
# 3.改
"""
sql = "update t1 set first_name = '王' where id = %s"
sql = "update t1 set first_name = '王' where id in (%s,%s,%s,%s)"
res = cursor.execute(sql , (6,7,8,9))
print(res) # 返回的是4,代表修改了4条
if res:
	print("修改成功")
else:
	print("修改失败")
"""
# 4.查
"""
fetchone  获取一条
fetchmany 获取多条
fetchall  获取所有
"""	
sql = "select * from t1"
res = cursor.execute(sql)
print(res) # 针对于查询语句来说,返回的res是总条数;
# (1) fetchone 获取一条
res = cursor.fetchone()
print(res)
res = cursor.fetchone()
print(res)
# (2) fetchmany 获取多条
res = cursor.fetchmany() # 默认获取的是一条数据,返回列表,里面里面是一组一组的字典;
data = cursor.fetchmany(3)
print(data)
"""
[
	{'id': 9, 'first_name': '王', 'last_name': '是元', 'sex': 0, 'age': 10, 'money': 10.0}, 
	{'id': 10, 'first_name': '孙', 'last_name': '健', 'sex': 0, 'age': 15, 'money': 20000.0}, 
	{'id': 11, 'first_name': '安', 'last_name': '晓东', 'sex': 0, 'age': 18, 'money': 30000.0}
]
"""
for row in data:
	first_name = row["first_name"]
	last_name = row["last_name"]
	sex = row["sex"]
	if sex == 0:
		sex = "男性"
	else:
		sex = "女性"
	age = row["age"]
	money = row["money"]
	strvar = "姓:{},名:{},性别:{},年龄:{},收入:{}".format(first_name,last_name,sex,age,money)
print(strvar)
# (3) fetchall 获取所有
# data = cursor.fetchall()
# print(data)
# (4) 自定义搜索查询的位置
print("<==================>")
# 1.相对滚动 relative
"""相对于上一次查询的位置往前移动(负数),或者往后移动(正数)"""
"""
cursor.scroll(-1,mode="relative")
# cursor.scroll(5,mode="relative")
res = cursor.fetchone()
print(res)
"""
# 2.绝对滚动 absolute
"""永远从数据的开头起始位置进行移动,不能向前滚"""
cursor.scroll(0,mode="absolute")
res = cursor.fetchone()
print(res)
conn.commit()
cursor.close()
conn.close()

4. mysql的数据恢复

# ### (1) 导入导出 不要加分号
导出数据库
1.退出mysql
2.选择要导出的默认路径
3.mysqldump -uroot -p db001 > db001.sql
mysqldump -uroot -p123456 db001 表1 表2 > ceshi01.sql
导入数据库
1.登录到mysql之后
2.创建新的数据库
# 注意:source db001.sql 后面没有分号";"
3.source 路径+文件
# ### (2) 如果服务器宕机了,有没有备份数据,如何恢复数据
myisam:
	直接新建一个数据库,然后把之前库中的三个文件,粘贴复制到新的数据库中,直接就恢复了
innodb:
# innodb 在只有frm和ibd文件的情况下,如何恢复数据;
安装 mysql utilities
https://downloads.mysql.com/archives/utilities/ 
cmd中找到frm那个文件,执行如下命令:
切换到对应目录,执行下面语句,不要加分号
mysqlfrm --diagnostic ./文件目录/t1.frm
查出建表语句,复制查询出来的建表语句在mysql中创建的新数据中使用(新建一个数据库,然后执行下面的建表语句)
create table `innodb1` (
  `id` int(11) default null
) engine=innodb;

#对已创建的表进行表空间卸载 删除ibd文件
mysql> alter table innodb1 discard tablespace;
把要恢复的idb文件替换进去
#对已创建的表进行空间装载
mysql> alter table innodb1 import tablespace;

# ### (3) 配置linux下的编码集
!includedir  /etc/mysql/conf.d/       客户端的修改
# 设置mysql客户端默认字符集
default-character-set=utf8
!includedir  /etc/mysql/mysql.conf.d/ 服务端的修改
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 重启数据库
service mysql restart

小总结:

-- 是mysql的注释  # 在sql注入那块用到了
默认python连接mysql的时候,都是开启了一个事务,尤其是增删改,
在改变数据的时候,一定要提交数据,不提交数据(默认回滚),不会真正的改变
\s 看一下服务器的数据

5. sql语句优化

(1) 避免使用select *,
(2) 不确定表大小时候,先用count(*)查下数据.
(3) 创建表时尽量使用 char 代替 varchar
(4) 定长的字段放前面,变长的字段放后面.(尽可能小的改变树状结构高度)
(5) 组合索引代替多个单列索引
   (由于mysql中每次只能使用一个索引,所以经常使用多个条件查询时更适     合使用组合索引)
(6) 尽量使用短索引(小数据值)
(7) 重复少的字段值不适合做索引,例:性别不适合
(8) 使用连接(join)来代替子查询(sub-queries)

总结

本篇文章就到这里了,希望能够给你带来帮助,也希望您能够多多关注的更多内容!