Braithwaite I/O

In [2]:
# Setup Jupyter and Django.
import os
import sys
from pathlib import Path

sys.path.append(Path.cwd())
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'demo_project.settings')

import django
django.setup()

from core.models import Prospect, HotList
 
/usr/local/lib/python3.6/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.
  """)
In [3]:
display_code_file('core/models.py')
Out[3]:
from django.contrib.postgres.fields import ArrayField
from django.db import models


class Prospect(models.Model):
    name = models.CharField('name', max_length=100)

    class Meta:
        db_table = 'prospects'
        ordering = ('name',)

    def __str__(self):
        return self.name


class HotList(models.Model):
    prospect_list = ArrayField(models.IntegerField(), size=10)

    class Meta:
        db_table = 'host_lists'
In [4]:
# Let's clean the database.
Prospect.objects.all().delete()
HotList.objects.all().delete()

# Got a list of Fantasy Bank Names here: <http://www.fantasynamegenerators.com/bank-names.php>
company_names = [
    'One Nation Credit Union',
    'Joint Banks',
    'Grand Summit Corporation',
    'Golden Gates Corporation',
    'Core Bancshares',
    'Lifespark Bank',
    'New Blossom Financial Holdings',
    'Boon Financial Corp.',
    'Life Essence Corporation',
    'Federal Financial Inc.',
]

for name in company_names:
    Prospect.objects.create(name=name)

Prospect.objects.all()
Out[4]:
<QuerySet [<Prospect: Boon Financial Corp.>, <Prospect: Core Bancshares>, <Prospect: Federal Financial Inc.>, <Prospect: Golden Gates Corporation>, <Prospect: Grand Summit Corporation>, <Prospect: Joint Banks>, <Prospect: Life Essence Corporation>, <Prospect: Lifespark Bank>, <Prospect: New Blossom Financial Holdings>, <Prospect: One Nation Credit Union>]>
In [5]:
five_random_companies = list(
    Prospect.objects.order_by('?')[:2].values_list(
        'pk', flat=True
    )
)

five_random_companies
Out[5]:
[117, 118]
In [6]:
hot_list = HotList.objects.create(prospect_list=five_random_companies)

hot_list.prospect_list
Out[6]:
[117, 118]
In [7]:
Prospect.objects.filter(pk__in=hot_list.prospect_list)
Out[7]:
<QuerySet [<Prospect: Boon Financial Corp.>, <Prospect: New Blossom Financial Holdings>]>
In [8]:
from django.db.models import BooleanField, Case, Value, When

prospect_list = (
    Prospect.objects.annotate(
        is_hot=Case(
            When(
                pk__in=hot_list.prospect_list,
                then=Value(True)
            ),
            default=False,
            output_field=BooleanField()
        )
    ).order_by('-is_hot', 'name')
)

for prospect in prospect_list:
    if prospect.is_hot:
        print('★\t{}'.format(prospect))
    else:
        print('☆\t{}'.format(prospect))
 
★	Boon Financial Corp.
★	New Blossom Financial Holdings
☆	Core Bancshares
☆	Federal Financial Inc.
☆	Golden Gates Corporation
☆	Grand Summit Corporation
☆	Joint Banks
☆	Life Essence Corporation
☆	Lifespark Bank
☆	One Nation Credit Union
In [9]:
import sqlparse

display_code(
    sqlparse.format(
        str(prospect_list.query),
        reindent=True,
        keyword_case='upper'),
    SqlLexer()
)
Out[9]:
SELECT "prospects"."id",
       "prospects"."name",
       CASE
           WHEN "prospects"."id" IN (117,
                                     118) THEN TRUE
           ELSE FALSE
       END AS "is_hot"
FROM "prospects"
ORDER BY "is_hot" DESC,
         "prospects"."name" ASC