python 操作mysql★★★★★★

### python 操作mysql(第三方操作)

import pymysql

1.基本语法

1:创建连接(返回对象)  host  user  password(数据库有密码写密码,没有就为空)  database(具体操作哪一个数据库) 这四个参数必须写
host 指定哪个数据库下的ip

conn = pymysql.connect(host="127.0.0.1",user="root",password="",database="db0618",charset="utf8"(指定字符集,可以不写,不需要加-),port=3306(指定端口,可以不写))

2:创建游标对象,该对象可以进行增删改查操作
cursor = conn.cursor()

3:执行sql语句 ==>对表或者数据进行操作,先在命令符中先use 该数据库,然后在pymysql进行操作(指定表或者内容)
sql = "select * from employee"
返回的是数据的总条数
res = cursor.execute(sql)  ==>execute执行  ,返回的是个数或条数
print(res)

4:获取数据
res = cursor.fetchone()==.fetch  获取  one 是一条
print(res)

5:释放游标对象
cursor.close()

6:关闭连接
conn.close()

通过pymysql执行操作

数据是存储到文件中,永久存储,不会丢失


创建/删除 表

conn = pymysql.connect(host="127.0.0.1",user="root",password="",database="db0619")
cursor = conn.cursor()


创建一张表

sql = """
create table t1(
id int unsigned primary key auto_increment,
first_name char(10) not null,
last_name char(10) not null,
age int unsigned,
sex tinyint,
money float
)

res = cursor.execute(sql)
print(res)


查看表结构
python 中的None=sql中的null
sql = "desc t1"
res = cursor.execute(sql)
print(res)  # 返回字段的个数
print(cursor.fetchone())


删除表==>在不指定删了没,用try

try :
sql = "drop table t1"
res = cursor.execute(sql)
print(res)
except:
	pass

cursor.close()
conn.close()

### 事务处理

防止sql注入 : 使用预处理的方式,提前对特殊字符进行过滤
注释的时候外面是三个单引号,里面是三个双引号
sql写语句的时候如果有多个引号,要将多个引号内外换成单双,要将引号岔开
可以执行多个sql语句,如果在启动事务处理的时候,最后要在执行一条提交语句,
获取一次就可以了
python 操作事务处理  必须通过commit提交数据,才会真正的更新数据,否则rollback回滚,恢复到以前状态

conn = pymysql.connect(host="127.0.0.1",user="root",password="",database="db0618")
cursor = conn.cursor()
sql1 = "begin"
sql2 = "select * from employee"
sql3 = "update employee set emp_name = '111egon'  where id = 1 " 
sql4 = "commit"

res1 = cursor.execute(sql1)
res2 = cursor.execute(sql2)
res3 = cursor.execute(sql3)
res4 = cursor.execute(sql4)
print(res1,res2,res3,res4)
print(cursor.fetchone())


cursor.close()
conn.close()

### sql 注入攻击

sql是跨行用三引号
创建一张表

create table usr_pwd(
id int unsigned primary key auto_increment,
username varchar(255) not null,
password varchar(255) not null
)


sql注入问题

import pymysql
user = input("user>>>: ").strip()
pwd = input("password>>>: ").strip()

conn = pymysql.connect(host="127.0.0.1",user="root",password="",database="db0619")
cursor = conn.cursor()

sql = "select * from usr_pwd where username='%s' and password='%s'  " % (user,pwd)  ==>想要登录成功,先登录自己的密码和账户,然后搜索表里面的内容,让客户的密码和账户和搜索表里面的账户和密码的内容相等进行执行,,若返回真(有数字1)代表登录成功,如果搜索返回的个数为0,代表假的,登录失败,%s左右两边要带上引号,因为账户和密码的字段要求是字符串
print(sql)

res = cursor.execute(sql)
print(res) # 查询数据的个数  0代表假,1代表真

if res:
	print("登陆成功")
else:
	print("登录失败")

cursor.close()
conn.close()

输入时: erwe234' or 10 = 10   --   234dfsdf
select * from usr_pwd where username='erwe234' or 10 = 10 -- 234dfsdf' and password='dfsdf'  
where username='erwe234' or 10 = 10  username的判断是假的 但是 后面or拼接的条件是真的,所以可以查询成功
-- 代表后面的代码是注释
把用户名和密码都绕开了,进行sql注入攻击




解决办法

使用预处理机制,可以避免绝大多数的sql注入问题(将参数和sql语句分开,且%s不加引号)
execute 参数1是一个sql语句,如果sql语句和里面的参数值分开执行,默认开启预处理
语法:execute(sql , (参数1,参数2,参数3))  ==>后面参数用元组包起来

import pymysql
user = input("user>>>: ").strip()
pwd = input("password>>>: ").strip()


conn = pymysql.connect(host="127.0.0.1",user="root",password="",database="db0619")
cursor = conn.cursor()
sql = "select * from usr_pwd where username=%s and password = %s"
res = cursor.execute(sql, (user,pwd))

print("登录成功" if res else "登录失败")

cursor.close()
conn.close()

### python 操作mysql 增删改查(对表里内容增删改查,或者叫数据的增删改查)

import pymysql
"""
	python 操作mysql时候,默认开启事务,必须在增删改之后
	提交数据,才会对数据库产生影响,否则默认回滚

	提交数据: conn.commit()
	回滚数据: conn.rollback()

