장고, SQL 이해하고 사용하는 Django ORM

출처: https://www.youtube.com/watch?v=J3pg-Ivl_MU

 

장고, SQL 이해하고 사용하는 Django ORM 

 

정리. 수알치 오상문

 

가장 아래 동영상 링크에 있는 코드 내용을 정리한 것입니다. 

[장고]           [MySQL]
Model     --  Table
instance --  record, raw

# 기본 쿼리문
User.objects.all()  # onjects : Table manager 
>>> 
Select * From USER;

# 조건 쿼리문
User.objects.filter(is_active=True)  # is_active : column 
>>> 
Select * From User Where is_active=1;

# 쿼리문 코드 출력하기
querySet = User.objects.all();
print(str(querySet.query));  # 쿼리문 출력

# 쿼리문 예쁘게 출력하기
import sqlparse  
querySet = User.objects.all()
query = querySet.query
print(sqlparse(query, reindent=True))  # 쿼리문 출력

# 쿼리문 실행 계획 테스트, 성능 분석
print(querySet.explain())  

# aggreate() 이용한 집계함수 사용
Null 값 제외하고 집계함수가 동작한다.

User.objects.aggreate(Count('id'))  # {"id_count": 3}
>>>
SELECT COUNT(id) AS id__count FROM User;

User.objects.aggreate(count=Count('id'))  # {"count": 3}

User.objects.count()
>>> 
SELECT COUNT(*) FROM User;

# 날짜 비교 1
User.objects.filter( joined_at__gte=date(2022, 1, 1),  joined_at__lte=date(2022, 12, 1))
>>> 
SELECT *
FROM User
WHERE   joined_at >= '2022-01-01'   AND    joined_at <= '2022-12-31';

# 날짜 비교 2
User.object.filter( joined_at__range=(date(2022, 1, 1), date(2022, 12, 31)) )
>>> 
SELECT *
FROM User
WHERE   joined_at BETWEEN '2022-01-01' AND '2022-12-31';

# [주의] date 필드 검사 시 시분초까지 적용되므로 말일 포함 안됨!!!
User.object.filter( joined_at__range=(date(2022, 1, 1), date(2022, 12, 31)) )
>>> 
SELECT *
FROM User
WHERE
joined_at >= '2022-01-01 00:00:00'
AND 
joined_at <= '2022-12-31 00:00:00';

# 다음처럼 설정해야 말일까지 포함된다.
User.object.filter( joined_at__range=(date(2022, 1, 1), date(2023, 1, 1)) )
>>> 
SELECT *
FROM User
WHERE   joined_at >= '2022-01-01 00:00:00'  AND  joined_at <= '2023-01-01 00:00:00';

# UNION : 테이블 합치기
book = Book.object.all()
ebook = EBook.objects.all()
books.union(ebooks)  # book에 ebook 결합 
>>> 
(SELECT * FROM book) UNION (SELECT * FROM EBook);

# 컬럼명이 다른 경우 합치기 : 컬럼명 순서를 지정해주자.
예를 들어, book은 title, ebook은 name 컬럼이고 다른 컬럼명 같을 때

