跳转至

在 Python 中使用 SQL

笔者认为这一节比较重要的是熟悉PyMySQL，传统的MySQL是面向控制台的，不便于执行大批量任务；而PyMySQL是脚本向的，这可能会方便我们写一些相关的MySQL脚本，便于批量自动化执行。

PyMySQL

可以使用 PyMySQL 与 MySQL 数据库进行连接，如 PyMySQL 0.7.2 documentation 中的例子：

Python
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             database='db',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

with connection:
    with connection.cursor() as cursor:
        # Create a new record
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        cursor.execute(sql, ('[email protected]', 'very-secret'))

    # connection is not autocommit by default. So you must commit to save
    # your changes.
    connection.commit()

    with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
        cursor.execute(sql, ('[email protected]',))
        result = cursor.fetchone()
        print(result)

这样的查询会返回如下格式：

Bash
1
{'id': 1, 'password': 'very-secret'}

Previous Query

可以使用如下语句来返回上一条执行的 SQL 语句：

Python
1
cursor._last_executed

在某些 cursor.execute 执行失败的情况下，该值可能不存在。

Note

Cursor(游标) 可以看作是记录中的一个指针

A cursor in the context of database operations is a database object used to retrieve, manipulate, and manage the results of a query. It acts like a pointer that enables you to fetch rows from the result set of a query one by one or all at once.

Example:

If you run a SQL query that returns multiple rows, a cursor helps you navigate through those rows, allowing you to fetch and process them one at a time or in bulk.

Different Cursor

如果要使用一个默认 Cursor 之外的 Cursor，也可以在执行时指定：

Python
1
2
3
with conn.cursor(sql.cursors.DictCursor) as cur:
    cur.execute(f"SELECT * FROM `video_info` WHERE author=%s;", ("alice",))
    res = cur.fetchall()

DictCursor 会将查询结果以 dict 形式返回，使用起来较方便。

详细解读这段指令：

  1. with conn.cursor(sql.cursors.DictCursor) as cur:
    • conn.cursor(...): This creates a cursor object, which you can use to execute SQL commands and retrieve data.
    • sql.cursors.DictCursor: This is a special type of cursor that returns each row as a Python dictionary instead of a tuple. The keys in the dictionary correspond to the column names in the table.
    • with ... as cur:: This is using a context manager (with statement) to ensure that the cursor is automatically closed after the block of code is executed, even if an error occurs.
  2. res = cur.fetchall():
    • cur.fetchall(): This fetches all rows from the result of the query and returns them. Since the cursor is of type DictCursor, each row is returned as a dictionary, with the column names as keys and the corresponding data as values.
    • res: The variable res will hold the list of dictionaries (rows) returned by the query.

Get the value of AUTO INCREMENT

Python
1
cursor.lastrowid

ORM

在实际开发中，通常使用成熟的后端框架来操作数据库，例如 Django 等，在这些框架中，无需手写 SQL 语句，而可以直接使用它的 ORM 框架（或者其他框架）。

ORM 全称为 Objected Relational Mapping，其构造了数据库对象与 Python 对象中的如下映射：

  • TABLE -> Class
  • Record -> Instance（实例）
  • Column -> Attribute（属性）

ORM（对象关系映射）在操作数据库时依然会转换成原生SQL语句，但它具有以下优点：

  1. 简化开发：通过面向对象的方式操作数据库，减少了编写SQL的工作量，提高了代码的可读性和维护性。
  2. 数据库无关性：ORM支持跨数据库操作，便于在不同数据库之间切换。
  3. 安全性：自动处理参数化查询，减少SQL注入风险。
  4. 关系管理：自动处理表之间的关联关系，无需手动编写复杂的连接查询。
  5. 快速迭代：支持快速原型开发，便于在开发过程中频繁修改数据库结构。