First models.py needs tuning

[Rich Shepard]

Converting from the postgres schema to a Django models.py for my first
Django project. Most of the syntax (64 lines in the file) should be correct,
but there are two conditions that I have not found how to handle.

First, a couple of classes have primary keys with three fields. I know
there's a models.PrimaryKey but haven't found an example of how to apply
that to a field that foreign key fields.

Second, I have a couple of fields with postgres check constraints; that
is, valid data is restricted to a list of strings. I've not found a Django
model field validator teaching me how to write this.

If you would be willing to look at the module, show me how to write the
two conditions above, and check syntax on the all 5 classes in it I will
send you the file off the mail list.

Rich

[Mike Dewhirst]

On 19/04/2017 8:13 AM, Rich Shepard wrote:
> Converting from the postgres schema to a Django models.py for my first
> Django project. Most of the syntax (64 lines in the file) should be
> correct,
> but there are two conditions that I have not found how to handle.
>
> First, a couple of classes have primary keys with three fields. I know
> there's a models.PrimaryKey but haven't found an example of how to apply
> that to a field that foreign key fields.

You probably need a single FK plus a meta option of unique_together

https://docs.djangoproject.com/en/1.8/ref/models/options/#unique-together

>
> Second, I have a couple of fields with postgres check constraints; that
> is, valid data is restricted to a list of strings. I've not found a
> Django
> model field validator teaching me how to write this.

Use the model's clean() method ...

https://docs.djangoproject.com/en/1.8/ref/models/instances/#django.db.models.Model.clean

Cheers

Mike

[Rich Shepard]

On Wed, 19 Apr 2017, Mike Dewhirst wrote:

> You probably need a single FK plus a meta option of unique_together
> https://docs.djangoproject.com/en/1.8/ref/models/options/#unique-together

> Use the model's clean() method ...
> https://docs.djangoproject.com/en/1.8/ref/models/instances/#django.db.models.Model.clean

Mike,

I'll read how to use these.

Thanks,

Rich

[Rich Shepard]

On Wed, 19 Apr 2017, Mike Dewhirst wrote:

> You probably need a single FK plus a meta option of unique_together
> https://docs.djangoproject.com/en/1.8/ref/models/options/#unique-together

Mike,

Okay. What would be the correct syntax for
PRIMARY KEY unique_together=('company', 'person', 'proj_nbr')

> Use the model's clean() method ...
> https://docs.djangoproject.com/en/1.8/ref/models/instances/#django.db.models.Model.clean

This I'll need to ponder more to figure out where to put the clean method
so it validates entries before they're saved.

Thanks,

Rich

[Mike Dewhirst]

On 21/04/2017 7:48 AM, Rich Shepard wrote:
> On Wed, 19 Apr 2017, Mike Dewhirst wrote:
>
>> You probably need a single FK plus a meta option of unique_together
>> https://docs.djangoproject.com/en/1.8/ref/models/options/#unique-together
>>
>
> Mike,
>
> Okay. What would be the correct syntax for
> PRIMARY KEY unique_together=('company', 'person', 'proj_nbr')

In general with SQL you want a single PK per table and constraints for
unique-ness. If I recall correctly your original question implied you
are starting with an existing database and sort of building a Django
project around it. If that is true it becomes a more difficult task. So
I can't help without lots more information.

In the more usual scenario you specify your models using Python and let
the Django ORM framework do the SQL. If that is your case ...

1. No need for a PK. Django automatically inserts a PK. You can refer to
this in your code as model_id or model.id where "model" is the
lower-case class name of the model. This is best-practice.

2. If you want to do it yourself you can make almost any field the PK
with an option primary_key=True in which case Django won't bother. Do
this only if you must. For example if you inherit an existing database
with specified PKs.

3. In the case of 1 above I would expect three models being Company,
Person and Project each of which would have a PK managed by Django. If
so, the model you are designing (possibly Project) presumably has a
field called 'proj_nbr' and foreign keys to Company and Person. Those
FKs are represented in your model as company =
models.ForeignKey('Company') and person = models.ForeignKey('Person')

https://docs.djangoproject.com/en/1.8/topics/db/models/#relationships

4. Within each model is a class Meta: to carry various options for the
model as a whole. The option you are looking for is: unique_together =
(('company', 'person', 'proj_nbr'),)

https://docs.djangoproject.com/en/1.8/ref/models/options/#unique-together

>
>> Use the model's clean() method ...
>> https://docs.djangoproject.com/en/1.8/ref/models/instances/#django.db.models.Model.clean
>>
>
> This I'll need to ponder more to figure out where to put the clean
> method
> so it validates entries before they're saved.

I put the clean() method after the save() method which follows the
__str__() method which follows the Meta class. It doesn't really matter
so long as you are consistent.

Django forms call your model.clean() method before saving but test code
needs to call it explicitly or if you are providing an API other callers
need to call model.clean() explicitly as well.

https://docs.djangoproject.com/en/1.8/ref/models/instances/#validating-objects

