One to Many Relationship

ORM에서의 or 연산

Q를 활용한다

In [1]: from django.db.models import Q                  

In [2]: User.objects.filter(Q(age=30) | Q(last_name='김'
   ...: )).count()                                      
Out[2]: 25

QuerySet

In [4]: User.objects.all()                              
Out[4]: <QuerySet [<User: User object (1)>, <User: User object (2)>, <User: User object (3)>, <User: User object (4)>, <User: User object (5)>, <User: User object (6)>, <User: User object (7)>, <User: User object (8)>, <User: User object (9)>, <User: User object (10)>, <User: User object (11)>, <User: User object (12)>, <User: User object (13)>, <User: User object (14)>, <User: User object (15)>, <User: User object (16)>, <User: User object (17)>, <User: User object (18)>, <User: User object (19)>, <User: User object (20)>, '...(remaining elements truncated)...']>

In [5]: type(User.objects.all())                        
Out[5]: django.db.models.query.QuerySet

• Query (method) 할 때

• 조회 (loop up)

  • get()

    • Returns the object matching the given lookup parameters

    • return오직 하나 or Error 발생

    • ex) RUD (Read / Update / Delete)

  • filter()

    • Returns a new QuerySet containing objects that match the given lookup parameters.

      • (없으면 비어있는 QuerySet)

    • ex) Search

    • AND

      • method chaining

      • filter. filter. ....

    • OR

      • Q Object

      • (Q ( ) | Q ( ) )

    • LIKE

      • ex) age__lte

      • ex) name__startswith

  • exclude()

    • Returns a new QuerySet containing objects that do not match the given lookup parameters.

ex)

In [1]: Article.objects.all()                                                                                      
Out[1]: SELECT "articles_article"."id",
       "articles_article"."title",
       "articles_article"."content"
  FROM "articles_article"
 LIMIT 21

Execution time: 0.000412s [Database: default]
<QuerySet []>

In [2]: Article.objects.create(title='1st post',content='haha')                                                    
INSERT INTO "articles_article" ("title", "content")
VALUES ('1st post', 'haha')

Execution time: 0.024278s [Database: default]
Out[2]: <Article: #1 (1st post - haha)>

Aggregation

• 개별 Object CRUD를 django query

• QuerySet을 합쳐진 결과로 보고싶을 때 사용

  • ex)

    # Average price across all books.
    >>> from django.db.models import Avg
    >>> Book.objects.all().aggregate(Avg('price'))
    {'price__avg': 34.35}
    
    # Max price across all books.
    >>> from django.db.models import Max
    >>> Book.objects.all().aggregate(Max('price'))
    {'price__max': Decimal('81.20')}
    
    # Difference between the highest priced book and the average price of all books.
    >>> from django.db.models import FloatField
    >>> Book.objects.aggregate(
    ...     price_diff=Max('price', output_field=FloatField()) - Avg('price'))
    {'price_diff': 46.85}
    
    # All the following queries involve traversing the Book<->Publisher
    # foreign key relationship backwards.
    
    # Each publisher, each with a count of books as a "num_books" attribute.
    >>> from django.db.models import Count
    >>> pubs = Publisher.objects.annotate(num_books=Count('book'))
    >>> pubs
    <QuerySet [<Publisher: BaloneyPress>, <Publisher: SalamiPress>, ...]>
    >>> pubs[0].num_books
    73
    
    # Each publisher, with a separate count of books with a rating above and below 5
    >>> from django.db.models import Q
    >>> above_5 = Count('book', filter=Q(book__rating__gt=5))
    >>> below_5 = Count('book', filter=Q(book__rating__lte=5))
    >>> pubs = Publisher.objects.annotate(below_5=below_5).annotate(above_5=above_5)
    >>> pubs[0].above_5
    23
    >>> pubs[0].below_5
    12
    
    # The top 5 publishers, in order by number of books.
    >>> pubs = Publisher.objects.annotate(num_books=Count('book')).order_by('-num_books')[:5]
    >>> pubs[0].num_books
    1323

Annotate

ex) COUNT( )

SELECT country, COUNT(country) FROM countries;

• DB에서 가져온 결과에서 필요한 값을 조작하여 가져옴

1:N (one to many)

• 1 has many N

• N must belong to 1

  • (그래서 cascading이 가능하다)

  • ex)

    • Article has many Comments

  • Comment belongs to Article

