django操作mysql_在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()

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

# 通过filter获取的是一个QuerySet类型

>>> l = TestModel.objects.filter(id=2)

>>> type(l)

# 获得所有的记录

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