execute     指定单条  ==>执行返回1或者0,1代表真,0代表假,可以作为判断条件
executemany 执行多行


创建mysql连接
conn = pymysql.connect(host="127.0.0.1",user="root",password="",database="db0619")
查询数据,默认是元组,可以设置返回字典类型, pymysql.cursors.DictCursor
cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)

 增  ==>用占位符%s添加,做预处理
"""
sql = "insert into t1(first_name , last_name ,age ,sex,money) 	values(%s,%s,%s,%s,%s)"
执行的时候一次插入一条数据 用元组包起来,注意要加逗号元组
res = cursor.execute(sql,("王","振",80,0,12000))==>做预处理,值要依次的写进去
print(res)

执行的时候一次插入多条数据  用列表将每次插入的数据包起来
res = cursor.executemany(sql,[("尉","翼麟",18,1,12000),("谢","晨",80,0,200),("主","胜",3,0,9.9)])
print(res)


获取最后插入这条数据的id号(针对于单条数据执行,获取最后的id,如果多条数据的执行,以第一条数据的id为主)
print(cursor.lastrowid)  判断是否插入成功,可以判断最后一位id号码,是否存在
针对于多条数据最后的id,可以通过倒序查询,找到id号
select id from t1 order by id desc limit 1

删

sql = "delete from t1 where id = %s"  ==>判断id ,因为ID是作为主键的,依次递增
res = cursor.execute(sql,(8,))  ==>返回的值可以进行判断,如果是1 ,为真,反之
print(res)

if res:
	print("删除成功")
else:
	print("删除失败")


改

sql = "update t1 set first_name = %s where id = %s"
res = cursor.execute(sql,('王',10))
print(res)

if res:
	print("更新成功")
else:	
	print("更新失败")


查  ★★★★★  不需要%s占位,直接按照条件搜索,除非是增删改需要占位符(%s)
sql = "select * from t1"
res = cursor.execute(sql)
print(res) # 总条数

获取一条数据 fetchone==>用在获取内容的时候,返回的是一个字典
 res = cursor.fetchone()
print(res) # {'id': 9, 'first_name': '王', 'last_name': '翼麟', 'age': 18, 'sex': 1, 'money': 12000.0}

 获取多条数据 fetchmany 默认从上一条数据继续向下搜索(性质类似于迭代器)
data = cursor.fetchmany() # 没有参数,默认只获取一条,返回的是用一个列表包起来的字典
data = cursor.fetchmany(3)  ==>获取3条,指定参数,按照指定的参数获取.返回的是用一个列表包起来的字典
print(data)
[
{'id': 11, 'first_name': '主', 'last_name': '胜', 'age': 3, 'sex': 0, 'money': 9.9}, 
{'id': 12, 'first_name': '尉', 'last_name': '翼麟', 'age': 18, 'sex': 1, 'money': 12000.0}, 
{'id': 13, 'first_name': '谢', 'last_name': '晨', 'age': 80, 'sex': 0, 'money': 200.0}
]


for row in data:★★★★★  row  行
	first_name = row["first_name"]
	last_name = row["last_name"]
	age = row["age"]
	if row["sex"] == 0:
		sex = "女性"
	else:
		sex = "男性"
	money = row["money"]
	print("姓:{},名:{},年龄:{},性别:{},收入:{}".format(first_name,last_name,age,sex,money))
	
"""
获取所有数据 fetchall 基于上一条数据往下搜索(性质类似于迭代器)
data = cursor.fetchall()   返回的是一个大列表包装起来的字典
print(data)







