SQL and Django ORM

Reference Documentation

Making queries | Django documentation | Djangoarrow-up-right

QuerySet API reference | Django documentation | Djangoarrow-up-right

Aggregation | Django documentation | Djangoarrow-up-right

Basic Prerequisites

  • django app

    • Install django_extensions

    • Create users app

    • Write models.py according to csv file and migrate

      python manage.py sqlmigrate users 0001
  • Utilize db.sqlite3 and apply data

    • Run sqlite3

      $ ls
      db.sqlite3 manage.py ...
      $ sqlite3 db.sqlite3
    • Load csv file data

      sqlite > .tables
      auth_group                  django_admin_log
      auth_group_permissions      django_content_type
      auth_permission             django_migrations
      auth_user                   django_session
      auth_user_groups            auth_user_user_permissions
      users_user
      sqlite > .mode csv
      sqlite > .import users.csv users_user
      sqlite > SELECT COUNT(*) FROM users_user;
      1000
  • Verification

    • Check schema in sqlite3

      sqlite > .schema users_user

Problems

Write SQL statements and corresponding ORM for the following problems.

Table Creation

  • django

  • SQL

    • Create the same table in sql.sqlite3

Basic CRUD Logic

  1. Retrieve all user records

  2. Create user record

    • Write after excluding one record and check the NOT NULL constraint error in both ORM and SQL.

  3. Retrieve the specific user record

    • get requires that the query result must be exactly one. (anything else returns an error!)

    SELECT * FROM users_user WHERE id = 100;

  4. Update the specific user record

  5. Delete the specific user record

Conditional Query Statements

  1. Total number of people

  2. Names of people aged 30

  3. Number of people aged 30 or older

    Comparison operators __gte : >=

    __gt : > __lte : <=

    __lt : <

  4. Number of people aged 30 with surname Kim

  5. Names of people living in Gangwon-do with surname Hwang

Sorting and LIMIT, OFFSET

  1. Top 10 oldest people

  2. Top 10 people with lowest balance (ascending order)

  3. The 5th person in descending order by last name and first name

Expressions

To use expressions, you need to know aggregatearrow-up-right.

  1. Overall average age

  2. Average age of people with surname Kim

  3. Highest account balance

  4. Total account balance

Group by

annotate adds additional fields to individual items. Will be used later in 1:N relationships.

  1. Number of people by region

Last updated