Pymysql

PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用mysqldb。

注意事项

在进行本文以下内容之前需要注意：

• 你有一个MySQL数据库，并且已经启动。
• 你有可以连接该数据库的用户名和密码
• 你有一个有权限操作的database

基本使用

import pymysql
# 创建连接(连接数据库ip,库名,用户名,密码,编码)
conn = pymysql.connect(host="localhost",database="test",user="root",password="123456",charset="utf8")
# 创建浮标
cursor = conn.cursor()
sql = "select * from course;"
# 执行sql语句
ret = cursor.execute(sql)
# 关闭浮标
cursor.close()
# 关闭连接
conn.close()
# 执行sql语句返回的是受影响的条数,查询时就是查到的条数
print(ret)

带有参数的sql语句与sql注入

简单的使用字符串拼接会有一些漏洞,比如我们的数据库中只有一个姓名为"年轻人",密码为"123"的账号

import pymysql
name = input("请输入用户名")
pwd = input("请输入密码")
conn = pymysql.connect(host="localhost",database="test",user="root",password="123456",charset="utf8")
cursor = conn.cursor()
sql = "select * from info where name='%s' and pwd='%s';"%(name,pwd)
print(sql)
ret = cursor.execute(sql)
cursor.close()
conn.close()
if ret:
    print("登录成功")
else:
    print("登录失败")

比如我们输入姓名时输入"年轻人' -- "(注意--两边有空格),这是-- 后面的内容被注释,不需要密码便能够登录

更甚至我们在"--"前用"or"连接一个成立条件,即使用户名不存在也能够登录

比如我们输入"bucunzai' or 1=1 -- "

pymysql帮我们解决了这些问题,我们不必自己拼接sql语句

import pymysql
name = input("请输入用户名")
pwd = input("请输入密码")
conn = pymysql.connect(host="localhost",database="test",user="root",password="123456",charset="utf8")
cursor = conn.cursor()
print(sql)
# 我们只需要向执行sql语句的方法中传递元组类型的参数
ret = cursor.execute( "select * from info where name=%s and pwd=%s;",(name,pwd))
# 或者绑定关键字,传一个字典
# ret = cursor.execute( "select * from info where name=%(name)s and pwd=%(pwd)s;",{name=name,pwd=pwd})
cursor.close()
conn.close()
if ret:
    print("登录成功")
else:
    print("登录失败")

增删改查操作

增

import pymysql
name = input("请输入用户名")
pwd = input("请输入密码")
conn = pymysql.connect(host="localhost",database="test",user="root",password="123456",charset="utf8")
cursor = conn.cursor()
sql = "insert into info(name,pwd) values (%s,%s)"
ret = cursor.execute(sql,(name,pwd))
conn.commit()#修改数据库需要提交才能够完成修改,否则只是在内存中修改
cursor.close()
conn.close()

有提交便有回滚rollback(),可以利用python中的异常机制来回滚操作

import pymysql
name = input("请输入用户名")
pwd = input("请输入密码")
conn = pymysql.connect(host="localhost",database="test",user="root",password="123456",charset="utf8")
cursor = conn.cursor()
sql = "insert into info(name,pwd) values (%s,%s)"
try:
    cursor.execute(sql,(name,pwd))
    conn.commit()  #修改数据库需要提交才能够完成修改,否则只是在内存中修改
except Exception as e:
    conn.rollback()  # 执行两条sql语句需要同时成功,如果有一条发生了错误,就会回滚到操作之前的数据
cursor.close()
conn.close()

获取插入数据的ID

import pymysql
name = input("请输入用户名")
conn = pymysql.connect(host="localhost",database="test",user="root",password="123456",charset="utf8")
cursor = conn.cursor()
sql = "insert into teacher(t_name) values (%s)"
try:
    cursor.execute(sql,(name,))
    conn.commit()
    id = cursor.lastrowid  # 获取插入行的主键id,主键要设置自增长
    print(id)
except Exception as e:
    conn.rollback()
cursor.close()
conn.close()

批量操作

import pymysql
conn = pymysql.connect(host="localhost",database="test",user="root",password="123456",charset="utf8")
cursor = conn.cursor()
sql = "insert into teacher(t_name) values (%s)"
name=(("童安",),("童猛",))
try:
    cursor.executemany(sql,name)  # 批量操作,executemany()方法,传递sql语句与(参数集合)的集合或列表
    conn.commit()
except Exception as e:
    print("错误了")
    conn.rollback()
cursor.close()
conn.close()

删

import pymysql
num=input("请输入序号")
conn = pymysql.connect(host="localhost",database="test",user="root",password="123456",charset="utf8")
cursor = conn.cursor()
sql = "delete from teacher where t_id=%s" # 执行删除的sql语句
try:
    cursor.execute(sql,(num,))  
    conn.commit()
except Exception as e:
    print("错误了")
    conn.rollback()
cursor.close()
conn.close()

改

增删改都是差不多的

import pymysql
conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”)
cursor = conn.cursor()
# 修改数据的SQL语句
sql = "UPDATE USER1 SET age=%s WHERE name=%s;"
username = "Alex"
age = 80
try:
    cursor.execute(sql, [age, username])
    conn.commit()
except Exception as e:
    conn.rollback()
cursor.close()
conn.close()

