Django中ORM简介与单表数据操作

一. ORM简介 

概念:.ORM框架是用于实现面向对象编程语言种不同类型系统的数据之间的转换

 构建模型的步骤:重点

(1).配置目标数据库信息,在seting.py中设置数据库信息
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME':'bms',# 要连接的数据库，连接前需要创建好
        'USER':'root',# 连接数据库的用户名
        'PASSWORD':'',# 连接数据库的密码
        'HOST':'127.0.0.1',# 连接主机，默认本级
        'PORT':3306 #  端口 默认3306
    }
}

(2) 构建虚拟对象数据库,在APP的models.py中以类的形式定义模型
所定义的模型类似于mysql中的表

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)
    price=models.DecimalField(max_digits=5,decimal_places=2)
    publish=models.CharField(max_length=32)
    pub_date=models.DateTimeField()
    def __str__(self):
        return self.title
    class Mate:
        db_table="book"  #给创建的模型定义一个别名

(3) 通过模型在目标数据库中创建相应的表,
#注意:在建表之前必须先创建好seting.py文件中mysql配置的库
在建表之前要在url.py路由分发的同级目录下的启动文件__init__.py中导入
import pymysql
pymysql.install_as_MySQLdb()

然后在项目路径文件下执行以下命令在mysql中创建表
python manage.py makemigrations
python manage.py migrate

(4) 在URL分发器中配置路由转发
from django.contrib import admin
from django.urls import path
from app01 import  views

urlpatterns = [
    path('admin/', admin.site.urls),
    path('add/',views.add),
    path('query/',views.query)
]

(5) 在视图函数views.py中 ,通过对模型的操作实现对数据库的操作
#1 添加记录
def add(request):
    # 方式1:
    # book=Book(title="Python",price="200",publish="人民出版社",pub_date="2012-12-12")
    # book.save()
    # print(book.title)
    # 方式2:
    book=Book.objects.create(title="go",price="100",publish="人民出版社",pub_date="2012-12-12")
    book=Book.objects.create(title="java",price="500",publish="人民出版社",pub_date="2012-12-12")
    # print(book.title)
    # print(book.price)
    return HttpResponse("添加成功")

