丝路通：导入商品数据
来自CloudWiki
目录
商品数据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)
