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.
# 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_books73# 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_523>>> pubs[0].below_512# 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_books1323
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# 1classReporter(models.Model): username = models.CharField(max_length=10)# NclassArticle(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)를 가져올 수 있다.
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 ---------------------------------------------------------------------------AttributeErrorTraceback (most recentcalllast)<ipython-input-11-e72fca1b8134>in<module>---->1comment.article_pkAttributeError:'Comment'objecthasnoattribute'article_pk'
무조건 article_id이다
별명 사용 불가!
In [12]: comment.article_id Out[12]:1In [13]: comment.article SELECT"articles_article"."id","articles_article"."title","articles_article"."content"FROM"articles_article"WHERE"articles_article"."id"=1LIMIT21Executiontime: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 1Execution 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"=1LIMIT21Executiontime:0.000308s [Database: default]<QuerySet [<Comment: Comment #1 for Post #1>]>
default related_name인.comment_set으로 호출하지 않기 위해 models.py 수정
classComment(models.Model): content = models.TextField() article = models.ForeignKey(Article, on_delete=models.CASCADE, related_name='comments')# model 중에 Article을 가리키고 있다def__str__(self):returnf'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 1Execution 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 21Execution time: 0.000353s [Database: default]In [4]: article Out[4]: <Article: #1 (1stpost-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 21Execution time: 0.000374s [Database: default]<QuerySet [<Comment: Comment #1 for Post #1>]>
sqlite>selectcount(*) from users_user;100sqlite> .schema users_userCREATE 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 onsqlite> .schema users_userCREATE 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 limit10;id,first_name,last_name,age,country,phone,balance1,"정호","유",40,"전라북도",016-7280-2855,3702,"경희","이",36,"경상남도",011-9854-5133,59003,"정자","구",37,"전라남도",011-4177-8170,31004,"미경","장",40,"충청남도",011-9079-4419,2500005,"영환","차",30,"충청북도",011-2921-4284,2206,"서준","이",26,"충청북도",02-8601-7361,5307,"주원","민",18,"경기도",011-2525-1976,3908,"예진","김",33,"충청북도",010-5123-9107,37009,"서현","김",23,"제주특별자치도",016-6839-1106,4300010,"서윤","오",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.