#2 查询操作
def query(request):
   '''
   # KEY : 一定区分queryset对象与模型类对象

   # 1 all() : 查询所有记录
   queryset=Book.objects.all()
   print(queryset)
   # <QuerySet [<Book: Book object (1)>, <Book: Book object (2)>]>
   # <QuerySet [<Book: Python>, <Book: Linux>]>

   for obj in queryset:   #  queryset对象是模型类对象的集合
       print(obj.title)
       print(obj.price)

   print(queryset[0].title)
   # print(queryset.title) #X
   print(queryset.count())

   # 2 first() ,last() #第一个最后一个
   book_obj=Book.objects.all().first() # 返回模型类对象
   print(book_obj.title)
   book_obj=Book.objects.all().last() # 返回模型类对象
   print(book_obj.title)
   # 3 filter:返回的queryset对象
   queryset=Book.objects.all().filter(price=100)
   print(queryset) #<QuerySet []>
   queryset=Book.objects.filter(title="Python",price=100)
   print(queryset)

   # 4 get:有且只有一个查询结果:返回模型类对象
   #obj=Book.objects.get(title="python1")
   #obj=Book.objects.get(price=200)
   obj=Book.objects.get(title="python")
   #print(obj.title)
   obj.count()

   # 5 排除方法;按条件排除
   # queryset=Book.objects.exclude(price=200)
   # 6 queryset.orderby
   # queryset=Book.objects.all() # 默认按id排序
   # queryset=Book.objects.all().order_by("-price","-id")
   # print("queryset",queryset)
   # 7 reverse方法
   # Book.objects.all().order_by("-price").reverse()
   # 8 exists
   print(Book.objects.all().exists())
   if Book.objects.all().exists():
       print("OK")

   '''
   # 9 values
   queryset=Book.objects.filter(price=200).values("title","price")
   '''
   l=[] # queryset()
   for obj in queryset:
      l.append({
         "title":obj.title,
         "price":obj.price,
      }) 
      
   [obj1,obj2]   
   [{"title":"python","price":200},{"title":"linux","price":200}]   
   
   '''
   print(queryset) # <QuerySet [{'title': 'Python', 'price': Decimal('200.00')}, {'title': 'Linux', 'price': Decimal('200.00')}]>
   print(queryset.count())

   queryset = Book.objects.filter(price=200).values_list("title", "price")
   '''
      l=[] # queryset()
      for obj in queryset:
         l.append((obj.title,obj.price)) 

      [obj1,obj2]   
      [{"title":"python","price":200},{"title":"linux","price":200}]   

   '''
   print(queryset) # <QuerySet [('Python', Decimal('200.00')), ('Linux', Decimal('200.00'))]>
   print(queryset.count())
   # 10 distinct
   ret=Book.objects.values("price").distinct()
   print(ret)

   ############  模糊查询  ######################

   # 查询价格大于200的书籍
   ret=Book.objects.filter(price__gte=200)
   print(ret)
   # 查询书籍名称以py开头
   ret=Book.objects.filter(title__startswith="py")
   ret=Book.objects.filter(title__contains="py")
   ret=Book.objects.filter(title__icontains="py")
   ret=Book.objects.filter(price__range=[100,500])
   ret=Book.objects.filter(price__in=[100,500])
   ret=Book.objects.filter(pub_date__year=2012,pub_date__month=12)

   #3 删除

   #Book.objects.all().delete()
   #Book.objects.filter(price=200).delete()
   # book=Book.objects.get(id=3)
   # book.delete()

  #4 修改
   Book.objects.all().update(price=600,pub_date="2011-11-11")


   '''
      queryset的可调用方法:
          queryset.count() 
          queryset.filter() # 返回的queryset对象
          queryset.order_by()  # 返回的queryset对象
          queryset.reverse()  # 返回的queryset对象
          queryset.values()  # 返回的queryset对象
          queryset.values_list()  # 返回的queryset对象
          queryset.exists  # 返回的布尔值
          queryset.delete()  # 返回的布尔值
   '''
   return HttpResponse("查询成功")

###################浓缩版数据库操作在视图函数vies.py中完成##################

二 表记录操作

(1) 添加记录
# 方式1
book=Book(title="",price="",publish="",pub_date="2012-12-12")
book.save()
# 方式2
book=Book.objects.create(title="",price="",publish="",pub_date="2012-12-12")
print(book.id)
print(book.title)

(2) 查询记录
KEY:queryset数据类型

Book.objects.all() # 返回queryset对象 [obj,obj2,obj3,...]
Book.objects.all()first() # Book模型类对象
Book.objects.all().last() # Book模型类对象
Book.objects.filter(title="python") # 返回queryset对象 [obj,obj2,obj3,...]
Book.objects.get(title="python") # Book模型类对象
Book.objects.exclude(title="python") # 返回queryset对象 [obj,obj2,obj3,...]
Book.objects.all().count() # 返回int
Book.objects.all().order_by("-price")# 返回queryset对象
Book.objects.all().order_by("-price").reverse() # 返回queryset对象
Book.objects.all().exist() # 返回布尔值
Book.objects.all().values("title") # 返回queryset对象 [{"title":""},{"title":""}]
Book.objects.all().values_list("title","price") # 返回queryset对象 [("python",122),("linux",233)]
Book.objects.all().values("price").distinct()

模糊查询:
Book.objects.filter(price__gt=100)
Book.objects.filter(price__lt=100)
Book.objects.filter(price__in=[100,200,300])
Book.objects.filter(price__range=[100,200])
Book.objects.filter(title__startswith="py")
Book.objects.filter(title__contains="py")
Book.objects.filter(title__icontains="py")
Book.objects.filter(pub_date__year="2012")

