Django，访问PostgreSQL序列 [英] Django, accessing PostgreSQL sequence

问题描述

在Django应用程序中，我需要创建一个订单号，如下所示：yyyymmddnnnn，其中yyyy = year，mm = month，dd = day和nnnn是介于1到9999之间的数字。

In a Django application I need to create an order number which looks like: yyyymmddnnnn in which yyyy=year, mm=month, dd=day and nnnn is a number between 1 and 9999.

我以为我可以使用PostgreSQL序列，因为生成的数字是原子的，所以我可以确定当进程得到一个数字时，该数字是唯一的。

I thought I could use a PostgreSQL sequence since the generated numbers are atomic, so I can be sure when the process gets a number that number is unique.

所以我创建了一个PostgreSQL序列：

So I created a PostgreSQL sequence:

CREATE SEQUENCE order_number_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9999
START 1
CACHE 1
CYCLE;

此序列可以作为具有一行的表来访问。因此，在文件checkout.py中，我创建了一个Django模型来访问此序列。

This sequence can be accessed as a tables having one row. So in the file checkout.py I created a Django model to access this sequence.

class OrderNumberSeq(models.Model):
    """
    This class maps to OrderNumberSeq which is a PostgreSQL sequence.
    This sequence runs from 1 to 9999 after which it restarts (cycles) at 1.
    A sequence is basically a special single row table.
    """
    sequence_name = models.CharField(max_length=128, primary_key=True)
    last_value = models.IntegerField()
    increment_by = models.IntegerField()
    max_value = models.IntegerField()
    min_value = models.IntegerField()
    cache_value = models.IntegerField()
    log_cnt = models.IntegerField()
    is_cycled = models.BooleanField()
    is_called = models.BooleanField()

    class Meta:
        db_table = u'order_number_seq'

我将sequence_name设置为主键，因为Django坚持要求在

I set the sequence_name as primary key as Django insists on having a primary key in a table.

我创建了一个文件get_order_number.py，内容如下：

The I created a file get_order_number.py with the contents:

def get_new_order_number():
    order_number = OrderNumberSeq.objects.raw("select sequence_name, nextval('order_number_seq') from order_number_seq")[0]

    today = datetime.date.today()
    year = u'%4s' % today.year
    month = u'%02i' % today.month
    day = u'%02i' % today.day

    new_number = u'%04i' % order_number.nextval
    return year+month+day+new_number

现在，当我从Django交互式shell调用 get_new_order_number（）时，它的行为便与预期的一样。

now when I call 'get_new_order_number()' from the django interactive shell it behaves as expected.

>>> checkout.order_number.get_new_order_number()
u'201007310047'
>>> checkout.order_number.get_new_order_number()
u'201007310048'
>>> checkout.order_number.get_new_order_number()
u'201007310049'

您看到的数字很好地递增每次调用该函数时减一。您可以启动多个交互式django会话，并且数字可以很好地递增，而在不同的会话中不会出现相同的数字。

You see the numbers nicely incrementing by one every time the function is called. You can start multiple interactive django sessions and the numbers increment nicely with no identical numbers appearing in the different sessions.

现在，我尝试使用从如下视图调用此函数：

Now I try to use call this function from a view as follows:

import get_order_number

order_number = get_order_number.get_new_order_number()

它给了我一个数字。但是，下次访问该视图时，它会将数字增加2。我不知道问题出在哪里。

and it gives me a number. However next time I access the view, it increments the number by 2. I have no idea where the problem is.

推荐答案

我能想到的最好的解决方案是：不要担心您的订单号是否稀疏。缺少订单号也没关系：没有办法确保订单号是连续的，在某些时候不会受到竞争条件的限制。

The best solution I can come up with is: don't worry if your order numbers are sparse. It should not matter if an order number is missing: there is no way to ensure that order numbers are contiguous that will not be subject to a race condition at some point.

您最大的问题可能是说服尖尖的人，让他们知道缺少订单号码不是问题。

Your biggest problem is likely to be convincing the pointy-haired ones that having 'missing' order numbers is not a problem.

有关更多详细信息，请参阅Psuedo-Key Neat SQL反模式中的怪胎条目。 （请注意，这是到书的链接，该书的全文免费提供）。

For more details, see the Psuedo-Key Neat Freak entry in SQL Antipatterns. (note, this is a link to a book, which the full text of is not available for free).

这篇关于Django，访问PostgreSQL序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！