在Django中使用MySQL

在Django中使用MySQL,有三种方式,一种是通过原始的DB Driver提供的接口来操作数据库;一种是以Django的方式执行raw SQL;另外一种是通过Django提供的ORM。这些方式有什么异同呢?本文就来比较一下。

数据库连接

在之前的文章《MySQL数据库连接的相关探索》
已经详解了使用原始DB Driver如何进行数据库连接,下面只取其中一例来展示。
这里展示了通过使用DBUtils库建立了一个persistent connection。通过persistent connection我们可以保持这个连接,而减少频繁建立/断开连接的开销。

#!/usr/bin/env python
# -*- coding: utf-8 -*-

"""
mysqlconn.py
~~~~~~~~~~~~
test mysql connection related functions.

:copyright: (c) 2019 by Geekpy.

"""
import MySQLdb
import MySQLdb.cursors as cursors  # 这里必须import, 单独引入MySQLdb无法使用DictCursor
import time
import threading
import os
# 注意这里PersistentDB是在PersistentDB Module当中
from DBUtils.PersistentDB import PersistentDB
from DBUtils.PooledDB import PooledDB
import sys

db_config = {
    'host': 'localhost',
    'port': 3306,
    'db': 'test',
    'user': 'root',
    'password': 'testpassword'
}

db_persis = PersistentDB(
    # creator即你使用的db driver
    creator=MySQLdb,

    # 如果在支持threading.local的环境下可以使用如下配置方式,性能更好
    threadlocal=threading.local,
    
    # 此项参数是传递给MySQLdb的connection方法的,也就是说MySQLdb允许的参数都可以在这里传递
    autocommit=False,  

    #此项参数同上也会传递给connection方法,它会使得我们fetch数据时返回的是dict类型的数据
    cursorclass=cursors.DictCursor,

    **db_config
)


def test_with_dbutils_persistent_conn():
    print('begin connecting to mysql')
    conn = db_persis.connection()

    sql = "update task set task1=1 where user_id='12345666"
    with conn.cursor() as cursor:
        cursor.execute(sql)

    # 这里close并没有真正关闭数据库的connection
    # 而是被PersistentDB回收
    conn.close()
    sys.exit()


if __name__ == '__main__':
    test_with_dbutils_persistent_conn()

而在Django中如果使用ORM,其数据库连接是自动建立的,但是需要我们在settings.py中进行设置

# settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': os.environ['DB_NAME'],
        'USER': os.environ['DB_USERNAME'],
        'PASSWORD': os.environ['DB_PASSWORD'],
        'CONN_MAX_AGE': 60,
        'HOST': os.environ['DB_HOST'],
        'PORT': os.environ['DB_PORT'],
        'OPTIONS': {'charset': 'utf8mb4'},
    },

    'app1_master': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'db_name',
        'USER': 'username',
        'PASSWORD': 'dbpassword',
        'CONN_MAX_AGE': 60,
        'HOST': 'db_master_host_address',
        'PORT': 3306,
        'OPTIONS': {'charset': 'utf8mb4'},
    },

    'app1_slave': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'db_name',
        'USER': 'username',
        'PASSWORD': 'dbpassword',
        'CONN_MAX_AGE': 60,
        'HOST': 'db_slave_host_address',
        'PORT': 3306,
        'OPTIONS': {'charset': 'utf8mb4'},
    },
    ...
}

# 这里针对DB的路由器的设置
DATABASE_ROUTERS = ['yourproject.routers.MasterSlaveDatabaseRouter']

# 这里设置DB mapping,会在路由器中用到
DATABASE_APPS_MAPPING = {
    'yourapplabel_master': 'app1_master',
    'yourapplabel_slave': 'app1_slave',
}

然后我们再来看下在routers.py中是如何进行DB路由的。

from django.conf import settings

DATABASE_MAPPING = settings.DATABASE_APPS_MAPPING


class MasterSlaveDatabaseRouter:
    """
    A router to control all database operations on models for different
    databases.

    In case an app is not set in settings.DATABASE_APPS_MAPPING, the router
    will fallback to the `default` database.

    Settings example:

    DATABASE_APPS_MAPPING = {'app1': 'db1', 'app2': 'db2'}
    """

    def db_for_read(self, model, **hints):
        """"Point all read operations to the specific database."""
        # 根据model的app_label来生成一个key,然后通过这个key从settings.py中定义的DATABASE_MAPPING
        # 来获取对应的Database Name,这个Database Name就是我们在settings中DATABASES中定义的数据库
        return DATABASE_MAPPING.get(f'{model._meta.app_label}_slave', 'default')

    def db_for_write(self, model, **hints):
        """Point all write operations to the specific database."""
        return DATABASE_MAPPING.get(f'{model._meta.app_label}_master', 'default')

    def allow_relation(self, obj1, obj2, **hints):
        """Allow any relation between apps that use the same database."""
        db1 = DATABASE_MAPPING.get(obj1._meta.app_label)
        db2 = DATABASE_MAPPING.get(obj2._meta.app_label)
        if db1 and db2:
            return db1 == db2
        return None

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        if db in DATABASE_MAPPING.values():
            label = "_".join([app_label, "master"])
            return DATABASE_MAPPING.get(label) == db
        elif app_label in DATABASE_MAPPING:
            return False
        return None

