新手Django项目练习（林子雨的淘宝双11数据可视化 model部分）

在实验四做完后可以从虚拟机中导出mysql数据（rebuy，user_log）：

SELECT * FROM  rebuy
INTO OUTFILE 'rebuy.txt'  
FIELDS TERMINATED BY ','  
OPTIONALLY ENCLOSED BY '"'  
LINES TERMINATED BY '\n';

SELECT * FROM  user_log
INTO OUTFILE 'user_log'  
FIELDS TERMINATED BY ','  
OPTIONALLY ENCLOSED BY '"'  
LINES TERMINATED BY '\n';

数据查看：

 

建立对应的model:

from django.db import models

# Create your models here.
#开始建立数据中的表
class User_Log(models.Model):
    class Meta:
        verbose_name = '用户行为日志'
    user_id = models.CharField(max_length=20,verbose_name='买家id')
    item_id = models.CharField(max_length=20,verbose_name='商品id')
    cat_id  = models.CharField(max_length=20,verbose_name='商品类别id')
    merchant_id = models.CharField(max_length=20,verbose_name='卖家id')
    brand_id= models.CharField(max_length=20,verbose_name='品牌id')
    month   = models.CharField(max_length=6,verbose_name='交易时间：月')
    day     = models.CharField(max_length=6,verbose_name='交易时间：日')
    action  = models.CharField(max_length=6,verbose_name='行为')
    age_range = models.CharField(max_length=6,verbose_name='买家年龄分段')
    gender = models.CharField(max_length=6,verbose_name='性别')
    province = models.CharField(max_length=10,verbose_name='收货地址省份')
    def __str__(self):
        return '买家id'+self.user_id+'商品id'+self.item_id+'商品类别id'+self.cat_id+'卖家' \
               'id'+self.merchant_id+'品牌id'+self.brand_id+'交易时间：月'+self.month+'交易时间：日' \
               ''+self.day+'行为'+self.action+'买家年龄分段'+self.age_range+'性别'+self.gender+'收货地址省份'+self.province
class Rebuy(models.Model):
    class Meta:
        verbose_name = '再次购买'
    score = models.CharField(max_length=40,verbose_name='分数')
    label = models.CharField(max_length=40,verbose_name='再次购买')
    def __str__(self):
        return '分数'+self.score+'再次购买标签'+self.label

 

下载mysql数据库，建立dbtaobao数据库（这里不加介绍了）

在配置文件中设置mysql数据库

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'dbtaobao',
        'USER': 'root',
        'PASSWORD': '123456',
        'HOST': '127.0.0.1',
        'PORT': '3306',
        'OPTIONS': {'init_command': "SET sql_mode='STRICT_TRANS_TABLES'", 'charset': 'utf8', },
    }
}

执行python manage.py migrations和python manage.py migrate对数据库进行初始化

然后即可查看数据库的出现以下的表：

 将数据加载到mysql的表中：

由于该原始的user_log文有两个G多,如果需要可以使用函数InitUser_log_Large()进行插入

#运行本文件初始化数据库中的数据
import os
#加载Django中配置文件的路径
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'big_work.settings')
import django
#加载各个app的配置，即实例化AppConfig
django.setup()
from taobao.models import User_Log,Rebuy
import argparse
def ReadDataFile(path):
    return [line.strip('\n').split(',') for line in open(path,'r',encoding='utf-8').readlines() if line.strip('\n')!='']

def InitRebuy():
    print("clearing the table 'rebuy' ... ")
    Rebuy.objects.all().delete()
    print("successfully cleared the table 'rebuy'")
    print("put data into the table 'rebuy' ... ")
    data = ReadDataFile(r"data/rebuy.txt")
    datanums=0
    for one in data:
        try:
            Rebuy.objects.create(score=one[0],label=one[1])
            datanums+=1
        except Exception:
            print("Rebuy数据错误：",one)
    print("successfully put " + str(datanums) + " data into 'rebuy'")
def InitUser_log():
    print("clearing the table 'user_log' ... ")
    User_Log.objects.all().delete()
    print("successfully cleared the table 'user_log'")
    print("put data into the table 'user_log' ... ")
    data = ReadDataFile(r"data/user_log.txt")
    datanums = 0
    for one in data:
        try:
            User_Log.objects.create(user_id =one[0],item_id =one[1],cat_id =one[2],
                                    merchant_id =one[3],brand_id =one[4],month =one[5],
                                    day = one[6],action =one[7],age_range =one[8],
                                    gender =one[9],province =one[10])
            datanums += 1
        except Exception:
            print("User_log数据错误：",one)
    print("successfully put " + str(datanums) + " data into 'user_log'")
def InitUser_log_Large():
    print("clearing the table 'user_log' ... ")
    User_Log.objects.all().delete()
    print("successfully cleared the table 'user_log'")
    with open(r"data/user_log.csv","r",encoding='utf-8') as f:
        ones = f.readline()
        i=0
        datanums=0
        while(ones):
            ones =f.readlines(10000000)
            task_details_objs = []
            for one in ones:
                one = one.strip('\n').split(',')
                try:
                    obj = User_Log(user_id =one[0],item_id =one[1],cat_id =one[2],
                                    merchant_id =one[3],brand_id =one[4],month =one[5],
                                    day = one[6],action =one[7],age_range =one[8],
                                    gender =one[9],province =one[10])
                    task_details_objs.append(obj)
                    datanums += 1
                    if (i == 0):
                        print(str(datanums) + "data:", one)
                    i = i + 1 if i != 9999 else 0
                except Exception:
                    print("User_log数据错误：",one)
            User_Log.objects.bulk_create(task_details_objs)
        print("successfully put "+str(datanums)+" data into 'user_log'")
if __name__== '__main__':
    parser = argparse.ArgumentParser()
    parser.add_argument('init',choices=['rebuy','all','user_log','user_log_large'],help='需要输入初始化的表')
    agv = parser.parse_args()

    if agv.init=='all':
        InitRebuy()
        InitUser_log()
    elif agv.init=='rebuy':
        InitRebuy()
    elif agv.init=='user_log':
        InitUser_log()
    elif agv.init=='user_log_large':
        InitUser_log_Large()

以下截图是插入10000条数据：

查看mysql数据库确实导入rebuy和user_log表分别10000条数据

 

至此完成model部分的数据部署。 

参考： 

(20条消息) django.setup() 源码分析_luo_xinyu的博客-CSDN博客_django.setup()