django中F函数的应用

mysite/feed/models.py

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

from django.db import models
from django.db.models import F
    
class Account(models.Model):
    """ 账户"""
    balance = models.DecimalField('余额', max_digits=19, decimal_places=4)
    version = models.IntegerField('版本号')
    created = models.DateTimeField('创建时间', auto_now_add=True)
    updated = models.DateTimeField('最后修改时间', auto_now=True)

    def add(self, amount):
        """ 增加金额"""
        self.balance += amount
        self.save()
        
        self = self.__class__.objects.get(pk=self.id)

        TradeRecord(
            account=self,
            amount=amount,
            balance=self.balance,
        ).save()
        return self.balance
    
    def f_add(self, amount):
        """ F函数"""
        self.balance = F('balance') + amount
        self.save()
        
        self = self.__class__.objects.get(pk=self.id)

        TradeRecord(
            account=self,
            amount=amount,
            balance=self.balance,
        ).save()
        return self.balance
        
    def lock_add(self, amount):
        """ 乐观锁"""
        self.balance += amount
        self.version += 1

        new_self = self.__class__.objects.get(pk=self.id)
        print self.version, new_self.version
        if self.version > new_self.version:
            self.save()
            
            TradeRecord(
                account=self,
                amount=amount,
                balance=self.balance,
            ).save()
            return self.balance
        return False
        
class TradeRecord(models.Model):
    """ 交易明细"""
    account = models.ForeignKey(Account)
    amount = models.DecimalField('交易金额', max_digits=19, decimal_places=4)
    balance = models.DecimalField('交易后余额', max_digits=19, decimal_places=4)
    created = models.DateTimeField('创建时间', auto_now_add=True)
    updated = models.DateTimeField('最后修改时间', auto_now=True)

终端1：

>>> from mysite.feed.models import *
>>> account = Account.objects.get(pk=1)
>>> account.balance
Decimal("9.0000")
>>> account.add(1)
Decimal("10.0000")
>>> account.balance
Decimal("10.0000")
>>> 

终端2：

>>> from mysite.feed.models import *
>>> account = Account.objects.get(pk=1)
>>> account.balance
Decimal("10.0000")
>>> account.add(1)
Decimal("11.0000")
>>> account.balance
Decimal("11.0000")
>>> 

终端1：

>>> account.balance
Decimal("10.0000")
>>> account.add(1)
Decimal("11.0000")
>>> account.balance
Decimal("11.0000")
>>> 

初始值为9，在终端1进行了两次加1，终端2进行了一次加1操作，期望值为12，实际值为11。因为在终端1进行第二次加1操作时，余额已经读出为10.

使用F函数

终端2：

>>> from django.db.models import F
>>> account.balance
Decimal("11.0000")
>>> account.balance = F('balance') + 1
>>> account.save()
>>> account.balance
<django.db.models.expressions.ExpressionNode object at 0x02984850>
>>> account = Account.objects.get(pk=1)
>>> account.balance
Decimal("12.0000")
>>> 

终端1：

>>> from django.db.models import F
>>> account.balance
Decimal("11.0000")
>>> account.balance = F('balance') + 1
>>> account.save()
>>> account.balance
<django.db.models.expressions.ExpressionNode object at 0x029BB8B0>
>>> account = Account.objects.get(pk=1)
>>> account.balance
Decimal("13.0000")
>>> 

在终端1执行时，虽然已经读出balance的值为11，但是加1后的结果仍然是13。可以看出使用F函数进行累加，在执行Save时，始终会去取数据库中最新的值，而不是已经取出的值。所以我们可以用F函数改写add函数，来保证并发执行时数据的正确性。

我们可以看到Django ORM在处理这两种累加在实际执行的SQL语句上的差异。

from django.db.models import F
from mysite.feed.models import *

account = Account.objects.get(pk=1)
account
<Account: Account object>
account.balance
Decimal("0.0000")
from django.db import connection
connection.queries

[{'time': '0.000', 'sql': u'SELECT `feed_account`.`id`, `feed_account`.`balance`, `feed_account`.`version`, `feed_account`.`created`, `feed_account`.`updated` FROM `feed_account` WHERE `feed_account`.`id` = 1 '}]

account.balance += 1
connection.queries

[{'time': '0.000', 'sql': u'SELECT `feed_account`.`id`, `feed_account`.`balance`, `feed_account`.`version`, `feed_account`.`created`, `feed_account`.`updated` FROM `feed_account` WHERE `feed_account`.`id` = 1 '}]

account.save()
connection.queries

[{'time': '0.000', 'sql': u'SELECT `feed_account`.`id`, `feed_account`.`balance`, `feed_account`.`version`, `feed_account`.`created`, `feed_account`.`updated` FROM `feed_account` WHERE `feed_account`.`id` = 1 '}, {'time': '0.000', 'sql': u'SELECT (1) AS `a` FROM `feed_account` WHERE `feed_account`.`id` = 1 LIMIT 1'}, {'time': '0.000', 'sql': u'UPDATE `feed_account` SET`balance` = 1.0000, `version` = 1, `created` = 2011-06-10 14:20:36, `updated` = 2011-06-10 16:00:52 WHERE `feed_account`.`id` = 1 '}]

account.balance = F('balance') + 1
account.save()
connection.queries

[{'time': '0.000', 'sql': u'SELECT `feed_account`.`id`, `feed_account`.`balance`, `feed_account`.`version`, `feed_account`.`created`, `feed_account`.`updated` FROM `feed_account` WHERE `feed_account`.`id` = 1 '}, {'time': '0.000', 'sql': u'SELECT (1) AS `a` FROM `feed_account` WHERE `feed_account`.`id` = 1 LIMIT 1'}, {'time': '0.000', 'sql': u'UPDATE `feed_account` SET `balance` = 1.0000, `version` = 1, `created` = 2011-06-10 14:20:36, `updated` = 2011-06-10 16:00:52 WHERE `feed_account`.`id` = 1 '}, {'time': '0.000', 'sql': u'SELECT (1) AS `a` FROM `feed_account` WHERE `feed_account`.`id` = 1 LIMIT 1'}, {'time': '0.000', 'sql': u'UPDATE `feed_account` SET `balance` = `feed_account`.`balance` + 1, `version` = 1, `created` = 2011-06-10 14:20:36, `updated` = 2011-06-10 16:02:21 WHERE `feed_account`.`id` = 1 '}]

参考资料：

https://docs.djangoproject.com/en/1.3/topics/db/queries/#filters-can-reference-fields-on-the-model