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
<CombinedExpression: F(price) + Value(1)>

>>> product.save()   # price 4001 
>>> product.price
<CombinedExpression: F(price) + Value(1)>

>>> 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
<CombinedExpression: F(price) + Value(1)>

>>> 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()
            )
        )