python-django学习足迹(三)

django的ORM的概念

python的类对数据库的表进行操作

.
.
django默认使用sqlite的数据库,默认自带sqlite的数据驱动,引擎名称: django.db.backends.sqlite3
mysql, 引擎名称: django.db.backends.mysql
pymysql:纯python的mysql驱动程序
.
.
ORM —– object relation mapping(对象关系映射表) 类和表做映射
python的类

class emp():
    id = 1003
    name = 'chs'
    salary = 8000

# 一个实例就是一个记录
demo = emp()
demo.id

使用sqlite数据驱动调用数据库

django 默认的数据驱动是sqlite,切换成mysql需要更改settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    }
}

在models.py中创建数据库表

from django.db import models

# Create your models here.



# create table book(
#   name varchar(32),
#   price float(4,2),
# )
# 创建一个书籍的表  一个表对应一个类
# 创建一个表必须继承models.Model类
class Book(models.Model):
    name = models.CharField(max_length=32)     # 字符串数据类型
    price = models.FloatField()       # IntegerField 整型
    pub_date = models.DateField()

# Book表创建完毕

在Terminal命令行输入命令: python manage.py makemigrations 创建初始化.py文件

此时在migrations目录下生成0001_initial.py

0001_initial.py

# Generated by Django 2.1 on 2018-08-24 07:20

from django.db import migrations, models


class Migration(migrations.Migration):

    initial = True

    dependencies = [
    ]

    operations = [
        migrations.CreateModel(
            name='Book',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                ('name', models.CharField(max_length=32)),
                ('price', models.FloatField()),
                ('pub_date', models.DateField()),
            ],
        ),
    ]

输入命令创建数据库表: python manage.py migrate

downloads->apply->ok

将db.sqlite3拖到Database窗口下

找到自定义创建的表book双击

可以直接添加数据到数据库表中

使用mysql数据驱动调用数据库

1.修改settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'Django_ORM',    # 你的数据库名字
        'USER': 'root',     # 你的数据库用户名
        'PASSWORD': '123456',  # 你的数据库密码
        'HOST': '',         # 你的数据库主机,留空默认为localhost
                     # 此处写IP地址Django就能访问这台计算机下的数据库
        'PORT': '3306',     # 你的数据库端口


    }
}

2.执行Terminal命令: python manage.py makemigration
报错:Error loading MySQLdb module.
此时需要 在项目下找到初始化文件init.py
添加:

import pymysql

pymysql.install_as_MySQLdb()

这两句话告诉django默认驱动引擎不要用MySQLdb, 更换成pymysql

输入命令创建数据库表: python manage.py makemigrations
如果报错: No module named ‘pymysql’
可能是pymysql没有安装成功
此时在Terminal命令行输入命令: pip install pymysql安装即可

创建数据库表到django中:
输入命令: python manage.py migrate

在django上直接操作数据库表

修改表内容后可以在mySQL中找到修改的数据

mysql> use Django_ORM
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_django_orm       |
+----------------------------+
| app01_book                 |
| 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             |
+----------------------------+
11 rows in set (0.00 sec)

mysql> desc app01_book;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(32) | NO   |     | NULL    |                |
| price    | double      | NO   |     | NULL    |                |
| pub_date | date        | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql>
mysql>
mysql> desc app01_book;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(32) | NO   |     | NULL    |                |
| price    | double      | NO   |     | NULL    |                |
| pub_date | date        | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql>
mysql>
------------------------------------->在django中添加了author表
mysql> show tables;
+----------------------------+
| Tables_in_django_orm       |
+----------------------------+
| app01_author               |
| app01_book                 |
| 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             |
+----------------------------+
12 rows in set (0.00 sec)

mysql>

Django 在更新完数据库表后直接点击DB进行提交即可更新数据到数据库中

知识补充:

1.环境变量配置（用户配置）找到mysql文件夹进入到bin目录下复制路径到我的电脑属性环境变量path新建


