1. pymysql的基本操作
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
|
# ### 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注入攻击
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
|
# ### 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增删改查
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
|
# ### 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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
# ### (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 |
小总结:
1
2
3
4
|
-- 是mysql的注释 # 在sql注入那块用到了 默认python连接mysql的时候,都是开启了一个事务,尤其是增删改, 在改变数据的时候,一定要提交数据,不提交数据(默认回滚),不会真正的改变 \s 看一下服务器的数据 |
5. sql语句优化
1
2
3
4
5
6
7
8
9
|
(1) 避免使用select *, (2) 不确定表大小时候,先用count(*)查下数据. (3) 创建表时尽量使用 char 代替 varchar (4) 定长的字段放前面,变长的字段放后面.(尽可能小的改变树状结构高度) (5) 组合索引代替多个单列索引 (由于mysql中每次只能使用一个索引,所以经常使用多个条件查询时更适 合使用组合索引) (6) 尽量使用短索引(小数据值) (7) 重复少的字段值不适合做索引,例:性别不适合 (8) 使用连接(JOIN)来代替子查询(Sub-Queries) |
总结
本篇文章就到这里了,希望能够给你带来帮助,也希望您能够多多关注服务器之家的更多内容!
原文链接:https://blog.csdn.net/weixin_46818279/article/details/122183781