pymysql: Python3.x的一个用于连接 MySQL 服务器的一个库
安装PyMysql
pip3 install PyMySQL
连接实例
import pymysql
# 1 创建连接对象
conn = pymysql.connect(host='192.168.101.37', user='root', password='1234', database='db1')
# 2.创建游标对象,操作数据库的增删改查
cursor = conn.cursor()
# 3. execute 执行sql语句, 返回数据库的总条数
sql = r'select * from products'
res = cursor.execute(sql)
# .获取数据
# # fetchone 获取单条数据,返回的是一个元组
# res1 = cursor.fetchone()
# print(res1)
# # fetchall 获取全部数据,返回的是一个嵌套的元组
# res1 = cursor.fetchall()
# print(res1)
# # fetchmany 获取自定条数据, 默认获取一条,返回一个列表
res1 , *_ = cursor.fetchmany(2)
print(res1)
# 5.释放游标对象
cursor.close()
# 7.释放连接对象
conn.close()
创建/删除 表操作
import pymysql
conn = pymysql.connect(host='192.168.101.37', user='root', password='1234', database='db1')
cursor = conn.cursor()
# 创建表
sql = r"""
create table t1(
id int unsigned primary key auto_increment,
name varchar(20) not null,
sex tinyint not null,
age tinyint unsigned not null,
salary float default 0.0
)
"""
res = cursor.execute(sql)
print(res) # 0
# 查询表结构
sql = r"""
desc t1;
"""
res = cursor.execute(sql)
print(res) # 5
res1 = cursor.fetchone()
print(res1) # ('id', 'int(10) unsigned', 'NO', 'PRI', None, 'auto_increment')
# 删除表
sql = r"""
drop table t1;
"""
res = cursor.execute(sql)
print(res) # 0
cursor.close()
conn.close()
插入数据
import pymysql
# '''pymysql 默认开启事务,所有增改删的数据都必须提交, 否则默认回滚 rollback'''
with pymysql.connect(host='192.168.101.37', user='root', password='1234', database='db1') as conn:
with conn.cursor() as cursor:
sql1 = r"""
insert into t1(name, sex, age) value ('王五','1', '13')
"""
res = cursor.execute(sql1)
# 用于事务的提交
conn.commit()
print(res)
sql注入现象
import pymysql
with pymysql.connect(host='192.168.101.37', user='root', password='1234', database='db1') as conn:
with conn.cursor() as cursor:
# 1 创建数据库
sql = r"""
create table usr_pwd(
id int unsigned primary key auto_increment,
usr varchar(20) not null,
pwd varchar(20) not null
)
"""
cursor.execute(sql)
# 2 创建用户密码
sql = r"""
insert into usr_pwd(usr, pwd) value ('root', 'vdagasd')
"""
cursor.execute(sql)
conn.commit()
# 3 登录
user = 'sasasads'
pswd = "sas' or 1=1 -- asasa"
sql = r"select usr, pwd from usr_pwd where usr='%s' and pwd='%s'" % (user, pswd)
res = cursor.execute(sql)
if res:
print('登录成功')
print(cursor.fetchall())
解决方案
import pymysql
with pymysql.connect(host='192.168.101.37', user='root', password='1234', database='db1') as conn:
with conn.cursor() as cursor:
# 预处理机制,在 execute函数内传入函数
user = r'root'
pswd = r"sas' or 1=1 -- asasa"
sql = r"select usr, pwd from usr_pwd where usr=%s and pwd=%s;"
res = cursor.execute(sql, (user, pswd))
if res:
print('登录成功')
print(cursor.fetchall())
else:
print('登录失败')
插入多条数据
import pymysql
with pymysql.connect(host='192.168.101.37', user='root', password='1234', database='db1') as conn:
# cursor 默认返回的是元组
# cursor = conn.cursor()
# 这样修改后就返回字典
with conn.cursor(pymysql.cursors.DictCursor) as cursor:
sql = r"""
insert into t1(name, sex, age, salary) values (%s, %s, %s, %s);
"""
cursor.execute(sql, ('哈斯', 0, 27, 1900))
# 当单条数据时,查看最后插入的自增id
print(cursor.lastrowid)
# 插入多条记录, 返回插入的 条数
res = cursor.executemany(sql, [('哈斯', 0, 27, 1900), ('哈斯', 0, 27, 1900)] )
conn.commit()
# 当插入多条数据时,这个查看第一条插入的自增id
print(cursor.lastrowid)
# 获取最后插入的自增id
sql = r"""
select id from t1 order by id desc limit 1;
"""
cursor.execute(sql)
last_row_id = cursor.fetchone()
print(last_row_id)