Django 使用原生的 SQL 語句操作 MySQL 數(shù)據(jù)庫(kù)

在 Django 中有兩種操作 MySQL 數(shù)據(jù)庫(kù)的方式，一種是使用原生的 SQL 語句操作 MySQL，另一種方式就是使用 Django 內(nèi)置的 ORM 模型完成數(shù)據(jù)庫(kù)的增刪改查操作。后者是 Django 框架的一個(gè)的核心模塊，它讓開發(fā)者對(duì)數(shù)據(jù)庫(kù)的操作更友好和優(yōu)雅。

1. python 操作 MySQL 數(shù)據(jù)庫(kù)

1.1 Python DB-API

在沒有 Python DB-API 之前，各數(shù)據(jù)庫(kù)之間的應(yīng)用接口非常混亂，實(shí)現(xiàn)各不相同。如果項(xiàng)目需要更換數(shù)據(jù)庫(kù)時(shí)，則需要在代碼層面做大量的修改，使用非常不便，之后 Python DB-API 的出現(xiàn)就是為了解決這樣的問題。

Python 的 DB-API，為大多數(shù)的數(shù)據(jù)庫(kù)實(shí)現(xiàn)了接口，使用它連接各數(shù)據(jù)庫(kù)后，就可以用相同的方式操作各數(shù)據(jù)庫(kù)。這就意味著我們不必區(qū)分底層連接的是 MySQL 還是 Oracle等等，可以使用相同的代碼來對(duì)連接的數(shù)據(jù)庫(kù)進(jìn)行增刪改查操作。

DB-API 是一個(gè)規(guī)范， 它定義了一系列必須的對(duì)象和數(shù)據(jù)庫(kù)存取方式, 以便為各種各樣的底層數(shù)據(jù)庫(kù)系統(tǒng)和多種多樣的數(shù)據(jù)庫(kù)接口程序提供一致的訪問接口 。Python 的 DB-API，為大多數(shù)的數(shù)據(jù)庫(kù)實(shí)現(xiàn)了接口，使用它連接各數(shù)據(jù)庫(kù)后，就可以用相同的方式操作各數(shù)據(jù)庫(kù)。不同的數(shù)據(jù)庫(kù)需要下載不同的 DB API 模塊，例如我們需要訪問 Oracle 數(shù)據(jù)庫(kù)和 MySQL 數(shù)據(jù)庫(kù)，就需要下載 Oracle 和 MySQL 數(shù)據(jù)庫(kù)的 API 模塊（也稱之為驅(qū)動(dòng)模塊）。

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

• 引入 API 模塊；
• 獲取與數(shù)據(jù)庫(kù)的連接；
• 執(zhí)行 SQL 語句和存儲(chǔ)過程；
• 關(guān)閉數(shù)據(jù)庫(kù)連接。

1.2 Python 中常用的 MySQL 驅(qū)動(dòng)模塊

Python 中常見的 MySQL 的 驅(qū)動(dòng)模塊有：

• MySQLdb: 它是對(duì) C 語言操作 MySQL 數(shù)據(jù)庫(kù)的一個(gè)簡(jiǎn)單封裝。遵循并實(shí)現(xiàn)了 Python DB API v2 協(xié)議。但是只支持 Python2, 目前還不支持 Python3；
• mysqlclient: 是 MySQLdb 的另外一個(gè)分支。支持 Python3 并且修復(fù)了一些 bug；
• pymysql: 純 Python 實(shí)現(xiàn)的一個(gè)驅(qū)動(dòng)。因?yàn)槭羌?Python 編寫的，因此執(zhí)行效率不如前面二者；
• MySQL Connector/Python: MySQL 官方推出的使用純 Python 連接 MySQL 的驅(qū)動(dòng)。同樣是純 Python 開發(fā)的，效率也不高。

其中 mysqlclient 和 pymysql 是在 python 開發(fā)中最常使用的 MySQL 驅(qū)動(dòng)模塊。而在 Django 內(nèi)部，我們接下來會(huì)看到，它的 ORM 模型其實(shí)是在 mysqlclient 基礎(chǔ)上再次封裝起來的。

1.3 實(shí)戰(zhàn) python 操作 MySQL 數(shù)據(jù)庫(kù)

這里我們將使用前面提到的 mysqlclient 模塊來操作 MySQL 數(shù)據(jù)庫(kù)。

第一步安裝 mysqlclient 模塊：

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

安裝好了之后，我們可以在 python 解釋器中導(dǎo)入下模塊：

