[DB] SQL과 django ORM

18 Jun 2021

SQL과 django ORM

참고문서

[Making queries

Django documentation

Django](https://docs.djangoproject.com/en/2.2/topics/db/queries/#making-queries)

[QuerySet API reference

Django documentation

Django](https://docs.djangoproject.com/en/2.2/ref/models/querysets/#queryset-api-reference)

[Aggregation

Django documentation

Django](https://docs.djangoproject.com/en/2.2/topics/db/aggregation/#aggregation)

기본 준비 사항

Gitlab에서 프로젝트를 다운받으면 아래의 내용이 이미 반영되어 있습니다.

django app
- django_extensions 설치
- users app 생성
- csv 파일에 맞춰 models.py 작성 및 migrate
```
  $ python manage.py sqlmigrate users 0001
```

db.sqlite3 활용 및 데이터 반영

sqlite3 실행

  $ ls
  db.sqlite3 manage.py ...
  $ sqlite3 db.sqlite3

csv 파일 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;
  100

확인
- sqlite3에서 스키마 확인
```
  sqlite > .schema users_user
```

문제

아래의 문제들을 sql문과 대응되는 orm을 작성 하세요.

Table 생성

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
- sql.sqlite3에 동일하게 테이블 생성
```
  --sql
```

기본 CRUD 로직

모든 user 레코드 조회

# orm
users = User.objects.all()
type(users)
#=> django.db.models.query.QuerySet
print(users.query) 
# queryset만 sql문 출력 가능
#=> 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;
   
id | first_name | last_name | age | country | phone | balance
1 | 정호 | 유 | 40 | 전라북도 | 016-7280-2855 | 370
...

user 레코드 생성

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

-- sql
INSERT INTO users_user ('first_name', 'last_name', 'age', 'country', 'phone', 'balance')
VALUES ('근제', '성', 80, '서울', '011-123-1324', 999999999999999999999);

하나의 레코드를 빼고 작성 후 NOT NULL constraint 오류를 orm과 sql에서 모두 확인 해보세요.

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

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

해당 user 레코드 조회

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

get 은 쿼리 결과가 반드시 하나여야 한다. 이외에는 모두 오류를 반환한다.

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

-- sql
SELECT * FROM users_user
WHERE id = 100;

해당 user 레코드 수정

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

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

해당 user 레코드 삭제

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

-- sql
DELETE FROM users_user
WHERE id = 102;

조건에 따른 쿼리문

전체 인원 수

# orm
User.objects.count()

-- sql
SELECT COUNT(*) FROM users_user;
COUNT(id)
100

나이가 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;
first_name
영환
보람
은영

나이가 30살 이상인 사람의 인원 수

대소관계

__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;
   
COUNT(*)
43

나이가 30이면서 성이 김씨인 사람의 인원 수

# 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_user
WHERE age = 30 AND last_name = '김';

지역번호가 02인 사람의 인원 수

https://docs.djangoproject.com/en/2.2/topics/db/queries/#escaping-percent-signs-and-underscores-in-like-statements
iexact, contains, icontains, startswith, istartswith, endswith, iendswith

# 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-%';

거주 지역이 강원도이면서 성이 황씨인 사람의 이름
```
# orm
```
```
-- sql
```

정렬 및 LIMIT, OFFSET

나이가 많은 사람 10명(내림차순)

# 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

잔액이 적은 사람 10명 (오름차순)

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

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

성, 이름 내림차순 순으로 5번째 있는 사람

# 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

표현식

표현식을 위해서는 aggregate 를 알아야한다.

전체 평균 나이

# 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

김씨의 평균 나이

# 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 = '김';

계좌 잔액 중 가장 높은 값

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

-- sql
SELECT MAX(balance) FROM users_user;

계좌 잔액 총액

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

-- sql
SELECT SUM(balance) FROM users_user;

Group by

annotate는 개별 item에 추가 필드를 구성한다. 추후 1:N 관계에서 활용된다.

지역별 인원 수 ```python
orm

User.objects.values(‘country’)

<QuerySet [{‘country’: ‘전라북도’}, {‘country’: ‘경상남도’}, {‘country’: ‘전라남도’}, …

from django.db.models import Count User.objects.values(‘country’).annotate(Count(‘country’))

<QuerySet [{‘country’: ‘강원도’, ‘countrycount’: 14}, {‘country’: ‘경기도’, ‘countrycount’: 9}, {‘country’: ‘경상남도’, ‘countrycount’: 9}, {‘country’: ‘경상북도’, ‘countrycount’: 15}, {‘country’: ‘전라남도’, ‘countrycount’: 10}, {‘country’: ‘전라북도’, ‘countrycount’: 11}, {‘country’: ‘제주특별자치도’, ‘countrycount’: 9}, {‘country’: ‘충청남도’, ‘countrycount’: 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