第三章数据库设计

作者: kevin_lln | 来源:发表于2023-03-25 13:26 被阅读0次

3.1 项目初始化

创建虚拟环境

pyenv virtualenv 3.6.5 vue_drf

激活虚拟环境

pyenv activate vue_drf

安装django和django rest framework

pip install django
pip install djangorestframework
pip install markdown django-filter

rest framework支持的

Python (3.6, 3.7, 3.8, 3.9, 3.10)
Django (2.2, 3.0, 3.1, 3.2, 4.0)

修改数据库配置

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'vue_shop',
        'USER': 'root',
        'PASSWORD': 'liulunan',
        'HOST': 'localhost',
        'PORT': '3306'
    }
}

LANGUAGE_CODE = 'zh-hans'
TIME_ZONE = 'Asia/Shanghai'

pip install mysqlclient

报错

django.db.utils.OperationalError: 
(1193, "Unknown system variable 'storage_engine'")

解决

'OPTIONS': {'init_command': 
'SET storage_engine=INNODB;'}
修改为
'OPTIONS': {'init_command': 
'SET default_storage_engine=INNODB;'}

windows python安装容易出错的包：www.lfd.uci.edu/~gohlke/pythonlibs/

# 上传图片，处理图片
pip install pillow

整理目录结构

1.创建apps packages
2.创建extra_apps packages

# 为了直接导入apps目录下的文件，不用带apps
import sys
sys.path.insert(0, BASE_DIR)
sys.path.insert(0, os.path.join(BASE_DIR, 'apps'))
sys.path.insert(0, os.path.join(BASE_DIR, 'extra_apps'))

创建app

python manage.py startapp goods
python manage.py startapp trade # 交易相关
python manage.py startapp user_operation # 用户操作

app移入apps文件夹中并加到配置文件中

INSTALLED_APPS = [
    ...
    'users',
    'goods',
    'trade',
    'user_operation',
]

3.2编写表

3.2.1 用户表

from django.db import models

from django.contrib.auth.models import AbstractUser

class UserProfile(AbstractUser):
    """
    用户
    """
    GENDER_CHOICES = (
        ('male', u'男'),
        ('female', '女')
    )
    username = models.CharField(max_length=20, null=True, blank=True, verbose_name='姓名')
    birthday = models.DateField(null=True, blank=True, verbose_name='出生年月')
    gender = models.CharField(max_length=6, choices=GENDER_CHOICES, default='male', verbose_name='性别')
    mobile = models.CharField(max_length=11, verbose_name='电话')
    email = models.EmailField(max_length=100, null=True, blank=True, verbose_name='邮箱')

    class Meta:
        verbose_name = '用户'
        verbose_name_plural = verbose_name

    def __str__(self):
        return self.username

替换系统用户

# settings.py
AUTH_USER_MODEL = 'users.UserProfile'

3.2.2 验证码表

class VerifyCode(models.Model):
    """
    短信验证码
    """
    code = models.CharField(max_length=10, verbose_name='验证码')
    mobile = models.CharField(max_length=11, verbose_name='手机')
    created_time = models.DateTimeField(default=datetime.now, verbose_name='添加时间')

    class Meta:
        verbose_name = '短信验证码'
        verbose_name_plural = verbose_name

    def __str__(self):
        return self.code

3.2.3 商品分类表

class GoodsCategory(models.Model):
    """
    商品分类
    此项目分为三类
    """
    SHOP_CATEGORY_TYPE = (
        (1, '一级类目'),
        (2, '二级类目'),
        (3, '三级类目')
    )
    name = models.CharField(max_length=30, null=True, blank=True, verbose_name='商品类别名', help_text='商品类别名')
    # 用于英文查找
    code = models.CharField(max_length=30, null=True, blank=True, verbose_name='商品类别code', help_text='商品类别code')
    desc = models.TextField(max_length=300, null=True, blank=True, verbose_name='商品类别描述', help_text='商品类别描述')
    category_type = models.IntegerField(choices=SHOP_CATEGORY_TYPE, verbose_name='类目级别', help_text='类目级别')
    # 多级关联，相当于树的上下级---可以一直关联---self是django中关联自己模型的
    # related_name--->查询用，后期讲解且学习
    parent_category = models.ForeignKey('self', verbose_name='父级类别', help_text='父级类别', related_name='sub_cat',
                                        on_delete=models.CASCADE, null=True, blank=True)

    # 主页中导航处显示的商品分类
    is_tab = models.BooleanField(default=False, verbose_name='是否导航', help_text='是否导航')
    add_time = models.DateTimeField(default=datetime.now, verbose_name="添加时间")

    class Meta:
        verbose_name = '商品分类'
        verbose_name_plural = verbose_name

    def __str__(self):
        return self.name

