16 使用原生的SQL语句操作MySQL数据库

在 Django 中有两种操作 MySQL 数据库的方式，一种是使用原生的 SQL 语句操作 MySQL，另一种方式就是使用 Django 内置的 ORM 模型完成数据库的增删改查操作。后者是 Django 框架的一个的核心模块，它让开发者对数据库的操作更友好和优雅。

1. python 操作 MySQL 数据库

1.1 Python DB-API

在没有 Python DB-API 之前，各数据库之间的应用接口非常混乱，实现各不相同。如果项目需要更换数据库时，则需要在代码层面做大量的修改，使用非常不便，之后 Python DB-API 的出现就是为了解决这样的问题。

Python 的 DB-API，为大多数的数据库实现了接口，使用它连接各数据库后，就可以用相同的方式操作各数据库。这就意味着我们不必区分底层连接的是 MySQL 还是 Oracle等等，可以使用相同的代码来对连接的数据库进行增删改查操作。

DB-API 是一个规范， 它定义了一系列必须的对象和数据库存取方式, 以便为各种各样的底层数据库系统和多种多样的数据库接口程序提供一致的访问接口 。Python 的 DB-API，为大多数的数据库实现了接口，使用它连接各数据库后，就可以用相同的方式操作各数据库。不同的数据库需要下载不同的 DB API 模块，例如我们需要访问 Oracle 数据库和 MySQL 数据库，就需要下载 Oracle 和 MySQL 数据库的 API 模块（也称之为驱动模块）。

Python DB-API 的使用流程如下：

• 引入 API 模块；
• 获取与数据库的连接；
• 执行 SQL 语句和存储过程；
• 关闭数据库连接。

1.2 Python 中常用的 MySQL 驱动模块

Python 中常见的 MySQL 的 驱动模块有：

• MySQLdb: 它是对 C 语言操作 MySQL 数据库的一个简单封装。遵循并实现了 Python DB API v2 协议。但是只支持 Python2, 目前还不支持 Python3；
• mysqlclient: 是 MySQLdb 的另外一个分支。支持 Python3 并且修复了一些 bug；
• pymysql: 纯 Python 实现的一个驱动。因为是纯 Python 编写的，因此执行效率不如前面二者；
• MySQL Connector/Python: MySQL 官方推出的使用纯 Python 连接 MySQL 的驱动。同样是纯 Python 开发的，效率也不高。

其中 mysqlclient 和 pymysql 是在 python 开发中最常使用的 MySQL 驱动模块。而在 Django 内部，我们接下来会看到，它的 ORM 模型其实是在 mysqlclient 基础上再次封装起来的。

1.3 实战 python 操作 MySQL 数据库

这里我们将使用前面提到的 mysqlclient 模块来操作 MySQL 数据库。

第一步安装 mysqlclient 模块：

$ pip3 install mysqlclient -i https://pypi.tuna.tsinghua.edu.cn/simple 

安装好了之后，我们可以在 python 解释器中导入下模块：

