#1 基本使用
# import pymysql
# conn=pymysql.connect(host='localhost',user='root',password='123',database='db4') # 链接
# cursor=conn.cursor() #拿到游标，即mysql >，接收命令
# sql='select * from userinfo where id>1;'
# rows=cursor.execute(sql) #提交命令，拿到受影响的行数
# print('%s rows in set (0.00 sec)' %rows)
# cursor.close()
# conn.close()
#2 小练习
# import pymysql
# user=input('用户名>>: ').strip()
# pwd=input('密码>>: ').strip()
# #链接，拿到游标
# conn=pymysql.connect(host='localhost',user='root',password='123',database='db4')
# cursor=conn.cursor() #拿到游标，即mysql >
# #执行sql
# sql='select * from userinfo where user="%s" and pwd="%s";' %(user,pwd)
# print(sql)
# rows=cursor.execute(sql) #拿到受影响的行数
# print('%s rows in set (0.00 sec)' %rows)
# cursor.close()
# conn.close()
# if rows:
# print('登录成功')
# else:
# print('登录失败')
#** 上面有sql注入，‘select * from user where user="%s" and password="%s"’;%(user,pwd)
# user>>egon" -- hahahaha 这里--"粘起来就会报错，必须有空格才能跳过密码，因为strip的原因，所以后面有内容
# select * from user where user="egon" -- hahahaha" and password="%s"’
# user>> xxx" or 1=1 -- hahaha
# select * from user where user="xxx" or 1=1 -- hahaha" -- hahahaha" and password="%s"’ 用户名密码都不需要要
#3:解决sql注入
# import pymysql
# user=input('用户名>>: ').strip()
# pwd=input('密码>>: ').strip()
# conn=pymysql.connect(host='localhost',user='root',password='123',database='db4')
# cursor=conn.cursor()
#
# sql='select * from userinfo where user=%s and pwd=%s;'
# rows=cursor.execute(sql,[user,pwd]) #就是将双引号去除了
# print('%s rows in set (0.00 sec)' %rows)
# cursor.close()
# conn.close()
# if rows:
# print('登录成功')
# else:
# print('登录失败')
#4 增删改
# import pymysql
# conn=pymysql.connect(host='localhost',user='root',password='123',database='db4',charset='utf8')#注意字符编码默认是latin
# cursor=conn.cursor()
# sql='insert into userinfo(user,pwd) values(%s,%s);'
# # rows=cursor.execute(sql,('alex','456')) #提交命令
# rows=cursor.executemany(sql,[('yuanhao','123'),('laowu','123'),('kgf','12323')]) #提交多组命令
# print('%s row in set (0.00 sec)' %rows)
# conn.commit() #执行命令
# cursor.close()
# conn.close()
#5 查:fetchone,fetchmany,fetchall
# import pymysql
# conn=pymysql.connect(host='localhost',user='root',password='123',database='db4',charset='utf8')
# cursor=conn.cursor()
# sql='select * from userinfo;'
# rows=cursor.execute(sql)
#5.1查单条，返回的是元祖
# res1=cursor.fetchone()
# res2=cursor.fetchone()
# res3=cursor.fetchone()
# print(res1[0])
# print(res2)
# print(res3)
#5.2查多条
# print(cursor.fetchmany(3)) #显示在一个大元祖里
# print(cursor.fetchone())
#5.3查所有
# print(cursor.fetchall()) #显示在一个大元祖里
# print(cursor.fetchone()) #None
#5.4光标的移动，绝对路径
# print(cursor.fetchall())
# print(cursor.fetchone())
# cursor.scroll(1,mode='absolute')
# print(cursor.fetchone())
# cursor.scroll(3,mode='absolute')
# print(cursor.fetchone())
#5.5相对路径
# print(cursor.fetchone())
# print(cursor.fetchone())
# cursor.scroll(-2,mode='relative')
# print(cursor.fetchone())
# cursor.close()
# conn.close()
#6 查看当前光标ID,用于多表关联，插入ID时候，其他表可以获取这个ID递增
# import pymysql
# conn=pymysql.connect(host='localhost',user='root',password='123',database='db4',charset='utf8')
# cursor=conn.cursor()
# sql='insert into userinfo(user,pwd) values(%s,%s);'
# rows=cursor.execute(sql,('alex阿什顿发','123'))
# conn.commit()
# print(cursor.lastrowid)
# cursor.close()
# conn.close()
