SQL and Django ORM

Reference Documentation

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

# django
class User(models.Model):
    first_name = models.CharField(max_length=10)
    last_name = models.CharField(max_length=10)
    age = models.IntegerField()
    country = models.CharField(max_length=10)
    phone = models.CharField(max_length=15)
    balance = models.IntegerField()
    
# python manage.py makemigrations
# python manage.py migrate

SQL

Create the same table in sql.sqlite3

--sql

CREATE TABLE IF NOT EXISTS "users_user" (
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
    "first_name" varchar(10) NOT NULL, 
    "last_name" varchar(10) NOT NULL, 
    "age" integer NOT NULL, 
    "country" varchar(10) NOT NULL, 
    "phone" varchar(15) NOT NULL, 
    "balance" integer NOT NULL
);

Basic CRUD Logic

Retrieve all user records

# orm

users = User.objects.all()
type(users)
# => django.db.models.query.QuerySet
print(users.query)
# only queryset can output sql statement
# => SELECT "users_user"."id", "users_user"."first_name", "users_user"."last_name", "users_user"."age", "users_user"."country", "users_user"."phone", "users_user"."balance" FROM "users_user"

-- sql

SELECT*FROM users_user;

Create user record

# orm

User.objects.create (
 first_name='구름',
    last_name='김',
    age=100,
    country='제주도',
    phone='010-1234-5678',
    balance=10000000
)

-- sql

INSERT INTO users_user(first_name, last_name, age, country, phone, balance)
VALUES ('주현', '김', 26, '경기도', '010-0000-0000', 100000000000);

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

# orm
IntegrityError: NOT NULL constraint failed: users_user.age

-- sql
Error: NOT NULL constraint failed: users_user.last_name

Retrieve the specific user record

# orm

User.objects.get(id=100)
#=> <User: User object (100)>

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

User.object.get(last_name='김')
# MultipleObjectsReturned: get() returned more than one User -- it returned 24!
User.objects.get(id=1000)
# DoesNotExists: User matching query does not exists.

-- sql

SELECT * FROM users_user WHERE id = 100;

Update the specific user record

# orm

user = User.objects.get(id=100)
user.last_name='성'
user.save()

-- sql

UPDATE users_user
SET last_name='최'
WHERE id=100;

Delete the specific user record

# orm

User.objects.get(id=101).delete()

-- sql
DELETE FROM users_user
WHERE id = 102;

Conditional Query Statements

Total number of people

# orm

# ver1)
User.objects.all().count()
# ver2) - use this one
User.objects.count()

-- sql

-- ver1)
SELECT COUNT(*) FROM users_user;

-- ver2)
SELECT COUNT(id) FROM users_user;

Names of people aged 30

# orm
User.objects.filter(age=30)
#=> <QuerySet [<User: User object (5)>, <User: User object (57)>, <User: User object (60)>]>

User.objects.filter(age=30).values('first_name')
#=> <QuerySet [{'first_name': '영환'}, {'first_name': '보람'}, {'first_name': '은영'}]>

type(User.objects.filter(age=30).values('first_name')[0])  
#=> dict

print(User.objects.filter(age=30).values('first_name').query)  
#=> SELECT "users_user"."first_name" FROM "users_user" WHERE "users_user"."age" = 30

-- sql

SELECT first_name FROM users_user
WHERE age = 30;

Number of people aged 30 or older

Comparison operators __gte : >=

__gt : > __lte : <=

__lt : <

# orm
User.objects.filter(age__gte=30)
print(User.objects.filter(age__gte=30).query)
# SELECT "users_user"."id", "users_user"."first_name", "users_user"."last_name", "users_user"."age", "users_user"."country", "users_user"."phone", "users_user"."balance" FROM "users_user" WHERE "users_user"."age" >= 30
User.objects.filter(age__gte=30).count()

-- sql

SELECT COUNT(*) FROM users_user
WHERE age>=30;

Number of people aged 30 with surname Kim

# orm -1 
User.objects.filter(age=30).filter(last_name='김').count()
# orm -2
User.objects.filter(age=30, last_name='김').count()
# query
print(User.objects.filter(age=30).filter(last_name='김').query)
# => SELECT "users_user"."id", "users_user"."first_name", "users_user"."last_name", "users_user"."age", "users_user"."country", "users_user"."phone", "users_user"."balance" FROM "users_user" WHERE ("users_user"."age" = 30 AND "users_user"."last_name" = 김)

-- sql

SELECT COUNT(*) from users_nuser
WHERE age = 30 AND last_name ='김';

Number of people with area code 02