3.2.4 品牌名

class GoodsCategoryBrand(models.Model):
    """
    品牌名
    """
    category = models.ForeignKey(GoodsCategory, related_name='brands', null=True, blank=True, verbose_name="商品类目",
                                 on_delete=models.CASCADE)
    name = models.CharField(max_length=30, null=True, blank=True, verbose_name='品牌名', help_text='品牌名')
    # 上传地址为media/brand/images/  数据库中存储为char类型，所以要设置最长值
    image = models.ImageField(max_length=200, upload_to='brands/')
    created_time = models.DateTimeField(default=datetime.now, verbose_name='添加时间')

    class Meta:
        verbose_name = '品牌名'
        verbose_name_plural = verbose_name

    def __str__(self):
        return self.name

3.2.5 商品表

from DjangoUeditor.models import UEditorField

class Goods(models.Model):
    """
    商品
    """
    # models.CASCADE ---> 级联删除, Django模拟SQL约束ON DELETE CASCADE的行为，并删除包含 ForeignKey 的对象
    category = models.ForeignKey(GoodsCategory, verbose_name="商品类目", on_delete=models.CASCADE)
    # 售货员凭借上屏货号拿货的
    goods_sn = models.CharField(max_length=50, default="", verbose_name="商品唯一货号")
    name = models.CharField(max_length=100, verbose_name="商品名", default='')
    click_num = models.IntegerField(default=0, verbose_name="点击数")
    # 卖出的多少件
    sold_num = models.IntegerField(default=0, verbose_name="商品销售量")
    fav_num = models.IntegerField(default=0, verbose_name="收藏数")
    goods_num = models.IntegerField(default=0, verbose_name="库存数")
    market_price = models.FloatField(default=0, verbose_name="市场价格")
    shop_price = models.FloatField(default=0, verbose_name="本店价格")
    goods_brief = models.TextField(max_length=500, verbose_name="商品简短描述")

    # DjangoUeditor的UEditorField使用
    goods_desc = UEditorField(verbose_name=u"内容", imagePath="goods/images/", width=1000, height=300,
                              filePath="goods/files/", default='')
    # 是否免运费
    ship_free = models.BooleanField(default=True, verbose_name="是否承担运费")
    goods_front_image = models.ImageField(upload_to="goods/images/", null=True, blank=True, verbose_name="封面图")
    # 是否为刚出炉的新品
    is_new = models.BooleanField(default=False, verbose_name="是否新品")
    # 右边显示的是否为热卖商品
    is_hot = models.BooleanField(default=False, verbose_name="是否热销")
    created_time = models.DateTimeField(default=datetime.now, verbose_name="添加时间")

    class Meta:
        verbose_name = '商品'
        verbose_name_plural = verbose_name

    def __str__(self):
        return self.name

配置文件中设置富文本DjangoUeditor

INSTALLED_APPS = [
    ...
    'DjangoUeditor',
]

3.2.6 商品详情页轮播图

class GoodsImage(models.Model, GoodsBase):
    """
    商品详情页轮播图
    """
    goods = models.ForeignKey(Goods, verbose_name="商品", related_name="images", on_delete=models.CASCADE)
    image = models.ImageField(upload_to="", verbose_name="图片", null=True, blank=True)

    class Meta:
        verbose_name = '商品图片'
        verbose_name_plural = verbose_name

    def __str__(self):
        return self.goods.name

3.2.7 主页面大的轮播的商品

