def run(self):
db = RoseVisionDb()
db.conn(getattr(gs, 'DATABASE')['DB_SPEC'])
if not self.brand_list:
rs = db.query_match(['brand_id'], 'products', distinct=True)
brand_list = [int(val[0]) for val in rs.fetch_row(maxrows=0)]
self.brand_list = brand_list
else:
brand_list = self.brand_list
if not brand_list:
# 如果没有任何品牌，则直接退出
return self.report
self.progress = 0
# 获得检查总数
self.tot = int(
db.query(
str.format(
'SELECT COUNT(*) FROM products WHERE brand_id IN ({0})',
','.join(str(tmp)
for tmp in brand_list))).fetch_row()[0][0])
for brand in brand_list:
if not self.silent:
print unicode.format(u'\nPROCESSING {0} / {1}\n', brand,
info.brand_info()[brand]['brandname_e'])
db.start_transaction()
try:
for model, pid, fingerprint in db.query_match(
['model', 'idproducts', 'fingerprint'], 'products', {
'brand_id': brand
}).fetch_row(maxrows=0):
self.progress += 1
new_fp = gen_fingerprint(brand, model)
if fingerprint != new_fp:
self.report.append({
'model': model,
'idproducts': pid,
'fingerprint_db': fingerprint,
'fingerprint_gen': new_fp,
'brand_id': brand
})
if not self.silent:
print unicode.format(
u'\nMismatched fingerprints! model={0}, idproducts={1}, brand_id={2}, '
u'fingerprints: {3} => {4}\n', model, pid,
brand, fingerprint, new_fp)
if self.update_fingerprint:
# 自动更新MD5指纹
db.update({'fingerprint': new_fp},
'products',
str.format('idproducts={0}', pid),
timestamps=['update_time'])
except:
db.rollback()
raise
finally:
db.commit()
db.close()
def process_editor_price(db_spec=getattr(glob, 'DATABASE')['DB_SPEC'],
table='products',
extra_cond=None):
"""
处理editor库中的价格信息
:param table: 需要操作的表。默认为products。
:param db_spec: 需要操作的数据库，默认为editor库。
:param extra_cond: 筛选条件。
"""
db = RoseVisionDb()
db.conn(db_spec)
extra_cond = ' AND '.join(
unicode.format(u'({0})', tuple(unicodify(v)))
for v in extra_cond) if extra_cond else '1'
db.lock([table])
db.start_transaction()
try:
# 根据update_flag字段判断哪些记录是需要更新的
obj = EditorPriceProcessor(
db.query(
unicode.format(
u'SELECT idproducts,price,region FROM {0} WHERE price IS NOT NULL AND {1}',
table, extra_cond).encode('utf-8')), db)
core.func_carrier(obj, 1)
db.commit()
except OperationalError:
db.rollback()
finally:
db.unlock()
db.close()
def currency_update(param_dict):
"""
更新货币的汇率信息
@param param_dict:
"""
db = RoseVisionDb()
db.conn(getattr(gs, 'DATABASE')['DB_SPEC'])
rs = db.query_match(['iso_code', 'currency'],
'region_info').fetch_row(maxrows=0)
db.start_transaction()
try:
for code, currency in rs:
print str.format('Fetching for currency data for {0}...', currency)
data = cm.get_data(url=str.format(
'http://download.finance.yahoo.com/d/quotes.csv?s={0}CNY=X'
'&f=sl1d1t1ba&e=.json', currency))
rdr = csv.reader(StringIO(data['body']))
line_data = [val for val in rdr][0]
timestamp = datetime.datetime.strptime(
str.format('{0} {1}', line_data[2], line_data[3]),
'%m/%d/%Y %I:%M%p')
db.update(
{
'rate': line_data[1],
'update_time': timestamp.strftime('%Y-%m-%d %H:%M:%S')
}, 'region_info', str.format('iso_code="{0}"', code))
db.commit()
except:
db.rollback()
raise
def run(self):
db = RoseVisionDb()
db.conn(getattr(gs, 'DATABASE')['DB_SPEC'])
if not self.brand_list:
rs = db.query_match(['brand_id'], 'products', distinct=True)
brand_list = [int(val[0]) for val in rs.fetch_row(maxrows=0)]
self.brand_list = brand_list
else:
brand_list = self.brand_list
self.progress = 0
self.tot = len(brand_list)
for brand in brand_list:
print unicode.format(u'PROCESSING {0} / {1}', brand,
info.brand_info()[brand]['brandname_e'])
self.progress += 1
rs = db.query(
str.format(
'SELECT * FROM (SELECT p2.idprice_history,p2.date,p2.price,p2.currency,p1.idproducts,p1.brand_id,'
'p1.region,p1.name,p1.model,p1.offline FROM products AS p1 JOIN products_price_history AS p2 ON '
'p1.idproducts=p2.idproducts '
'WHERE p1.brand_id={0} ORDER BY p2.date DESC) AS p3 GROUP BY p3.idproducts',
brand))
# 以model为键值，将同一个model下，不同区域的价格放在一起。
records = rs.fetch_row(maxrows=0, how=1)
price_data = {}
for r in records:
model = r['model']
# # 仅有那些price不为None，且offline为0的数据，才加入到price check中。
# if r['price'] and int(r['offline']) == 0:
# 这里更改为不管offline，全检查
if r['price']:
# 首先检查model是否已存在
if model not in price_data:
price_data[model] = []
price_data[model].append(r)
# 最大值和最小值之间，如果差别过大，则说明价格可能有问题
for model in price_data:
for item in price_data[model]:
price = float(item['price'])
item['nprice'] = info.currency_info()[
item['currency']]['rate'] * price
# 按照nprice大小排序
sorted_data = sorted(price_data[model],
key=lambda item: item['nprice'])
max_price = sorted_data[-1]['nprice']
min_price = sorted_data[0]['nprice']
if min_price > 0 and max_price / min_price > self.threshold:
print unicode.format(
u'WARNING: {0}:{6} MODEL={1}, {2} / {3} => {4} / {5}',
brand, model, sorted_data[0]['nprice'],
sorted_data[0]['region'], sorted_data[-1]['nprice'],
sorted_data[-1]['region'],
info.brand_info()[brand]['brandname_e'])
db.close()
def urlprocess_main():
db_spec = {
"host": "127.0.0.1",
"port": 3306,
"username": "******",
"password": "******",
"schema": "editor_stores"
}
db = RoseVisionDb()
db.conn(db_spec)
idproducts_start = 0
idproducts_count = 100
opts, args = getopt.getopt(sys.argv[1:], "s:c:")
for opt, arg in opts:
if opt == '-s':
idproducts_start = int(arg)
elif opt == '-c':
idproducts_count = int(arg)
logger.info(str.format("Url process start"))
while 1:
products = get_products(db, idproducts_start, idproducts_count)
if not products:
logger.info(str.format("Url process end"))
break
else:
logger.info(
str.format("Url process offset : {0} count : {1}",
idproducts_start, len(products)))
idproducts_start += idproducts_count
for product in products:
origin_url = product['url']
url = None
try:
url = urlencode(origin_url)
except:
url = None
logger.info(
str.format("Error: {0} encode {1} failed",
product['idproducts'], origin_url))
pass
if url:
try:
db.update({'url': url}, 'products',
str.format('idproducts="{0}"',
product['idproducts']))
except:
logger.info(
str.format("Error: {0} update {1} failed",
product['idproducts'], url))
pass
def process_editor_tags(db_spec=getattr(glob, 'DATABASE')['DB_SPEC'],
db_spider_spec=getattr(glob, 'SPIDER_SPEC'),
table='products',
extra_cond=None):
"""
给editor库的数据添加tags字段
"""
db = RoseVisionDb()
db.conn(db_spider_spec)
try:
extra_cond = ' AND '.join(
unicode.format(u'({0})', tuple(unicodify(v)))
for v in extra_cond) if extra_cond else '1'
rs = db.query(
unicode.format(
u'SELECT tag_name,mapping_list FROM products_tag_mapping WHERE {1}',
extra_cond).encode('utf-8'))
temp = rs.fetch_row(maxrows=0)
mapping_rules = dict(temp)
finally:
db.close()
db.conn(db_spec)
db.start_transaction()
try:
rs = db.query(
unicode.format(u'SELECT * FROM {0} WHERE {1}', table, extra_cond))
for i in xrange(rs.num_rows()):
record = rs.fetch_row(how=1)[0]
extra = json.loads(record['extra'])
tags = []
for k in extra:
tags.extend(extra[k])
tags = set(tags)
tag_names = []
for v in tags:
if v in mapping_rules:
tag_names.extend(json.loads(mapping_rules[v]))
tag_names = list(set(tag_names))
db.update({'tags': json.dumps(tag_names, ensure_ascii=False)},
str.format('idproducts={0}', record['idproducts']))
db.commit()
pass
except OperationalError:
db.rollback()
finally:
db.close()
def run(self):
db_src = RoseVisionDb()
db_src.conn(self.src_spec)
db_dst = RoseVisionDb()
db_dst.conn(self.dst_spec)
# 备选记录
idproducts_list = [
int(val[0]) for val in db_src.query(
unicode.format(u'SELECT idproducts FROM products WHERE {0}',
u' AND '.join(self.cond)).encode(
'utf-8')).fetch_row(maxrows=0)
]
self.tot = len(idproducts_list)
self.progress = 0
db_dst.execute('SET AUTOCOMMIT=0')
# db_dst.execute('ALTER TABLE products DISABLE KEYS')
for pid_src in idproducts_list:
self.progress += 1
record = db_src.query(
str.format('SELECT * FROM products WHERE idproducts={0}',
pid_src)).fetch_row(how=1)[0]
db_dst.start_transaction()
try:
rs = db_dst.query(
str.format(
'SELECT idproducts FROM products WHERE brand_id={0} AND model="{1}" '
'AND region="{2}"', record['brand_id'],
record['model'], record['region']))
pid_dst = int(
rs.fetch_row()[0][0]) if rs.num_rows() > 0 else None
entry = {k: record[k] for k in record if k != 'idproducts'}
price = process_price(record['price'], record['region'])
if price:
entry['price_rev'] = price['price']
entry['currency_rev'] = price['currency']
if entry['details']:
entry['details'] = self.process_text(
unicodify(entry['details']))
if entry['description']:
entry['description'] = self.process_text(
unicodify(entry['description']))
if entry['name']:
entry['name'] = self.process_text(unicodify(entry['name']))
if entry['category']:
entry['category'] = self.process_text(
unicodify(entry['category']))
if entry['extra']:
entry['extra'] = self.process_text(
unicodify(entry['extra']))
if pid_dst:
db_dst.update(entry, 'products',
str.format('idproducts={0}', pid_dst))
else:
db_dst.insert(entry, 'products')
pid_dst = int(
db_dst.query(
str.format(
'SELECT idproducts FROM products WHERE brand_id={0} AND model="{1}" '
'AND region="{2}"', record['brand_id'],
record['model'],
record['region'])).fetch_row()[0][0])
# 是否需要处理价格信息
if price:
record_price = db_dst.query(
str.format(
'SELECT price,currency FROM products_price_history '
'WHERE idproducts={0} ORDER BY date DESC LIMIT 1',
pid_dst)).fetch_row(how=1)
if not record_price or float(record_price[0]['price']) != price['price'] or \
record_price[0]['currency'] != price['currency']:
db_dst.insert(
{
'idproducts': pid_dst,
'date': record['update_time'],
'brand_id': record['brand_id'],
'region': record['region'],
'model': record['model'],
'price': price['price'],
'currency': price['currency']
}, 'products_price_history')
# 处理图像信息
tmp = db_src.query(
str.format(
'SELECT checksum,brand_id,url,path,width,height,format FROM products_image '
'WHERE brand_id={0} AND model="{1}"',
record['brand_id'],
record['model'])).fetch_row(maxrows=0, how=1)
image_record = {val['checksum']: val for val in tmp}
checksum_src = set(image_record.keys())
# 完善images_store信息。如果checksum没有在images_store中出现，则添加之。
for checksum in checksum_src:
if db_dst.query(
str.format(
'SELECT checksum FROM images_store WHERE checksum="{0}"',
checksum)).num_rows() == 0:
db_dst.insert(
{
'checksum': checksum,
'brand_id': image_record[checksum]['brand_id'],
'url': image_record[checksum]['url'],
'path': image_record[checksum]['path'],
'width': image_record[checksum]['width'],
'height': image_record[checksum]['height'],
'format': image_record[checksum]['format']
}, 'images_store')
# 补充目标数据库的products_image表，添加相应的checksum
checksum_dst = set([
val[0] for val in db_dst.query(
str.format(
'SELECT checksum FROM products_image WHERE brand_id={0} AND model="{1}"',
record['brand_id'], record['model'])).fetch_row(
maxrows=0)
])
for checksum in checksum_src - checksum_dst:
db_dst.insert(
{
'checksum': checksum,
'brand_id': record['brand_id'],
'model': record['model']
}, 'products_image')
db_dst.commit()
except:
db_dst.rollback()
raise
brand_id = 10226
categories = {
'books--stationery', 'handbags', 'travel', 'watches', 'timepieces',
'shoes', 'fine-jewelry', 'ready-to-wear', 'show-fall-winter-2013',
'mens-bags', 'small-leather-goods', 'icons',
'accessories/scarves-and-more', 'accessories/belts',
'accessories/sunglasses', 'accessories/fashion-jewelry',
'accessories/key-holders-bag-charms-and-more',
'accessories/scarves-ties-and-more',
'accessories/key-holders-and-other-accessories'
}
db = RoseVisionDb()
db.conn(getattr(glob, 'DATABASE')['DB_SPEC'])
def make_post_str(post_data):
"""
给定post数据，生成post字符串。
:param post_data:
"""
def func(val):
if val == "+":
return val
else:
return urllib.quote(val, safe="")
return u'&'.join(
map(lambda k: unicode.format(u'{0}={1}', func(k), func(post_data[k])),
class ProcessTags(object):
"""
标签的映射规则有变动，需要更新
"""
original_tags_tbl = 'original_tags'
mfashion_tags_tbl = 'mfashion_tags'
prod_tag_tbl = 'products_original_tags'
prod_mt_tbl = 'products_mfashion_tags'
products = 'products'
db_spec = getattr(gs, 'DATABASE')['DB_SPEC']
tot = 1
progress = 0
def __init__(self, last_update=None, extra_cond=None):
print str.format(
'Processing tags (last_update="{0}", extra_cond="{1}")...',
last_update, extra_cond)
self.db = RoseVisionDb()
self.db.conn(self.db_spec)
self.last_update = last_update
self.extra_cond = extra_cond
def get_msg(self):
return str.format(
'{0}/{1}({2:.1%}) PROCESSED', self.progress, self.tot,
float(self.progress) / self.tot) if self.tot > 0 else 'IDLE'
def run(self):
last_update = self.last_update
extra_cond = self.extra_cond
if not extra_cond:
extra_cond = []
elif not iterable(extra_cond):
extra_cond = [extra_cond]
if last_update:
extra_cond.append(
unicode.format(u'update_time > "{0}"', last_update))
extra_cond.append('mapping_list IS NOT NULL')
# MFashion标签的缓存记录
cached_mfashion = {}
# 标签更新原理：original_tags存放原始标签。根据update_time字段可以得到最近更新过的标签。由于标签系统具备一定传染性，所以
# 该标签对应brand/region下的所有标签都必须重做
rs = self.db.query_match(['brand_id', 'region'],
self.original_tags_tbl, {},
extra=extra_cond,
distinct=True)
# 需要处理的标签
tag_dict = {}
for i in xrange(rs.num_rows()):
brand_id, region = rs.fetch_row()[0]
for val in self.db.query_match(
['idmappings', 'mapping_list'],
self.original_tags_tbl, {
'brand_id': brand_id,
'region': region
},
extra='mapping_list IS NOT NULL').fetch_row(maxrows=0):
tag_dict[val[0]] = json.loads(val[1].replace("'", '"'))
# 删除旧单品/标签关系
self.db.execute(
str.format(
'DELETE FROM p2 USING {0} AS p1, {1} AS p2 WHERE p1.idproducts=p2.idproducts '
'AND p1.brand_id={2} AND region="{3}"', self.products,
self.prod_mt_tbl, brand_id, region))
self.tot = len(tag_dict)
self.progress = 0
for tid, rule in tag_dict.items():
self.progress += 1
self.db.start_transaction()
try:
# 所有相关的单品
pid_list = [
int(val[0]) for val in self.db.query_match(
['idproducts'], self.prod_tag_tbl, {
'id_original_tags': tid
}).fetch_row(maxrows=0)
]
# 添加MFashion标签
for tag in rule:
if tag not in cached_mfashion:
self.db.insert({'tag': tag},
self.mfashion_tags_tbl,
ignore=True)
tid = int(
self.db.query_match(['idmfashion_tags'],
self.mfashion_tags_tbl, {
'tag': tag
}).fetch_row()[0][0])
cached_mfashion[tag] = tid
self.db.insert([{
'idproducts': pid,
'id_mfashion_tags': cached_mfashion[tag]
} for pid in pid_list],
self.prod_mt_tbl,
ignore=True)
self.db.commit()
except ValueError:
self.db.rollback()
except:
self.db.rollback()
raise
class UpdateSpider(MFashionBaseSpider):
handle_httpstatus_list = [404]
def __init__(self, brand_list, region_list, db_spec, *a, **kw):
self.name = str.format(
'update-{0}-{1}',
'-'.join(str(tmp) for tmp in brand_list) if brand_list else 'all',
'-'.join(region_list) if region_list else 'all')
super(UpdateSpider, self).__init__(*a, **kw)
self.brand_list = brand_list
self.region_list = region_list
self.db = RoseVisionDb()
self.db.conn(db_spec)
# self.python_logger = get_logger()
# PythonLoggingObserver(loggerName='rosevision').start()
def start_requests(self):
# 如果未指定brand_list，则默认对所有的品牌进行更新
# 获得所有的品牌数据
if not self.brand_list:
self.brand_list = info.brand_info().keys()
# self.log('TEST', log.INFO)
#
# return
# UpdateSpider的可选区域参数
region_cond = str.format(
'region IN ({0})', ','.join(
"'" + tmp + "'"
for tmp in self.region_list)) if self.region_list else '1'
rs = self.db.query(
str.format(
'SELECT COUNT(*) FROM products WHERE brand_id IN ({0}) AND {1}',
','.join(str(tmp) for tmp in self.brand_list), region_cond))
tot_num = int(rs.fetch_row()[0][0])
self.log(str.format('Total number of records to update: {0}', tot_num),
level=log.INFO)
for brand in self.brand_list:
# 获得该品牌下所有记录
# 如果未指定region_list，则默认对所有的的确进行更新
if self.region_list:
region_list = self.region_list
else:
rs = self.db.query(
str.format(
'SELECT DISTINCT region FROM products WHERE brand_id={0}',
brand))
region_list = [
tmp['region'] for tmp in rs.fetch_row(maxrows=0, how=1)
]
region_info = info.region_info()
region_list = filter(lambda val: int(region_info[val]['status']),
region_list)
for region in region_list:
# 对该brand，该区域的所有商品，无论其下线状态是什么，都进行更新。
rs = self.db.query_match(
{'idproducts', 'url', 'region', 'model'}, 'products', {
'brand_id': brand,
'region': region
})
products_map = {
int(tmp['idproducts']): {
'url': tmp['url'],
'region': tmp['region'],
'model': tmp['model']
}
for tmp in rs.fetch_row(maxrows=0, how=1)
}
for pid, data in products_map.items():
url = data['url']
region = data['region']
model = data['model']
# url = 'http://www.gucci.com/us/styles/3085353G0109060'
# region = 'us'
# pid = 196907
#
# return [Request(url=url,
# callback=self.parse,
# meta={'brand': brand, 'pid': pid, 'region': region},
# errback=self.onerror,
# dont_filter=True)]
if url:
try:
yield Request(url=url,
callback=self.parse,
meta={
'brand': brand,
'pid': pid,
'region': region,
'model': model
},
errback=self.onerror,
dont_filter=True)
except TypeError:
continue
else:
continue
def parse(self, response):
brand = response.meta['brand']
item = UpdateItem()
item['idproduct'] = response.meta['pid']
item['brand'] = brand
item['region'] = response.meta['region']
sc = info.spider_info()[brand]['spider_class']
metadata = {}
item['metadata'] = metadata
metadata['url'] = response.url
if response.status == 404:
item['offline'] = 1
return item
elif response.status < 200 or response.status > 300:
return
else:
func = getattr(sc, 'is_offline')
item['offline'] = 1 if func(response, self) else 0
if item['offline'] == 1:
return item
if 'fetch_price' in dir(sc):
ret = getattr(sc, 'fetch_price')(response, self)
if isinstance(ret, Request):
metadata['price'] = ret
else:
if 'price' in ret:
metadata['price'] = ret['price']
if 'price_discount' in ret:
metadata['price_discount'] = ret['price_discount']
if 'fetch_name' in dir(sc):
name = getattr(sc, 'fetch_name')(response, self)
if name:
metadata['name'] = name
if 'fetch_model' in dir(sc):
model = getattr(sc, 'fetch_model')(response, self)
if model:
metadata['model'] = model
if 'fetch_description' in dir(sc):
description = getattr(sc, 'fetch_description')(response, self)
if description:
metadata['description'] = description
if 'fetch_details' in dir(sc):
details = getattr(sc, 'fetch_details')(response, self)
if details:
metadata['details'] = details
if 'fetch_color' in dir(sc):
color = getattr(sc, 'fetch_color')(response, self)
if color:
metadata['color'] = color
return self.resolve_requests(item)
def update_callback(self, response):
spider_cb = response.meta['spider_cb']
item = response.meta['item']
key = response.meta['key']
ret = spider_cb(response)
# key对应的
item['metadata'].pop(key)
if ret:
if key == 'price':
for tmp in ('price', 'price_discount'):
if tmp in ret:
item['metadata'][tmp] = ret[tmp]
else:
item['metadata'][key] = ret
else:
# 如果返回值为None，说明没有相对应的信息。
if key == 'price':
for tmp in ('price', 'price_discount'):
if tmp in item['metadata']:
item['metadata'].pop(tmp)
return self.resolve_requests(item)
def resolve_requests(self, item):
"""
检查metadata里面是否有Request对象。如果有，需要进一步提交这些request。
仅当所有的对象都不是Request时，该过程才结束。
应该注意的是，metadata中的键只会处理一次。
@param metadata:
"""
metadata = item['metadata']
while True:
resolved = True
for func_key, value in metadata.items():
if isinstance(value, Request):
value.meta['spider_cb'] = value.callback
value.meta['item'] = item
value.meta['key'] = func_key
value.callback = self.update_callback
value.dont_filter = True
return value
if resolved:
break
return item
@staticmethod
def onerror(reason):
meta = None
try:
if hasattr(reason.value, 'response'):
response = reason.value.response
# 这里response可能为None，比如出现
# ERROR: Error downloading <GET xxx>:
# [<twisted.python.failure.Failure <class 'twisted.internet.error.ConnectionDone'>>]
if response:
meta = response.meta
except AttributeError:
pass
try:
if not meta:
meta = reason.request.meta
except AttributeError:
pass
if meta:
try:
brand = meta['brand']
item = UpdateItem()
item['idproduct'] = meta['pid']
item['brand'] = brand
item['region'] = meta['region']
# sc = glob.spider_info()[brand]
metadata = {}
item['metadata'] = metadata
item['offline'] = 1
return item
except KeyError:
return
def run(self):
db = RoseVisionDb()
db.conn(getattr(gs, 'DATABASE')['DB_SPEC'])
# 如果没有指定brand_list，则默认使用数据库中所有的brand_list
if not self.brand_list:
rs = db.query_match(['brand_id'], 'products', distinct=True)
brand_list = [int(val[0]) for val in rs.fetch_row(maxrows=0)]
self.brand_list = brand_list
else:
brand_list = self.brand_list
self.progress = 0
self.tot = len(brand_list)
# 最终生成的表格
tot_results = []
for brand in brand_list:
results = {}
print unicode.format(u'PROCESSING {0} / {1}', brand,
info.brand_info()[brand]['brandname_e'])
brand_name = info.brand_info()[brand]['brandname_e']
self.progress += 1
rs = db.query(
str.format(
'''SELECT p1.idproducts,p1.brand_id,p1.model,p1.region,p2.price,p2.price_discount,p2.currency,p2.date,p1.name,p4.tag,p1.url FROM products AS p1
JOIN products_price_history AS p2 ON p1.idproducts=p2.idproducts
LEFT JOIN products_mfashion_tags AS p3 ON p3.idproducts=p1.idproducts
LEFT JOIN mfashion_tags AS p4 ON p3.id_mfashion_tags=p4.idmfashion_tags
WHERE p1.brand_id={0} AND p1.offline=0''', brand))
records = rs.fetch_row(maxrows=0, how=1)
for r in records:
pid = int(r['idproducts'])
timestamp = datetime.datetime.strptime(r['date'],
'%Y-%m-%d %H:%M:%S')
tag = unicodify(r['tag'])
if pid in results:
# 如果已经存在相应单品的记录
old_rec = results[pid]
old_rec['tag'].add(tag)
old_t = datetime.datetime.strptime(old_rec['date'],
'%Y-%m-%d %H:%M:%S')
if timestamp > old_t:
old_rec['price'] = unicodify(r['price'])
old_rec['price_discount'] = unicodify(
r['price_discount'])
old_rec['currency'] = unicodify(r['currency'])
old_rec['date'] = unicodify(r['date'])
else:
# 如果该单品的记录不存在
results[pid] = {k: unicodify(r[k]) for k in r}
tmp = results[pid]['tag']
if tmp:
results[pid]['tag'] = {tmp}
else:
results[pid]['tag'] = set({})
results[pid]['brand'] = brand_name
results[pid].pop('idproducts')
tot_results.extend(self.random_extract(results.values()))
db.close()
# 将所有的tag转换为[]
data = []
for r in tot_results:
r['tag'] = json.dumps(list(r['tag']), ensure_ascii=False)
data.append(
{k: r[k].encode('utf-8') if r[k] else 'NULL'
for k in r})
# 写入CSV文件
with open(
str.format('extract_{0}.csv',
datetime.datetime.now().strftime('%Y%m%d%H%M%S')),
'wb') as f:
f.write(u'\ufeff'.encode('utf8'))
dict_writer = csv.DictWriter(f,
fieldnames=[
'brand_id', 'brand', 'model',
'region', 'price',
'price_discount', 'currency',
'date', 'name', 'tag', 'url'
])
dict_writer.writeheader()
dict_writer.writerows(data)