Common Queries

We often need a quick way to validate the data in the database, for example after loading test data or running a migration or testing various other operations. This page details a number of specific queries that can be run individually from the Python shell or all at once via a management command.

Python shell

You may query the database through Python shell using python manage.py shell. These queries can be very easily modified, and some common modifications are listed at the bottom.

Helper functions

Some of the queries require one or more of the helper functions defined on this page. Entering them once at the start of a session is enough.

def get_averages(items):
    length = len(items)
    if length == 0:
        raise ValueError("Cannot get averages for an empty list")
    
    mean = sum(items)/length
    if len(items) < 4:
        return {"Mean": mean}
    
    items.sort()
    first_q = items[:length//4]
    second_q = items[length//4:length//2]
    third_q = items[length//2:length//4*3]
    fourth_q = items[length//4*3:]
    return {"1st quartile": first_q[-1], "2nd quartile": second_q[-1], "3rd quartile": third_q[-1], "Max": fourth_q[-1], "Mean": mean}

def print_keyvalues(dict):
    for key in dict.keys():
        print(f"{key}: {dict[key]}")

Quick queries

Number of committees

CommitteeAccount.objects.count()

Number of users

User.objects.count()

Number of reports per committee

committee_report_counts = []
for committee in CommitteeAccount.objects.all():
    r_count = Report.objects.filter(committee_account=committee).count()
    committee_report_counts.append(r_count)

averages = get_averages(committee_report_counts)
print_keyvalues(averages)

Number of transactions per committee

committee_transaction_counts = []
highest_count = 0
biggest_committee = None
for committee in CommitteeAccount.objects.all():
    t_count = Transaction.objects.filter(committee_account=committee).count()
    committee_transaction_counts.append(t_count)
    if t_count > highest_count:
        highest_count = t_count
        biggest_committee = committee

averages = get_averages(committee_transaction_counts)
print_keyvalues(averages)
print(f"The largest committee is {biggest_committee.committee_id} with {highest_count} transactions")

Number of transactions per report

report_transaction_counts = []
for report in Report.objects.all():
    t_count = Transaction.objects.filter(reports=report)
    report_transaction_counts.append(t_count)

averages = get_averages(report_transaction_counts)
print_keyvalues(averages)

Number of transactions per contact

contact_transaction_counts = []
for c in Contact.objects.all():
    ct_set_keys = []
    for i in range(1, 6):
        ct_set_keys.append(f"contact_{i}_transaction_set")
    
    for n in ["I", "II", "III", "IV", "V"]:
        ct_set_keys.append(f"contact_candidate_{n}_transaction_set")
    
    ct_set_keys.append("contact_affiliated_transaction_set")
    transaction_count = 0
    for key in ct_set_keys:
        transaction_count += getattr(c, key).count()
    
    contact_transaction_counts.append(transaction_count)
    
averages = get_averages(contact_transaction_counts)
print_keyvalues(averages)

Breakdown of transaction types

tti_counts = {}
for transaction in Transaction.objects.all():
    tti = transaction.transaction_type_identifier
    tti_counts[tti] = tti_counts.get(tti, 0) + 1

print(tti_counts)

Breakdown of transaction tiers

filter_keys = [
  {"parent_transaction__isnull":True},
  {"parent_transaction__isnull":False, "parent_transaction__parent_transaction__isnull": True},
  {"parent_transaction__parent_transaction__isnull":False}
]
for i in range(3):
  print(f"Tier {'I'*(i+1)}: {Transaction.objects.filter(**filter_keys[i]).count()}")

"Carryover" type transactions (C/C2/D)

for model in [ScheduleC, ScheduleC2, ScheduleD]: print(f"{model.__name__}: {model.objects.count()}")

Adjustments

• Examples of common models: User, CommitteeAccount, MemoText, Transaction, Contact, ScheduleA, ScheduleB, Report, Form3X
• Filtering for a specific committee ID: you can add committee_account__committee_id="C????????" to any existing filter() or you can replace any all() with a filter()

Management Command

All of the above queries can be run as a management command with: python django-backend/manage.py get_overview [--committee_id <id>]