(3) 删除

Book.objects.all().delete()
Book.objects.get(id=1).delete()

(4) 更新
Book.objects.filter(id__gt=5).update(price=100)

作业讲解:
1 查询老男孩出版社出版过的价格大于200的书籍
Book.objects.filter(price__gt=200,publish="老男孩出版社")

2 查询2017年8月出版的所有以py开头的书籍名称
Book.objects.filter(title__icontains="py",pub_date__year=2017,pub_date__month=8).values("title")

3 查询价格为50,100或者150的所有书籍名称及其出版社名称
Book.objects.filter(price__in=[50,100,150]).values("title","publish")

4 查询价格在100到200之间的所有书籍名称及其价格
Book.objects.filter(price__range=[100,200]).values("title","price")

5 查询所有人民出版社出版的书籍的价格（从高到低排序，去重）
Book.objects.filter(publish="人民出版社").order_by("-price").values("price").distinct()

二 项目运行流程 

 

1 确定项目需求,实现相应的功能
第一步 先创建模型对数据库进行操作
第二步 创建URL与该URL调用的视图函数之间的映射表
第三部 按照页面的操作对视图函数中对功能进行封装
1 render 对视图函数传递的参数进行渲染 
2 redirect 传递队像硬编码 index.html

第四步 在模板文件建立相应的前端页面

2 对项目进行测试其功能

实例1ORM图书管理系统

第一步 创建项目,先创建模型对数据库进行操作
1.使用默认数据库配置文件

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)
    price=models.DecimalField(max_digits=5,decimal_places=2)
    publish=models.CharField(max_length=32)
    pub_date=models.DateTimeField()
    def __str__(self):
        return self.title
    class Mate:
        db_table="book"

第二步 创建URL与该URL调用的视图函数之间的映射表

from django.contrib import admin
from django.urls import path,re_path

from book import views
urlpatterns = [
    path('admin/', admin.site.urls),
    path('books/', views.books),
    path('books/add/', views.addbook),
    re_path('books/delete/(d+)', views.delbook),
    re_path('^books/edit/(d+)$', views.editbook),
]

第三步 创建视图函数中对数据的操作进行封装

from django.shortcuts import render,HttpResponse,redirect

# Create your views here.
from book import models

def books(request):

    queryset=models.Book.objects.all()
    return render(request,"books.html",{"queryset":queryset})

def addbook(request):
    if request.method=="POST":
        # 方式1
        # title=request.POST.get("title")
        # price=request.POST.get("price")
        # publish=request.POST.get("publish")
        # pub_date=request.POST.get("pub_date")
        # book=models.Book.objects.create(titles=title,price=price,publish=publish,pub_date=pub_date)
        # 方式2
        print(request.POST)
        data=request.POST.dict()
        data.pop("csrfmiddlewaretoken")
        book=models.Book.objects.create(**data)  #  保证提交键值对的键必须和数据库表字段一致
        #return HttpResponse("添加成功")
        return redirect("/books/")
    else:
        return render(request,'addbook.html')


def delbook(request,del_id):

    models.Book.objects.filter(nid=del_id).delete()
    return redirect("/books/")



def editbook(request,edit_book_id):
    if request.method=="GET":
        edit_book=models.Book.objects.filter(nid=edit_book_id).first()

        return render(request,"editbook.html",{"edit_book":edit_book})
    else:
        title = request.POST.get("title")
        price = request.POST.get("price")
        publish = request.POST.get("publish")
        pub_date = request.POST.get("pub_date")
        models.Book.objects.filter(nid=edit_book_id).update(title=title,price=price,publish=publish,pub_date=pub_date)

        return redirect("/books/")

第四步 创建静态文件夹static放置Jquery,css,bootstrap样式文件