class Banner(models.Model, GoodsBase):
    """
    主页面大的轮播的商品
    """
    goods = models.ForeignKey(Goods, verbose_name="商品", on_delete=models.CASCADE)
    image = models.ImageField(upload_to='banner/', verbose_name="轮播图片")
    index = models.IntegerField(default=0, verbose_name="轮播顺序")

    class Meta:
        verbose_name = '轮播商品'
        verbose_name_plural = verbose_name

    def __str__(self):
        return self.goods.name

3.2.8 交易相关表

from datetime import datetime

from django.db import models
from django.contrib.auth import get_user_model

from goods.models import Goods

# 获取用户模型
User = get_user_model()

class ShoppingCart(models.Model):
    """
    购物车
    """
    user = models.ForeignKey(User, verbose_name=u"用户", on_delete=models.CASCADE)
    goods = models.ForeignKey(Goods, verbose_name=u"商品", on_delete=models.CASCADE)
    nums = models.IntegerField(default=0, verbose_name="购买数量")

    create_time = models.DateTimeField(default=datetime.now, verbose_name=u"添加时间")

    class Meta:
        verbose_name = '购物车'
        verbose_name_plural = verbose_name

    def __str__(self):
        return "%s(%d)".format(self.goods.name, self.nums)

class OrderInfo(models.Model):
    """
    订单
    """
    ORDER_STATUS = (
        ("TRADE_SUCCESS", "成功"),
        ("TRADE_CLOSED", "超时关闭"),
        ("WAIT_BUYER_PAY", "交易创建"),
        ("TRADE_FINISHED", "交易结束"),
        ("paying", "待支付"),
    )

    user = models.ForeignKey(User, verbose_name="用户", on_delete=models.CASCADE)
    order_sn = models.CharField(max_length=30, null=True, blank=True, unique=True, verbose_name="订单号")
    # 支付宝支付时，支付宝生成订单号返回给我们，我们把它与本系统的订单号关联
    trade_no = models.CharField(max_length=100, unique=True, null=True, blank=True, verbose_name=u"交易号")
    # 支付状态
    pay_status = models.CharField(choices=ORDER_STATUS, default="paying", max_length=30, verbose_name="订单状态")
    post_script = models.CharField(max_length=200, verbose_name="订单留言")
    order_mount = models.FloatField(default=0.0, verbose_name="订单金额")
    pay_time = models.DateTimeField(null=True, blank=True, verbose_name="支付时间")

    # 用户信息
    address = models.CharField(max_length=100, default="", verbose_name="收货地址")
    signer_name = models.CharField(max_length=20, default="", verbose_name="签收人")
    singer_mobile = models.CharField(max_length=11, verbose_name="联系电话")

    created_time = models.DateTimeField(default=datetime.now, verbose_name="添加时间")

    class Meta:
        verbose_name = "订单"
        verbose_name_plural = verbose_name

    def __str__(self):
        return str(self.order_sn)

class OrderGoods(models.Model):
    """
    订单的商品详情
    """
    order = models.ForeignKey(OrderInfo, verbose_name="订单信息", related_name="goods", on_delete=models.CASCADE)
    goods = models.ForeignKey(Goods, verbose_name="商品", on_delete=models.CASCADE)
    goods_num = models.IntegerField(default=0, verbose_name="商品数量")

    created_time = models.DateTimeField(default=datetime.now, verbose_name="添加时间")

    class Meta:
        verbose_name = "订单商品"
        verbose_name_plural = verbose_name

    def __str__(self):
        return str(self.order.order_sn)

3.2.9 用户操作相关的

from datetime import datetime

from django.db import models
from django.contrib.auth import get_user_model

from goods.models import Goods

User = get_user_model()

class UserFav(models.Model):
    """
    用户收藏
    """
    user = models.ForeignKey(User, verbose_name="用户", on_delete=models.CASCADE)
    goods = models.ForeignKey(Goods, verbose_name="商品", help_text="商品id", on_delete=models.CASCADE)
    add_time = models.DateTimeField(default=datetime.now, verbose_name=u"添加时间")

    class Meta:
        verbose_name = '用户收藏'
        verbose_name_plural = verbose_name
        # 联合唯一索引
        unique_together = ("user", "goods")

    def __str__(self):
        return self.user.username

