python开发mysql:Pymysql模块
pymysql模块的使用
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()