[GCC 4.8.5 20150623 (Red Hat 4.8.5-39)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
>>> MySQLdb.__version__
'1.4.6'
>>> 

我们事先准备好了一个 MySQL 服务, 部署在云服务器上。本地安装好 mysql 客户端，然后通过如下方式连接 MySQL 数据库：

[shen@shen ~]$ mysql -h 180.76.152.113  -P 9002 -u store -pstore.123@
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 68920
Server version: 5.7.26 MySQL Community Server (GPL)
  
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
  
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
  
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> 

新建一个数据库，名为 django-manual，然后在该数据库中新建了一个简单的 user 表。接下来我们会使用 mysqlclient 模块对该 user 表中的数据进行增删改查操作：

mysql> create database django_manual  default charset utf8;
Query OK, 1 row affected (0.14 sec)
mysql> use django_manual
Database changed
MySQL [django_manual]> show tables;
Empty set (0.00 sec)
mysql> CREATE TABLE `user` (
    -> `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `name` char(30) NOT NULL,
    -> `password` char(10) NOT NULL,
    -> `email` char(30) NOT NULL,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARSET = utf8;
mysql> show tables;
+-------------------------+
| Tables_in_django_manual |
+-------------------------+
| user                    |
+-------------------------+
1 row in set (0.00 sec)

来看看如和使用 mysqlclient，模块操作数据库 django-manual。

>>> import MySQLdb
>>> conn = MySQLdb.connect(host='180.76.152.113', port=9002, user='store', passwd='store.123@', db='django_manual') # 连接数据库
>>> sql = "insert into user(`name`, `password`, `email`) VALUES (NULL, 'xxxxxx', '222@qq.com')"           # 插入数据的sql语句
>>> cur = conn.cursor()  # 获取游标
>>> cur.execute(sql)     # 执行sql语句
1 
>>> conn.commit()        # 提交操作


# commit 成功后，去另一个窗口查看 mysql 中的数据库数据
mysql > select * from user;
+----+------+----------+------------+
| id | name | password | email      |
+----+------+----------+------------+
| 10 | test | xxxxxx   | 222@qq.com |
+----+------+----------+------------+
1 row in set (0.00 sec)

这里我们可以看到 mysqlclient 模块中的几个常用方法：

• MySQLdb.connect() 方法：连接 mysql 数据库，会在这里输入 mysql 服务地址，开放端口，用户名和密码以及要使用到的数据库名；

• conn.cursor()：创建游标，固定做法；

• cur.execute()：通过游标的 execute() 方法可以执行 sql 语句，其返回值表示的是操作的记录数，比如这里我们新增了一条记录，返回的值为1；

• conn.commit()：对于数据库有更新的动作，比如新增数据、修改数据和删除数据等，最后需要使用 commit() 方法提交动作，而对于查询操作而言则不需要。如果想自动 commit 动作，也是有办法的：

  >>> conn = MySQLdb.connect(...)
  >>> conn.autocommit(True)
  >>> ...
  

上面是新增单条记录，我们也可以新增多条记录，操作如下：

>>> # 在前面的基础上继续执行
>>> conn.autocommit(True)  # 设置自动提交
>>> cur = conn.cursor()
>>> data = (('user%d' % i, 'xxxxxx', '28%d@qq.com' % i) for i in range(10))
>>> cur.executemany('insert into user(`name`, `password`, `email`) VALUES (NULL, %s, %s);', data)
10

# 在另一个窗口，可以看到 user 表中的记录已经有11条了
select count(*) from user;
+----------+
| count(*) |
+----------+
|       11 |
+----------+
1 row in set (0.00 sec)

这里插入多条数据，使用的是游标的 executemany() 方法。如果在插入多条记录中遇到异常，需要执行回滚动作，一般写法如下：

conn = MySQLdb.connect(...)
try:
    # 执行动作
    ...
except Exception as e:
    conn.rollback()

此外，我们一般用到的比较多的是查询相关的操作。这里有游标的方法：

• fetchone()：只取一条记录，然后游标后移一位；
• fetchmany()：取多条记录，参数为获取的记录数，执行完后游标移动相应位置；
• fetchall()：取出 sql 执行的所有记录，游标移动至末尾；

下面我们用前面生成的 11 条记录来进行操作：

>>> # 假设前面已经获得连接信息conn和游标cur
>>> sql = 'select * from user where 1=1 and name like "user%"'
>>> cur.execute(sql)
10
>>> data1 = cur.fetchone()
>>> print(data1)
(11, 'user0', 'xxxxxx', '280@qq.com')
# 看到再次获取一条记录时，取得是下一条数据
>>> data2 = cur.fetchone()
>>> print(data2)
(12, 'user1', 'xxxxxx', '281@qq.com')
# 这次获取5条数据，从user2开始
>>> data3 = cur.fetchmany(5)
>>> print(data3)
((13, 'user2', 'xxxxxx', '282@qq.com'), (14, 'user3', 'xxxxxx', '283@qq.com'), (15, 'user4', 'xxxxxx', '284@qq.com'), (16, 'user5', 'xxxxxx', '285@qq.com'), (17, 'user6', 'xxxxxx', '286@qq.com'))
# 最后用fetchall()方法获取最后的所有数据，还剩下10-1-1-5=3条记录
>>> print(data4)
((18, 'user7', 'xxxxxx', '287@qq.com'), (19, 'user8', 'xxxxxx', '288@qq.com'), (20, 'user9', 'xxxxxx', '289@qq.com'))
# 游标指向最后位置，再次获取时已经没有数据了
>>> data5 = cur.fetchone()
>>> print(data5)
None

通过上面的代码演示，我想我们应该理解游标的作用了，就是每执行一次 fetch 函数，对应的游标会向后移动相应位置。

2. Django 使用原生 SQL 操作 MySQL 数据库

在 Django 中配置数据库驱动以及填写相应信息的位置在 settings.py 文件中的 DATABASE 变量：

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'django_manual',
        'USER': 'store',
        'PASSWORD': 'store.123@',
        'HOST': '180.76.152.113',
        'PORT': '9002',
    }
}

接下来，我们使用 django 自带的 shell 进入交互式模式进行操作。我们同样使用前面已经创建的 user 表和生成的11条数据进行 sql 操作，具体如下：

[root@server ~]# cd django-manual/first_django_app/
[root@server first_django_app]# pyenv activate django-manual 
pyenv-virtualenv: prompt changing will be removed from future release. configure `export PYENV_VIRTUALENV_DISABLE_PROMPT=1' to simulate the behavior.
(django-manual) [root@server first_django_app]# clear
(django-manual) [root@server first_django_app]# python manage.py shell
Python 3.8.1 (default, Dec 24 2019, 17:04:00) 
[GCC 4.8.5 20150623 (Red Hat 4.8.5-39)] on linux
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from django.db import connection
>>> cur = connection.cursor()
>>> cur.execute('select * from user where 1=1 and name like "user%"')
10
>>> data1 = cur.fetchone()
>>> print(data1)
(11, 'user0', 'xxxxxx', '280@qq.com')
>>> data2 = cur.fetchone()
>>> print(data2)
(12, 'user1', 'xxxxxx', '281@qq.com')
>>> data3 = cur.fetchmany(5)
>>> print(data3)
((13, 'user2', 'xxxxxx', '282@qq.com'), (14, 'user3', 'xxxxxx', '283@qq.com'), (15, 'user4', 'xxxxxx', '284@qq.com'), (16, 'user5', 'xxxxxx', '285@qq.com'), (17, 'user6', 'xxxxxx', '286@qq.com'))
>>> data4 = cur.fetchall()
>>> print(data4)
((18, 'user7', 'xxxxxx', '287@qq.com'), (19, 'user8', 'xxxxxx', '288@qq.com'), (20, 'user9', 'xxxxxx', '289@qq.com'))
>>> data5 = cur.fetchone()
>>> print(data5)
None

这里，我们可以看到，在 Django 内部中使用原生的 SQL 操作和我们前面使用 mysqlclient 操作数据库几乎是一模一样，函数接口、返回值以及用法都是一致的。接下来我们可以进入下源码内部一探究竟，看看 Django 内部的 connection 究竟是怎么做的。

# 源码位置 django/db/__init__.py
# 忽略部分代码

DEFAULT_DB_ALIAS = 'default'

# 忽略部分代码

class DefaultConnectionProxy:
    """
    Proxy for accessing the default DatabaseWrapper object's attributes. If you
    need to access the DatabaseWrapper object itself, use
    connections[DEFAULT_DB_ALIAS] instead.
    """
    def __getattr__(self, item):
        return getattr(connections[DEFAULT_DB_ALIAS], item)

    def __setattr__(self, name, value):
        return setattr(connections[DEFAULT_DB_ALIAS], name, value)

    def __delattr__(self, name):
        return delattr(connections[DEFAULT_DB_ALIAS], name)

    def __eq__(self, other):
        return connections[DEFAULT_DB_ALIAS] == other


# For backwards compatibility. Prefer connections['default'] instead.
connection = DefaultConnectionProxy()

...

当我们执行 cur = connection.cursor() 时，其实会执行 __getattr__ 这个魔法函数，我们看到它又去调用connections 这个类实例的 cursor() 方法。我们继续追踪 connections，这个也在 __init__.py 文件中：

# django/db/__init__.py
# ...
connections = ConnectionHandler()
# ...


# django/db/utils.py

# 省略部分代码

class ConnectionHandler:
    def __init__(self, databases=None):
        """
        databases is an optional dictionary of database definitions (structured
        like settings.DATABASES).
        """
        self._databases = databases
        self._connections = local()

    @cached_property
    def databases(self):
        if self._databases is None:
            # 获取settings.DATABASES中的值，并解析相关参数
            self._databases = settings.DATABASES
        if self._databases == {}:
            self._databases = {
                DEFAULT_DB_ALIAS: {
                    'ENGINE': 'django.db.backends.dummy',
                },
            }
        if DEFAULT_DB_ALIAS not in self._databases:
            raise ImproperlyConfigured("You must define a '%s' database." % DEFAULT_DB_ALIAS)
        if self._databases[DEFAULT_DB_ALIAS] == {}:
            self._databases[DEFAULT_DB_ALIAS]['ENGINE'] = 'django.db.backends.dummy'
        return self._databases

    def ensure_defaults(self, alias):
        """
        Put the defaults into the settings dictionary for a given connection
        where no settings is provided.
        """
        try:
            conn = self.databases[alias]
        except KeyError:
            raise ConnectionDoesNotExist("The connection %s doesn't exist" % alias)

        conn.setdefault('ATOMIC_REQUESTS', False)
        conn.setdefault('AUTOCOMMIT', True)
        conn.setdefault('ENGINE', 'django.db.backends.dummy')
        if conn['ENGINE'] == 'django.db.backends.' or not conn['ENGINE']:
            conn['ENGINE'] = 'django.db.backends.dummy'
        conn.setdefault('CONN_MAX_AGE', 0)
        conn.setdefault('OPTIONS', {})
        conn.setdefault('TIME_ZONE', None)
        for setting in ['NAME', 'USER', 'PASSWORD', 'HOST', 'PORT']:
            conn.setdefault(setting, '')

    # 省略部分方法

    def __getitem__(self, alias):
        if hasattr(self._connections, alias):
            return getattr(self._connections, alias)

        self.ensure_defaults(alias)
        self.prepare_test_settings(alias)
        db = self.databases[alias]
        # 使用mysql引擎
        backend = load_backend(db['ENGINE'])
        conn = backend.DatabaseWrapper(db, alias)
        setattr(self._connections, alias, conn)
        return conn

    # 忽略部分代码

# 忽略部分代码      

这里最核心的地方在于这个__getitem__()魔法函数。首先我们在前面的 connection 中调用 __gatattr__ 魔法函数，而该函数中又使用了 connections[DEFAULT_DB_ALIAS] 这样的操作，这个操作又会调用 __getitem__ 魔法函数。

 def __getattr__(self, item):
     return getattr(connections[DEFAULT_DB_ALIAS], item)

来重点看__getitem__()这个魔法函数：

def __getitem__(self, alias):
    if hasattr(self._connections, alias):
        return getattr(self._connections, alias)

    self.ensure_defaults(alias)
    self.prepare_test_settings(alias)
    db = self.databases[alias]
    # 使用mysql引擎
    backend = load_backend(db['ENGINE'])
    conn = backend.DatabaseWrapper(db, alias)
    setattr(self._connections, alias, conn)
    return conn

注意：代码首先是要获取 settings.py 中关于数据库的配置，注意我们前面设置的 db[‘ENGINE’] 的值为：django.db.backends.mysql，下面的 load_backend() 方法只是一个简单的导入模块，最核心的就是一句：import_module('%s.base' % backend_name)，相当于导入了模块 django.db.backends.mysql.base：

def load_backend(backend_name):
    """
    Return a database backend's "base" module given a fully qualified database
    backend name, or raise an error if it doesn't exist.
    """
    # This backend was renamed in Django 1.9.
    if backend_name == 'django.db.backends.postgresql_psycopg2':
        backend_name = 'django.db.backends.postgresql'

    try:
        # 最核心的部分
        return import_module('%s.base' % backend_name)
    except ImportError as e_user:
        # 异常处理，代码省略
        ...

在前面导入的 django.db.backends.mysql.base文件中，我们可以看到如下代码段：

# 源码位置 django/db/backends/mysql/base.py

try:
    import MySQLdb as Database
except ImportError as err:
    raise ImproperlyConfigured(
        'Error loading MySQLdb module.n'
        'Did you install mysqlclient?'
    ) from err
    
# ...


class DatabaseWrapper(BaseDatabaseWrapper):
    # ...
    
    Database = Database
    
    # ...
    
    def get_new_connection(self, conn_params):
        return Database.connect(**conn_params)
    
    # ...
    
    
# 源码位置 django/db/backends/base/base.py
# ...

class BaseDatabaseWrapper:
    # ...
    
    def connect(self):
        """Connect to the database. Assume that the connection is closed."""
        # Check for invalid configurations.
        ...
        # Establish the connection
        conn_params = self.get_connection_params()
        ############ 注意，这里的连接会调用下面这个方法得到 ######################
        self.connection = self.get_new_connection(conn_params)
        ####################################################################
        ...

    # ...

其实从我简化的代码来看，可以看到在 Django 中，对于 MySQL 数据库的连接来说，使用的就是 python 中的 mysqlclient 模块，只不过 Django 在 mysqlclient 基础上又封装了一层，包括里面定义的游标，以及游标的方法都是 mysqlclient 中的函数。后面再介绍 Django 的内置 ORM 模型时候，我们会继续分析这个 mysql 引擎目录下的源码，看 Django 如何一步步封装 mysqlcient 进而实现自己内置的 ORM 模型。

3. 小结

本小节中我们介绍了 Python DB-API 相关概念， 然后介绍了 Python 中操作 MySQL 常用第三方模块，并以 pymysql 为例对数据库进行了增删改查操作。接下来介绍了在 Django 中如何使用原生的 SQL 语句来操作 MySQL 数据库并进行了代码演示和说明，此外还追踪了部分源码，发现其内部实现机制就是在 mysqlcient 上做的二次封装。接下来，我将继续为大家介绍 Django 中操作数据库的常用方式-基于内嵌的 ORM 模型。