[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'
>>> 

我們事先準(zhǔn)備好了一個(gè) MySQL 服務(wù), 部署在云服務(wù)器上。本地安裝好 mysql 客戶端，然后通過如下方式連接 MySQL 數(shù)據(jù)庫(kù)：

[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> 

新建一個(gè)數(shù)據(jù)庫(kù)，名為 django-manual，然后在該數(shù)據(jù)庫(kù)中新建了一個(gè)簡(jiǎn)單的 user 表。接下來我們會(huì)使用 mysqlclient 模塊對(duì)該 user 表中的數(shù)據(jù)進(jìn)行增刪改查操作：

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，模塊操作數(shù)據(jù)庫(kù) django-manual。

>>> import MySQLdb
>>> conn = MySQLdb.connect(host='180.76.152.113', port=9002, user='store', passwd='store.123@', db='django_manual') # 連接數(shù)據(jù)庫(kù)
>>> sql = "insert into user(`name`, `password`, `email`) values ('test', 'xxxxxx', '222@qq.com')"           # 插入數(shù)據(jù)的sql語句
>>> cur = conn.cursor()  # 獲取游標(biāo)
>>> cur.execute(sql)     # 執(zhí)行sql語句
1 
>>> conn.commit()        # 提交操作


# commit 成功后，去另一個(gè)窗口查看 mysql 中的數(shù)據(jù)庫(kù)數(shù)據(jù)
mysql > select * from user;
+----+------+----------+------------+
| id | name | password | email      |
+----+------+----------+------------+
| 10 | test | xxxxxx   | 222@qq.com |
+----+------+----------+------------+
1 row in set (0.00 sec)

這里我們可以看到 mysqlclient 模塊中的幾個(gè)常用方法：

• MySQLdb.connect() 方法：連接 mysql 數(shù)據(jù)庫(kù)，會(huì)在這里輸入 mysql 服務(wù)地址，開放端口，用戶名和密碼以及要使用到的數(shù)據(jù)庫(kù)名；

• conn.cursor()：創(chuàng)建游標(biāo)，固定做法；

• cur.execute()：通過游標(biāo)的 execute() 方法可以執(zhí)行 sql 語句，其返回值表示的是操作的記錄數(shù)，比如這里我們新增了一條記錄，返回的值為1；

• conn.commit()：對(duì)于數(shù)據(jù)庫(kù)有更新的動(dòng)作，比如新增數(shù)據(jù)、修改數(shù)據(jù)和刪除數(shù)據(jù)等，最后需要使用 commit() 方法提交動(dòng)作，而對(duì)于查詢操作而言則不需要。如果想自動(dòng) commit 動(dòng)作，也是有辦法的：

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

上面是新增單條記錄，我們也可以新增多條記錄，操作如下：

>>> # 在前面的基礎(chǔ)上繼續(xù)執(zhí)行
>>> conn.autocommit(True)  # 設(shè)置自動(dòng)提交
>>> 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 (%s, %s, %s);', data)
10

# 在另一個(gè)窗口，可以看到 user 表中的記錄已經(jīng)有11條了
select count(*) from user;
+----------+
| count(*) |
+----------+
|       11 |
+----------+
1 row in set (0.00 sec)

這里插入多條數(shù)據(jù)，使用的是游標(biāo)的 executemany() 方法。如果在插入多條記錄中遇到異常，需要執(zhí)行回滾動(dòng)作，一般寫法如下：

conn = MySQLdb.connect(...)
try:
    # 執(zhí)行動(dòng)作
    ...
except Exception as e:
    conn.rollback()

此外，我們一般用到的比較多的是查詢相關(guān)的操作。這里有游標(biāo)的方法：

• fetchone()：只取一條記錄，然后游標(biāo)后移一位；
• fetchmany()：取多條記錄，參數(shù)為獲取的記錄數(shù)，執(zhí)行完后游標(biāo)移動(dòng)相應(yīng)位置；
• fetchall()：取出 sql 執(zhí)行的所有記錄，游標(biāo)移動(dòng)至末尾；

下面我們用前面生成的 11 條記錄來進(jìn)行操作：

>>> # 假設(shè)前面已經(jīng)獲得連接信息conn和游標(biāo)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')
# 看到再次獲取一條記錄時(shí)，取得是下一條數(shù)據(jù)
>>> data2 = cur.fetchone()
>>> print(data2)
(12, 'user1', 'xxxxxx', '281@qq.com')
# 這次獲取5條數(shù)據(jù)，從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()方法獲取最后的所有數(shù)據(jù)，還剩下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'))
# 游標(biāo)指向最后位置，再次獲取時(shí)已經(jīng)沒有數(shù)據(jù)了
>>> data5 = cur.fetchone()
>>> print(data5)
None

通過上面的代碼演示，我想我們應(yīng)該理解游標(biāo)的作用了，就是每執(zhí)行一次 fetch 函數(shù)，對(duì)應(yīng)的游標(biāo)會(huì)向后移動(dòng)相應(yīng)位置。

2. Django 使用原生 SQL 操作 MySQL 數(shù)據(jù)庫(kù)

在 Django 中配置數(shù)據(jù)庫(kù)驅(qū)動(dòng)以及填寫相應(yīng)信息的位置在 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 進(jìn)入交互式模式進(jìn)行操作。我們同樣使用前面已經(jīng)創(chuàng)建的 user 表和生成的11條數(shù)據(jù)進(jìn)行 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 內(nèi)部中使用原生的 SQL 操作和我們前面使用 mysqlclient 操作數(shù)據(jù)庫(kù)幾乎是一模一樣，函數(shù)接口、返回值以及用法都是一致的。接下來我們可以進(jìn)入下源碼內(nèi)部一探究竟，看看 Django 內(nèi)部的 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()

...

當(dāng)我們執(zhí)行 cur = connection.cursor() 時(shí)，其實(shí)會(huì)執(zhí)行 __getattr__ 這個(gè)魔法函數(shù)，我們看到它又去調(diào)用connections 這個(gè)類實(shí)例的 cursor() 方法。我們繼續(xù)追蹤 connections，這個(gè)也在 __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中的值，并解析相關(guān)參數(shù)
            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

    # 忽略部分代碼

# 忽略部分代碼      

這里最核心的地方在于這個(gè)__getitem__()魔法函數(shù)。首先我們?cè)谇懊娴?connection 中調(diào)用 __gatattr__ 魔法函數(shù)，而該函數(shù)中又使用了 connections[DEFAULT_DB_ALIAS] 這樣的操作，這個(gè)操作又會(huì)調(diào)用 __getitem__ 魔法函數(shù)。

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

來重點(diǎn)看__getitem__()這個(gè)魔法函數(shù)：

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 中關(guān)于數(shù)據(jù)庫(kù)的配置，注意我們前面設(shè)置的 db[‘ENGINE’] 的值為：django.db.backends.mysql，下面的 load_backend() 方法只是一個(gè)簡(jiǎn)單的導(dǎo)入模塊，最核心的就是一句：import_module('%s.base' % backend_name)，相當(dāng)于導(dǎo)入了模塊 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:
        # 異常處理，代碼省略
        ...

在前面導(dǎo)入的 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()
        ############ 注意，這里的連接會(huì)調(diào)用下面這個(gè)方法得到 ######################
        self.connection = self.get_new_connection(conn_params)
        ####################################################################
        ...

    # ...

其實(shí)從我簡(jiǎn)化的代碼來看，可以看到在 Django 中，對(duì)于 MySQL 數(shù)據(jù)庫(kù)的連接來說，使用的就是 python 中的 mysqlclient 模塊，只不過 Django 在 mysqlclient 基礎(chǔ)上又封裝了一層，包括里面定義的游標(biāo)，以及游標(biāo)的方法都是 mysqlclient 中的函數(shù)。后面再介紹 Django 的內(nèi)置 ORM 模型時(shí)候，我們會(huì)繼續(xù)分析這個(gè) mysql 引擎目錄下的源碼，看 Django 如何一步步封裝 mysqlcient 進(jìn)而實(shí)現(xiàn)自己內(nèi)置的 ORM 模型。

3. 小結(jié)

本小節(jié)中我們介紹了 Python DB-API 相關(guān)概念， 然后介紹了 Python 中操作 MySQL 常用第三方模塊，并以 pymysql 為例對(duì)數(shù)據(jù)庫(kù)進(jìn)行了增刪改查操作。接下來介紹了在 Django 中如何使用原生的 SQL 語句來操作 MySQL 數(shù)據(jù)庫(kù)并進(jìn)行了代碼演示和說明，此外還追蹤了部分源碼，發(fā)現(xiàn)其內(nèi)部實(shí)現(xiàn)機(jī)制就是在 mysqlcient 上做的二次封裝。接下來，我將繼續(xù)為大家介紹 Django 中操作數(shù)據(jù)庫(kù)的常用方式-基于內(nèi)嵌的 ORM 模型。