• Foreign Key는 N 에게 준다

from django.db import models

# 1
class Reporter(models.Model):
    username = models.CharField(max_length=10)

# N
class Article(models.Model):
    title = models.CharField(max_length=10)
    content = models.TextField()
    reporter = models.ForeignKey(Reporter, on_delete=models.CASCADE)
    #reporter는 우리가 만든 이름인데, 모델명과 같게 하는 것이 best practice!

• articles_article table에 reporter_id column이 추가된다

• reporter의 경우 article_set으로 N개 (QuerySet)를 가져올 수 있다.

• article의 경우 reporter`로 1에 해당하는 object를 가져올 수 있다

• on_delete : 참조 대상이 삭제되는 경우

  • CASCADE

    • 해당 객체(reporter)가 삭제 되었을 때 참조하는 객체도(article) 모두 삭제

  • PROTECT

    • 참조하는 객체(article)가 존재하면 삭제 금지

  • SET_NULL

    • NULL 값으로 치환

    • NOT NULL option이 있는 경우 활용 할 수 없음

  • SET_DEFAULT

    • default값(article)을 참조하게 함

-- sql

CREATE TABLE "aricles_article" (
 "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "title" VARCHAR(10) NOT NULL,
    "content" TEXT NOT NULL,
    "reporter_id" INTEGER NOT NULL REFERENCES "artices_reporter" ("id") DEFERRABLE INITIALLY DEFERRED
);

기본 쿼리

1. 준비

Reporter.objects.create(username='요트맨')
Reporter.objects.create(username='chloe')
Reporter.objects.create(username='camila')
Reporter.objects.create(username='bella')

r1 = Reporter.objects.get(pk=1)

2. article 생성 (N)

In [3]: article = Article()                             

In [4]: article.title = '제목1'                         

In [5]: article.content = '내용1'                       

In [6]: r1 = Reporter.objects.get(pk=1)                 

In [7]: article.reporter = r1                           
  # reporter_id는 숫자(INTEGER)를 저장
  # article.reporter_id = 1
  
In [8]: article.save()                                  

In [9]: article                                         
Out[9]: <Article: Article object (1)>

In [10]: article.reporter                               
Out[10]: <Reporter: Reporter object (1)>

In [11]: article.reporter.username                      
Out[11]: '요트맨'

a2 = Article.objects.create(title='제목2', conent='내용2', reporter=r1)

3. 1:N 관계 활용

# 1. 글의 작성자
a2 = Article.objects.get(pk=2)
a2.reporter

# 2. 글의 작성자의 username
a2.reporter.username

# 3. 글의 작성자의 id
a2.reporter.id
a2.reporter_id

# 4. 작성자(1)의 글
r1 = Reporter.objects.get(pk=1)
r1.article_set.all()
# -> <QuerySet [<Article: Article object(2)>]

# 5. 1번 글의 작성자가 쓴 모든 글
a1 = Article.objects.get(pk=1)
a1.reporter.article_set.all()

• article_set

Comment exercise

In [3]: comment = Comment()                                                                                        

In [4]: comment                                                                                                    
Out[4]: <Comment: Comment #None for Post #None>

In [5]: comment.content = 'Comment for Post #1'                                                                    

In [6]: comment                                                                                                    
Out[6]: <Comment: Comment #None for Post #None>

In [7]: comment.article_id                                                                                         

In [8]: comment.article_id =1                                                                                      

In [9]: comment                                                                                                    
Out[9]: <Comment: Comment #None for Post #1>

주의 할 점

In [11]: comment.article_pk                                                                                        
---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
<ipython-input-11-e72fca1b8134> in <module>
----> 1 comment.article_pk

AttributeError: 'Comment' object has no attribute 'article_pk'

• 무조건 article_id이다

  • 별명 사용 불가!

In [12]: comment.article_id                                                                                        
Out[12]: 1

In [13]: comment.article                                                                                           
SELECT "articles_article"."id",
       "articles_article"."title",
       "articles_article"."content"
  FROM "articles_article"
 WHERE "articles_article"."id" = 1
 LIMIT 21

Execution time: 0.000417s [Database: default]
Out[13]: <Article: #1 (1st post - haha)>

In [16]: article = Article.objects.first()                                                                         
SELECT "articles_article"."id",
       "articles_article"."title",
       "articles_article"."content"
  FROM "articles_article"
 ORDER BY "articles_article"."id" ASC
 LIMIT 1

Execution time: 0.000373s [Database: default]

In [17]: article.comment_set                                                                                       
Out[17]: <django.db.models.fields.related_descriptors.create_reverse_many_to_one_manager.<locals>.RelatedManager at 0x7f9603eb1ac8>

In [18]: article.comment_set.all()                                                                                 
Out[18]: SELECT "articles_comment"."id",
       "articles_comment"."content",
       "articles_comment"."article_id"
  FROM "articles_comment"
 WHERE "articles_comment"."article_id" = 1
 LIMIT 21

Execution time: 0.000308s [Database: default]
<QuerySet [<Comment: Comment #1 for Post #1>]>

• default related_name인.comment_set으로 호출하지 않기 위해 models.py 수정

  class Comment(models.Model):
      content = models.TextField()
      article = models.ForeignKey(Article, on_delete=models.CASCADE, related_name='comments')
                                  # model 중에 Article을 가리키고 있다
      
      def __str__(self):
          return f'Comment #{self.pk} for Post #{self.article_id}'

  • related_name= 'comments' 로 설정함

    • 단, 여기서는 option을 바꾼 것 이므로 migration 안해도 됨!

• 수정 후 comments로 호출 가능해짐

  In [2]: comment = Comment.objects.first()                                                                          
  SELECT "articles_comment"."id",
         "articles_comment"."content",
         "articles_comment"."article_id"
    FROM "articles_comment"
   ORDER BY "articles_comment"."id" ASC
   LIMIT 1
  
  Execution time: 0.000873s [Database: default]
  
  In [3]: article = comment.article                                                                                  
  SELECT "articles_article"."id",
         "articles_article"."title",
         "articles_article"."content"
    FROM "articles_article"
   WHERE "articles_article"."id" = 1
   LIMIT 21
  
  Execution time: 0.000353s [Database: default]
  
  In [4]: article                                                                                                    
  Out[4]: <Article: #1 (1st post - haha)>
  
  In [5]: comment                                                                                                    
  Out[5]: <Comment: Comment #1 for Post #1>
  
  In [6]: comment.article                                                                                            
  Out[6]: <Article: #1 (1st post - haha)>
  
  In [7]: article.comments                                                                                           
  Out[7]: <django.db.models.fields.related_descriptors.create_reverse_many_to_one_manager.<locals>.RelatedManager at 0x7f4a84996320>
  
  In [8]: article.comments.all()                                                                                     
  Out[8]: SELECT "articles_comment"."id",
         "articles_comment"."content",
         "articles_comment"."article_id"
    FROM "articles_comment"
   WHERE "articles_comment"."article_id" = 1
   LIMIT 21
  
  Execution time: 0.000374s [Database: default]
  <QuerySet [<Comment: Comment #1 for Post #1>]>

Data Seeding

CSV -> DB

sqlite> .mode csv

sqlite> .import users.csv users_user

sqlite> select count(*) from users_user;
100
sqlite> .schema users_user
CREATE TABLE IF NOT EXISTS "users_user" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "first_name" varchar(20) NOT NULL, "last_name" varchar(20) NOT NULL, "age" integer NOT NULL, "country" varchar(20) NOT NULL, "phone" varchar(20) NOT NULL, "balance" integer NOT NULL);
sqlite> .headers on
sqlite> .schema users_user
CREATE TABLE IF NOT EXISTS "users_user" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "first_name" varchar(20) NOT NULL, "last_name" varchar(20) NOT NULL, "age" integer NOT NULL, "country" varchar(20) NOT NULL, "phone" varchar(20) NOT NULL, "balance" integer NOT NULL);
sqlite> select*from users_user limit 10;
id,first_name,last_name,age,country,phone,balance
1,"정호","유",40,"전라북도",016-7280-2855,370
2,"경희","이",36,"경상남도",011-9854-5133,5900
3,"정자","구",37,"전라남도",011-4177-8170,3100
4,"미경","장",40,"충청남도",011-9079-4419,250000
5,"영환","차",30,"충청북도",011-2921-4284,220
6,"서준","이",26,"충청북도",02-8601-7361,530
7,"주원","민",18,"경기도",011-2525-1976,390
8,"예진","김",33,"충청북도",010-5123-9107,3700
9,"서현","김",23,"제주특별자치도",016-6839-1106,43000
10,"서윤","오",22,"충청남도",011-9693-6452,49000

Data Integrity

• the maintenance of, and the assurance of the accuracy and consistency of data over its entire life-cycle

• a critical aspect to the design, implementation and usage of any system which stores, processes, or retrieves data

Entity Integrity

• defines each row to be unique within its table.

  • No two rows can be the same.

• To achieve this, a primary key can be defined.

  • The primary key field contains a unique identifier – no two rows can contain the same unique identifier.

Referential Integrity

• concerned with relationships.

  • When two or more tables have a relationship, we have to ensure that the foreign key value matches the primary key value at all times.

  • We don’t want to have a situation where a foreign key value has no matching primary key value in the primary table.

  • This would result in an orphaned record.

So referential integrity will prevent users from:

• Adding records to a related table if there is no associated record in the primary table.

• Changing values in a primary table that result in orphaned records in a related table.

• Deleting records from a primary table if there are matching related records.

Domain Integrity

• concerns the validity of entries for a given column.

• Selecting the appropriate data type for a column is the first step in maintaining domain integrity.

• Other steps could include, setting up appropriate constraints and rules to define the data format and/or restricting the range of possible values.

User-Defined Integrity

• allows the user to apply business rules to the database that aren’t covered by any of the other three data integrity types.

Django settings.py

https://github.com/django/django/blob/master/django/conf/global_settings.py

참고하기

Update, delete는 개별 객체와 쿼리셋에 적용가능

Excercises

준비

onetomany app 생성

# models.py
class User(models.Model):
    username = models.CharField(max_length=10)
    
class Article(models.Model):
    title = models.CharField(max_length=100)
    content = models.TextField()
    user = models.ForeignKey(User, on_delete=models.CASCADE)

class Comment(models.Model):
    content = models.TextField()
    article = models.ForeignKey(Article, on_delete=models.CASCADE)
    user = models.ForeignKey(User, on_delete=models.CASCADE)

from onetomany.models import User, Article, Comment

# objects
u1 = User.objects.create(username='Kim')
u2 = User.objects.create(username='Lee')

a1 = Article.objects.create(title='1글', user=u1)
a2 = Article.objects.create(title='2글', user=u2)
a3 = Article.objects.create(title='3글', user=u2)
a4 = Article.objects.create(title='4글', user=u2)

c1 = Comment.objects.create(content='1글1댓', article=a1, user=u2)
c2 = Comment.objects.create(content='1글2댓', article=a1, user=u2)
c3 = Comment.objects.create(content='2글1댓', article=a2, user=u1)
c4 = Comment.objects.create(content='4글1댓', article=a4, user=u1)
c5 = Comment.objects.create(content='3글1댓', article=a3, user=u2)
c6 = Comment.objects.create(content='3글2댓', article=a3, user=u1)

문제

1. 1번 유저가 작성한 글들

   u1.article_set.all()

2. 2번 유저가 작성한 댓글의 내용을 모두 출력

   for comment in u2.comment_set.all():
       print(comment.content)

3. 3번 글의 작성된 댓글의 내용을 모두 출력

   for comment in a3.comment_set.all():
       print(comment.content)

   {% for comment in article.comment_set.all %}
      {{ comment.content }}
   {% endfor %}

4. 1글이라는 제목인 게시글들

   Article.objects.filter(title='1글')

5. 글이라는 단어가 들어간 게시글들

   Article.objects.filter(title__contains='글')

6. 댓글(N)들 중에 해당되는 글(1)의 제목이 1글인 것

   Comment.objects.filter(article__title='1글')
   print(Comment.objects.filter(article__title='1글').query)

   • 1:N 관계에서 1의 열에 따라서, 필터링

     SELECT "onetomany_comment"."id", "onetomany_comment"."content", "onetomany_comment"."article_id", "onetomany_comment"."user_id" FROM "onetomany_comment" INNER JOIN "onetomany_article" ON ("onetomany_comment"."article_id" = "onetomany_article"."id") WHERE "onetomany_article"."title" = 1글

+

Django 에게 맡겨서 sqlite 열기

python manage.py dbshell

Shell 에서 ORM query 바로 보여주는 option

python manage.py shell_plus --print-sql