Another place I sometimes use is the save() method. Django also provides
pre_save and post_save hooks so you never need to use database triggers.

HTH

>
> Thanks,
>
> Rich
>
>

[Rich Shepard]

On Fri, 21 Apr 2017, Mike Dewhirst wrote:

> In the more usual scenario you specify your models using Python and let the
> Django ORM framework do the SQL. If that is your case ...

Mike,

I wrote the schema for postgres but had not created the database. So I
renamed the file models.py and converted the tables to classes.

> 1. No need for a PK. Django automatically inserts a PK. You can refer to
> this in your code as model_id or model.id where "model" is the lower-case
> class name of the model. This is best-practice.

I understand that Django creates the PK when a class has only a single
variable. I have a table that contains one FK and two other variables form a
combined primary key. Perhaps this is a case for the many-to-one model type
in Django?

> 3. In the case of 1 above I would expect three models being Company,
> Person and Project each of which would have a PK managed by Django. If so,
> the model you are designing (possibly Project) presumably has a field
> called 'proj_nbr' and foreign keys to Company and Person. Those FKs are
> represented in your model as company = models.ForeignKey('Company') and
> person = models.ForeignKey('Person')

> 4. Within each model is a class Meta: to carry various options for the model
> as a whole. The option you are looking for is: unique_together = (('company',
> 'person', 'proj_nbr'),)

Here's one class which requires three columns to make each row unique:

class Projects (models.Model):
company=models.ForeignKeyField('Companies', on_delete=models.CASCADE)
person=models.ForeignKeyField('Contacts', on_delete=models.CASCADE)
proj_nbr=models.CharField(max_length=8, Field_null=True)
proj_name=models.TextField(Field_null=True)
description=models.TextField()

PRIMARY KEY unique_together=('company', 'person', 'proj_nbr')

If I delete 'PRIMARY KEY' and leave the rest of the line, does this
provide the relational integrity?

>> This I'll need to ponder more to figure out where to put the clean method
>> so it validates entries before they're saved.
>
> I put the clean() method after the save() method which follows the __str__()
> method which follows the Meta class. It doesn't really matter so long as you
> are consistent.

Okay. I'll try writing these methods after I get the multi-variable PKs
correct.

> Another place I sometimes use is the save() method. Django also provides
> pre_save and post_save hooks so you never need to use database triggers.

This goes beyond what I've learned. I'll get there step-by-step.

Yes, you are helping me smooth off the rough spots.

Thanks,

Rich

[Mike Dewhirst]

Rich

I've run out of time this week. Maybe someone else can help.

Mike

[Michal Petrucha]

On Tue, Apr 18, 2017 at 03:13:24PM -0700, Rich Shepard wrote:
> Converting from the postgres schema to a Django models.py for my first
> Django project. Most of the syntax (64 lines in the file) should be correct,
> but there are two conditions that I have not found how to handle.
>
> First, a couple of classes have primary keys with three fields. I know
> there's a models.PrimaryKey but haven't found an example of how to apply
> that to a field that foreign key fields.

I have bad news for you – Django does not at this point in time have
support for multi-column primary keys. A large part of the ORM is
built around the assumption that each model instance is identified by
a single field acting as its primary key.

A lot of time and effort has been spent trying to implement them, but
it's a huge project, and it's not even close to being ready to get
merged. As far as I know, nobody is currently even actively working on
this feature. If you're interested, look up composite fields in the
archives of django-developers@.

For the foreseeable future, though, I'd strongly recommend that you
save yourself a lot of trouble, and just add a surrogate primary key
field to all your tables and models.

Cheers,

Michal

[Rich Shepard]

On Fri, 21 Apr 2017, Michal Petrucha wrote:

> I have bad news for you – Django does not at this point in time have
> support for multi-column primary keys. A large part of the ORM is built
> around the assumption that each model instance is identified by a single
> field acting as its primary key.

Michal,

Ah, okay. I did not get far enough into the docs to read this.

> For the foreseeable future, though, I'd strongly recommend that you save
> yourself a lot of trouble, and just add a surrogate primary key field to
> all your tables and models.

I can live with this. Many, if not most, of the databases I've developed
will have a table which stores a unique value based on more than one other
table row. But, using a surrogate key works as long as I can retrieve the
appropriate records. Guess I'll get to that point in the not too distant
future.

What about my other question? When I want to limit acceptable strings in a
data entry field to a provided list, as in postgres's check constraint? Is
Mike's suggestion of clean() the way to handle these?

Thanks very much,

Rich

[Rich Shepard]

On Fri, 21 Apr 2017, Michal Petrucha wrote:

> For the foreseeable future, though, I'd strongly recommend that you save
> yourself a lot of trouble, and just add a surrogate primary key field to
> all your tables and models.

Michal,

I'll let Django do this. However, should I still specify unique_together()
for the columns that would have comprised the PK?

Regards,

Rich

[Michal Petrucha]

Hi Rich,

