Using Django ArrayField to Store Dates and Value

Question

I'm currently having a hard time wrapping my head around Django's Array Field. What i'm hoping to do is have an array that looks something like this:

Price(close=[
    [1/1/2018, 3.00],
    [1/2/2018, 1.00],
])

It's basically an array that stores a date followed by a corresponding value tied to that date. However, thus far my model looks like this:

class Price(models.Model):
    close = ArrayField(
            models.DecimalField(max_digits=10, decimal_places=4),
            size=365,           
        )

I am not certain how to create an array with two different types of fields, one DateTime, the other decimal. Any help would be much appreciated.

illagrenan · Accepted Answer · 2018-07-03 18:10:30Z

1

You can't mix types stored in the ArrayField. [1] I recommend you to change model schema (aka Database normalization [2]).

This is my suggestion:

from django.db import models


class Price(models.Model):
    pass


class PriceItem(models.Model):
    datetime = models.DateTimeField()
    ammount = models.DecimalField(max_digits=10, decimal_places=4)
    price = models.ForeignKey(Price, on_delete=models.CASCADE)

[1] https://stackoverflow.com/a/8168017/752142

[2] https://en.wikipedia.org/wiki/Database_normalization

answered Jul 3, 2018 at 18:10

illagrenan

6,6352 gold badges59 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ng150716 Over a year ago

Gotcha, I'm confused though as to why PriceItem.price is a foreign key to the Price model.

illagrenan Over a year ago

You want to store a sequence (array) of pair values (date and decimal). You cannot do this with ArrayField: A) values are of different types; B) it is not normalized (1NF). My suggestion is: store each pair of values (items of the array) as rows in the database (an instance of PriceItem). Then you need to associate n rows (items of the array) together --> that's why I suggested ForeignKey to Price model. Maybe if you describe your use case, I could think of something better.

shlomoa · Accepted Answer · 2018-08-01 07:41:26Z

0

It depends on how important it is to the model.

postgresql provides composite types

The generous contributor of psycopg2 (django's posgresql driver) is supporting it,

define this type in postgresql:

   CREATE TYPE date_price AS (
       start date,
       float8 price
   );

and using the methods described here to implement CompositeField

   from django.db import models
   from django.contrib.postgres.fields import ArrayField
   from django.db import connection
   from psycopg2.extras import register_composite

   # register the composite 

   register_composite('date_price', connection.cursor().cursor)


   # CompositeField implementation here . . . . . .



   class DatePriceField(CompositeField):
       '''
       DatePriceField specifics
       '''
       pass

   class Price(models.Model):
       close = ArrayField(base_field=DatePriceField(), size=365,)

I am going to follow this route and update soon.

edited Aug 1, 2018 at 7:41

answered Jul 31, 2018 at 14:04

shlomoa

4345 silver badges13 bronze badges

1 Comment

EliyaMelamed Over a year ago

Where should I write the first code block? (the one that create date_price)

Collectives™ on Stack Overflow

Using Django ArrayField to Store Dates and Value

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related