class UserLeavingMessage(models.Model):
    """
    用户留言
    """
    MESSAGE_CHOICES = (
        (1, "留言"),
        (2, "投诉"),
        (3, "询问"),
        (4, "售后"),
        (5, "求购")
    )
    user = models.ForeignKey(User, verbose_name="用户", on_delete=models.CASCADE)
    message_type = models.IntegerField(default=1, choices=MESSAGE_CHOICES, verbose_name="留言类型",
                                       help_text=u"留言类型: 1(留言),2(投诉),3(询问),4(售后),5(求购)")
    subject = models.CharField(max_length=100, default="", verbose_name="主题")
    message = models.TextField(default="", verbose_name="留言内容", help_text="留言内容")
    file = models.FileField(upload_to="message/images/", verbose_name="上传的文件", help_text="上传的文件")
    add_time = models.DateTimeField(default=datetime.now, verbose_name="添加时间")

    class Meta:
        verbose_name = "用户留言"
        verbose_name_plural = verbose_name

    def __str__(self):
        return self.subject

class UserAddress(models.Model):
    """
    用户收货地址
    """
    user = models.ForeignKey(User, verbose_name="用户", on_delete=models.CASCADE)
    province = models.CharField(max_length=100, default="", verbose_name="省份")
    city = models.CharField(max_length=100, default="", verbose_name="城市")
    district = models.CharField(max_length=100, default="", verbose_name="区域")
    address = models.CharField(max_length=100, default="", verbose_name="详细地址")
    signer_name = models.CharField(max_length=100, default="", verbose_name="签收人")
    signer_mobile = models.CharField(max_length=11, default="", verbose_name="电话")
    add_time = models.DateTimeField(default=datetime.now, verbose_name="添加时间")

    class Meta:
        verbose_name = "收货地址"
        verbose_name_plural = verbose_name

    def __str__(self):
        return self.address

3.3 migrate迁移表

报错

users.UserProfile: (auth.E003) 'UserProfile.username' 
must be unique because it is named as the 'USERNAME_FIELD'

解决方案

在重写django的User model时，需要将username设置为unique=True，
否则会报这个错误。

makemigrations
migrate

django migrations表记录了执行过哪些py文件，如果表中有了就会跳过。如果想重新迁移两步操作：

删除表
删除django migrations表中的记录

3.4 xadmin后台管理系统的配置

拷贝xadmin.py文件

在settings.py 加上xadmin

INSTALLED_APPS = [
    ...
    'crispy_forms',
    'xadmin',
]

安装xadmin依赖包，在github上搜索xadmin,找到requirements.txt中没有安装的包安装

pip install django-crispy-forms django-import-export django-reversion django-formtools future httplib2 six

还有两个包没装，excel文件导出用的,xlwt,xlsxwriter

pip install xlwt xlsxwriter

xadmin配置一个访问路径

import xadmin

urlpatterns = [
    path('admin/', xadmin.site.urls),
]

创建超级用户

python manage.py createsuperuser

报错

ImportError: cannot import name 'six

修改：extra_apps/xadmin/sites.py文件from django.utils import six修改改为import six

import six

然后又报错

ImportError: cannot import name 'python_2_unicode_compatible'

修改为

from six import python_2_unicode_compatible

ImportError: cannot import name 'FieldDoesNotExist'

把from django.db.models.fields import FieldDoesNotExist
改为from django.core.exceptions import FieldDoesNotExist

https://blog.csdn.net/qq_38315711/article/details/110650279
https://blog.51cto.com/u_14234228/2604924
https://www.cnblogs.com/hehecat/p/9358033.html
https://blog.csdn.net/huanhuanq1209/article/details/77884014

后台app名称英文改为中文

# apps/goods/apps.py
from django.apps import AppConfig

class GoodsConfig(AppConfig):
    name = 'goods'
    # 后台管理应用显示
    verbose_name = '商品'

xadmin配置和ueditor富文本显示

