◆ 数据库增删改查:
import pymysql
# 创建连接,charset指定字符集
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='xxx', db='sys', charset='utf8')
# 创建游标
cursor = conn.cursor()
# 执行sql语句,返回sql查询成功的记录数目
cursor.execute("select * from sys.names")
# 获取一行的数据
row = cursor.fetchone()
# 获取第二行数据
row = cursor.fetchone()
# 获取所有的数据
rows = cursor.fetchall()
# 获取指定的条数数据
row = cursor.fetchmany(3)
# 插入单条数据
insert_sql = "insert into names(name) values('demo1')"
effect_row = cursor.execute(insert_sql) # effect_row=1
# 插入多条数据
insert_many_sql = 'insert into names(name) values(%s)'
effect_row = cursor.executemany(insert_many_sql, [('demo2'), ('demo3'), ('demo4')])
# 删除数据
delete_sql = "delete from names where name in ('demo1','demo2','demo3','demo4')"
effect_row = cursor.execute(delete_sql)
# 修改数据
update_sql = "update names set name = 'demo5'"
effect_row = cursor.execute(update_sql)
# 数据修改提交
conn.commit()
# 关闭连接,游标和连接都要关闭
cursor.close()
conn.close()
◆ execute()之sql注入:
数据库连接:
user = input('请输入用户名:')
pwd = input('请输入密码:')
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123', db='t1', charset='utf8')
print(conn)
sql注入方式:
#1、sql注入之:用户存在,绕过密码
lisi' -- 任意字符
#2、sql注入之:用户不存在,绕过用户与密码
xxx' or 1=1 -- 任意字符
连接结果:
请输入用户名:sdj;fja;' or 1=1 -- ;j;j;jj;jjkdsjfjsd
请输入密码:123
<pymysql.connections.Connection object at 0x000001EF2BE3E240>
select * from t1.userinfo where username='sdj;fja;' or 1=1 -- ;j;j;jj;jjkdsjfjsd' and pwd='123'
1
登陆成功
sql注入解决方案:
1、采用列表的方式:
# 原来是我们对sql进行字符串拼接
# sql="select * from userinfo where name='%s' and password='%s'" %(username,pwd)
# print(sql)
# result=cursor.execute(sql)
#改写为(execute帮我们做字符串拼接,我们无需且一定不能再为%s加引号了)
sql="select * from userinfo where name=%s and pwd=%s" #!!!注意%s需要去掉引号,因为pymysql会自动为我们加上
result=cursor.execute(sql,[user,pwd]) #pymysql模块自动帮我们解决sql注入的问题,只要我们按照pymysql的规矩来。
2、采用字典的方法:
# -*- coding:utf-8 -*-
import pymysql
user = input('请输入用户名').strip()
pwd = input('请输入密码').strip()
# 连接服务端
conn = pymysql.connect(
host='127.0.0.1',
user='root',
password="123",
database='t1',
port=3306,
charset='utf8'
)
# -- ddadad
# 创建游标对象
cur = conn.cursor()
sql = "select * from userinfo where username = %(name)s and pwd = %(password)s"
print(sql)
# resultNum = cur.execute(sql,[user,pwd])
resultNum = cur.execute(sql,{"name":user,"password":pwd})
print(resultNum)
cur.close()
conn.close()
if resultNum:
print('登陆成功')
else:
print('登陆失败')
◆ 游标移动:
语法:
# 当mode=absolute时,num不能小于0
cursor.scroll(num,mode='relative|absolute')
示例:
# 查询第一行的数据
row = cursor.fetchone()
print(row) # (1, 'mjj', '123')
# 查询第二行数据
row = cursor.fetchone() # (3, '张三', '110')
print(row)
cursor.scroll(-1,mode='relative') #设置之后,光标相对于当前位置往前移动了一行,所以打印的结果为第二行的数据
row = cursor.fetchone()
print(row)
cursor.scroll(0,mode='absolute') #设置之后,光标相对于首行没有任何变化,所以打印的结果为第一行数据
row = cursor.fetchone()
print(row)