SQL and Django ORM

참고문서

Making queries | Django documentation | Django

QuerySet API reference | Django documentation | Django

Aggregation | Django documentation | Django

기본 준비 사항

• 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;
    1000

• 확인

  • 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
    
    CREATE TABLE IF NOT EXISTS "users_user" (
        "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
        "first_name" varchar(10) NOT NULL, 
        "last_name" varchar(10) NOT NULL, 
        "age" integer NOT NULL, 
        "country" varchar(10) NOT NULL, 
        "phone" varchar(15) NOT NULL, 
        "balance" integer NOT NULL
    );

기본 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;

2. user 레코드 생성

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

   -- sql
   
   INSERT INTO users_user(first_name, last_name, age, country, phone, balance)
   VALUES ('주현', '김', 26, '경기도', '010-0000-0000', 100000000000);

   • 하나의 레코드를 빼고 작성 후 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은 query 결과가 반드시 하나여야 한다. (이외에는 모두 return error!)

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

     -- 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
   
   # ver1)
   User.objects.all().count()
   # ver2) - 이걸 쓰라
   User.objects.count()

   -- sql
   
   -- ver1)
   SELECT COUNT(*) FROM users_user;
   
   -- ver2)
   SELECT COUNT(id) FROM users_user;

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;

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;

1. 나이가 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_nuser
   WHERE age = 30 AND last_name ='김';

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

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

   # 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-%';

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

   # 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 를 알아야한다.

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