Lesson 8 - Advanced Modeling and Querying

Transcript

1. Lesson 8 Advanced Modeling and Querying Dana Spiegel dana@sociabledesign.com 1
2. Field Options • All fields support certain standard parameters used when they are created • Offer a way to influence database column definition, Django admin (more on this later), or general usage • default: provides a default value when the object is created; can either be a value, or a “callable” • db_index: adds database index for the column • unique: adds database index for the column making it unique (all rows) • null: indicates that the field is NULL-able in the database • blank: field can be blank (unrelated to DB); if False, field is required • editable: if False, field won’t be displayed in admin or in forms • help_text: extra text that will be displayed in a form • error_messages: dict of user facing error messages for form errors (null, blank, invalid, invalid_choice, and unique) • verbose_name: human readable name for field 2 contact_info = CharField(default = lambda: uuid.uuid4().hex)
3. Field Options (cont’d.) • choices: used for CharField in order to restrict the options available • Think dropdown list or checkboxes in a web form • Defined in a constant as an iterable of 2-tuples of the form: • (value, human-readable name) • First element will be stored in the database as the value for that field • Second element will be shown to users in forms for selection • Use Model.get_FOO_display() to get the human readable version of the Foo field 3 YEAR_IN_SCHOOL_CHOICES = ( ('FR', 'Freshman'), ('SO', 'Sophomore'), ('JR', 'Junior'), ('SR', 'Senior'), ('GR', 'Graduate'), ) year_in_school = models.CharField(max_length=2, choices=YEAR_IN_SCHOOL_CHOICES)
4. Working with Fields: Numbers • IntegerField is used for most number storage • Stores 4 bytes of data - (28) ^ 4 = -2,147,483,648 to +2,147,483,647 • PositiveIntegerField should be used when you only care about values > 0 • Stores 4 bytes of data - (28) ^ 4 = 0 to 4,294,967,295 • BigIntegerField is used if you need to store a much larger number (like some ids) • Stores 8 bytes of data - (28) ^ 8 = -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 • SmallIntegerField stores only 2 bytes of information (not really that useful) • (28) * 2 = -32,768 to 32,787 • PositiveSmallIntegerField can be used much like PositiveIntegerField • DecimalField stores a fixed precision decimal number, using a python Decimal number • Requires 2 arguments: max_digits and decimal places • FloatField stores a floating point number (not fixed precision) as a float 4
5. Working with Fields: More Numbers • CommaSeparatedIntegerField is used to store a list of numbers, separated by commas • Stored as a text field in the database • Not available for computation and aggregation at the database level • Requires a max_length parameter (like a text field) to limit its size 5
6. Working with Fields: Booleans • BooleanField is used to keep track of True/False values or “flags” • They can be used for settings, like “send email alerts” • Sometimes stored as 1 bit ints in the database (MySQL) • Useful for database queries like: • find all users that have email alerts turned on • Users.objects.filter(email_alerts=True) • Sometimes, its useful to know if a flag has been set: • Similar to 3-way switches: on, off, unset • Use a NullBooleanField, which can also have the value of None • Use the default parameter on the field to indicate the default value you want for this field when an object is created 6
7. Working with Fields: Text • CharField is the standard way to store text in models • Uses a varchar in the database • Has a limit of 255 characters, making it great for storing names, address fields, labels, etc. • Requires max_length to enforce a maximum amount of text • TextField is used for storing larger text values • Can store up to 4,294,967,295 characters (4GB!) • Don’t store that much information in the database; use files! • Good for longer text values, like descriptions or a few paragraphs of text • SlugField keeps a text identifier useful for URLs (like in blogs) • Allows only letters, numbers, underscores, and hyphens • The EmailField validates that the text is an valid email address • URLField validates that the text is a valid URL (but won’t check existence) 7
8. Working with Fields: Dates • DateTimeField, DateField, TimeField used for keeping track of any date/time information • All 3 are used in the same way • All 3 use various datetime.* objects • Returned objects have no timezone associated with them, so be sure to always store UTC dates/times • datetime.datetime.utcnow() vs. now() • Useful parameters: • auto_now will set the field to the current time whenever it is saved; useful for updated_on • auto_now_add will set the field to the current time only upon creation; useful for created_on 8 >>> datetime.datetime.utcnow() datetime.datetime(2012, 10, 27, 0, 5, 17, 296049) >>> datetime.datetime.now() datetime.datetime(2012, 10, 26, 20, 5, 30, 33746)
9. Using Fields: Address 9 COUNTRIES = ( ('US', 'United States of America'), ... ) ADDRESS_TYPES = ( ('W', 'Work'), ('H', 'Home'), ('O', 'Other'), ... ) class Address(models.Model): person = models.ForeignKey('Person', related_name='addresses') type = models.CharField(max_length=1, choices=ADDRESS_TYPES, db_index=True) street_address_1 = models.CharField(max_length=100) street_address_2 = models.CharField(max_length=100, blank=True) city = models.CharField(max_length=100, blank=True) state_province = models.CharField(max_length=100, db_index=True) postal_code = models.CharField(max_length=10) country = models.CharField(max_length=2, choices=COUNTRIES, db_index=True) is_primary_address = models.BooleanField(default=True, db_index=True) https://github.com/danaspiegel/hatchery-code/blob/master/project/models.py
10. Using Fields: Parking Lot 10 from django.db import models LOT_TYPES = ( ('S', 'Street Lot'), ('M', 'Multi-level Above Ground'), ('O', 'Multi-level Below Ground'), ) SPOT_SIZES = ( ('S', 'Standard'), ('C', 'Compact'), ('T', 'Truck'), ('U', 'Unusable'), ) ORIENTATIONS = ( ('N', 'North'), ('NE', 'North East'), ('E', 'East'), ) class Lot(models.Model): name = models.CharField(max_length=100) slug = models.SlugField(max_length=100) lot_type = models.CharField(max_length=1, choices=LOT_TYPES, db_index=True) open_time = models.TimeField('Lot opens') ... https://github.com/danaspiegel/hatchery-code/blob/master/parking_lot/models.py
11. Model Meta Options 11 • When defining a Model, you can specify Meta options • Inform Django how to handle certain aspects of your Model • ordering: provides a default ordering to use when querying objects; incurs a cost, so don’t use it unless necessary; can take a - (negative) • permissions: a collection of 2-tuples that define additional permissions to be used by Django’s permissions system (not covered) • unique_together: collection of 2-tuples that should be treaded as unique together by creating a compound index in the database • verbose_name: used by Django admin for labeling the model • verbose_name_plural: used by Django admin for labeling multiple of the model
12. Validators • Small methods that are used to ensure fields conform to certain criteria • Specified when creating a field • Work at the python level, not the database level, so more functional, but logic won’t reside in database • Useful ones live in module django.core.validators, but you can write your own • Used for both Models and Forms (more on this when we cover Forms) • When saving an object, you must explicitly call full_clean; Validators aren’t enforced on save()! 12 https://docs.djangoproject.com/en/1.4/ref/forms/validation/ from django.core.validators import * class Foo(models.Model): bar = models.CharField(max_length=100, validators=[MinLengthValidator(3)]) pct = models.IntegerField(validators=[ MinValueValidator(0), MaxValueValidator(100)])
13. Advanced Querying • Use all() to get all objects for a given Model • Returns a QuerySet, which efficiently iterates over all items • Use values() or values_list() to get just dicts or tuples of data • Name the fields you want, including related fields • You can use multiple filters to shave down your queryset • Also, you can filter on related fields • Use exclude() to invert the filtering logic • Aggregate queries run computation on columns with group_by, returning the results • Annotate works like Aggregate queries, but work on related objects 13 Lot.objects.values('name', 'spots__level', 'spots__designation') Lot.objects.filter(lot_type='M').filter(spots__level=-1) Lot.objects.exclude(lot_type='M') rates = Lot.objects.all().aggregate(avg=Avg('hourly_rate'), max=Max('hourly_rate')) lots_with_spots = Lot.objects.annotate(spots_count=Count('spots'))
14. Building a Softball Game Tracker • In class exercise: Build the models for an Softball Tracker • We want to track: • Multiple teams • Multiple players • Multiple games • Various stats for each player for each game 14
15. Review Softball Game Tracker 15 Game Team n..1 Roster Roster home away n..1 Statistic Statistic Statistic Player Player Player 1..1 n..1 Statistic Statistic Statistic 1..n