pymysql 模块

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)