django query 기초1

모델 정의

class Product(models.Model): name = models.CharField(max_length=30) price = models.IntegerField(default=0) stock = models.IntegerField(default=0)

Create

>>> from polls.models import Product >>> p = Product(name="coffee", price=4000, stock=1) >>> p.save() >>> p = Product(name="water", price=1000, stock=10) >>> p.save() >>> p = Product(name="chocolate", price=5000, stock=20) >>> p.save()

bulk Create

>>> products = [("coffee", 4000, 1), ("water", 1000, 10), ("chocolate", 5000, 20)] >>> bulk_list = [] >>> for p in products: bulk_list.append(Product(name=[0], price=p[1], stock=p[2])) >>> Product.objects.bulk_create(bulk_list)

Delete

>>> p = Product.objects.get(id=6) >>> p.delete() >>> p = Product.objects.get(id=6) Product.DoesNotExist: Product matching query does not exist.

Bulk delete

>>> p = Product.objects.filter(id__in=[4, 5]).delete()

update

>>> p = Product.objects.get(id = 1) >>> p.price = 100 >>> p.save()

>>> products = Product.objects.all() >>> for p products: p.price = 100 p.save()

Ef) 배열의 index로 접근하여 update는 안됨!! (정보만 볼수 있음)

>>> products[0].price = 100 >>> products[0].save() # 동작 안함

Bulk update

>>> Product.objects.update(price=4000)

Select ( in / not in )

1, 3번 데이터만 가져오고 싶을때

>>> Product.objects.filter(id__in=[1, 3]) >>> exclude_list = [2] >>> Product.objects.exclude(id__in=exclude_list) >>> Product.objects.filter(name__contains='e') .filter(id__in=[1, 3]) .exclude(id__in=[1])

union

>>> p1 = Product.objects.filter(id=1) >>> p2 = Product.objects.filter(id=2) >>> p1.union(p2)

subquery

>>> from django.db.models import Subquery >>> p = Product.objects.all() >>> return_obj = p.filter(id=Subquery(Product.objects.filter(id=2).values('id'))) >>> return_obj.query >>> return_obj.explain()

Sql : select * from polls_product as pp,

(select id from polls_product as p2 where p2.id = 2) pp2

where pp.id = pp2.id

Q() : 쿼리 담당!! ( filter의 조건을 쉽게 쓰자)

>>> from django.db.models import Q >>> Product.objects.filter(Q(name="coffee")) >>> Product.objects.filter(name="coffee")

And

>>> Product.objects.filter(Q(name="coffee"), Q(price__lte=10000)) >>> Product.objects.filter(name="coffee", price__lte=10000)

or

>>> Product.objects.filter(Q(name="coffee") | Q(name="water")) >>> Product.objects.filter(name="coffee") | Product.objects.filter(name="water")

Not

>>> Product.objects.filter(~Q(name="coffee")) >>> Product.objects.exclude(name="coffee")

in

>>> Product.objects.filter(Q(name__in=["coffee", "water"])) >>> Product.objects.filter(name__in= ["coffee", "water"])

Like + and + not in

>>> Product.objects.filter(name__contains="e") .filter(id__in=[1, 3]) .exclude(id__in=[1]) >>> Product.objects.filter(Q(name__contains="e") & Q(id__in=[1, 3]) & ~Q(id__in=[1]))

Union

>>> p1 = Product.objects.filter(Q(id=1)) >>> p2 = Product.objects.filter(Q(id=2)) >>> p1.union(p2)

쿼리 확장

>>> query_dict = { 'name__contains': 'e', 'pk__in' : [1, 3] } >>> Product.objects.filter(**query_dict)

>>> import operator >>> from functools import reduce >>> predicates = [('id__in', [1, 2, 3]), ('name__contains', 'o')] >>> query_list = [Q(x) for x in predicates] >>> Product.objects.filter(reduce(operator.and_, query_list)) >>> Product.objects.filter(reduce(operator.and_, query_list), **query_dict)

F() : 데이터베이스에서 모델 필드 값을 직접 참조하는 데 사용

모든 product price 가격을 20% 인상

>>> products = Product.objects.all() >>> for product in products: product.price *= 1.2 product.save()

cf) 일괄 업데이트 (모든 product price를 4000으로 업데이트)

>>> Product.objects.update(price=4000)

F() 사용

주의!! F() 모델을 객체에 저장 후 유지 됨

>>> product = Product.objects.get(pk=1) >>> product.price 4000 >>> product.price = F('price') + 1 >>> product.price >>> product.save() # price 4001 >>> product.price >>> product.name = 'F() test' >>> product.save(). # price 4002 >>> product = Product.objects.get(pk=1) >>> product.price 4002

>>> product.price = F('price') + 1 >>> product.save() >>> product.price >>> product.refresh_from_db() >>> product.price 4001

두 필드에 대한 연산 ( 파이썬으로 하지 않기 )

F()를 쓰지 않으면 inner select로 가져와야 함

두필드의 연산이 다른 타입이면 해당 타입을 알려줘야 한다. (ExpressionWrapper 사용)

>>> p = Product.objects.annotate(price_stock=F('price')-F('stock')) >>> from django.db.models import ExpressionWrapper, DecimalField >>> p = Product.objects.all().annotate( value_in_stock=ExpressionWrapper( F('price') * F('stock'), output_field=DecimalField() ) ) >>> from django.db.models import CharField >>> p = Product.objects.all().annotate( value_in_stock=ExpressionWrapper( Concat(F('name'), F('stock')), output_field=CharField() ) )

from http://uiandwe.tistory.com/1253 by ccl(A) rewrite - 2020-03-07 02:20:46