book = Book.object.values_list('id', 'title', 'price)
ebook = EBook.objects.values_list('id', 'name', 'price')

books.union(ebooks)  # book에 ebook 결합 
>>> 
(SELECT id, title, price FROM book) UNION (SELECT id, name, price FROM EBook);

# UNION 작업에서 레코드 유니크 검사 불필요 시 
books.union(ebooks, all=True)  # book에 ebook 결합 
>>> 
(SELECT * FROM book) UNION ALL (SELECT * FROM EBook);

# 사용자 컬럼 추가 : annotate 기능
일반책과 전자책을 결합할 때 책 구분 컬럼 book_type 추가하고 싶으면

from django.db.models import Value
book  =  Book.object.annotate(book_type=Value('일반책'))
ebook = EBook.objects.annotate(book_type=Value('전자책'))

books.union(ebooks, all=True)  # book에 ebook 결합 

# F( ) : 컬럼 계산하여 새 컬럼 추가 
from django.db.models import F 

book  =  Book.object.annotate(sail_price=F('price) - F('discount'))
>>> 
SELECT id, title, price, discount, price-discount AS sale_price
FROM Book;

# 특정 컬럼만 가져오고 싶을 때 : values / values_list
User.object.values('id', 'name')       # 컬럼명:값 사전형 <QuerySet [{'id': 1, 'name': 'Guido'}]>
User.object.values_list('id', 'name')  # 값 리스트 <QuerySet [(1, 'Guido')]>
>>>
SELECT id, name FROM User;

# values, values_list 이용하면 외래키를 이용한 연결이 안됨. 
User.objects.first().profile            # 인스턴스이므로 외래키 연결됨 : <UserProfile: Guido profile>
User.objects.values('profile').first() # 결과가 값이므로 외래키 연결 안됨, 그냥 id 나옴: {'profile': 1}

# JOIN을 위한 select_related는 values...에서 적용 안됨
User.object.select_related('profile')  # 이건 JOIN 적용됨
>>>
SELECT *
FROM User U
LEFT OUTER JOIN 
  Profile P
  ON 
  U.id = P.user_id

User.object.select_related('profile').values( )  # 이건 JOIN 적용 안됨
>>>
SELECT *  FROM User 

# only( ) 특정 컬럼만 사용하기 : 인덱스 적용 시 속도 향상 
User.objects.only('name')
>>>
SELECT 
  id,  # id는 항상 포함
  name
FROM
  User

# values 이용한 컬럼명과 순서 변경
book  =  Book.object
.annotate(sail_price=F('price) - F('discount'))
.values(                                    
_sale_price=F('sail_price'),  # _ 추가 이유는 원본명과 같으면 안되기에...
_price=F('price'),
_discount=F('discount'),
)
>>> 
SELECT 
price - discount AS _sale_price,
price AS _price,
discount AS _discount
FROM Book;

# GROUP BY : values는 annotate와 사용하면 그룹 바이로 동작한다. 
# 'type' : 그룹지정 컬럼명, annotate에 그룹 컬럼에 적용할 집계함수 지정

User.objects.values('type').annotate(count=Count('id'))  
# <QuerySet [{'type': 'A', 'count': 2}, {'type': 'B', 'count: 1}]>
>>>
SELECT type, COUNT(id) AS count,
FROM User
GROUP BY type;

단, 그룹바이 컬럼 이외는 선택할 수 없다. 
values에 여러 컬럼 지정하면 함께 집계함수가 적용된다.
그런데 id 경우에 특정한 대표 id를 나타내고 싶으면 아래처럼 적용할 수 있다.

User.objects.values('type', 'age').annotate(count=Count('id'), representative_id=Min('id')))
>>>
SELECT MIN(id) AS representative_id, type, age, COUNT(id) AS count,
FROM User
GROUP BY type, age;

# Index 인덱스 
장고는 유니크 제약 조건이 걸린 컬럼은 인덱스를 자동 생성한다.

class Foo(models.Model):
...
class Meta:
constraints = [
models.UniqueConstraint(fields=['single_column']), # 한 개 지정
models.UniqueConstraint(fields=['multi', 'column']), # 여러 개 지정
]

class Foo(models.Model):
...
class Meta:
indexes = [
models.Index(fields=['single_column']), # 한 개 지정
models.Index(fields=['multi', 'column']), # 여러 개 지정
]

인덱스 컬럼 순서는 동치 조건을 비교보다 앞에 놓자.

SELECT *
FROM Order
WHERE
product_id = 1 AND
ordered_at BETWEEN '2022-10-01' AND '2022-10-02';

class Order(models.Model):
...
class Meta:
indexes = [
models.Index(fields=['product', 'ordered_at]),  # 순서 주의 
]

# 중복 인덱스 제거 
class Order(models.Model):
product = models.ForeignKey('Product', 
on_delete=models.CASCADE, 
db_index=False)  # 불필요한 인덱스 생성 제거 

class Meta:
indexes = [
models.Index(fields=['product', 'ordered_at]),  # 이곳에서 인덱스 생성 설정!
]

# 인덱스 활용하는 쿼리와 아닌 쿼리?
User.objects.filter(name_contains='Guido')  # 인덱스 활용 못함!!!
>>>
SELECT *
FROM User
WHERE name LIKE '%Guido%';

--> Guido로 시작하는 이름을 찾으려면 
User.objects.filter(='Guido')  # 인덱스 활용!!!
>>>
SELECT *
FROM User
WHERE name LIKE 'Guido%';

--> 날짜 컬럼도 인덱스 활용 못함
User.objects.filter(joined_at__year=2022)
>>>
SELECT *
FROM User
WHERE YEAR(joined_at) = '2022';

--> 이렇게 바꾸자 
User.objects.filter(joined_at__gte='2022-01-01', joined_at__lt='2023-01-01')
>>>
SELECT *
FROM User
WHERE joined_at >= '2022-01-01' AND  joined_at < '2023-01-01';

# 커버링 인덱스 : 인덱스 테이블만으로 모두 검색 가능한 경우 
queryset = (
Order.objects
.filter(product_id=1, ordered_at__gte='2022-01-01)
.only('product', 'ordered_at')  # 커버링 인덱스 가능하면 인덱스 테이블에서 바로 가져옴 
)

# 인덱스를 활용한 검색(스캔) 범위 축소
class Order(models.Model):
...
ordered_at = models.DateTimeField(auto_now_add=True, db_index=True)
paid_at = models.DateTimeField(null=True, db_index=False)  # 검색 시 풀스캔 적용되어 느림

Order.objects.filter(paid_at__isnull=True)

paid_at에 인덱스를 적용하면 속도 향상이 있지만, 수억개 자료인 경우라면 달라진다. 
하루에 한번 정도 스캔하는 경우라면 인덱스를 적용하기 보다는 
아래처럼 인덱스가 걸린 컬럼을 활용하여 탐색 범위를 줄여보자.

Order.objects.filter(paid_at__isnull=True, ordered_at__gte='2022-01-01)
Order.objects.filter(paid_at__isnull=True, pk_gte=50_000)

# OR 조건은 인덱스에 활용 시 문제가 있다
User.objects.filter(Q(id=1) | Q(type='A'))  # 이런 OR 구조는 인덱스를 이용할 수 없다.
>>>
SELECT *
FROM User
WHERE id = 1 OR type = 'A';

다음처럼 적용해야 인덱스를 아용할 수 있다.
User.objects.filter(id=1).UNION(User.objects.filter(type='A')  # 인덱스 이용!!!
>>>
(SELECT *
FROM User
WHERE id = 1) UNION (
SELECT *
FROM User
WHERE type = 'A');

# 조건부 정렬 : 별도 컬럼 없이 서울을 항상 앞에 높고 싶다면?
cities = ( 
City.objects
.annotate(
search_order=Case(
When(name='Seoul', then=Value(1)),
default=Valur(0),
output_field=IntegerField(),
)
}
.order_by('-search_order')
}

>>>
SELECT *, 
CASE
WHEN name = 'Seoul' THEN 1
ELSE 0
END AS search_order
FROM City
ORDER BY
search_order DESC

# 동적 컬럼 추가 annotation 

 

[동영상]  SQL을 이해하고 사용하는 Django ORM 신동현 - Pycon Korea 2022

https://www.youtube.com/watch?v=J3pg-Ivl_MU