一:ORM(Object Relationship Mapping)
ORM:对象关系(关系型数据库)映射。程序中的模型类映射到 关系型数据库中的一个表,模型类的实例化对象映射到 数据表中的一条记录。 操作模型类中的对象,就可以对应于表中的记录。
二:模型类
Django中的模型类需要继承Model类(django.db.models.Model), 模型类属性对应于表字段。 例如:创建Product模型类 class Product(models.Model): proname = models.CharField(max_length=20) price = models.FloatField()
模型创建完成后,需要迁移,迁移需要两步:
1.制作迁移计划
python manage.py makemigrations
2.执行迁移任务
python manage.py migrate
三:控制台操作Model
1. 进入控制台
python manage.py shell 注意:这种进入控制台的方式的搜索路径包含了当前项目
2插入表记录
方式一:通过模型类的对象管理器插入记录 p1 = Product.objects.create(proname='洗衣机',price=208.5)
方式二:实例化模型对象,并save() p2 = Product(proname='电视机',price=5000) p2.save()
3.查询记录
查询单条记录product = Product.objects.get(id=1) # get方法只能返回一条记录,否则报错
查询所有记录products = Product.objects.all()注意:all()方法查询出的对象是一个QuerySet类型的对象,该对象可以被遍历。
4.过滤查询
使用对象管理器的filter()方法进行过滤查询articles = Article.objects.filter(pub_date=date.today())对应的SQL为:select * from articles where pub_date='2018-09-19'
字段后面跟双下划线"__",表示特殊查询articles = Article.objects.filter(pub_date__year='2001')注:查询的是年份为2001年的文章
articles = Article.objects.filter(pub_date__gte='2000-01-01')注:查询的是日期大于等于2000-01-01。
常用的双下划线魔法参数有:
__year __month __startswith __gte __lte __contains (相当于模糊查询)
articles = Article.objects.exclude(pub_date__month='8') 注:exclude()方法排除指定条件的记录
5.限制查询
articles = Article.objects.all()[:2] # 查询前两条记录articles = Article.objects.all()[1:3] # 查询从索引为1(第二条记录)到索引为2的记录articles = Article.objects.all()[:3:2] # 从索引为0开始查询,结束索引为2,步幅为2
6.排序查询
articles = Article.objects.order_by("pub_date") # 按照pub_date字段升序排序
articles = Article.objects.order_by("-pub_date") # 按照pub_date字段降序排序
还可以对排序后的结果进行切片,或取得某个对象article = Article.objects.order_by("-pub_date")[0] # 降序排序后,取第一个模型对象
articles = Article.objects.order_by("-pub_date")[:2] # 降序后,取前两个对象,存储于QuerySet中
四:原生SQL查询
原生模糊查询
articles = Article.objects.raw("select * from articles where title like '%%o%%'")
在原生SQL中使用占位符articles = Article.objects.raw("select * from articles where title='%s'"%'good day')如果SQL中有多个占位符:articles = Article.objects.raw("select * from articles where title='%s' or title='%s'"%('good day','ordinary day'))
articles = Article.objects.raw("select * from articles where title=%s",['good day'])如果SQL中有多个占位符:articles = Article.objects.raw("select * from articles where title=%s or title=%s",['good day','bad day'])
五:Q查询
对于复杂组合查询条件,使用Q查询很方便。 Q查询依赖于Q对象(from django.db.models import Q) 通过实例化Q对象,将Q对象传递到filter()、get()方法中可以组合出多种查询条件。 Q对象之间可以使用&、|。在Q对象前加~表示Q封装条件的否定条件。
例一:查询21世纪的文章,并且title中包含字母'o'。q1 = Q(pub_date__gte='2000-01-01')q2 = Q(title__contains='o')articles = Article.objects.filter(q1&q2)
例二:q1与q2先进行或,然后再与title字段的条件进行and操作。articles = Article.objects.filter(q1|q2,title__contains='o')
例三:查询非q1条件的记录articles = Article.objects.filter(~q1)
六:自定义对象管理器
自定义对象管理器继承Manager类(from django.db.models import Manager) 在自定义管理器中实现自己的方法
class CakeManager(Manager): # 自定义对象管理器 def create_cake(self,name,price,color): cake = self.model() cake.name = name cake.price = price cake.color = color cake.save() return cake
class Cake(models.Model): name = models.CharField(max_length=20) price = models.FloatField() color = models.CharField(max_length=20) cakemanager = CakeManager() # 关联自定义对象管理器
七、附录
1、python终端代码
C:\python\DjangoDay3>python manage.py shell #进入控制台,可加载当前项目
Python 3.7.0 (v3.7.0:1bf9cc5093, Jun 27 2018, 04:59:51) [MSC v.1914 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> import sys
>>> sys.path #当前路径
['C:\\python\\DjangoDay3', 'C:\\python37\\python37.zip', 'C:\\pyt
hon37\\DLLs', 'C:\\python37\\lib', 'C:\\python37', 'C:\\python37\
\lib\\site-packages']
>>> from myapp.models import * #导入模型
>>> p1 = Product.objects.create(proname='洗衣机',price=208.5) #添加记录
>>> p2 = Product.objects.create(proname='电视机',price=508.5)
>>> products = Product.objects.all()
>>> products = Product.objects.all()
>>> p1.price=300
>>> p1.save
<bound method Model.save of <Product: Product object (2)>>
>>> p1.save()
>>> p3 =Product(proname ='冰箱',price = 5000) #实例化对象添加记录
>>> p3.save()# 保存上面记录
>>> product = Product.objects.get(id=1) #查询id为1的记录
>>> product.proname
'洗衣机'
>>> p3.proname
'冰箱'
>>> product = Product.objects.get(id=3) #获取id 为3的记录
>>> product.price #调用属性
508.5
>>> products =Product.objects.all() #获取所有记录
>>> type(products) #查看类型
<class 'django.db.models.query.QuerySet'>
>>> for p in products: #遍历查询到的所有记录
... print(p.proname)
...
洗衣机
洗衣机
电视机
冰箱
>>> p1.proname #调用类属性
'洗衣机'
>>> p2.proname
'电视机'
>>> p1.proname ='电扇' 调用类属性并更改记录
>>> p1.save() #保存上面更改的记录
>>> for p in products: #遍历 数据表中的记录
... print(p.proname)
...
洗衣机
洗衣机
电视机
冰箱
>>> products = Product.objects.exclude(proname='电视机') 查询除电视机以外的记录
>>> for p in products:
... print(p.proname) #遍历查询到的记录
...
洗衣机
电扇
冰箱
>>> products = Product.objects.filter(proname='电视机') #过滤查询
>>> for p in products:
... print(p.proname)
...
电视机
>>>
2、MySQL代码
ywh@ubuntu:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.23-0ubuntu0.16.04.1 (Ubuntu)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use mydb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables
-> ;
+----------------------------+
| Tables_in_mydb |
+----------------------------+
| auth_group |
| auth_group_permissions |
| auth_permission |
| auth_user |
| auth_user_groups |
| auth_user_user_permissions |
| django_admin_log |
| django_content_type |
| django_migrations |
| django_session |
| myapp_product |
+----------------------------+
11 rows in set (0.00 sec)
mysql> select * from myapp_product;
+----+-----------+-------+
| id | proname | price |
+----+-----------+-------+
| 1 | 洗衣机 | 208 |
+----+-----------+-------+
1 row in set (0.00 sec)
mysql> select * from myapp_product;
+----+-----------+-------+
| id | proname | price |
+----+-----------+-------+
| 1 | 洗衣机 | 208 |
| 2 | 洗衣机 | 208.5 |
| 3 | 电视机 | 508.5 |
+----+-----------+-------+
3 rows in set (0.00 sec)
mysql> select * from myapp_product;
+----+-----------+-------+
| id | proname | price |
+----+-----------+-------+
| 1 | 洗衣机 | 208 |
| 2 | 洗衣机 | 208.5 |
| 3 | 电视机 | 508.5 |
+----+-----------+-------+
3 rows in set (0.00 sec)
mysql> select * from myapp_product;
+----+-----------+-------+
| id | proname | price |
+----+-----------+-------+
| 1 | 洗衣机 | 208 |
| 2 | 洗衣机 | 208.5 |
| 3 | 电视机 | 508.5 |
+----+-----------+-------+
3 rows in set (0.00 sec)
mysql> select * from myapp_product;
+----+-----------+-------+
| id | proname | price |
+----+-----------+-------+
| 1 | 洗衣机 | 208 |
| 2 | 洗衣机 | 208.5 |
| 3 | 电视机 | 508.5 |
+----+-----------+-------+
3 rows in set (0.00 sec)
mysql> select * from myapp_product;
+----+-----------+-------+
| id | proname | price |
+----+-----------+-------+
| 1 | 洗衣机 | 208 |
| 2 | 洗衣机 | 300 |
| 3 | 电视机 | 508.5 |
+----+-----------+-------+
3 rows in set (0.00 sec)
mysql> select * from myapp_product;
+----+-----------+-------+
| id | proname | price |
+----+-----------+-------+
| 1 | 洗衣机 | 208 |
| 2 | 洗衣机 | 300 |
| 3 | 电视机 | 508.5 |
| 4 | 冰箱 | 5000 |
+----+-----------+-------+
4 rows in set (0.00 sec)
mysql> select * from myapp_product;
+----+-----------+-------+
| id | proname | price |
+----+-----------+-------+
| 1 | 洗衣机 | 208 |
| 2 | 电扇 | 300 |
| 3 | 电视机 | 508.5 |
| 4 | 冰箱 | 5000 |
+----+-----------+-------+
4 rows in set (0.00 sec)
mysql>
3、python终端代码
python manage.py shell#进入控制台
Python 3.7.0 (v3.7.0:1bf9cc5093, Jun 27 2018, 04:59:51) [MSC v.1914
64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more informati
on.
(InteractiveConsole)
>>> from myapp.models import * #导入模型
>>> Article.objects.create(title='bad day',content='糟糕的一天',pub 插入记录
_dae ='1998-10-10')
<Article: Article object (2)>
>>> from datetime import date #导入模块
>>> Article.objects.create(title='nice day',content='good的一天',pu
b_dae =date.today())
<Article: Article object (3)>
>>> Article.objects.create(title='nice day',content='good的一天',pu
b_dae =date(2015,12,21))
<Article: Article object (4)>
>>> article =Article.objects.get(id=3)
>>> articles = Article.objects.filter(pub_dae=date.today())
>>> len(articles) #查看查询到的记录个数
1
>>> articles = Article.objects.filter(pub_dae__year='1988') #魔法方法 查询出版年份为1988年
>>> articles[0]
<Article: Article object (1)>
>>> articles[0].title
'good day'
C:\python\DjangoDay3>python manage.py shell #进入控制台
Python 3.7.0 (v3.7.0:1bf9cc5093, Jun 27 2018, 04:59:51) [MSC v.1914
64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more informati
on.
(InteractiveConsole)
>>> from datetime import date
>>> articles =Article.objects.filter(title__startswith='b') #魔法方法,查询标题以b开头的记录
>>> articles[0].title
'bad day'
>>>
>>> articles =Article.objects.filter(title__contains='a') #魔法方法,查询标题中包含a字母的所有记录
>>> len(articles) #查看查询到记录的个数
4
>>> articles =Article.objects.filter(pub_dae__gte='2001-01-01') #魔法方法,查询2001-01-01之后的记录/gte表示大于等于。
>>> len(articles)#查看查询到记录的个数
2
>>> articles[0].pub_dae #查看查询到的第一条记录
datetime.date(2018, 10, 8)
>>> articles[1].pub_dae #查看查询到的第二条记录
datetime.date(2015, 12, 21)
>>>
>>> articles =Article.objects.filter(pub_dae__lte='2001-01-01') #查询2001-01-01之前的记录, lte表示小于等于
>>> articles[1].pub_dae #查看查询到的记录
datetime.date(1998, 10, 10)
>>> articles =Article.objects.all()[:2] #切片查询 0到1两条记录
>>> articles[1].title
'bad day'
>>> articles =Article.objects.all()[:3:2] #切片查询,0到2中步频为2的记录
>>> articles[1].title
'nice day'
>>> articles[0].title
'good day'
>>> articles =Article.objects.all()[1:3] #切片查询1到2的记录
>>> articles[0].title
'bad day'
>>> articles =Article.objects.order_by("pub_dae") #以pub_dae字段,排序查询(默认升序)
>>> for a in articles:
... print(a.title) #遍历
...
good day
bad day
nice day
nice day
>>> for a in articles:
... print(a.pub_dae)
...
1988-08-08
1998-10-10
2015-12-21
2018-10-08
>>> articles =Article.objects.order_by("-pub_dae")#以pub_dae字段,排序查询,降序。
>>> for a in articles:
... print(a.pub_dae) #遍历
...
2018-10-08
2015-12-21
1998-10-10
1988-08-08
>>> articles =Article.objects.order_by("pub_dae")[:3]
>>> for a in articles:
... print(a.pub_dae)
...
1988-08-08
1998-10-10
2015-12-21
#原生MySQL语句查询
>>> articles =Article.objects.raw("select * from articles") #查询所有记录
>>> type(articles)
<class 'django.db.models.query.RawQuerySet'>
>>> for a in articles: #遍历
... print(a.title)
...
good day
bad day
nice day
nice day
>>> articles =Article.objects.raw("select * from article
s where title like '%%o%%'") 模糊查询,查询标题中含有字母o的记录
>>> for a in articles:
... print(a.title)
...
good day
>>> articles =Article.objects.raw("select * from article
s where title=%s",['good day']) #模糊查询,占位%S 查询title为good dayd的记录
>>> for a in articles:
... print(a.title) 遍历
...
good day
>>> articles =Article.objects.raw("select * from article
s where title=%s or title= %s",['good day','bad day'])#模糊查询,占位%S 查询title为good dayd 或bad day的记录.
>>> for a in articles:
... print(a.title)
...
good day
bad day
>>> type(articles) #查看数据类型
<class 'django.db.models.query.RawQuerySet'>
#Q查询
>>> from django.db.models import Q #导入模块Q
>>> q = Q(title__contains='o') #查询title中包含字母o的记录
>>> articles =Article.objects.filter(q)
>>> for a in articles:
... print(a.title)
...
good day
>>> q1 =Q(pub_dae__gte='2000-01-01') #查询时间大于等于2000-01-01的记录
>>> q2 = Q(title__contains='o') #查询title中包含字母o的记录
>>> articles =Article.objects.filter(q1|q2) #查询满足q1或q2的记录
>>> for a in articles:
... print(a.title)
...
good day
nice day
nice day
>>> articles =Article.objects.filter(q1&q2) ##查询满足q1或q2的记录
>>> for a in articles:
... print(a.title)
...
>>> articles =Article.objects.filter(~q1) #查询不满足q1的记录
>>> for a in articles:
... print(a.title)
...
good day
bad day
>>>
# manager应用终端
C:\python\DjangoDay3>python manage.py startapp managerapp #创建应用
C:\python\DjangoDay3>python manage.py makemigrations managerapp 迁移计划日志
Migrations for 'managerapp':
managerapp\migrations\0001_initial.py
- Create model Cake
C:\python\DjangoDay3>python manage.py migrate #迁移到数据库
Operations to perform:
Apply all migrations: admin, auth, contenttypes, managerapp, myapp, sessions
Running migrations:
Applying managerapp.0001_initial... OK
C:\python\DjangoDay3>python manage.py shell 启动控制台
Python 3.7.0 (v3.7.0:1bf9cc5093, Jun 27 2018, 04:59:51) [MSC v.19
14 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more informa
tion.
(InteractiveConsole)
>>> from managerapp.models import * #导入模型
>>> Cake.cakemanager.create_cake('生日蛋糕',20.5,'黄色')
>>> Cake.cakemanager.create_cake('祝贺蛋糕',100,'红色' )
>>> cakes = Cake.cakemanager.query_cake() #查询所有记录
>>> type(cakes)
<class 'django.db.models.query.QuerySet'>
>>> for i in cakes:
... print(i.name)
...
生日蛋糕
祝贺蛋糕
祝贺蛋糕
>>> for i in cakes:
... print(i)
...
生日蛋糕
祝贺蛋糕
祝贺蛋糕
>>>
4、python Django下myapp应用中模型类代码
# models 文件
from django.db import models
from django.db.models import Manager
class CakeManager(Manager):#自定义对象管理器
def create_cake(self,name,price,color):
cake = self.model()
cake.name =name
cake.price = price
cake.color = color
cake.save()
def query_cake(self): #类方法
return super().all()
class Cake(models.Model):
name = models.CharField(max_length=20)
price = models.FloatField()
color = models.CharField(max_length=20)
cakemanager =CakeManager() #关联自定义对象管理器
def __str__(self):
return self.name
class Meta:
db_table ='cakes'
5、python Django下managerappp应用中模型类代码
#models文件
from django.db import models
class Product(models.Model):
proname = models.CharField(max_length=20)
price = models.FloatField()
class Article(models.Model):
title = models.CharField(max_length=20) #标题
content = models.TextField()# 内容,类型
pub_dae = models.DateField()#发表日期
class Meta:
db_table ='articles'#自定义模型对应名称