class GoodsAdmin(object):
    style_fields = {"goods_desc": "ueditor"}

3.5 导入商品数据

3.5.1 导入商品类别数据

# 独立使用django的model
import os
import sys

# 获取当前文件路径

pwd = os.path.dirname(os.path.realpath(__file__))
# 将项目根目录加入到python根搜索目录下，和setting一样
# 相对路径：pwd+'../'
sys.path.append(pwd + '../')
# 根据项目目录查找setting中的配置
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "VueShop.settings")

import django

django.setup()

from goods.models import GoodsCategory
from common_tools.data_import.data.category_data import row_data

for lev1_cat in row_data:
    lev1_intance = GoodsCategory()
    lev1_intance.code = lev1_cat["code"]
    lev1_intance.name = lev1_cat["name"]
    lev1_intance.category_type = 1
    lev1_intance.save()

    for lev2_cat in lev1_cat["sub_categorys"]:
        lev2_intance = GoodsCategory()
        lev2_intance.code = lev2_cat["code"]
        lev2_intance.name = lev2_cat["name"]
        lev2_intance.category_type = 2
        lev2_intance.parent_category = lev1_intance
        lev2_intance.save()

        for lev3_cat in lev2_cat["sub_categorys"]:
            lev3_intance = GoodsCategory()
            lev3_intance.code = lev3_cat["code"]
            lev3_intance.name = lev3_cat["name"]
            lev3_intance.category_type = 3
            lev3_intance.parent_category = lev2_intance
            lev3_intance.save()

3.5.2 导入商品数据和商品详情页轮播图

# -*- coding: utf-8 -*-
import sys
import os

pwd = os.path.dirname(os.path.realpath(__file__))
sys.path.append(pwd + "../")
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "VueShop.settings")

import django

django.setup()

from goods.models import Goods, GoodsCategory, GoodsImage
from common_tools.data_import.data.product_data import row_data
goods_sn = 1
for goods_detail in row_data:
    goods = Goods()
    goods.name = goods_detail["name"]
    goods.market_price = float(int(goods_detail["market_price"].replace("￥", "").replace("元", "")))
    goods.shop_price = float(int(goods_detail["sale_price"].replace("￥", "").replace("元", "")))
    goods.goods_brief = goods_detail["desc"] if goods_detail["desc"] is not None else ""
    goods.goods_desc = goods_detail["goods_desc"] if goods_detail["goods_desc"] is not None else ""
    goods.goods_front_image = goods_detail["images"][0] if goods_detail["images"] else ""
    goods.goods_sn = goods_sn

    category_name = goods_detail["categorys"][-1]
    category = GoodsCategory.objects.filter(name=category_name)
    if category:
        goods.category = category[0]
    goods.save()
    goods_sn += 1

    for goods_image in goods_detail["images"]:
        goods_image_instance = GoodsImage()
        goods_image_instance.image = goods_image
        goods_image_instance.goods = goods
        goods_image_instance.save()

第三章数据库设计

3.1 项目初始化

3.2编写表

3.2.1 用户表

3.2.2 验证码表

3.2.3 商品分类表

3.2.4 品牌名

3.2.5 商品表

3.2.6 商品详情页轮播图

3.2.7 主页面大的轮播的商品

3.2.8 交易相关表

3.2.9 用户操作相关的

3.3 migrate迁移表

3.4 xadmin后台管理系统的配置

3.5 导入商品数据

3.5.1 导入商品类别数据

3.5.2 导入商品数据和商品详情页轮播图

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

第三章 数据库设计

3.1 项目初始化

3.2编写表

3.2.1 用户表

3.2.2 验证码表

3.2.3 商品分类表

3.2.4 品牌名

3.2.5 商品表

3.2.6 商品详情页轮播图

3.2.7 主页面大的轮播的商品

3.2.8 交易相关表

3.2.9 用户操作相关的

3.3 migrate迁移表

3.4 xadmin后台管理系统的配置

3.5 导入商品数据

3.5.1 导入商品类别数据

3.5.2 导入商品数据和商品详情页轮播图

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

第三章数据库设计