“丝路通：导入商品数据”的版本间的差异

2020年9月30日 (三) 02:24的最新版本

商品数据Goods的结构

Model中的结构

class Goods(models.Model):
    """
    商品
    """
    category = models.ForeignKey(GoodsCategory, verbose_name="商品类目")
    goods_sn = models.CharField(max_length=50, default="", verbose_name="商品唯一货号")
    name = models.CharField(max_length=100, verbose_name="商品名")
    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="商品简短描述")
    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.CharField(default="",null=True,max_length=200, verbose_name="封面图")
    is_new = models.BooleanField(default=False, verbose_name="是否新品")
    is_hot = models.BooleanField(default=False, verbose_name="是否热销")
    add_time = models.DateTimeField(default=datetime.now, verbose_name="添加时间")
    category1_name =  models.CharField(default="",null=True,max_length=100, verbose_name="一级类目")
    category2_name =  models.CharField(default="",null=True,max_length=100, verbose_name="二级类目")
    category3_name =  models.CharField(default="",null=True,max_length=100, verbose_name="三级类目")
    goods_url = models.CharField(default="",null=True,max_length=150, verbose_name="商品网址")
    source_site = models.CharField(default="",null=True,max_length=30, verbose_name="来源网站")

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

    def __str__(self):

模型迁移及导入

python3 manage.py makemigrations

python3 manage.py sqlmigrate goods 0001

python3 manage.py migrate

商品表goods的结构

MariaDB [mxshop]> desc goods_goods;
+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| id                | int(11)      | NO   | PRI | NULL    | auto_increment |
| goods_sn          | varchar(50)  | NO   |     | NULL    |                |
| name              | varchar(100) | NO   |     | NULL    |                |
| click_num         | int(11)      | NO   |     | NULL    |                |
| sold_num          | int(11)      | NO   |     | NULL    |                |
| fav_num           | int(11)      | NO   |     | NULL    |                |
| goods_num         | int(11)      | NO   |     | NULL    |                |
| market_price      | double       | NO   |     | NULL    |                |
| shop_price        | double       | NO   |     | NULL    |                |
| goods_brief       | longtext     | NO   |     | NULL    |                |
| goods_desc        | longtext     | NO   |     | NULL    |                |
| ship_free         | tinyint(1)   | NO   |     | NULL    |                |
| goods_front_image | varchar(100) | YES  |     | NULL    |                |
| is_new            | tinyint(1)   | NO   |     | NULL    |                |
| is_hot            | tinyint(1)   | NO   |     | NULL    |                |
| add_time          | datetime     | NO   |     | NULL    |                |
| category_id       | int(11)      | NO   | MUL | NULL    |                |
| category1_name    | varchar(100) | YES  |     | NULL    |                |
| category2_name    | varchar(100) | YES  |     | NULL    |                |
| category3_name    | varchar(100) | YES  |     | NULL    |                |
| goods_url         | varchar(150) | YES  |     | NULL    |                |
| source_site       | varchar(30)  | YES  |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+
22 rows in set (0.01 sec)

生成CSV文件

敦煌网

import time

goods_file ='dh_goods_data_final3.csv'
def read_category_file():
    goods_list =""
    fp = open('dh_goods_data.csv', "rt")  # 打开csv文件

    count= 0

    #类别名  类目级别  父类目级别


    for line in fp:  # 文件对象可以直接迭代
        count +=1
        
        d = {};
        now_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        line_info=""
        data = line.split(',')
        d['id']="NULL"
        d['goods_sn']="NULL"
        d['name']=data[5]        
        d['click_num']=0
        d['sold_num']=0
        d['fav_num']=0
        d['goods_num']=0
        d['market_price']=data[12].strip()
        d['shop_price']=data[12].strip()
        d['goods_brief'] = "NULL"
        d['goods_desc'] = "<p><img src=\""+data[4]+"\" title=\"\" alt=\"2.jpg\"/></p>;"
        d['ship_free'] = 0;
        d['goods_front_image'] = data[4];
        d['is_new'] = 1
        d['is_hot'] = 1
        d['add_time'] = now_time
        d['category_id'] = "NULL"
        d['category1_name']=data[2]
        d['category2_name']=data[2]
        d['category3_name']=data[3]
        d['goods_url']=data[6]
        d['source_site']=data[0]
                    
        for k in d:
            line_info += str(d.get(k))+","

        print(line_info)
            
        goods_list += line_info+",\n"
        
        if count%10 ==0:
            fw = open(goods_file,"a",encoding="utf-8")
            fw.write(goods_list)
            fw.close()
            goods_list =""
        
    fw = open(goods_file,"a",encoding="utf-8")
    fw.write(goods_list)
    fw.close()
    goods_list =""   
    fp.close()
    return goods_list

if __name__ == '__main__':
    cat_list =read_category_file()
    

导入CSV文件到数据库

敦煌网

MariaDB [mxshop]> SET foreign_key_checks = 0;

Query OK, 0 rows affected (0.00 sec)

MariaDB [mxshop]> load data infile '/opt/dh_goods_data_final.csv' into table goods_goods fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';

Query OK, 40 rows affected, 322 warnings (0.02 sec)
Records: 40  Deleted: 0  Skipped: 0  Warnings: 322

MariaDB [mxshop]> SET foreign_key_checks = 1;

Query OK, 0 rows affected (0.00 sec)

MariaDB [mxshop]> select * from goods_goodscategory limit 0,10;

清空原有数据（可选，慎重操作！）

MariaDB [mxshop]> SET foreign_key_checks = 0
    -> ;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mxshop]> truncate goods_goods;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mxshop]> SET foreign_key_checks = 1
    -> ;
Query OK, 0 rows affected (0.00 sec)