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 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 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_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 = 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)