自定义搜索查询的位置

sql = "select * from t1  where id >=10"
res = cursor.execute(sql)


res = cursor.fetchone()
print(res)

1.相对滚动 (向后) 基于上一条位置滚动, 滚几下在获取下一个值,不是获取滚的最后一个值
"""
cursor.scroll(3,mode="relative")
res = cursor.fetchone()
print(res)

方向向前 (无论前后,不能超出范围)
cursor.scroll(-3,mode="relative")
res = cursor.fetchone()
print(res)


2.绝对滚动 , 永远基于第一条数据的位置滚动  滚几下在获取下一个值,不是获取滚的最后一个值
"""
cursor.scroll(0,mode="absolute")  0代表第一个
print(cursor.fetchone())

cursor.scroll(1,mode="absolute")
print(cursor.fetchone())

cursor.scroll(2,mode="absolute")
print(cursor.fetchone())

往前滚没数据,超出范围error
cursor.scroll(-1,mode="absolute")
print(cursor.fetchone())
"""

 在进行增删改的时候,必须通过commit提交数据,才会对数据库进行更新,否则默认回滚.
conn.commit()
cursor.close()
conn.close()

导出数据库(备份),也可以用Navicat导出

第一步 : 先退出数据库mysql
第二部 : 切换到对应的导出路径(找一个想要道出去的目录,比如导到d盘,在命令符中写 d: )
第三步 :
	# 导出所有
	mysqldump -uroot -p(有密码写密码,没密码写空) 数据库名(想要导出的数据库名)  >(相当于as,起一个名字)  数据库名.sql (后缀)
	# 导出单个
	mysqldump -uroot -p db0619 t1 > t1.sql

导入数据库(如果数据不存在,将备份放进去),Navicat直接导入(拉进去点击开始),记得F5刷新

第一步 : 先创建一个空的数据库(登录进来),然后use 创建的数据库
第二部 : 找到对应的sql文件的路径 D:\db25.sql
第三步 : source 路径/sql文件  例如: D:\db25.sql

##innodb 在只有frm和ibd文件的情况下,如何恢复数据

先在MySQL的数据库中手动创建一个文件夹,将丢失的数据文件后缀为frm(表结构,储存相应的字段和类型)的复制放到创建好的文件夹中,然后打开cmd,

第一步: d: (切换到d盘)

第二步: cd 后面是 找到frm那个文件路径(在创建好的文件夹中,相对路径)执行.

注释:cd代表切换 ,然后下一步dir

第三步: mysqlfrm --diagnostic ./文件名

解释: ./代表当前路径,文件名(你想要执行的文件,就是复制到创建好的文件夹中的文件frm(丢失的文件)记得要加后缀)

第四步:cmd会出现一个建表语句,先把之前创建好的文件夹中复制过来的文件frm删掉.然后复制建表语句,进入cmd(musql -uroot -p),show databases,然后在去创建一个全新的数据库,然后 use 第二次创建好的数据库(文件夹),将复制好的建表语句手动创建一次(粘贴),然后show tables (出现一个新的表1,没有任何的数据),然后在执行 alter table 表名(之前丢失数据的表名,没有后缀) discard tablespace;然后将丢失的数据文件后缀为idb复制放到第二次建好的文件夹中(手动,不是在cmd中),然后在cmd中继续执行alter table 表名(上面的表1) import tablespace; 即可

安装 MySQL Utilities
https://downloads.mysql.com/archives/utilities/