2.数据模型models.py 中构建数据库表的字段

        from datetime import datetime

        # 布尔值选项 
        sale = models.BooleanField( default=True )
        # 出版日期  必须添加默认值 需要导入datetime模块
            bDate = models.DateTimeField( default = datetime.now( ) )

3.在更改表结构时进行数据迁移时执行数据迁移时候可能会报错，需将项目应用下migrations内除__init__文件都删除
再进行数据迁移可解决问题

---

ORM对单表的增删改查

对单表添加字段
model.py

添加作者author字段,null=False 不允许默认值为null
添加完毕执行命令更新数据库表结构: python manage.py migrate
此时报错，基本内容为让root添加一个author的默认值

class Book(models.Model):
    name = models.CharField(max_length=32)     # 字符串数据类型
    price = models.FloatField()       # IntegerField 整型
    pub_date = models.DateField()
    author = models.CharField(max_length=32, null=False)
# Book表创建完毕

1.添加一个index.html页面
index.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>

    <style>


        .head{
            line-height: 40px;
            background-color:orange;
            color:white;
            height:50px;
        }
    </style>


</head>
<body>

<div class="outer">

    <div class="head">标题</div>
    <div class="content">
        <a href="/addbook/">添加书籍</a>
        <a href="/update/">修改书籍</a>
        <a href="/delete/">删除书籍</a>
        <a href=""></a>



    </div>

</div>



</body>
</html>

2.添加路径

urls.py

from django.contrib import admin
from django.conf.urls import url

from app01 import views

urlpatterns = [
    url(r'^admin/', admin.site.urls),
    url(r'^index', views.index),
    url(r'^addbook', views.addbook),
    url(r'^update', views.update),
    url(r'^delete', views.delete),
]

3.试图函数实现功能
views.py

from django.shortcuts import render, HttpResponse

# Create your views here.
from app01.models import *

def index(req):
    return render(req, 'index.html')

# 增
def addbook(req):

    ## 方式一
    #add1 = Book(name="python2222", price=99, author='chung',
    #                    pub_date='2018-08-15')
    # 调用save方法将数据保存到数据库
    #add1.save()
    #return HttpResponse('添加成功!')


    ## 方式二
    Book.objects.create(name="天才在左疯子在右", price=69, author='chs',
                        pub_date='2018-08-15')
    return HttpResponse('添加成功!')


    #Book.objects.create(**dic)
    #return HttpResponse('添加成功!')


# 改
def update(req):

    ## 方式一
    Book.objects.filter(author='chs').update(price=100)
    return HttpResponse('修改成功!')

    ## 方式二
    '''
        第二种方式修改不能用get的原因是: update是QuerySet对象的方法
        get返回的是一个model对象,它没有update方法，而filter返回的是
        一个QuerySet对象(filter里面的条件可能有多个条件符合，比如
        author='chs',可能有两个author='chs'的行数据)
    '''
    #upd1 = Book.objects.get(author='chs')
    #upd1.price = 56
    #upd1.save()
    #return HttpResponse('修改成功!')

# 删
def delete(req):
    #找到表  调用objects
    Book.objects.filter(author='chung').delete()
    return HttpResponse('删除成功!')

ORM对单表的查询

1.添加路径

urlpatterns = [
    url(r'^select', views.select),
]

2.index.html 页面渲染

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>

    <style>


        .head{
            line-height: 40px;
            background-color:orange;
            color:white;
            height:50px;
        }
        span{
            color:red;
        }
    </style>


</head>
<body>

<div class="outer">

    <div class="head">标题</div>
    <div class="content">
        <a href="/addbook/">添加书籍</a>
        <a href="/update/">修改书籍</a>
        <a href="/delete/">删除书籍</a>
        <a href="/select/">查看书籍</a>
    </div>

    <hr>
    <div class="queryResult">

        {% for book in book_list %}
            <div>
                <p>书名: <span>{{ book.name }}</span></p>
                <p>作者: {{ book.author }}</p>
                <p>价格: {{ book.price }}</p>
                <p>创作时间: {{ book.pub_date }}</p>
                <hr>
            </div>
        {% endfor %}


    </div>

