python - mysql交互

python 与 mysql 交互，能找到两个库 pymysql 和 mysql-connector-python

因为两个都是基于 DB-API 2.0 标准‌，使用上差别并不大，区别就是 mysql-connector-python 是由 Oracle 官方提供，性能可能会好一些。

安装依赖

pip install mysql-connector-python

事务（update）

import mysql.connector

# 连接到MySQL数据库
config = {
    'user': 'root',
    'password': 'root',
    'host': '127.0.0.1',
    'database': 'med'
}

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


try:
    # 创建一个游标对象（等同于其它语言的 session、prepare-statement）
    cursor = connection.cursor()

    cursor.execute("INSERT INTO `med`.`t_temp` (`id`, `desc`, `sql`) VALUES ('18', '1', NULL)")

    cursor.execute("UPDATE `t_temp` SET `desc`='1' WHERE (`id`='18') LIMIT 1")

    opts = cursor.execute("DELETE FROM `t_temp` WHERE (`id`='18')")

    # 获取受影响行数
    print(cursor.rowcount)

    connection.commit()

    connection.close()
except Exception as e:
    connection.rollback()
    print(e)
    raise e
finally:
    if connection.is_connected():
        connection.close()

查询（query）

import mysql.connector

# 连接到MySQL数据库
config = {
    'user': 'root',
    'password': 'root',
    'host': '127.0.0.1',
    'database': 'med'
}

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


try:
    # 创建一个游标对象
    cursor = connection.cursor()

    # 执行一个查询
    query = ("SELECT * FROM t_temp")
    opts = cursor.execute(query)

    # 获取查询结果
    for (column1, column2, column3) in cursor:
        print("{}, {}, {}".format(column1, column2, column3))

    connection.close()
except Exception as e:
    connection.rollback()
    print(e)
    raise e
finally:
    if connection.is_connected():
        connection.close()

结果集中获取字段属性

使用 cursor.description 获取属性，这个属性返回一个列表（列表元素是一个元组）。

每个元组包含以下信息：

• 列的名称（name）
• 列的类型（type_code）
• 一个标识列的可选的显示宽度（display_size）
• 列的数据类型名称（internal_size）
• 小数点后的数字位数（precision）
• 小数点前的数字位数（scale）
• 列是否可以为NULL（nullable）
• 列的列表示（column_type）

SQL 防注入

    cursor.execute("UPDATE `t_temp` SET `desc`=%s WHERE (`id`= %s) LIMIT 1",('OR 1=1', 18))

使用${}作为占位符

直接使用默认的防注入写法，因为很容易错位，实用性不佳，

你可能会想到，能不能用 ${} 作为占位符，比如：

select * from table where id=${id}

源码

如果想在 mysql-connector-python 基础上直接封装，可以参考下列方式。

更简单的做法，是直接使用 SQLAlchemy，这个库已经实现了类似的功能。

# ${} 占位符
pattern = r'\$\{([^}]+)\}'

sql_compile = re.compile(pattern)


# 执行一个 sql
def execute_sql(cursor, sql, map: dict):
    if sql is None:
        raise ValueError('sql cannot be None')

    # 入参，这里需要更强大的参数解析
    in_params = map

    # 所有占位符
    matches = re.findall(pattern, sql)

    # 预编译 sql
    prepared_sql = sql_compile.sub('%s', sql)

    logger.debug('prepared sql: ' + prepared_sql)

    prepared_params = []

    for match in matches:
        val = in_params.get(match)
        if val is None:
            raise ValueError('value is not found: ' + match)
        prepared_params.append(val)

    logger.debug('prepared params: ' + str(prepared_params))

    cursor.execute(prepared_sql, tuple(prepared_params))

使用 ORM 库

常见的有 ORM 库有：SQLAlchemy、Django ORM、SQLObject、Storm、peewee等等。