在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官网的说明
网友评论