查

sql语句的执行函数返回的是受影响的行,该怎样拿到查询结果呢

查询单条数据

import pymysql
conn = pymysql.connect(host="localhost",database="test",user="root",password="123456",charset="utf8")
cursor = conn.cursor()
sql = "select * from teacher"
cursor.execute(sql)
ret = cursor.fetchone()  # 拿到一条元组类型的查询结果
cursor.close()
conn.close()
print(ret) # (2, '老子')

查询指定数量的数据(超出范围返回全部)

import pymysql
conn = pymysql.connect(host="localhost",database="test",user="root",password="123456",charset="utf8")
cursor = conn.cursor()
sql = "select * from teacher"
cursor.execute(sql)
ret = cursor.fetchmany(3) # 元组中嵌套元组
cursor.close()
conn.close()
print(ret) # ((2, '老子'), (3, '墨子'), (4, '谢逊'))

查询全部数据

import pymysql
conn = pymysql.connect(host="localhost",database="test",user="root",password="123456",charset="utf8")
cursor = conn.cursor()
sql = "select * from teacher"
cursor.execute(sql)
ret = cursor.fetchall() # 元组中嵌套元组
cursor.close()
conn.close()

另外还可以使查到的数据成为键值对形式,需要在创建光标时提供cursor=pymysql.cursors.DictCursor参数

import pymysql
conn = pymysql.connect(host="localhost", database="test", user="root", password="123456", charset="utf8")
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = "select id,cname from class order by id"
cursor.execute(sql)
ret = cursor.fetchall()  # 拿到全部查询结果
cursor.close()
conn.close()
print(ret)
# [{'id': 8, 'cname': '全栈8期'}, {'id': 9, 'cname': '全栈7期'}, {'id': 10, 'cname': '全栈12期'}, {'id': 11, 'cname': '全栈9期'}]
# 获取多条数据是列表中嵌套着字典,字典中是字段名与值的键值对

conn = pymysql.connect(host="localhost", database="test", user="root", password="123456", charset="utf8")
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = "select id,cname from class order by id"
cursor.execute(sql)
ret = cursor.fetchone()  # 拿到全部查询结果
cursor.close()
conn.close()
print(ret)
# {'id': 8, 'cname': '全栈8期'}  获取单条数据是一个字典

移动光标

查询到哪里光标便移动到哪里,再次查询就从那里开始.查询超出范围查询单个返回None,查询多个返回().

# 光标按绝对位置移动n 按绝对位置移动参数不可以为负
cursor.scroll(1, mode="absolute")
# 光标按照相对位置(当前位置)移动n 默认 ,不可以移动到开始之前
cursor.scroll(1)
cursor.scroll(1, mode="relative")

相对移动

import pymysql
conn = pymysql.connect(host="localhost",database="test",user="root",password="123456",charset="utf8")
cursor = conn.cursor()
sql = "select t_id from teacher"
cursor.execute(sql)
ret = cursor.fetchmany(2) # 元组中嵌套元组,id从2开始的
cursor.scroll(-1) # 向前移动1
ret1 = cursor.fetchmany(2)
cursor.close()
conn.close()
print(ret) # ((2,), (3,))
print(ret1) # ((3,), (4,))

绝对移动

import pymysql
conn = pymysql.connect(host="localhost",database="test",user="root",password="123456",charset="utf8")
cursor = conn.cursor()
sql = "select t_id from teacher"
cursor.execute(sql)
ret = cursor.fetchmany(2) # 元组中嵌套元组,id从2开始的
cursor.scroll(1, mode="absolute") #移动到1
ret1 = cursor.fetchmany(2)
cursor.close()
conn.close()
print(ret) # ((2,), (3,))
print(ret1) # ((3,), (4,))

通过面向对象封装

import pymysql
# 数据库配置信息
DB_CONFIG = {
    "host": "localhost",
    "user": "root",
    "password": "root1234",
    "database": "s8",
    "charset": "utf8"
}
class SQLHelper(object):

    @staticmethod
    def open(cursor):
        # 创建连接,cursor决定是返回字典还是元组
        conn = pymysql.connect(**DB_CONFIG)
        cursor = conn.cursor(cursor=cursor)
        return conn,cursor

    @staticmethod
    def close(conn,cursor):
        # 关闭连接
        conn.commit()
        cursor.close()
        conn.close()

    @classmethod
    def fetch_one(cls,sql,args,cursor =pymysql.cursors.DictCursor):
        # 查询一条
        conn,cursor = cls.open(cursor)
        cursor.execute(sql, args)
        obj = cursor.fetchone()
        cls.close(conn,cursor)
        return obj

    @classmethod
    def fetch_all(cls,sql, args,cursor =pymysql.cursors.DictCursor):
        # 查询多条
        conn, cursor = cls.open(cursor)
        cursor.execute(sql, args)
        obj = cursor.fetchall()
        cls.close(conn, cursor)
        return obj

    @classmethod
    def update(cls,sql, arg=None):
        # 增删改,其实增还可以单独列出来,因为可以去获取他的主键ID
        conn, cursor = cls.open(cursor=None)
        try:
            cursor.execute(sql, arg)
            conn.commit()
        except Exception as e:
            conn.rollback()
            raise
        finally:
            cls.close(conn, cursor)