Django - SQL

SQL과 django ORM

참고문서

Making queries | Django documentation | Django

QuerySet API reference | Django documentation | Django

Aggregation | Django documentation | Django

기본 준비 사항

Gitlab에서 프로젝트를 다운받으면 아래의 내용이 이미 반영되어 있습니다.

• django app

  • django_extensions 설치

  • users app 생성

  • csv 파일에 맞춰 models.py 작성 및 migrate

    $ python manage.py sqlmigrate users 0001

• db.sqlite3 활용 및 데이터 반영

  • sqlite3 실행

      $ ls
      db.sqlite3 manage.py ...
    $ sqlite3 db.sqlite3

  • csv 파일 data 로드

      sqlite > .tables
      auth_group                  django_admin_log
      auth_group_permissions      django_content_type
      auth_permission             django_migrations
      auth_user                   django_session
      auth_user_groups            auth_user_user_permissions
      users_user
      sqlite > .mode csv
    sqlite > .import users.csv users_user
      sqlite > SELECT COUNT(*) FROM users_user;
      100

• 확인

  • sqlite3에서 스키마 확인

      sqlite > .schema users_user

문제

아래의 문제들을 sql문과 대응되는 orm을 작성 하세요.

Table 생성

• django

    # django
    class User(models.Model):
        first_name = models.CharField(max_length=10)
        last_name = models.CharField(max_length=10)
        age = models.IntegerField()
        country = models.CharField(max_length=10)
        phone = models.CharField(max_length=15)
        balance = models.IntegerField()
  
    # python manage.py makemigrations
    # python manage.py migrate

• SQL

  • sql.sqlite3에 동일하게 테이블 생성

      -- sql

기본 CRUD 로직

1. 모든 user 레코드 조회

   # orm
   users = User.objects.all()
   type(users)
   #=> django.db.models.query.QuerySet
   print(users.query) 
   # queryset만 sql문 출력 가능
   #=> SELECT "users_user"."id", "users_user"."first_name", "users_user"."last_name", "users_user"."age", "users_user"."country", "users_user"."phone", "users_user"."balance" FROM "users_user"

   -- sql
   SELECT * FROM users_user;
   
   id | first_name | last_name | age | country | phone | balance
   1 | 정호 | 유 | 40 | 전라북도 | 016-7280-2855 | 370
   ...

2. user 레코드 생성

   # orm
   User.objects.create(
       first_name='구름',
       last_name='김',
       age=100,
       country='제주도',
       phone='010-1234-1234',
       balance=10000000000000
   )

   -- sql
   INSERT INTO users_user ('first_name', 'last_name', 'age', 'country', 'phone', 'balance')
   VALUES ('근제', '성', 80, '서울', '011-123-1324', 999999999999999999999);

   • 하나의 레코드를 빼고 작성 후 NOT NULL constraint 오류를 orm과 sql에서 모두 확인 해보세요.

     # orm
     IntegrityError: NOT NULL constraint failed: users_user.age

     -- sql
     Error: NOT NULL constraint failed: users_user.last_name

3. 해당 user 레코드 조회

   # orm
   User.objects.get(id=100)
   #=> <User: User object (100)>

   • get 은 쿼리 결과가 반드시 하나여야 한다. 이외에는 모두 오류를 반환한다.

     User.objects.get(last_name='김')
     # MultipleObjectsReturned: get() returned more than one User -- it returned 24!
     User.objects.get(id=1000)
     # DoesNotExist: User matching query does not exist.

     -- sql
     SELECT * FROM users_user
     WHERE id = 100;

4. 해당 user 레코드 수정

   # orm
   user = User.objects.get(id=100)
   user.last_name = '성'
   user.save()

   -- sql
   UPDATE users_user
   SET last_name='최'
   WHERE id = 100;

5. 해당 user 레코드 삭제

   # orm
   User.objects.get(id=101).delete()

   -- sql
   DELETE FROM users_user
   WHERE id = 102;

