pymysql模块

pymysql模块

基本语法

# (1) 创建连接对象 host user password database 这四个参数必写
conn = pymysql.connect( host="127.0.0.1" , user="root" , password="123456" , database="db003" , charset="utf8" , port=3306 )


# (2) 创建游标对象 (用来操作数据库的增删改查)
cursor = conn.cursor()
print(cursor)

# (3) 执行sql语句
sql = "select * from employee"
# 执行查询语句返回的总条数
res = cursor.execute(sql)
print(res)

# (4) 获取数据 fetchone 获取一条数据
# 返回的是元组,里面包含的是第一条的完整数据 
# 类迭代器一般操作
res = cursor.fetchone()
print(res)
res = cursor.fetchone()
print(res)
res = cursor.fetchone()
print(res)

# (5) 释放游标对象
cursor.close()
# (6) 释放连接对象
conn.close()


# 一般在查询的时候,通过fetchone来获取结果
res1 = cursor.fetchone()

创建/删除表操作

# conn = pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db003")
# cursor = conn.cursor()

# 1.创建一张表
sql = """
create table t1(
id int unsigned primary key auto_increment,
first_name varchar(255) not null,
last_name varchar(255) not null,
sex tinyint not null,
age tinyint unsigned not null,
money float
);
"""
# res = cursor.execute(sql)
# print(res) # 无意义返回值

# 2.查询表结构
"""
sql = "desc t1"
res = cursor.execute(sql)
print(res) # 返回的是创建的字段的个数
res = cursor.fetchone()
print(res)
res = cursor.fetchone()
print(res)
res = cursor.fetchone()
print(res)
"""

# 3.删除表
"""
try:
	sql = "drop table t1"
	res = cursor.execute(sql)
	print(res) # 无意义返回值
except:
	pass
"""

事务处理

[!IMPORTANT]

增删改数据必须进行事务处理

# ### 3.事务处理

"""pymysql 默认开启事务的,所有增删改的数据必须提交,否则默认回滚;rollback"""
conn = pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db003")
cursor = conn.cursor()
sql1 = "begin"
sql2 = "update employee set emp_name='程咬钻石' where id = 18 "
sql3 = "commit"

res1 = cursor.execute(sql1)
res1 = cursor.execute(sql2)
res1 = cursor.execute(sql3)

print(res1)


cursor.close()
conn.close()

SQL注入攻击

SQL注入攻击现象

import pymysql
user = input("请输入您的用户名>>>")
pwd  = input("请输入您的密码>>>")

conn = pymysql.connect(host="127.0.0.1" , user="root" , password="123456",database="wbc")

cursor = conn.cursor()
sql1 = """
create table usr_pwd(
id int unsigned primary key auto_increment,
username varchar(255) not null,
password varchar(255) not null
)
"""

sql2 = "select * from usr_pwd where username='%s' and password='%s' " % (user,pwd)
# select * from usr_pwd where username='user' and password='2222 or 4=4 --'
print(sql2)

# res = cursor.execute(sql1)
res = cursor.execute(sql2)

"""
select * from usr_pwd where username='2222' or 4=4 -- aaa' and password='' 
相当于 : select * from usr_pwd where 10=10; 绕开了账户和密码的判断 -- 代表的是注释;
"""
if res:
	print("登录成功")
else:
	print("登录失败")

cursor.close()
conn.close()

请输入您的用户名>>>111
请输入您的密码>>>ddd' or 1=1 --'

预处理机制

# (2) 预处理机制
""" 在执行sql语句之前,提前对sql语句中出现的字符进行过滤优化,避免sql注入攻击 """
""" execute( sql , (参数1,参数2,参数3 .... ) ) execute2个参数默认开启预处理机制 """
""" 填写 234234' or 100=100 -- sdfsdfsdfsdf  尝试攻击  """


user = input("请输入您的用户名>>>")
pwd  = input("请输入您的密码>>>")

conn = pymysql.connect(host="127.0.0.1" , user="root" , password="123456",database="db005")
cursor = conn.cursor()
sql = "select * from usr_pwd where username=%s and password=%s"
res = cursor.execute(sql , (user,pwd)  )
print(res)


