“丝路通:导入商品数据”的版本间的差异
来自CloudWiki
(→敦煌网) |
|||
第83行: | 第83行: | ||
import time | import time | ||
− | goods_file =' | + | goods_file ='dh_goods_data_final3.csv' |
def read_category_file(): | def read_category_file(): | ||
goods_list ="" | goods_list ="" | ||
第102行: | 第102行: | ||
d['id']="NULL" | d['id']="NULL" | ||
d['goods_sn']="NULL" | d['goods_sn']="NULL" | ||
− | d['name']=data[5] | + | d['name']=data[5] |
− | |||
− | |||
− | |||
d['click_num']=0 | d['click_num']=0 | ||
d['sold_num']=0 | d['sold_num']=0 | ||
+ | d['fav_num']=0 | ||
+ | d['goods_num']=0 | ||
d['market_price']=data[12].strip() | d['market_price']=data[12].strip() | ||
d['shop_price']=data[12].strip() | d['shop_price']=data[12].strip() | ||
第118行: | 第117行: | ||
d['add_time'] = now_time | d['add_time'] = now_time | ||
d['category_id'] = "NULL" | 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: | for k in d: | ||
line_info += str(d.get(k))+"," | line_info += str(d.get(k))+"," | ||
第142行: | 第146行: | ||
cat_list =read_category_file() | cat_list =read_category_file() | ||
+ | |||
</nowiki> | </nowiki> |
2020年9月28日 (一) 03:47的版本
目录
商品数据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.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="是否热销") 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)