- pymysql模块初识
- SQL的注入问题
- pymysql的增删改
- 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‘}]