</div>

</body>
</html>

3.视图函数实现功能

from django.shortcuts import render, HttpResponse

# Create your views here.
from app01.models import *


# 查
def select(req):

    # 得到一个对象的集合
    book_list = Book.objects.all()

    # book_list = Book.objects.filter(id=2)
    # book_list = Book.objects.all()[::2]
    # book_list = Book.objects.all()[::-1]

    # first,last,get 取到的是一个实例对象，并非一个QuerySet的集合对象
    # book_list = Book.objects.first()
    # book_list = Book.objects.last()
    # get不会得到集合,只能取出一条记录的时候才不会报错
    # book_list = Book.objects.get(id=2)

    #ret = Book.objects.filter(author='chung').values('name', 'price')
    #print(ret)
    # 打印 <QuerySet [{'name': '河对岸', 'price': 57.0}]>

    # 查询除了author='chung'的书的信息
    # ret = Book.objects.exclude(author='chung').values('name', 'price')
    # print(ret)
    # 打印 <QuerySet [{'name': '清明水上', 'price': 100.0}, {'name': '天才在左
    # 疯子在右', 'price': 69.0}, {'name': 'PHP', 'price': 60.0}]>


    # 去重(某个字段重复)
    # book_list = Book.objects.all().values('name').distinct()
    # book_count = Book.objects.all().values('name').distinct().count()
    # print(book_count)
    # 打印 4


    return render(req, 'index.html', locals())

4.页面查询书籍效果

---

ORM对单表的模糊匹配查询

views.py

def select(req):

    # 万能的双下划线  __gt：大于  __lt：小于
    # book_list = Book.objects.filter(price__gt=50).values('name', 'price', 'author', 'pub_date')

    # 模糊查询 icontains:大小写不敏感  contains:敏感大小写
    book_list = Book.objects.filter(name__icontains='P').values('name', 'price', 'author', 'pub_date')

    return render(req, 'index.html', locals())

页面效果

案例：对单表Author Book 的增删改查

models.py

from datetime import datetime



class Book(models.Model):
    name = models.CharField(max_length=32)
    price = models.FloatField()
    author = models.CharField(max_length=32)
    addr = models.CharField(max_length=32)

    sale = models.BooleanField(default=True)
    # 出版日期  必须添加默认值
    bDate = models.DateTimeField(default=datetime.now())


class Publish(models.Model):
    name = models.CharField(max_length=50)
    money = models.FloatField(default=1000000)
    addr = models.CharField(max_length=100)



class Author(models.Model):
    name = models.CharField(max_length=32)
    age = models.PositiveSmallIntegerField(default=20)
    addr = models.CharField(max_length=100)
    sex = models.BooleanField(default=True)


class Info(models.Model):
    introduction = models.TextField()
    pub_num = models.IntegerField(default=0)
    SINB = models.IntegerField(primary_key=True)

    python manage.py makemigrations
    python manage.py migrate

admin.py

from django.contrib import admin
from django.contrib.admin import ModelAdmin


from .models import Publish,Author, Info
from .models import Book



注册数据库表模型
admin.site.register(Publish)
admin.site.register(Author)
admin.site.register(Info)

urls.py

from django.urls import path
from .views import *



urlpatterns = [

    path('index/',getIndex),

    path('stus/',get_stus),

    path('addStu/<str:name>/',addStu),

    path('getAllBooks/',getAllBooks),

    path('getSaleBooks/',getSaleBooks),

    path('getSpecialBooks/<int:id>',getSpecialBooks),

    path('getStartBooks/<str:value>',getStartBooks),



    # Author表操作
    path('addAuthorName/<str:name><str:sex>',addAuthorName),

    path('getAuthorSex/<int:sex>',getAuthorSex),

    path('getFName/<str:firstname>',getFName),

    path('getAuthorId/<int:id>',getAuthorId),

    path('getAuthorHas/<str:value>',getAuthorHas),

    path('getAuthorAge/<int:min>/<int:max>/',getAuthorAge),

    # Author 表 更改操作
    path('modifyAuthor/<int:id>/<str:name>',modifyAuthor),

    # Author 表 删操作
    path('deleteAuthor/<int:id>',deleteAuthor),


]