On Fri, Apr 21, 2017 at 05:48:01AM -0700, Rich Shepard wrote:
> What about my other question? When I want to limit acceptable strings in a
> data entry field to a provided list, as in postgres's check constraint? Is
> Mike's suggestion of clean() the way to handle these?

As far as I know, there's no way to declare CHECK constraints in your
model definitions, at least not with vanilla Django. However, you can
still add those constraints in your migrations using RunSQL.

Anyway, that only takes care of database-level enforcement of such
constraints; however, if any of them get violated, you'll just get an
integrity error from the database. If you want to be able to
gracefully handle violations of those constraints, and produce more
meaningful error messages (for example, when validating a form that
processes one of these models), you'd also implement those checks in
Python, either in your models' clean methods, or if it's a constraint
only involving a single field, you can also just add a custom
validator to that field.

On Fri, Apr 21, 2017 at 09:41:05AM -0700, Rich Shepard wrote:
> I'll let Django do this. However, should I still specify unique_together()
> for the columns that would have comprised the PK?

Indeed – if you want to be certain that those natural keys are really
keys, you should list them in unique_together. That will enforce
uniqueness both with UNIQUE constraints in the table definition, as
well as higher-level validation in Python.

Cheers,

Michal

[Rich Shepard]

On Fri, 21 Apr 2017, Michal Petrucha wrote:

> If you want to be able to gracefully handle violations of those
> constraints, and produce more meaningful error messages (for example, when
> validating a form that processes one of these models), you'd also
> implement those checks in Python, either in your models' clean methods, or
> if it's a constraint only involving a single field, you can also just add
> a custom validator to that field.

Michal,

Yes, I can see validating data entered by users in the view or a class
method. I'm used to letting postgres do as much work as possible because
it's quicker and cleaner. So I adapt my thinking and learn to do it Django's
way.

There are two fields in one class where data needs to be restricted to
certain values. I'll learn to write custom validators for those two fields.

>> I'll let Django do this. However, should I still specify
>> unique_together() for the columns that would have comprised the PK?
>
> Indeed – if you want to be certain that those natural keys are really
> keys, you should list them in unique_together. That will enforce
> uniqueness both with UNIQUE constraints in the table definition, as
> well as higher-level validation in Python.

As I thought. I did not think of adding the UNIQUE constraint to the
appropriate fields, but will.

Many thanks,

Rich

[Rich Shepard]

On Wed, 19 Apr 2017, Mike Dewhirst wrote:

>> Second, I have a couple of fields with postgres check constraints; that
>> is, valid data is restricted to a list of strings. I've not found a
>> Django model field validator teaching me how to write this.

> Use the model's clean() method ...

> https://docs.djangoproject.com/en/1.11/ref/models/instances/#django.db.models.Model.clean

What I see in Model.clean is a validation check which returns an error
message to the user if the check fails. I understand using this in cases
where required data are missing (such as the example of a draft article not
having a blank publication date). What I don't see is how to write a
validation check for an attribute whose value should be limited to a list.

Example: a table attribute (column) for 'industry' in which valid entries
are limited to a list such as 'Agriculture', 'Business, other', 'Chemicals',
'Energy', 'Law').

In postgres SQL this would be a constraint check. How do I write this in a
django Model.clean or its relatives?

Rich

[Guilherme Leal]

It wouldn't be the case to use the field choices?

https://docs.djangoproject.com/en/1.11/ref/models/fields/#django.db.models.Field.choices

[Rich Shepard]

On Thu, 29 Jun 2017, Guilherme Leal wrote:

> It wouldn't be the case to use the field choices?
> https://docs.djangoproject.com/en/1.11/ref/models/fields/#django.db.models.Field.choices

Guilherme,

Now that I know Field.choices exists it will certainly do the job.

I read the example as translating a quoted string in two steps to a
two-character, uppercase abbreviation. Would the following one-step list of
choices be correct?

industry_choices (
('Agriculture'),
('Business, other'),
('Chemicals'),
('Energy'),
('Law'),
('Manufacturing'),
('Mining'),
('Municipalities'),
('Ports/Marine Services'),
('Transportation'),
)
industry=models.CharField(max_length=24, choices=industry_choices, default='Agriculture')

Thanks,

Rich

[Guilherme Leal]

"An iterable (e.g., a list or tuple) consisting itself of iterables of exactly two items"

I don't think that constructing choices as a "iterableof 1 item iterables" will work.

Think of each item in the choices iterables as a key-value pair, where the first item is the key (literally is the value that is saved on the DB field), and the value is the "display value".

[Rich Shepard]

On Thu, 29 Jun 2017, Guilherme Leal wrote:

> "An iterable (e.g., a list or tuple) consisting itself of iterables of
> exactly two items"
>
> I don't think that constructing choices as a "iterableof 1 item iterables"
> will work.
>
> Think of each item in the choices iterables as a key-value pair, where the
> first item is the key (literally is the value that is saved on the DB
> field), and the value is the "display value".

Guilherme,

I missed the first sentence when I looked at the page. Your third
paragraph explains the syntax very clearly. I'll change the code to comply.

Thanks very much,

Rich