[大数据][阶段二] 6. MySQL与Python交互

mysql 与 python 交互

• Connector 模块语法：

MySQL Connector 是 mysql 官方的驱动模块，兼容性非常好。下载地址：https://dev.mysql.com/downloads/connector/python/

# coding:utf-8

import mysql.connector

# 创建连接
con = mysql.connector.connect(
    host="localhost", port="3306",
    user="root", password="123456789",
    database="demo"
)

# 关闭连接
con.close()

MySQL Connector 里面的游标（cursor）用来执行 sql 语句，而且查询的结果集也会保存在游标之中。

# coding:utf-8

import mysql.connector

# 创建连接
con = mysql.connector.connect(
    host="localhost", port="3306",
    user="root", password="123456789",
    database="vega"
)

# 执行sql
cursor = con.cursor()
sql = "SELECT username,password,email,role_id FROM t_user;"
cursor.execute(sql)

for result in cursor:
    print(result[0], result[1], result[2], result[3])

# 关闭连接
con.close()

• sql 的注入攻击：

由于 sql 语句是解释型语言，所以在拼接 sql 语句的时候，容易被注入恶意的 sql 语句。

sql 注入攻击示例：

# coding:utf-8

import mysql.connector

config = {
    "host": "localhost",
    "port": "3306",
    "user": "root",
    "password": "123456789",
    "database": "vega"
}

con = mysql.connector.connect(**config)

username = "1 OR True"
password = "1 OR True"
sql = "SELECT COUNT(*) FROM t_user WHERE username=%s AND AES_DECRYPT(UNHEX(password),'HelloWorld')=%s;"

cursor = con.cursor()
cursor.execute(sql % (username, password))
print(cursor.fetchone()[0])

con.close()

即使攻击者不知道真实的账号密码，他也可以使用 1 OR True 这样的账号密码来注入他想要执行的恶意的 sql 语句，好在 sql 注入攻击是可以被 sql 预编译机制抵御的。

mysql 有一个 sql 预编译机制，预编译 sql 就是数据库提前把 sql 语句编译成二进制，这样反复执行同一条 sql 语句的效率就会大大提升。

sql 语句编译的过程中，关键字已经被解析过了，所以想编译后的 sql 语句传入参数，都会被当作字符串处理，数据库不会解析其中注入的 sql 语句。

预防 sql 注入攻击示例：

# coding:utf-8

import mysql.connector

config = {
    "host": "localhost",
    "port": "3306",
    "user": "root",
    "password": "123456789",
    "database": "vega"
}

con = mysql.connector.connect(**config)

username = "1 OR True"
password = "1 OR True"
sql = "SELECT COUNT(*) FROM t_user WHERE username=%s AND AES_DECRYPT(UNHEX(password),'HelloWorld')=%s;"

cursor = con.cursor()
cursor.execute(sql, (username, password))               #提前编译 sql 语句，传入参数会被当作字符串处理
print(cursor.fetchone()[0])

con.close()

通常，用户登录时容易被 sql 注入，因此不要相信用户的输入，时刻进行筛选，永远进行 sql 预编译机制。

• Connector 的异常处理：

Connector 提供了非常简单的事务控制函数。

# coding:utf-8

import mysql.connector

con = mysql.connector.connect(
    host="localhost", port="3306",
    user="root", password="123456789",
    database="vega"
)

# 开启事务
con.start_transaction(isolation_level='repeatable read')                #参数 isolation_level 指定事务隔离级别

# 提交事务
con.commit()

# 回滚事务
con.rollback()

con.close()

Connector 的异常处理：

# coding:utf-8

import mysql.connector

try:
    con = mysql.connector.connect(
        host="localhost", port="3306",
        user="root", password="123456789",
        database="vega"
    )

    con.start_transaction(isolation_level='repeatable read')
    
    # 执行 sql 语句
    cursor = con.cursor()
    sql = "INSERT INTO t_user(username,password,email,role_id) " \
          "VALUES('bai', HEX(AES_ENCRYPT('123456','HelloWorld')),'bai@bai.com', 3);"
    cursor.execute(sql)
    
    con.commit()

except Exception as e:
    if "con" in dir():
        con.rollback()
    print(e)

finally:
    if "con" in dir():
        con.close()

• 数据库连接池：

TCP 连接往往需要经过三次握手、四次挥手来建立和关闭，然后数据库还要验证用户信息。因此数据库连接是一种关键的、有限的、昂贵的资源，在并发执行的应用程序中体现得尤为突出。

数据库连接池会预先创建出一些数据库连接，然后缓存起来，避免了程序语言反复创建和销毁连接的昂贵代价。

# coding:utf-8

import mysql.connector.pooling

config = {
    "host": "localhost",
    "port": "3306",
    "user": "root",
    "password": "123456789",
    "database": "vega"
}

try:
    # 创建连接池
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **config,
        pool_size=10
    )
    
    # 获取连接池的连接
    con = pool.get_connection()
    
    con.start_transaction(isolation_level='repeatable read')
    cursor = con.cursor()
    sql = "UPDATE t_user SET username=%s WHERE role_id=%s;"
    cursor.execute(sql, ('xiaohei', 3))
    con.commit()
    
except Exception as e:
    if "con" in dir():
        con.rollback()
    print(e)

• Connector 删除数据：

# coding:utf-8

import mysql.connector.pooling

config = {
    "host": "localhost",
    "port": "3306",
    "user": "root",
    "password": "123456789",
    "database": "vega"
}

try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **config,
        pool_size=10
    )

    con = pool.get_connection()

    con.start_transaction(isolation_level='repeatable read')
    cursor = con.cursor()
    sql = "DELETE IGNORE FROM t_user WHERE username=%s;"
    cursor.execute(sql, ('xiaohei',))
    con.commit()

except Exception as e:
    if "con" in dir():
        con.rollback()
    print(e)

如果是使用 TRUNCATE TABLE 数据表名称; 来清空数据表，则不需要开启事务。

另外，游标对象中的 executemany 函数可以反复执行一条 sql 语句。

# coding:utf-8

import mysql.connector.pooling

config = {
    "host": "localhost",
    "port": "3306",
    "user": "root",
    "password": "123456789",
    "database": "vega"
}

try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **config,
        pool_size=10
    )

    con = pool.get_connection()

    con.start_transaction(isolation_level='repeatable read')
    cursor = con.cursor()
    sql = "INSERT INTO t_user(username,password,email,role_id) " \
          "VALUES(%s, HEX(AES_ENCRYPT('123456','HelloWorld')),%s, %s);"
    person_list = [['aaa', 'aaa@aaa.com', 3], ['bbb', 'bbb@bbb.com', 4], ['ccc', 'ccc@ccc.com', 5]]
    cursor.executemany(sql, person_list)                # 反复执行 sql 语句
    con.commit()

except Exception as e:
    if "con" in dir():
        con.rollback()
    print(e)