How to properly use MySQL Generated Fields in Django

[rmschne]

I have a generated field in a MySQL table "alpha = upper(substr(`lname`,1,1))" where the field lname is a last name of a person. Idea is that I get a field of the first letter of the last name.  

In Django, I have in the table 'attendees' a field alpha = models.CharField("alpha", max_length=1, db_column='alpha', help_text='leading alphabetic letter of lastname.')

This works ok when I a new record into the database and the field "alpha" is computed correctly.  It works where I can query out the data using Django.  

Now I have discovered that if in Django I adjust one of the the other fields (not lname), when I do a "save() on the record", Django throws an error:

"The value specified for generated column 'alpha' in table 'attendees' is not allowed.")

The one field i am changing in the "attendee" table is a foreign key field; and I'm giving Django the foreign key object.  So I think I'm doing that correctly; but the new attendee object is not being saved.  

I've searched Google for "Django Mysql generated field" and come up with nothing relevant on anything special needed; but I suspect I'm missing something hence this query. Looking for something special about generated fields in MySQL and Django, if anything special?

Some code extracts:

class Guest(models.Model):

    lname = models.CharField(max_length=150, db_column='Lname', blank=True,help_text="Last name")

    alpha = models.CharField("alpha", max_length=1, db_column='alpha', help_text='leading alphabetic letter of lastname.')

    contact = models.ForeignKey(Contact, related_name='adcontact', db_column='ContactID',blank=True, null=True, help_text="Name of linked contact for this guest")

    timestamp = models.DateTimeField(db_column='timestamp',auto_now=True,blank=False)

cad=Guest.objects.filter(contact__id=idfrom)

            print "Guest: ",len(cad)

            if cad:

                for i in cad:

                    print "i.contact:",i.id,i.contact

                    print "contactto:",contactto.id,contactto

                    i.contact=contactto

                    try:

                        i.save(["contact"])

                    except Exception as e:

                        print ">>>>Exception. cad contact save failed.",e

            

[Matthew Pava]

I wouldn’t save alpha in the database. I would use a property on the model or annotate the field to the object manager.

 

Property:

class Guest(models.Model):

               @property

               def alpha(self):

                              return self.lname[0] if self.lname else ''

 

Or on the object manager:

class GuestManager(models.Manager):

               def get_queryset(self):

                              return super().get_queryset().annotate(alpha=Left('lname', 1))

 

class Guest(models.Model):

               objects = GuestManager()

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/45a3e32d-29d7-4129-896b-01698beec3e1%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Rob Schneider]

Thanks!

I’ll give this a try.

I did have “alpha” as a function in the model, but found I could not easily include that in queries as easily as a field in the db. so into the db it went. I’ll try this, and see if I can get the data out as wanted.

thanks!
—rms