[Django, MySQL] Query Set API

• 장고 ORM 쿼리와 SQL문을 직접 비교하면서 학습해본다.
• 먼저 예시로 들 Table은 아래와 같다.

CREATE TABLE Person (
    id int,
    name varchar(50),
    age int NOT NULL,
    gender varchar(10),
);

class Person(models.Model):
    name = models.CharField(max_length=50, blank=True)
    age = models.IntegerField()
    gender = models.CharField(max_length=10, blank=True)

1. Select

1) Select All rows

• SQL문

Select * from Person;

• QuerySet API

persons = Person.objects.all()
for person in persons:
    print(person.name)
    print(person.gender)
    print(person.age)

2) Select 특정 Column들

• SQL문

SELECT name, age FROM Person;

• QuerySet API

Person.objects.only('name', 'age')

3) Fetch distinct rows

• SQL문

Select DISTINCT name, age FROM Person;

• QuerySet API

Person.objects.values('name', 'age').distinct()

4) Fetch specific number of rows

• SQL문

Select * FROM Person LIMIT 10;

• QuerySet API

Person.objects.all()[0:10]

5) LIMIT AND OFFSET Keywords

• SQL문

Select * FROM Person OFFSET 5 LIMIT 5;

• QuerySet API

Person.objects.all()[5:10]

2. Where

1) Filter by single column

• SQL문

Select * FROM Person WHERE id =1;

• QuerySet API

Person.objects.filter(id=1)

2) Filter by comparison operators

• SQL문

WHERE age > 18;
WHERE age >= 18;
WHERE age < 18;
WHERE age <= 18;
WHERE age != 18;

• QuerySet API

Person.objects.filter(age__gt=18)
Person.objects.filter(age__gte=18)
Person.objects.filter(age__lt=18)
Person.objects.filter(age__lte=18)
Person.objects.exclude(age=18)

3) BETWEEN CLAUSE

• SQL문

SELECT * from Person WHERE age BETWEEN 10 AND 20;

• QuerySet API

Person.objects.filter(age__range=(10,20))

4) LIKE OPERATOR

• SQL문

WHERE name like '%A%';
WHERE name like binary '%A%';
WHERE name like 'A%';
WHERE name like binary 'A%';
WHERE name like '%A';
WHERE name like binary '%A';

• QuerySet API

Person.objects.filter(name__icontains='A')
Person.objects.filter(name__contains='A')
Person.objects.filter(name__istartswith='A')
Person.objects.filter(name__startswith='A')
Person.objects.filter(name__iendswith='A')
Person.objects.filter(name__endswith='A')

5) IN OPERATOR

• SQL문

WHERE id in (1,2);

• QuerySet API

Person.objects.filter(id__in=[1,2]);

6) AND, OR, and NOT Operators

• SQL문

WHERE gender='male' AND age > 25;
WHERE gender = 'male' OR age > 25;
WHERE NOT gender = 'male';

• QuerySet API

Person.objects.filter(gender='male', age__gt = 25)
from django.db.models import Q
Person.objects.filter(Q(gender='male') | Q(age__gt = 25))
Person.objects.exclude(gender='male')

7) NULL Values

• SQL문

WHERE age is NULL;
WHERE age is NOT NULL;

• QuerySet API

Person.objects.filter(age__isnull=True)
Person.objects.filter(age__isnull=False)

#Alternate approach
Person.objects.filter(age=None)
Person.objects.esclude(age=None)

8) ORDER BY Keyword

• SQL문

SELECT * from Person order by age;
SELECT * from Person order by age DESC;

• QuerySet API

Person.objects.order_by('age')
Person.objects.order_by('-age')

3. Update

1) Update single row

• SQL문

UPDATE Person SET age = 20 WHERE id = 1;

• QuerySet API

person = Person.objects.get(id=1)
person.age = 20
person.save()

2) Update multiple rows

• SQL문

UPDATE Person SET age = age * 1.5;

• QuerySet API

from django.db.models import F
Person.objects.update(age = F('age')*1.5)

4. Insert Into

• SQL문

INSERT INTO Person VALUES ('jack', '23', 'male');

• QuerySet API

Person.objects.create(name='jack', age=23, gender='male')

5. DELETE

• SQL문

DELETE from Person;
DELETE from Person WHERE age < 10

• QuerySet API

Person.objects.all().delete()
Person.objects.filter(age__lt=10).delete()

6. Aggregation

• SQL문

SELECT MIN(age) from Person;
SELECT MAX(age) from Person;
SELECT AVG(age) from Person;
SELECT SUM(age) from Person;
SELECT COUNT(*) from Person;

• QuerySet API

Person.objects.all().aggregate(Min('age'))
Person.objects.all().aggregate(Max('age'))
Person.objects.all().aggregate(Avg('age'))
Person.objects.all().aggregate(Sum('age'))
Person.objects.count()

7. Join

• JOIN이란, 참조관계에 있는 두 컬럼이 참조키를 교집합으로 테이블을 결합하는 행위를 말한다.

class Publisher(models.Model):
    name = models.CharField(max_length=100)

class Book(models.Model):
    publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)

• Fetch publisher name for a book

SELECT name
FROM Book
LEFT JOIN Publisher
ON Book.publisher_id = Publisher.id
WHERE Book.id=1;

• SQL문

publisher = Book.objects.get(id=1).select_related('publisher')
book.publisher.name

• Fetch books which have specific publisher

SELECT *
FROM Book
WHERE Book.publisher_id = 1;

• SQL문

publisher = Publisher.objects.get(id=1).prefetch_related('book_set')
publisher.book_set.all()