https://docs.djangoproject.com/en/2.2/topics/db/queries/#escaping-percent-signs-and-underscores-in-like-statements

# orm

User.objects.filter(phone__startswith='02-').count()

# query
print(User.objects.filter(phone__startswith='02-').query)
#=> SELECT "users_user"."id", "users_user"."first_name", "users_user"."last_name", "users_user"."age", "users_user"."country", "users_user"."phone", "users_user"."balance" FROM "users_user" WHERE "users_user"."phone" LIKE 02-% ESCAPE '\'

-- sql

SELECT COUNT(*) FROM users_user
WHERE phone LIKE '02-%';

Names of people living in Gangwon-do with surname Hwang
```
# orm
```
```
-- sql
```

Sorting and LIMIT, OFFSET

Top 10 oldest people

# orm
User.objects.order_by('-age')[:10]

# query
print(User.objects.order_by('-age')[:10].query)
#=> SELECT "users_user"."id", "users_user"."first_name", "users_user"."last_name", "users_user"."age", "users_user"."country", "users_user"."phone", "users_user"."balance" FROM "users_user" ORDER BY "users_user"."age" DESC  LIMIT 10

-- sql

SELECT * FROM users_user
ORDER BY age DESC
LIMIT 10;

id | first_name | last_name | age | country | phone | balance
1 | 정호 | 유 | 40 | 전라북도 | 016-7280-2855 | 370
4 | 미경 | 장 | 40 | 충청남도 | 011-9079-4419 | 250000
28 | 성현 | 박 | 40 | 경상남도 | 011-2884-6546 | 580000

Top 10 people with lowest balance (ascending order)

# orm
User.objects.order_by('balance')[:10]

-- sql
SELECT * FROM users_user
ORDER BY balance ASC
LIMIT 10;

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

# orm

User.objects.order_by('-last_name', '-first_name')[4]
#=>  <User: User object (67)>

-- sql

SELECT * FROM users_user
ORDER BY last_name DESC, first_name DESC
LIMIT 1 OFFSET 4;

id | first_name | last_name | age | country | phone | balance
67 | 보람 | 허 | 28 | 충청북도 | 016-4392-9432 | 82000

Expressions

To use expressions, you need to know aggregate.

Overall average age

# orm

from django.db.models import Avg
User.objects.aggregate(Avg('age'))
#=> {'age__avg': 28.23}

-- sql

SELECT AVG(age) FROM users_user;
AVG(age)
28.23

Average age of people with surname Kim

# orm

from django.db.models import Avg
User.objects.filter(last_name='김').aggregate(Avg('age'))

-- sql

SELECT AVG(age) FROM users_user
WHERE last_name = '김';

Highest account balance

# orm

from django.db.models import Max
User.objects.aggregate(Max('balance'))

-- sql

SELECT MAX(balance) FROM users_user;

Total account balance

# orm

from django.db.models import Sum
User.objects.aggregate(Sum('balance'))

-- sql

SELECT SUM(balance) FROM users_user;

Group by

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

Number of people by region

# orm

User.objects.values('country')
 # <QuerySet [{'country': '전라북도'}, {'country': '경상남도'}, {'country': '전라남도'}, ...
from django.db.models import Count
User.objects.values('country').annotate(Count('country'))
# <QuerySet [{'country': '강원도', 'country__count': 14}, {'country': '경기도', 'country__count': 9}, {'country': '경상남도', 'country__count': 9}, {'country': '경상북도', 'country__count': 15}, {'country': '전라남도', 'country__count': 10}, {'country': '전라북도', 'country__count': 11}, {'country': '제주특별자치도', 'country__count': 9}, {'country': '충청남도', 'country__count': 9}, {'country': '충청북도', 'country__count': 14}]>

-- sql

SELECT country, COUNT(country) FROM users_user
GROUP BY country;

country | COUNT(country)
강원도 | 14
경기도 | 9
경상남도 | 9
경상북도 | 15
전라남도 | 10
전라북도 | 11
제주특별자치도 | 9
충청남도 | 9
충청북도 | 14

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

SQL and Django ORM

Reference Documentation

Basic Prerequisites

Problems

Table Creation

Basic CRUD Logic

Conditional Query Statements

Sorting and LIMIT, OFFSET

Expressions

Group by

FilesExpand file tree

02_SQL_and_Django_ORM.md

Latest commit

History

02_SQL_and_Django_ORM.md

File metadata and controls

SQL and Django ORM

Reference Documentation

Basic Prerequisites

Problems

Table Creation

Basic CRUD Logic

Conditional Query Statements

Sorting and LIMIT, OFFSET

Expressions

Group by