这样设置完成之后,我们在使用ORM进行数据库操作时就会自动选取对应的DB,并建立连接,完成相应的操作。需要注意的是当我们在settings.py中设置了CONN_MAX_AGE时(为正数或者为None), Django会自动帮我们创建一个persistent connection,这个connection保持的时间就是由CONN_MAX_AGE来设定的。

但是,有时我们并不想(或不能)通过ORM来进行数据库操作,这时,就需要我们手动获取db connection。在Django下获取db connection有两种方式:

# connection使用的是default db,当我们需要制定不同的db时,需要使用connections
from django.db import connection
from django.db import connections

sql = "select name from user_table where id=2'
with connection.cursor() as cursor:
    cursor.execute(sql)
    user = cursor.fetchone()
    print(user['name'])

# 当我们要指定使用的db时,需要通过connections来获取对应的db connection
conn = connections['user_db']
with conn.cursor() as cursor:
    cursor.execute(sql)
    user = cursor.fetchone()
    print(user['name'])

增删改查

当我们使用cursor来执行SQL语句时(无论是原生的db driver,还是Django的db connection),我们只需要按照SQL语法来实现增删改查即可,唯一需要注意的有两点:
1,在Django中autocommit默认是打开的,而在原生的db driver中默认是关闭的;
2,我们应当尽可能使用parameterized query来进行这些操作,这是由于其使用了prepared statement技术,使得数据库操作具有以下几个优点:

  • 只需要预编译一次,之后不再需要预编译,从而提高了性能
  • 防止了SQL注入

示例, 比较两种不同的用法:

def test_without_parameterized_query():
    print('begin connecting to mysql')
    conn = db_persis.connection()
    sql = "select * from nb_task where user_id='123456'"
    with conn.cursor() as cursor:
        cursor.execute(sql)
        nb = cursor.fetchone()
        print(nb['earned'])

    conn.close()

# 推荐使用此种方法
def test_with_parameterized_query():
    print('begin connecting to mysql')
    conn = db_persis.connection()

    # parameterized query使用%s作为占位符
    sql = "select * from nb_task where user_id=%s"
    with conn.cursor() as cursor:
        user_id = '123456'
        cursor.execute(sql, (user_id,))
        nb = cursor.fetchone()
        print(nb['earned'])

    conn.close()

所有增删改查操作都应尽量使用parameterized query技术。

我们也可以使用Django的connection以及connections来执行SQL操作,需要注意的是使用Django的connection或者connections时,其autocommit默认是打开的(即为True),这跟DB API 2.0规范是不同的。

from django.db import connection, connections

# connection使用的是'default' database
with connection.cursor as c:
    sql = """select * from user where uid=%s"""
    c.execute(sql, ('12345', ))

# 使用connections可以根据settings中的设置选择对应的db
with connections['another_db'] as c:
    sql = """select * from company where cid=%s"""
    c.execute(sql, ('12345', ))

下面再来看下,通过ORM进行增删改查是什么样子:

# ----增-----
from testapp.models import TestModel

# 通过create方式
TestModel.objects.create(name="John", point=33)

# 通过save方式
t = TestModel(name="John", point=33)
t.save()
# ----删-----
from testapp.models import TestModel

TestModel.objects.get(id=1).delete()
# ----改-----
# 改有两种方式,一种通过update方法,一种是属性赋值后再save
# 注意直接用model对象是无法update的
# 必须用filter,返回的QuerySet可以update
>>> TestModel.objects.filter(id=2).update(name="Jenny")
1
>>> TestModel.objects.get()
<TestModel: Jenny>

>>> u = TestModel.objects.get()
>>> u.name = 'Eric'
>>> u.save()
>>> u.name
'Eric'
# ----查-----
# 查也有两种方式
>>> from testapp.models import TestModel

# 注意过滤条件使用的是'=',而不是'=='
>>> u = TestModel.objects.get(id=2) 
>>> type(u)
<class 'testapp.models.TestModel'>

# 通过filter获取的是一个QuerySet类型
>>> l = TestModel.objects.filter(id=2)
>>> type(l)
<class 'django.db.models.query.QuerySet'>

# 获得所有的记录
>>> all = TestModel.objects.all()

# 排除某些条件的记录后返回剩下的记录
>>> exc = TestModel.objects.exclude(id=2)