조건에 따른 쿼리문

1. 전체 인원 수

   # orm
   User.objects.count()

   -- sql
   SELECT COUNT(*) FROM users_user;
   COUNT(id)
   100

2. 나이가 30인 사람의 이름

   # orm
   User.objects.filter(age=30)
   #=> <QuerySet [<User: User object (5)>, <User: User object (57)>, <User: User object (60)>]>
   
   User.objects.filter(age=30).values('first_name')
   #=> <QuerySet [{'first_name': '영환'}, {'first_name': '보람'}, {'first_name': '은영'}]>
   
   type(User.objects.filter(age=30).values('first_name')[0])  
   #=> dict
   
   print(User.objects.filter(age=30).values('first_name').query)  
   #=> SELECT "users_user"."first_name" FROM "users_user" WHERE "users_user"."age" = 30

   -- sql
   SELECT first_name FROM users_user
   WHERE age = 30;
   first_name
   영환
   보람
   은영

3. 나이가 30살 이상인 사람의 인원 수

   대소관계

   __gte : >=

   __gt : >

   __lte : <=

   __lt : <

   # orm
   User.objects.filter(age__gte=30)
   print(User.objects.filter(age__gte=30).query)
   # SELECT "users_user"."id", "users_user"."first_name", "users_user"."last_name", "users_user"."age", "users_user"."country", "users_user"."phone", "users_user"."balance" FROM "users_user" WHERE "users_user"."age" >= 30
   User.objects.filter(age__gte=30).count()

   -- sql
   SELECT COUNT(*) FROM users_user
   WHERE age >= 30;
   
   COUNT(*)
   43

4. 나이가 30이면서 성이 김씨인 사람의 인원 수

   # orm -1 
   User.objects.filter(age=30).filter(last_name='김').count()
   # orm -2
   User.objects.filter(age=30, last_name='김').count()
   # query
   print(User.objects.filter(age=30).filter(last_name='김').query)
   # => SELECT "users_user"."id", "users_user"."first_name", "users_user"."last_name", "users_user"."age", "users_user"."country", "users_user"."phone", "users_user"."balance" FROM "users_user" WHERE ("users_user"."age" = 30 AND "users_user"."last_name" = 김)

   -- sql
   SELECT COUNT(*) FROM users_user
   WHERE age = 30 AND last_name = '김';

   • OR 연산을 하기 위해서는 Q 오브젝트가 필요하다.

     If you need to execute more complex queries (for example, queries with OR statements), you can use Q objects.

     A Q object (django.db.models.Q) is an object used to encapsulate a collection of keyword arguments. These keyword arguments are specified as in “Field lookups” above.

     from django.db.models import Q
     User.objects.filter(Q(age=30) | Q(last_name='김')).count()

5. 지역번호가 02인 사람의 인원 수

   https://docs.djangoproject.com/en/2.2/topics/db/queries/#escaping-percent-signs-and-underscores-in-like-statements

   • i prefix는 case-insensitive의 의미를 지닌다.

   iexact, contains, icontains, startswith, istartswith, endswith, iendswith

   # orm
   User.objects.filter(phone__startswith='02-').count()
   
   # query
   print(User.objects.filter(phone__startswith='02-').query)
   #=> SELECT "users_user"."id", "users_user"."first_name", "users_user"."last_name", "users_user"."age", "users_user"."country", "users_user"."phone", "users_user"."balance" FROM "users_user" WHERE "users_user"."phone" LIKE 02-% ESCAPE '\'

   -- sql
   SELECT COUNT(*) FROM users_user
   WHERE phone LIKE '02-%';

6. 거주 지역이 강원도이면서 성이 황씨인 사람의 이름

   # orm

   -- sql

정렬 및 LIMIT, OFFSET