STATIC_URL = '/static/' # 静态文件的物理路径的别名

STATICFILES_DIRS=[
    os.path.join(BASE_DIR,"static") #  静态文件的物理路径
]

第五步 在模板文件夹template下建立相应的前端页面
 添加功能页面

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <link rel="stylesheet" href="/static/bootstrap/css/bootstrap.css">
</head>
<body>
<h3>添加书籍</h3>
<div class="row">
    <div class="col-md-4 col-md-offset-3">
        <form action="/books/add/" method="post">
            {% csrf_token %}
            <div class="form-group">
                <label for="title">书籍名称</label>
                <input class="form-control" type="text" id="title" placeholder="名称" name="title">
            </div>
            <div class="form-group">
                <label for="price"> 价格</label>
                <input class="form-control" type="text" id="price" placeholder="价格" name="price">
            </div>
            <div class="form-group">
                <label for="publish">出版社</label>
                <input class="form-control" type="text" id="publish" placeholder="出版社" name="publish">
            </div>
            <div class="form-group">
                <label for="pub_date">出版日期</label>
                <input class="form-control" type="date" id="pub_date" placeholder="出版日期" name="pub_date">
            </div>
            <input type="submit"value="submit" class="btn btn-default pull-right">
        </form>
    </div>
</div>

</body>
</html>

 查看功能页面

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <script src="/static/jquery-3.1.js"></script>
    <link rel="stylesheet" href="/static/bootstrap/css/bootstrap.css">
</head>
<body>

<h3>书籍列表</h3>

<div class="row">
    <div class="col-md-8 col-md-offset-2">
        <a href="/books/add/" class="btn btn-primary"> 添加书籍</a>
        <table class="table table-striped table-hover">
            <thead>
            <tr>
                <th>ID</th>
                <th>书籍名称</th>
                <th>价格</th>
                <th>出版社</th>
                <th>出版日期</th>
                <th>操作</th>
            </tr>
            </thead>
            <tbody>
                {% for book in queryset %}
                <tr>
                     <td>{{ forloop.counter }}</td>
                     <td>{{ book.title }}</td>
                     <td>{{ book.price }}</td>
                     <td>{{ book.publish }}</td>
                     <td>{{ book.pub_date|date:"Y/m/d" }}</td>
                     <td>
                         <a href="/books/delete/{{ book.nid }}">删除</a>
                         <a href="/books/edit/{{ book.nid }}">编辑</a>
                     </td>
                </tr>
                {% endfor %}

            </tbody>
        </table>
    </div>
</div>


<script>
     $("h3").click(function () {
         $(this).css("color","red")
     })
</script>

</body>
</html>

   编辑功能的页面

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <link rel="stylesheet" href="/static/bootstrap/css/bootstrap.css">
</head>
<body>
<h3>编辑书籍</h3>
<div class="row">
    <div class="col-md-4 col-md-offset-3">
        <form action="" method="post">
            {% csrf_token %}
            <div class="form-group">
                <label for="title">书籍名称</label>
                <input class="form-control" type="text" id="title" placeholder="名称" name="title" value="{{ edit_book.title }}">
            </div>
            <div class="form-group">
                <label for="price"> 价格</label>
                <input class="form-control" type="text" id="price" placeholder="价格" name="price" value="{{ edit_book.price }}">
            </div>
            <div class="form-group">
                <label for="publish">出版社</label>
                <input class="form-control" type="text" id="publish" placeholder="出版社" name="publish" value="{{ edit_book.publish }}">
            </div>
            <div class="form-group">
                <label for="pub_date">出版日期</label>
                <input class="form-control" type="date" id="pub_date" placeholder="出版日期" name="pub_date" value="{{ edit_book.pub_date|date:'Y-m-d' }}">
            </div>
            <input type="submit"value="submit" class="btn btn-default pull-right">
        </form>
    </div>
</div>

</body>
</html>