python数据库驱动 --- pymysql

python中的mysql驱动（库）

python中封装了与mysql服务之间的通讯接口，从而实现在Python程序中简单方法调用就可以实现数据库操作。

连接数据库进行操作需要支持数据库和网络传输的大量协议，直接使用socket连接数据库并进行通信是相当复杂的，所以Python提供了访问数据库的接口，这些协议和复杂操作都被封装在底层的协议中，不用我们自己实现。

提供的驱动（库）

• MySQLdb
  这是较为底层的库，但是该库只支持Python2，但不能支持Python3且不再更新。

• mysqlclient
  在MySQLdb上，增加了Python3的支持

• pymysql
  语法兼容MySQLdb，使用纯Python写的MySQLdb客户端，支持Python2.7，3.4+，MySQL5.5+，MariaDB 5.5+

pymysql使用

使用pymysql连接某一个数据库，通过cursor方法获取一个游标，这样就可以直接使用这个游标执行sql语句，进行数据库的交互操作了。由于调用了数据库，注意异常捕获和最后的资源释放

基本使用

一般流程

• 建立连接

• 获取游标

• 执行SQL

• 提交事务（失败回滚）

• 释放资源

import pymysql

conn = None
try:
    conn = pymysql.connect(host="127.0.0.1", user="root", password="root", databse="test", port=3306)  # 指定ip，端口，用户，密码，库等信息

    cursor = conn.cursor()  # 指定一个游标，库中指定了部分cursor类型
    n = cursor.execute("select * from table_name") # 在execute方法中写入SQL语句，进行查询，返回服务端对应的操作的行数。并不是返回查询的结果。结果被保存在cursor中。
    
    # 获取查询的结果，使用fetchone, fetchall, fetchmany函数均可
    cursor.fetchone()    # 从返回的结果集中返回一条
    cursor.fetchall()    # 获取剩余的所有结果
    cursor.fetchmany(3)  # 无法在获取，cursor位置已到最后

    # cursor.rownumber 记录了当前cursor的位置，重置即可再次访问这些数据
    cursor.rownumber = 0  # 回到开始，该 数值正负都支持，超界不报错
    
except Exception as e:
    # 记录错误日志
    logging.error(e) 
    if conn:
        conn.rollback()    # 事务中发生了错误，将执行的结果回滚。 
    

finally:
    # 关闭链接和cursor
    cursor.close()
    if conn:
        conn.close()

可以同时在conn 上创建多个cursor 对象，这些cursor之间互不影响。

上下文管理器的使用

conn对象和cursor对象均可以使用with上下文管理器

conn
conn 是Connections.py 下的Connection 类实例，Connection 中的源码定义如下：

class Connection:
    def __enter__(self):
        warnings.warn(
            "Context manager API of Connection object is deprecated; Use conn.begin()",
            DeprecationWarning)
        return self.cursor()  # 返回一个cursor 

    # 执行提交或回滚
    def __exit__(self, exc, value, traceback):
        if exc:
            self.rollback()
        else:
            self.commit()

使用示例

conn = pymysql.connect(host="127.0.0.1", user="root", password="root", databse="test", port=3306)

with conn as cursor:
    cursor.execute("update table_name set name='tom' where name='jerry'")
    # conn 的 enter 方法返回一个cursor 对象，exit将事务提交commit

conn 对象的上下文管理并不会将关闭这个连接，最后我们必须手动关闭这连接，避免系统资源浪费。

cursor

cursor上下文管理源码：

class Cursor(object):
    def __enter__(self):           # 返回自身 
        return self

    def __exit__(self, *exc_info): # 结束时关闭这个 cursor
        del exc_info
        self.close()

cursor 的上下文管理结束时会自动关闭这个cursor。示例

import pymysql

conn = None
try:
    conn = pymysql.connect(host="127.0.0.1", user="root", password="root", databse="test", port=3306) 

    with conn.cursor() as cursor:
        n = cursor.execute("select * from table_name") 入。
        cursor.fetchone()    # 查询一条数据
    # 退出with 自动关闭 cursor，即使报错