views.py

from django.shortcuts import render,HttpResponse

from django.db.models import Q
from . models import Student
from .models import *


# Create your views here.



def getIndex(request):
    return render(request, 'app01/index.html')


def get_stus(request):

    # 相当于数据库命令里的 select *
    stus = Student.objects.all()

    for s in stus:
        print(s.name)

    return HttpResponse('查询成功')



# django对单表的增删改查

# 增
def addStu(request,name):
    book = Book()
    book.name = name
    book.price = 19.99
    book.sale = True
    book.save()
    return HttpResponse('添加成功!')

# 查
def getAllBooks(request):
    # 查询中进行排序
    books = Book.objects.all().order_by('name')
    str = ''
    for book in books:
        str += book.name
        str += ','
    return HttpResponse(str)


def getSaleBooks(request):
    sales = Book.objects.exclude(sale=True)
    str = ''
    for s in sales:
        str += s.name
        str += ' ,'
    return HttpResponse(str)


def getSpecialBooks(request,id):

    # pk 定义好的主键的意思  可当做id使用
    book = Book.objects.get(pk = id)

    return HttpResponse(book.name)


def getStartBooks(request,value):
    books = Book.objects.filter(name__istartswith=value)
    str = ''
    for b in books:
        str += b.name
        str += ' ,'

    return HttpResponse(str)



# 增加查询关于Author表

def addAuthorName(request,name,sex):
    author = Author()
    author.name = name
    author.age = 20
    author.addr = '郎溪'
    author.sex = sex
    author.save()

    return HttpResponse('添加成功')

# Author 表查询



# 按照性别查询
def getAuthorSex(request,sex):
    name = Author.objects.all().filter(sex=sex)
    str = ''
    for n in name:
        str += n.name
        str += ' ,'

    return HttpResponse(str)


# 按照姓查询
def getFName(request,firstname):
    authors = Author.objects.filter(name__istartswith=firstname)
    str = ''
    for author in authors:
        str += author.name
        str += ' ,'

    return HttpResponse(str)



# 按照id
def getAuthorId(request,id):
    author = Author.objects.get(pk = id)
    return HttpResponse(author.name)


# 按照名字中包含某个字
def getAuthorHas(request,value):
    authors = Author.objects.filter(name__contains=value)
    str = ''
    for author in authors:
        str += author.name
        str += ' ,'

    return HttpResponse(str)




# 按照age查询
def getAuthorAge(request,min,max):
    # 查询年龄大于min 小于max 的作者的名字
    # authors = Author.objects.filter(age__gt=min).filter(age__lt=max)

    # Q对象查询
    '''
        导包:
            from django.db.models import Q
        Q() & Q()
        Q() | Q()
        ~Q() 取反

    '''
    authors = Author.objects.filter(Q(age__gt=min) & Q(age__lt=max))

    str = ''
    for author in authors:
        str += author.name
        str += ' ,'

    return HttpResponse(str)


# 改
# 修改Author表
def modifyAuthor(request,id,name):
    author = Author.objects.get(pk=id)
    author.name = name
    author.save()
    return HttpResponse('修改成功')

    # author = Author.objects.get(pk=id).update(name=name)
    # return HttpResponse('修改成功')




# 删
# 删除Author 表元素
def deleteAuthor(request,id):
    author = Author.objects.get(pk=id)
    author.delete()
    return HttpResponse('删除成功')

    # author = Author.objects.get(pk=id).delete()
    # return HttpResponse('删除成功')