1. 나이가 많은 사람 10명(내림차순)

   # orm
   User.objects.order_by('-age')[:10]
   
   # query
   print(User.objects.order_by('-age')[:10].query)
   #=> SELECT "users_user"."id", "users_user"."first_name", "users_user"."last_name", "users_user"."age", "users_user"."country", "users_user"."phone", "users_user"."balance" FROM "users_user" ORDER BY "users_user"."age" DESC  LIMIT 10

   -- sql
   SELECT * FROM users_user
   ORDER BY age DESC
   LIMIT 10;
   
   id | first_name | last_name | age | country | phone | balance
   1 | 정호 | 유 | 40 | 전라북도 | 016-7280-2855 | 370
   4 | 미경 | 장 | 40 | 충청남도 | 011-9079-4419 | 250000
   28 | 성현 | 박 | 40 | 경상남도 | 011-2884-6546 | 580000

2. 잔액이 적은 사람 10명 (오름차순)

   # orm
   User.objects.order_by('balance')[:10]

   -- sql
   SELECT * FROM users_user
   ORDER BY balance ASC
   LIMIT 10;

3. 성, 이름 내림차순 순으로 5번째 있는 사람

   # orm
   User.objects.order_by('-last_name', '-first_name')[4]
   #=>  <User: User object (67)>

   -- sql
   SELECT * FROM users_user
   ORDER BY last_name DESC, first_name DESC
   LIMIT 1 OFFSET 4;
   
   id | first_name | last_name | age | country | phone | balance
   67 | 보람 | 허 | 28 | 충청북도 | 016-4392-9432 | 82000

표현식

표현식을 위해서는 aggregate 를 알아야한다.

you can use Django queries that create, retrieve, update and delete individual objects. However, sometimes you will need to retrieve values that are derived by summarizing or aggregating a collection of objects.

개별 object CRUD를 django 쿼리를 통해서 가능.

만약, QuerySet의 요약된 정보를 얻고 싶다면, aggregate 메서드를 써야 한다.

1. 전체 평균 나이

   # orm
   from django.db.models import Avg
   User.objects.aggregate(Avg('age'))
   #=> {'age__avg': 28.23}

   -- sql
   SELECT AVG(age) FROM users_user;
   AVG(age)
   28.23

2. 김씨의 평균 나이

   # orm
   from django.db.models import Avg
   User.objects.filter(last_name='김').aggregate(Avg('age'))

   -- sql
   SELECT AVG(age) FROM users_user
   WHERE last_name = '김';

3. 계좌 잔액 중 가장 높은 값

   # orm
   from django.db.models import Max
   User.objects.aggregate(Max('balance'))

   -- sql
   SELECT MAX(balance) FROM users_user;

4. 계좌 잔액 총액

   # orm
   from django.db.models import Sum
   User.objects.aggregate(Sum('balance'))

   -- sql
   SELECT SUM(balance) FROM users_user;

Group by

annotate는 개별 item에 추가 필드를 구성한다.
추후 1:N 관계에서 활용된다.

1. 지역별 인원 수

   # orm
   User.objects.values('country')
    # <QuerySet [{'country': '전라북도'}, {'country': '경상남도'}, {'country': '전라남도'}, ...
   from django.db.models import Count
   User.objects.values('country').annotate(Count('country'))
   # <QuerySet [{'country': '강원도', 'country__count': 14}, {'country': '경기도', 'country__count': 9}, {'country': '경상남도', 'country__count': 9}, {'country': '경상북도', 'country__count': 15}, {'country': '전라남도', 'country__count': 10}, {'country': '전라북도', 'country__count': 11}, {'country': '제주특별자치도', 'country__count': 9}, {'country': '충청남도', 'country__count': 9}, {'country': '충청북도', 'country__count': 14}]>

   -- sql
   SELECT country, COUNT(country) FROM users_user
   GROUP BY country;
   
   country | COUNT(country)
   강원도 | 14
   경기도 | 9
   경상남도 | 9
   경상북도 | 15
   전라남도 | 10
   전라북도 | 11
   제주특별자치도 | 9
   충청남도 | 9
   충청북도 | 14