except Exception as e:
    # 记录错误日志
    logging.error(e)
    if conn:
        conn.rollback()    # 事务中发生了错误，将执行的结果回滚。

finally:
    # 关闭链接和cursor
    if conn:
        conn.close()

Dictcursor

使用默认 cursor 时，查询的数据是会以元组的形式返回，元组中只有数据信息，而没有字段名；而使用Dictcursor返回的数据以字典的形式返回

import pymysql

conn = pymysql.connect(host="127.0.0.1", user="root", password="root", databse="test", port=3306) 

with conn.cursor(pymysql.cursors.Dictcursor) as cursor:
    n = cursor.execute("select * from Person") 入。
    print(cursor.fetchone())

======= 使用Dictcursor 输出得结果 ====
{id:1, name:tom, age:18}

======= 使用默认cursor的结果 =====
(1, tom, age)

这样我们可以方便的在结果中获取字段名。

 

SQL注入

SQL注入攻击是猜测后台SQL语句使用的字符串拼接形式，从而经过专门的设计传参，拼接出一些特殊的，非本意的SQL语句在数据库执行，使攻击者获取想要的结果

例如查询数据库所有的数据

cmd = input(">>>")
cursor.execute("select * from name=`{}`".format(cmd))

上面用户可以输入名字获取查询的结果，例如我们可以输入tom，这样就会返回名为tom的person信息。根据这拼接字符串的规律，我们可以拼接出其他用途的SQL语句。输入tom or "1" = "1"进行查询，我们将获得这个表中的全部人员信息，这并不是我们希望的，这样我们的数据就被轻易获取了。

参数化查询

 

参数化查询（Parameterized Query 或 Parameterized Statement）是访问数据库时，在需要填入数值或数据的地方，使用参数 (Parameter) 来给值。

cmd = input(">>>")
cursor.execute("select * from name= %(name)s", {"name":cmd})  #  使用字典映射
# 或者 cursor.execute("select * from name= %s", (name,)) 使用位置对应

使用这种方式拼接SQL时，将不会发生上面的现象

cmd = "10001 or '1'='1'"
n = cursor.execute("select * from employees where emp_no=%s", (cmd, )) 
print(cursor.fetchall)

====输出结果====
((10001, datetime.date(1953, 9, 2), 'Georgi', 'Facello', 'M', datetime.date(1986, 6, 26)),)   # 返回的数据，只获取了一条数据
Warning: (1292, "Truncated incorrect DOUBLE value: '10001 or '1'='1''")
  result = self._query(query)  # 显示的警告信息，程序检测出了这个字符串的"问题"

参数化查询已被视为最有效可预防SQL注入攻击 (SQL Injection) 的攻击手法的防御方式。在使用参数化查询的情况下，数据库服务器不会将参数的内容视为SQL指令的一部份来处理，而是在数据库完成SQL指令的编译后，才套用参数运行，因此就算参数中含有指令，也不会被数据库运行。Access、SQL Server、MySQL、SQLite等常用数据库都支持参数化查询。

参数化查询按还能减少sql语句的编译，较少资源的销号，在上面的程序中，使用参数化查询时，上面的字符串select * from employees where emp_no=%s将会被编译一次然后被缓存，缓存在未失效的情况不会重复对字符串进行编译，而如果直接使用sql语句字符串，每次都会对其编译，耗费不少资源。因此使用参数化查询几乎不会降低查询效率。

mysqlclient

通过mysqlclient也可以实现Python与数据库的连接，他们使用相同接口函数，其余用法基本相同

pip install mysqlclient

import MySQLdb

conn = None
try:
    conn = MySQLdb.connect(host="127.0.0.1", user="root", password="root", databse="test", port=3306)

    with conn.cursor() as cursor:
        params = "10010"
        n = cursor.execute("select * from employees where id=%s", args=(params,))
        res = cursor.fetchall()
        print(res)

except Exception as e:
    print(e)  # 写入日志

finally:
    if None:
        conn.close()    # 关闭连接