TIL | Django | Django ORM과 SQL 관계 : WHERE

WHERE

filter by single column

# SQL
SELECT * FROM Person WHERE id = 1;

# Django ORM
Person.objects.filter(id=1)

filter by comparison perators (비교 연산자)

WHERE age > 10;
Person.objects.filter(age__gt=10)

WHERE age >= 10;
Person.objects.filter(age__gte=10)

WHERE age < 10;
Person.objects.filter(age__lt=10)

WHERE age <= 10;
Person.objects.filter(age__lte=10)

WHERE age != 10;
Person.objects.exclude(age=10)

BETWEEN

# SQL
SELECT * FROM Person WHERE age BETWEEN 10 AND 20;

# Django ORM
Person.objects.filter(age__range=(10, 20))

LIKE

• like binary는 대소문자를 구분한다.

WHERE name like '%A%';
Person.objects.filter(name__icontains='A')

WHERE name like binary '%A%';
Person.objects.filter(name__contains='A')

WHERE name like 'A%';
Person.objects.filter(name__istartwith='A')

WHERE name like binary 'A%';
Person.objects.filter(name__startwith='A')

WHERE name like '%A';
Person.objects.filter(name__iendswith='A')

WHERE name like binary '%A';
Person.objects.filter(name__endswith='A')

IN

# SQL
WHERE id in (1, 2, 3);

# Django ORM
Person.objects.filter(id__in=[1, 2, 3])

AND, OR, NOT (논리 연산자)

# SQL
WHERE gender='male' AND age > 25;

# Django ORM
Person.objects.filter(gender='male', age__gt=25)

# SQL
WHERE gender='male' OR age > 25;

# Django ORM
from django.db.models.import Q
Person.objects.filter(Q(gender='male') | Q(age__gt=25))

# SQL
WHERE NOT gender='male';
Person.objects.exclude(gender='male')

NULL values

WHERE age is NULL;
Person.objects.filter(age__isnull=True)

WHERE age is NOT NULL;
Person.objects.filter(age__isnull=False)

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