pymysql操作说明
1.查询数据
import pymysql
conn = pymysql.connect(
user = 'root',#用户名
password ='123',#用户密码
host = '127.0.0.1',#ip地址
port =3306,#端口
charset = 'utf8',#编码
database = 'day36'#库名
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)#产生游标
#cursor=pymysql.cursors.DictCursor 将查询出来的结果制成字典的行式返回
sql ="select * from course"
res =cursor.execute(sql)#查询可以控制的行数
print(res)
ret =cursor.fetchone()#只获取查询结果中的一条数据
print(cursor.fetchmany())#查询多条数据,可添加参数,更改查询条数
print(cursor.fetchmany(2))
print(cursor.fetchall())#查询所有数
光标移动
数据查询光标会根据读取内容向后移动,相当于文件操作
可以通过光标移动
相对移动:
cursor.scroll(1,'relative')#基于现在指针的位置向后移动1条数据
cursor.scroll(1,'absolute')#基于起始位置,向后移动1条数据
进行增,删,改需要确认操作
conn = pymysql.connect(
user = 'root',
passwd = '123456',
db = 'day36',
host = '127.0.0.1',
port = 3306,
charset = 'utf8',
autocommit = True # 自动提交确认
)
增加数据
sql ='insert into user(name,password)value("123",123)'
res = cursor.execute(sql)
修改数据
sql ='update user set name="4567" where password=123'
res = cursor.execute(sql)
删除数据
sql='delete from user where name="345"'
res = cursor.execute(sql)
sql注入问题
import pymysql
conn = pymysql.connect(
user = 'root',
password = '123',
host = '127.0.0.1',
port = 3306,
charset = 'utf8',
database = 'day36',
autocommit = True
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
usernamne =input('请输入账户')
password = input("密码")
sql ='select * from user where name="%s"and password="%s"'%(usernamne,password)
print(sql)
res =cursor.execute(sql)
if res:
print(res)
else:
print('错误')
知道账户
请输入账户xxx" or 1=1 -- asdf asd f
# 密码53452
# select * from user where name="xxx" or 1=1 -- asdf asd f"and password="41235"
不知道账户
# 请输入账户4567" -- asdfasdfasdf
# 密码52345
# select * from user where name="4567" -- asdfasdfasdf"and password="52345"
为了结局注入问题利用execute进行其操作
cursor.execute(sql,(username,password))