print(    "登录成功"  if res else "登录失败"    )

cursor.close()
conn.close()

增删改查

"""
	python 操作mysql增删改时,默认是开启事务的,
	必须最后commit提交数据,才能产生变化
	
	提交数据: commit 
	默认回滚: rollback
	
"""

conn = pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db005")
# 默认获取查询结果时是元组,可以设置返回字典;  cursor=pymysql.cursors.DictCursor
# 获取字典号进行查询的处理
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 执行对mysql 的操作


conn.commit()
cursor.close()
conn.close()

增

​ lastrowid 获取最后插入的数据ID

一次插入一条

# 1.增


sql = "insert into t1(first_name,last_name,sex,age,money) values(%s,%s,%s,%s,%s)"

# (1) 一次插入一条
res = cursor.execute( sql , ("孙","健",0,15,20000)  )
print(res) # 1

# 获取最后插入这条数据的id号
print(cursor.lastrowid)

一次性插入多条

# 返回插入的条数
res = cursor.executemany(  sql , [  ("安","晓东",0,18,30000) , ("刘","玉波",1,20,50000) ,("张","光旭",0,80,60000) , ("李","是元",0,10,10) , ("高","大奥",1,20,80000)   ]   )
print(res) # 返回插入的条数

# 插入5条数据中的第一条数据的id
print(cursor.lastrowid)
# 获取最后一个数据的id
sql = "select id from t1 order by id desc limit 1"
res = cursor.execute(sql)
print(res)

查询

sql = "select * from t1"
res = cursor.execute(sql)
print(res) # 针对于查询语句来说,返回的res是总条数;

# (1) fetchone 获取一条 迭代器获得
res = cursor.fetchone()
print(res)
res = cursor.fetchone()
print(res)

# (2) fetchmany 获取多条
res = cursor.fetchmany() # 默认获取的是一条数据,返回列表,里面里面是一组一组的字典;
data = cursor.fetchmany(3)
print(data)
"""
[
	{'id': 9, 'first_name': '王', 'last_name': '是元', 'sex': 0, 'age': 10, 'money': 10.0}, 
	{'id': 10, 'first_name': '孙', 'last_name': '健', 'sex': 0, 'age': 15, 'money': 20000.0}, 
	{'id': 11, 'first_name': '安', 'last_name': '晓东', 'sex': 0, 'age': 18, 'money': 30000.0}
]
"""

# 处理字符串
for row in data:
	first_name = row["first_name"]
	last_name = row["last_name"]
	sex = row["sex"]
	if sex == 0:
		sex = "男性"
	else:
		sex = "女性"
	age = row["age"]
	money = row["money"]
	strvar = "姓:{},名:{},性别:{},年龄:{},收入:{}".format(first_name,last_name,sex,age,money)
print(strvar)

# (3) fetchall 获取所有
# data = cursor.fetchall()
# print(data)

删

"""
sql = "delete from t1 where id in (%s,%s,%s)"
res = cursor.execute(sql , (3,4,5) )
print(res) # 返回的是3,代表删除了3条

if res:
	print("删除成功")
else:
	print("删除失败")
"""

改

# 3.改
"""
sql = "update t1 set first_name = '王' where id = %s"
sql = "update t1 set first_name = '王' where id in (%s,%s,%s,%s)"
res = cursor.execute(sql , (6,7,8,9))
print(res) # 返回的是4,代表修改了4条

if res:
	print("修改成功")
else:
	print("修改失败")
"""

滚动数据

相对滚动

# 1.相对滚动 relative
"""相对于上一次查询的位置往前移动(负数),或者往后移动(正数)"""
"""
cursor.scroll(-1,mode="relative")
# cursor.scroll(5,mode="relative")
res = cursor.fetchone()
print(res)
"""

绝对滚动

# 2.绝对滚动 absolute"""永远从数据的开头起始位置进行移动,不能向前滚"""
cursor.scroll(0,mode="absolute")
res = cursor.fetchone()
print(res)