1 #1 基本使用
2 # import pymysql
3 # conn=pymysql.connect(host='localhost',user='root',password='123',database='db4') # 链接
4 # cursor=conn.cursor() #拿到游标，即mysql >，接收命令
5 # sql='select * from userinfo where id>1;'
6 # rows=cursor.execute(sql) #提交命令，拿到受影响的行数
7 # print('%s rows in set (0.00 sec)' %rows)
8 # cursor.close()
9 # conn.close()
10
11 #2 小练习
12 # import pymysql
13 # user=input('用户名>>: ').strip()
14 # pwd=input('密码>>: ').strip()
15 # #链接，拿到游标
16 # conn=pymysql.connect(host='localhost',user='root',password='123',database='db4')
17 # cursor=conn.cursor() #拿到游标，即mysql >
18 # #执行sql
19 # sql='select * from userinfo where user="%s" and pwd="%s";' %(user,pwd)
20 # print(sql)
21 # rows=cursor.execute(sql) #拿到受影响的行数
22 # print('%s rows in set (0.00 sec)' %rows)
23 # cursor.close()
24 # conn.close()
25 # if rows:
26 # print('登录成功')
27 # else:
28 # print('登录失败')
29
30 #** 上面有sql注入，‘select * from user where user="%s" and password="%s"’;%(user,pwd)
31 # user>>egon" -- hahahaha 这里--"粘起来就会报错，必须有空格才能跳过密码，因为strip的原因，所以后面有内容
32 # select * from user where user="egon" -- hahahaha" and password="%s"’
33 # user>> xxx" or 1=1 -- hahaha
34 # select * from user where user="xxx" or 1=1 -- hahaha" -- hahahaha" and password="%s"’ 用户名密码都不需要要
35
36
37 #3:解决sql注入
38 # import pymysql
39 # user=input('用户名>>: ').strip()
40 # pwd=input('密码>>: ').strip()
41 # conn=pymysql.connect(host='localhost',user='root',password='123',database='db4')
42 # cursor=conn.cursor()
43 #
44 # sql='select * from userinfo where user=%s and pwd=%s;'
45 # rows=cursor.execute(sql,[user,pwd]) #就是将双引号去除了
46 # print('%s rows in set (0.00 sec)' %rows)
47 # cursor.close()
48 # conn.close()
49 # if rows:
50 # print('登录成功')
51 # else:
52 # print('登录失败')
53
54
55 #4 增删改
56 # import pymysql
57 # conn=pymysql.connect(host='localhost',user='root',password='123',database='db4',charset='utf8')#注意字符编码默认是latin
58 # cursor=conn.cursor()
59 # sql='insert into userinfo(user,pwd) values(%s,%s);'
60 # # rows=cursor.execute(sql,('alex','456')) #提交命令
61 # rows=cursor.executemany(sql,[('yuanhao','123'),('laowu','123'),('kgf','12323')]) #提交多组命令
62 # print('%s row in set (0.00 sec)' %rows)
63 # conn.commit() #执行命令
64 # cursor.close()
65 # conn.close()
66
67
68 #5 查:fetchone,fetchmany,fetchall
69 # import pymysql
70 # conn=pymysql.connect(host='localhost',user='root',password='123',database='db4',charset='utf8')
71 # cursor=conn.cursor()
72 # sql='select * from userinfo;'
73 # rows=cursor.execute(sql)
74
75 #5.1查单条，返回的是元祖
76 # res1=cursor.fetchone()
77 # res2=cursor.fetchone()
78 # res3=cursor.fetchone()
79 # print(res1[0])
80 # print(res2)
81 # print(res3)
82
83 #5.2查多条
84 # print(cursor.fetchmany(3)) #显示在一个大元祖里
85 # print(cursor.fetchone())
86
87 #5.3查所有
88 # print(cursor.fetchall()) #显示在一个大元祖里
89 # print(cursor.fetchone()) #None
90
91 #5.4光标的移动，绝对路径
92 # print(cursor.fetchall())
93 # print(cursor.fetchone())
94 # cursor.scroll(1,mode='absolute')
95 # print(cursor.fetchone())
96 # cursor.scroll(3,mode='absolute')
97 # print(cursor.fetchone())
98
99 #5.5相对路径
100 # print(cursor.fetchone())
101 # print(cursor.fetchone())
102 # cursor.scroll(-2,mode='relative')
103 # print(cursor.fetchone())
104
105 # cursor.close()
106 # conn.close()
107
108 #6 查看当前光标ID,用于多表关联，插入ID时候，其他表可以获取这个ID递增
109 # import pymysql
110 # conn=pymysql.connect(host='localhost',user='root',password='123',database='db4',charset='utf8')
111 # cursor=conn.cursor()
112 # sql='insert into userinfo(user,pwd) values(%s,%s);'
113 # rows=cursor.execute(sql,('alex阿什顿发','123'))
114 # conn.commit()
115 # print(cursor.lastrowid)
116 # cursor.close()
117 # conn.close()