这里简单说下get和filter的区别:

  • get只返回一行数据;filter可以返回所有符合条件的数据
  • get直接返回Model对象;filter返回QuerySet,set中包含model对象
  • get如果没有找到对应的数据会raise exception;而filter不会报错,只返回一个空的QuerySet

事务

使用MySQL我们经常会使用事务,通过事务我们可以实现数据库的ACID。Python使用事务有多种方法,各不相同,非常容易混淆,下面我们就各种不同使用事务的方式进行分别说明:

通过原生的MySQLdb

首先,我们要明白,使用原生的MySQLdb driver进行事务时,必须要将autocommit设为False,这时由于当autocommit打开时,每次执行execute数据库操作时都会作为一个事务自动提交,从而无法将我们的一系列操作放在一个事务当中。

默认情况下,MySQLdb的autocommit是关闭的,我们也可以通过connection对象的get_autocommit()来获取当前connection的autocommit状态。如果我们想要确保autocommit是关闭状态,可以有两种方式来改变autocommit状态,如下所示。

import MySQLdb

db_config = {
    'host': 'localhost',
    'port': 3306,
    'db': 'test',
    'user': 'root',
    'password': 'Test'
}
# 可以通过autocommit参数来设置autocommit状态
conn = MySQLdb.Connection(autocommit=False, **db_config)
print(conn.get_autocommit())  # False
conn.autocommit(True)  # 我们也可以通过autocommit()来改变autocommit状态
print(conn.get_autocommit())  # True

# 网上有人说可以通过autocommit属性来设置,亲测不行
conn.autocommit = False
print(conn.get_autocommit())  # True

当我们确保autocommit为False后,我们就可以使用事务,简单说就是在执行一系列操作的过程中不去commit,而在这一系列操作的最后再去commit。如下:

conn = MySQLdb.Connection(**db_config)
conn.autocommit(False)
try:
    with conn.cursor() as cursor:
        insert_sql = "insert into user(id, name) values(1, 'Eric')"
        cursor.execute(insert_sql)
        update_sql = "update user set name='John' where id=1"
        cursor.execute(update_sql)
        conn.commit()  # 在事务的最后提交
except:
    conn.rollback()  # 当发生异常时,全部回滚
finally:
    conn.close()

通过DBUtils

DBUtils在执行事务时,需要先调用begin()来开启事务。另外,DBUtils不支持autocommit()方法,我们只能在参数中设置autocommit

db_persis = PersistentDB(
    creator=MySQLdb,
    threadlocal=threading.local,
    autocommit=False,    # 可以在参数中设置autocommit, 默认就是False,所以也可以不设
    cursorclass=cursors.DictCursor,
    **db_config
)
conn = db_persis.connection()
try:
    conn.begin()    # 必须显式地调用begin()
    with conn.cursor() as cursor:
        insert_sql = "insert into user(id, name) values(1, 'Eric')"
        cursor.execute(insert_sql)
        update_sql = "update user set name='John' where id=1"
        cursor.execute(update_sql)
        conn.commit()  # 在事务的最后提交
except:
    conn.rollback()  # 当发生异常时,全部回滚
finally:
    conn.close()

使用Django原生的connection

与原生MySQLdb相比较,主要的区别就在于需要将autocommit设为False。

>> from django.db.transaction import get_autocommit, set_autocommit
>> get_autocommit()  # 返回'default'数据库的autocommit状态
>> get_autocommit(using='db_name')  # 返回指定数据库的autocommit状态
>> set_autocommit(False)  # 设置'default'数据库的autocommit状态
>> set_autocommit(False, using='db_name')  # 设置指定数据库的autocommit状态

当我们将autocommit状态设置为False之后,我们就可以像MySQLdb的connection那样去进行事务处理了。

使用Django的atomic装饰器

在Django中很多时候我们使用的ORM进行数据库操作,这时我们一般是通过atomic装饰器来执行事务。
关于这点,可以参考Django官网的说明

References

推荐阅读更多精彩内容

  • 转载,觉得这篇写 SQLAlchemy Core,写得非常不错。不过后续他没写SQLAlchemy ORM... ...
    非梦nj阅读 3,728评论 1 14
  • Django 准备 “虚拟环境为什么需要虚拟环境:到目前位置,我们所有的第三方包安装都是直接通过 pip inst...
    33jubi阅读 950评论 0 5
  • 一、简介 Django 模型是与数据库相关的,与数据库相关的代码一般写在 models.py 中,Django 支...
    唯老阅读 303评论 0 6
  • 江南繁花盛开的春和满目苍翠的秋,是人们由然而然走出家门,怡情山水,畅享自然的好季节。 爽风拂面,碧空如洗,万水澄明...
    立幺逸文阅读 336评论 4 25
  • 这周练习让我感受最深刻的是克服恐惧这一天的练习。 "恐惧会碍阻我们去爱自已、原谅他人、也让我们无法过上自己理应拥有...
    夜廊小薇阅读 99评论 0 0