Leave Your Inhibitions at the Database Connection

leave_your_inhibitions_at_the_database.md

The Django ORM

# models.py

from django.db import models

class Pasta(models.Model):
    name = models.CharField(max_length=200)
    price_per_pound = models.DecimalField(decimal_places=2)

    def __str__(self):
        return self.name

# views.py

from models import Pasta

hollow_pasta = Pasta.objects.get(name="bucatini")
>>> Returns a Pasta object

luxurious_pasta = Pasta.objects.filter(price_per_pound__gte=5)
>>> Returns a queryset (a collection of Pasta objects)

https://docs.djangoproject.com/en/2.2/topics/db/queries/

SQL: The “raw” Django manager

from models import Pasta


query = '''
    SELECT * FROM pasta 
    WHERE name = %s'
'''

stuffed_pasta = Pasta.objects.raw(query, ['manicotti'])
>>>Returns a RawQuerySet (behaves very much like a queryset)

https://docs.djangoproject.com/en/2.2/topics/db/sql/#passing-parameters-into-raw

SQL: A direct database connection

from django.db import connection

with connection.cursor() as cursor:
    query = '''
        SELECT * FROM pasta 
        WHERE price_per_pound <= 1
    '''
    cursor.execute(query)
    affordable_pasta = cursor.fetchall()

# Returns a list of tuples
>>>(('spaghetti', 0.95), ('elbow macaroni', 0.80))

https://docs.djangoproject.com/en/2.2/topics/db/sql/#executing-custom-sql-directly

Metro Councilmatic (PersonDetailView): SQL Solution

with connection.cursor() as cursor:

   sql = ('''
       SELECT o.name as organization, o.slug as org_slug, m.role
       FROM councilmatic_core_membership AS m
       JOIN councilmatic_core_organization AS o
       ON o.ocd_id = m.organization_id
       WHERE m.person_id = %s
       AND m.end_date::date > NOW()::date
       AND m.organization_id != %s
       ORDER BY
           CASE
               WHEN m.role='Chair' THEN 0
               WHEN m.role='Vice Chair' THEN 1
               WHEN m.role='Member' THEN 2
           END
       ''')

   cursor.execute(sql, [person.ocd_id, settings.OCD_CITY_COUNCIL_ID])

   columns = [c[0] for c in cursor.description]

   results_tuple = namedtuple('Member', columns)
   memberships_list = [results_tuple(*r) for r in cursor]
   context['memberships_list'] = memberships_list

https://github.com/datamade/la-metro-councilmatic/blob/25ab4a6489e0862b06d714c8a7dc529130dcb08f/lametro/views.py#L671-L695

Metro Councilmatic (PersonDetailView): ORM Solution

from django.db.models import Case, Value, When
from django.db.models import IntegerField

memberships = Membership.objects.select_related('_organization')\
                    .filter(_person_id=person.ocd_id, end_date__gt=timezone.now())\
                    .exclude(_organization_id=settings.OCD_CITY_COUNCIL_ID)\
                    .annotate(
                        role_order=Case(
                            When(role='Chair', then=Value(1)),
                            When(role='Vice Chair', then=Value(2)),
                            When(role='Member', then=Value(3)),
                            output_field=IntegerField(),
                        )
                    ).order_by('role_order')

Metro Councilmatic: Transform query results

cursor.execute(sql, [person.ocd_id, settings.OCD_CITY_COUNCIL_ID])

columns = [c[0] for c in cursor.description]

results_tuple = namedtuple('Member', columns)
memberships_list = [results_tuple(*r) for r in cursor]
context['memberships_list'] = memberships_list

Coordinated Entry Screening: ORM Solution

survey_trees = Tree.objects.annotate(times_used=Count('sessions'))\
                   .exclude(trigger='connect')\
                   .exclude(times_used=0)

https://github.com/datamade/coordinated-entry-screening/blob/d218c35788aa81e82a1b226d4f93f29cc7d79456/ces_admin/mixins.py#L186-L188

Coordinated Entry Screening: SQL Solution

SELECT tree.id, tree.trigger, COUNT(session.id) AS times_used 
FROM decisiontree_tree AS tree
LEFT OUTER JOIN decisiontree_session AS session
ON (tree.id = session.tree_id) 
WHERE NOT (tree.trigger = ‘connect’) 
GROUP BY tree.id 
HAVING NOT (COUNT(session.id) = 0)

Metro Councilmatic: Advanced ORM Solution

# models.py
class LAMetroEventManager(models.Manager):

    def with_media(self):
        mediaqueryset = LAMetroEventMedia.objects.annotate(
            olabel=Case(
                When(note__endswith='(SAP)', then=Value(0)),
                output_field=models.CharField(),
            )
        ).order_by('-olabel')

        return self.prefetch_related(Prefetch('media_urls', queryset=mediaqueryset))

https://github.com/datamade/la-metro-councilmatic/blob/ecc2657f714689c8547ed8474e6a59e555d14c12/lametro/models.py#L239-L257

# views.py
all_events = LAMetroEvent.objects\
                         .with_media()\
                         .order_by('-start_time')

https://github.com/datamade/la-metro-councilmatic/blob/ecc2657f714689c8547ed8474e6a59e555d14c12/lametro/views.py#L315-L318

https://docs.djangoproject.com/en/1.11/ref/models/querysets/#prefetch-objects