pymysql模块初识

1. pymysql模块初识
2. SQL的注入问题
3. pymysql的增删改
4. pymysql的查询

       

---

1.pymys ql模块初识 import pymysql conn = pymysql.connect(host=‘localhost‘,user=‘root‘,password=‘ren666666‘,database=‘test1‘,charset=‘utf8‘) cursor = conn.cursor() print(‘----1----‘) sql = "SELECT * FROM chart1" cursor.execute(sql) result = cursor.fetchone() cursor.close() conn.close() print(result)  

---

2.SQL注入的问题： sql = "SELECT * FROM chart1 where username=‘%s‘ and password=‘%s‘"%(user,pwd)    #这样以字符串拼接会出现sql注入的问题 cursor.execute(sql) 问题如下： 假如输入的user=uu‘ or 1=1 -- 会发生一下情况 select * from chart1 where username=‘uu‘ or 1=1 --‘ and password=‘%s‘    #注意这里的--是mysql中的注释 这将会导致，即使不输入密码，账号也不知道的情况下，依然能够登陆成功  

---

3.pymysql的增删改： 插入单个值 import pymysql user = 1 pwd = ‘pycharm‘ conn = pymysql.connect(host=‘localhost‘,user=‘root‘,password=‘ren666666‘,database=‘test1‘) cursor = conn.cursor() print(‘----1----‘) sql = "insert into chart1(id,name) values(%s,%s)" cursor.executemany(sql, [user,pwd] ) conn.commit ()    #数据修改必须要用这个命令提交 result = cursor.fetchone() cursor.close() conn.close() print(result)   #插入多个值 import pymysql conn = pymysql.connect(host=‘localhost‘,user=‘root‘,password=‘ren666666‘,database=‘test1‘) cursor = conn.cursor() print(‘----1----‘) sql = "insert into chart1(id,name) values(%s,%s)" cursor.executemany(sql, [(5,‘pycharm‘),(6,‘sublime‘)] ) conn.commit ()    #数据修改必须要用这个命令提交 result = cursor.fetchone() cursor.close() conn.close() print(result)  

---

4.pymysql的查询： import pymysql conn = pymysql.connect(host=‘localhost‘,user=‘root‘,password=‘ren666666‘,database=‘test1‘) cursor = conn.cursor() print(‘----1----‘) sql = "select * from chart1" cursor.execute(sql) # result = cursor.fetchone()    #只能拿到一个数据 # result = cursot.fetchall()    #拿到所以数据 result = cursor.fetchmany(4)    #拿到4个数据 print(result) cursor.close() conn.close() import pymysql conn = pymysql.connect(host=‘localhost‘,user=‘root‘,password=‘ren666666‘,database=‘test1‘) #cursor = conn.cursor()    #这个的话会返回元组类型的结果 cursor = conn.cursor( cursor=pymysql.cursors.DictCursor)    #这个会以字典形式返回 print(‘----1----‘) sql = "select * from chart1" cursor.execute(sql) result = cursor.fetchmany(4)    #拿到4个数据 print(result) cursor.close() conn.close() # [{‘id‘: 1, ‘name‘: ‘python‘}, {‘id‘: 2, ‘name‘: ‘pycharm‘}, {‘id‘: 3, ‘name‘: ‘pycharm‘}, {‘id‘: 4, ‘name‘: ‘anaconda‘}]