1、orm简介
2、
models.py
from django.db import models # Create your models here. class Book(models.Model):
id = models.AutoField(primary_key=True)
title = models.CharField(max_length=32, unique=True)
state = models.BooleanField()
pub_date = models.DateField()
price = models.DecimalField(max_digits=8, decimal_places=2) # 999999.00
publish = models.CharField(max_length=22) # 10000000.00 溢出
C:\Windows\system32>mysql -uroot -proot mysql> create database orm; # 创建orm数据库
Query OK, 1 row affected (0.00 sec)
settings默认sqlite3数据库
设置database为mysql
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME': 'orm', # 要连接的数据库，连接前需要创建好
'USER': 'root', # 连接数据库的用户名
'PASSWORD': 'root', # 连接数据库的密码
'HOST': '127.0.0.1', # 连接主机，默认本级
'PORT': 3306, # 端口 默认3306
}
}
（3）django调用的是pymysql
报错：
E:\PycharmProjects\cnblog>python manage.py makemigrations
Traceback (most recent call last):
File "C:\Users\Venicid\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\mysql\base.py", line , in <module>
import MySQLdb as Database
ModuleNotFoundError: No module named 'MySQLdb' The above exception was the direct cause of the following exception: Traceback (most recent call last):
File "manage.py", line , in <module>
execute_from_command_line(sys.argv)
File "C:\Users\Venicid\AppData\Local\Programs\Python\Python36\lib\site-packages\django\core\management\__init__.py", line , in execute_from_command_line
utility.execute()
File "C:\Users\Venicid\AppData\Local\Programs\Python\Python36\lib\site-packages\django\core\management\__init__.py", line , in execute
django.setup()
File "C:\Users\Venicid\AppData\Local\Programs\Python\Python36\lib\site-packages\django\__init__.py", line , in setup
apps.populate(settings.INSTALLED_APPS)
File "C:\Users\Venicid\AppData\Local\Programs\Python\Python36\lib\site-packages\django\apps\registry.py", line , in populate
app_config.import_models()
File "C:\Users\Venicid\AppData\Local\Programs\Python\Python36\lib\site-packages\django\apps\config.py", line , in import_models
self.models_module = import_module(models_module_name)
File "C:\Users\Venicid\AppData\Local\Programs\Python\Python36\lib\importlib\__init__.py", line , in import_module
return _bootstrap._gcd_import(name[level:], package, level)
File "<frozen importlib._bootstrap>", line , in _gcd_import
File "<frozen importlib._bootstrap>", line , in _find_and_load
File "<frozen importlib._bootstrap>", line , in _find_and_load_unlocked
File "<frozen importlib._bootstrap>", line , in _load_unlocked
File "<frozen importlib._bootstrap_external>", line , in exec_module
File "<frozen importlib._bootstrap>", line , in _call_with_frames_removed
File "C:\Users\Venicid\AppData\Local\Programs\Python\Python36\lib\site-packages\django\contrib\auth\models.py", line , in <module>
from django.contrib.auth.base_user import AbstractBaseUser, BaseUserManager
File "C:\Users\Venicid\AppData\Local\Programs\Python\Python36\lib\site-packages\django\contrib\auth\base_user.py", line , in <module>
class AbstractBaseUser(models.Model):
File "C:\Users\Venicid\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\models\base.py", line , in __new__
new_class.add_to_class('_meta', Options(meta, app_label))
File "C:\Users\Venicid\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\models\base.py", line , in add_to_class
value.contribute_to_class(cls, name)
File "C:\Users\Venicid\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\models\options.py", line , in contribute_to_class
self.db_table = truncate_name(self.db_table, connection.ops.max_name_length())
File "C:\Users\Venicid\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\__init__.py", line , in __getattr__
return getattr(connections[DEFAULT_DB_ALIAS], item)
File "C:\Users\Venicid\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\utils.py", line , in __getitem__
backend = load_backend(db['ENGINE'])
File "C:\Users\Venicid\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\utils.py", line , in load_backend
return import_module('%s.base' % backend_name)
File "C:\Users\Venicid\AppData\Local\Programs\Python\Python36\lib\importlib\__init__.py", line , in import_module
return _bootstrap._gcd_import(name[level:], package, level)
File "C:\Users\Venicid\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\mysql\base.py", line , in <module>
) from err
django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module.
解决：
__init__.py
import pymysql pymysql.install_as_MySQLdb()
注意2:确保配置文件中的INSTALLED_APPS中写入我们创建的app名称
注意3：如果报错的话
django.core.exceptions.ImproperlyConfigured: mysqlclient 1.3.3 or newer is required; you have 0.7.11.None
注意4: 如果想打印orm转换过程中的sql，需要在settings中进行如下配置：
LOGGING = {
'version': 1,
'disable_existing_loggers': False,
'handlers': {
'console':{
'level':'DEBUG',
'class':'logging.StreamHandler',
},
},
'loggers': {
'django.db.backends': {
'handlers': ['console'],
'propagate': True,
'level':'DEBUG',
},
}
}
最后通过两条数据库迁移命令即可在指定的数据库中创建表 ： python manage.py makemigrations
python manage.py migrate
插入数据，创建表
3、单表操作之添加记录
url
from django.urls import path, re_path, include
from app01 import views urlpatterns = [
re_path(r'^index/$', views.index, name='index')
]
方法1：
view
from django.shortcuts import render, HttpResponse # Create your views here. from app01.models import Book # 导入Book对象 def index(request):
# 添加表记录 # 方式1： # 所有字段必须和modles的一一对应
book_obj = Book(
id=1,
title='pyhon红宝书',
state=True,
price=10,
pub_date='2012-02-21',
publish='人民出版社'
)
book_obj.save() # 相当于commit提交 return HttpResponse('<h1>数据操作成功</h1>')
启动django
方法2：
from django.shortcuts import render, HttpResponse # Create your views here. from app01.models import Book def index(request):
# 添加表记录 # 方式2： 推荐使用 Book.objects：表的管理器
book_obj = Book.objects.create(
title='php',
state=True,
price=100,
pub_date='2018-09-09',
publish='南京出版社',
) # create返回值就是当前生成表的对象记录
print(book_obj.title)
print(book_obj.price)
print(book_obj.pub_date) return HttpResponse('<h1>数据操作成功</h1>')
4、单表操作之查询API
查询API
<1> all(): 查询所有结果 <2> filter(**kwargs): 它包含了与所给筛选条件相匹配的对象 <3> get(**kwargs): 返回与所给筛选条件相匹配的对象，返回结果有且只有一个，
如果符合筛选条件的对象超过一个或者没有都会抛出错误。 <4> exclude(**kwargs): 它包含了与所给筛选条件不匹配的对象 <5> order_by(*field): 对查询结果排序 <6> reverse(): 对查询结果反向排序 <8> count(): 返回数据库中匹配查询(QuerySet)的对象数量。 <9> first(): 返回第一条记录 <10> last(): 返回最后一条记录 <11> exists(): 如果QuerySet包含数据，就返回True，否则返回False <12> values(*field): 返回一个ValueQuerySet——一个特殊的QuerySet，运行后得到的并不是一系列
model的实例化对象，而是一个可迭代的字典序列
<13> values_list(*field): 它与values()非常相似，它返回的是一个元组序列，values返回的是一个字典序列 <14> distinct(): 从返回结果中剔除重复纪录
modles
from django.db import models # Create your models here. class Book(models.Model):
id = models.AutoField(primary_key=True)
title = models.CharField(max_length=32, unique=True)
state = models.BooleanField()
pub_date = models.DateField()
price = models.DecimalField(max_digits=8, decimal_places=2) # 999999.00
publish = models.CharField(max_length=22) # 10000000.00 溢出 def __str__(self): # 调用Book对象返回title
return self.title
QuerySet：新的数据类型
<QuerySet [<Book: pyhon红宝书>, <Book: php>]>
# ###### 查询表记录
"""
1.方法的返回值
2.方法的调用者
"""
（1）all方法
# 1. all() 调用者：Book.objects 返回值QuerySet对象
book_list = Book.objects.all()
print(book_list) # [obj1, obj2, ....] for obj in book_list:
print(obj.title, obj.price) print(book_list[1].title)
print(book_list[1].price)
（2）first last
# 2. first,last() : 调用者QuerySet对象 返回值：model对象
book_obj1 = Book.objects.all().first()
book_obj2 = Book.objects.all()[0]
print(book_obj1)
print(book_obj2)
（3）filter
# 3.filter() 调用者：Book.objects 返回值QuerySet对象
book_list = Book.objects.filter(price=100) # [obj1,obj2...]
print(book_list)
book_list1 = Book.objects.filter(price=100, title='go')
print(book_list1)
（4）get方法
# 4.get() 有且只有一个返回值，才有意义 返回值model对象
book_obj1 = Book.objects.get(title='go') # book_obj1 = Book.objects.get(title='lalala') # 没有返回值，报错
# book_obj2 = Book.objects.get(price=100) # php go都是price=100 报错
print(book_obj1)
（5）exclude
# 5.exclude() 返回值QuerySet对象
book_obj = Book.objects.exclude(title='go') # 取反
print(book_obj)
（6）order by
# 6. order by() 调用者QuerySet对象 返回值QuerySet对象
book_obj1 = Book.objects.order_by('id')
book_obj2 = Book.objects.order_by('-id') # 反序
book_obj3 = Book.objects.order_by('price', 'id') # price一样的话，按照id排序
print(book_obj1)
print(book_obj2)
print(book_obj3)
（7）count
# 7. count 调用者QuerySet对象 返回值int数字
ret = Book.objects.all().count() # 数字
print(ret)
（8） exist
# 8. exist()
ret = Book.objects.all()
ret1 = Book.objects.all().exists() # limit 1
if ret:
print('ok') if ret1:
print('oK2')
（9）values
# 9. values() 调用者QuerySet对象 返回者QuerySet对象
ret = Book.objects.all()
for item in ret:
print(item.title)
ret1 = Book.objects.all().values('price')
# < QuerySet[{'price': Decimal('10.00')}, {'price': Decimal('100.00')}, {'price': Decimal('100.00')}] >
print(ret1)
ret2 = Book.objects.all().values('price', 'title')
# <QuerySet [{'price': Decimal('10.00'), 'title': 'pyhon红宝书'}, {'price': Decimal('100.00'), 'title': 'php'}, {'price': Decimal('100.00'), 'title': 'go'}]>
print(ret2)
"""
values:
temp = []
for obj in Book.objects.all():
temp.append({
'price'=obj.price,
'title'=obj.title,
})
return temp
"""
（10）values_list
# 10. values_list方法 调用者QuerySet对象 返回值QuerySet对象
ret = Book.objects.all().values_list('price', 'title')
# < QuerySet[(Decimal('10.00'), 'pyhon红宝书'), (Decimal('100.00'), 'php'), (Decimal('100.00'), 'go')] > print(ret)
"""
values: list字典
<QuerySet [{'price': Decimal('10.00'), 'title': 'pyhon红宝书'},
{'price': Decimal('100.00'), 'title': 'php'},
{'price': Decimal('100.00'), 'title': 'go'}]>
values_list: 元组
<QuerySet [(Decimal('10.00'), 'pyhon红宝书'),
(Decimal('100.00'), 'php'),
(Decimal('100.00'), 'go')]>
"""
（11）distinct
# 11. distinct
ret = Book.objects.all().distinct() # id不同，不能去重
print(ret) ret1 = Book.objects.all().values('price')
print(ret1) ret = Book.objects.all().values('price').distinct() # 存在相同price，可以去重
print(ret)
（12）点语法
# 12. 点语法
Book.objects.all().filter('price').order_by('id').filter('title').reverse().first()
5、单表查询之模糊查询
基于双下划线的模糊查询
Book.objects.filter(price__in=[100,200,300])
Book.objects.filter(price__gt=100)
Book.objects.filter(price__lt=100)
Book.objects.filter(price__range=[100,200])
Book.objects.filter(title__contains="python")
Book.objects.filter(title__icontains="python")
Book.objects.filter(title__startswith="py")
Book.objects.filter(pub_date__year=2012)
# ############ 单表查询 模糊查询
ret = Book.objects.filter(price__gt=100) # 大于100
print(ret)
ret1 = Book.objects.filter(title__startswith='p') # 以p开头
print(ret1)
ret2 = Book.objects.filter(title__startswith='py')
print(ret2)
ret3 = Book.objects.filter(title__contains='h') # 包含h就行
print(ret3)
ret4 = Book.objects.filter(title__icontains='h') # 包含h，H就行
print(ret4)
ret5 = Book.objects.filter(price__in=[10, 100]) # 价格是10 或者100
print(ret5) # 对于 pub_date = models.DateField()
ret6 = Book.objects.filter(pub_date__year=2018) # year是2018
print(ret6)
ret7 = Book.objects.filter(pub_date__month=6) # year是2018
print(ret7)
6、单表之删除与修改表记录
（1）delete
# 13. delete
# 调用者QuerySet对象
Book.objects.filter(price=200).delete() # 直接过滤删除
ret = Book.objects.filter(price=100).delete()
print(ret) # (2, {'app01.Book': 2}) 返回删除的数据2条 # 调用者models对象
Book.objects.filter(price=10).first().delete() # 先查询再删除
（2）update
# 14. update 调用者QuerySet对象
ret = Book.objects.filter(title='go').update(title='PYTHON')
print(ret)