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๋ฒ ์ ์ ๊ฐ ์์ฑํ ๊ธ๋ค
u1.article_set.all()
2๋ฒ ์ ์ ๊ฐ ์์ฑํ ๋๊ธ์ ๋ด์ฉ์ ๋ชจ๋ ์ถ๋ ฅ
for comment in u2.comment_set.all(): print(comment.content)
3๋ฒ ๊ธ์ ์์ฑ๋ ๋๊ธ์ ๋ด์ฉ์ ๋ชจ๋ ์ถ๋ ฅ
for comment in a3.comment_set.all(): print(comment.content)
<div data-gb-custom-block data-tag="for"> {{ comment.content }}
```
1๊ธ์ด๋ผ๋ ์ ๋ชฉ์ธ ๊ฒ์๊ธ๋ค
Article.objects.filter(title='1๊ธ')
๊ธ์ด๋ผ๋ ๋จ์ด๊ฐ ๋ค์ด๊ฐ ๊ฒ์๊ธ๋ค
Article.objects.filter(title__contains='๊ธ')
๋๊ธ(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
์ด๊ธฐ
sqlite
์ด๊ธฐpython manage.py dbshell
Shell ์์ ORM query ๋ฐ๋ก ๋ณด์ฌ์ฃผ๋ option
python manage.py shell_plus --print-sql
Last updated
Was this helpful?