def saveTrades(trades):
db = getDBConn(DB)
cmdStr = 'INSERT INTO %s VALUES(%s,NOW())' % (
mapping_trade['table'], ','.join(
['%s' for t in mapping_trade['fields']]))
args = [[(trade[key] if trade.has_key(key) else None)
for key in mapping_trade['fields']] for trade in trades]
args = [[(field.encode('utf-8') if isinstance(field, unicode) else field)
for field in trade] for trade in args]
c = db.cursor()
rowNum = c.executemany(cmdStr, args)
orders = [[
dict(order.items() + {'tid': trade['tid']}.items())
for order in trade['orders']['order']
] for trade in trades]
orders = [order[0] for order in orders]
cmdStr = 'INSERT INTO `%s` VALUES(%s,NOW())' % (
mapping_order['table'], ','.join(
['%s' for t in mapping_order['fields']]))
args = [[(order[key] if order.has_key(key) else None)
for key in mapping_order['fields']] for order in orders]
args = [[(field.encode('utf-8') if isinstance(field, unicode) else field)
for field in order] for order in args]
rowNum += c.executemany(cmdStr, args)
db.commit()
c.close()
db.close()
return rowNum
def updateItemProps():
db = getDBConn('ibbd')
c = db.cursor()
c.execute('SELECT DISTINCT(cid) FROM ibbd.lib_topitemcats WHERE is_parent=0'
)
cidList = c.fetchall()
c.close()
for (i, cid) in enumerate(cidList):
if i <= 5010:
continue
metadata = json.loads(getItemProps(cid[0]))
if not metadata.has_key('itemprops_get_response'):
print json.dumps(metadata, indent=4)
break
if not metadata['itemprops_get_response'].has_key('item_props'):
continue
metadata = metadata['itemprops_get_response']['item_props']['item_prop']
for item_prop in metadata:
print json.dumps(item_prop, indent=4, ensure_ascii=False)
if not item_prop.has_key('prop_values'):
continue
for prop_value in item_prop['prop_values']['prop_value']:
cmdStr = \
"INSERT INTO lib_topitem_props VALUES('{0}',CURDATE(),'{1}','{2}','{3}','{4}')".format(cid[0],
item_prop['pid'], escapeSQLCommand(item_prop['name'
].encode('utf-8')), prop_value['vid'],
escapeSQLCommand(prop_value['name'].encode('utf-8')))
db.query(cmdStr)
db.commit()
db.close()
def updateItemProps():
db = getDBConn('ibbd')
c = db.cursor()
c.execute(
'SELECT DISTINCT(cid) FROM ibbd.lib_topitemcats WHERE is_parent=0')
cidList = c.fetchall()
c.close()
for (i, cid) in enumerate(cidList):
if i <= 5010:
continue
metadata = json.loads(getItemProps(cid[0]))
if not metadata.has_key('itemprops_get_response'):
print json.dumps(metadata, indent=4)
break
if not metadata['itemprops_get_response'].has_key('item_props'):
continue
metadata = metadata['itemprops_get_response']['item_props'][
'item_prop']
for item_prop in metadata:
print json.dumps(item_prop, indent=4, ensure_ascii=False)
if not item_prop.has_key('prop_values'):
continue
for prop_value in item_prop['prop_values']['prop_value']:
cmdStr = \
"INSERT INTO lib_topitem_props VALUES('{0}',CURDATE(),'{1}','{2}','{3}','{4}')".format(cid[0],
item_prop['pid'], escapeSQLCommand(item_prop['name'
].encode('utf-8')), prop_value['vid'],
escapeSQLCommand(prop_value['name'].encode('utf-8')))
db.query(cmdStr)
db.commit()
db.close()
def setLastestTimeOfKeyword(keyword, dbname='topspider'):
db = getDBConn(dbname, **mysql_ip_config.get('152'))
cursor = db.cursor()
cursor.execute('replace into top_ztc_keyword_update_time values(%s,now())',
[keyword])
db.commit()
cursor.close()
db.close()
def getLastestTimeOfKeyword(keyword, dbname='topspider'):
db = getDBConn(dbname, **mysql_ip_config.get('152'))
cursor = db.cursor()
cursor.execute(
'select timestampdiff(minute,lastest_tsmp,now()) from top_ztc_keyword_update_time where keyword=%s',
[keyword])
ret = cursor.fetchone()
db.commit()
cursor.close()
db.close()
ret = (ret[0] if ret != None else 9999) # 返回值可能为0
return ret
def sum_ztc(keyword, dayinterval=2, dbname='topspider'):
sql = \
'''replace into ibbd2.top_ztc_sum(`id`,`keyword`,`key`,`item_id`,`avg_rank`,`lastest_tsmp`,`shop_id`)
(select `item_id`,`keyword`,`key`,`item_id`,AVG(rank),now(),`shop_id`
from topspider.top_ztc_simple
where keyword=%s
and population_tsmp > (now() - INTERVAL {0} DAY)
group by item_id);
'''.format(dayinterval)
db = getDBConn(dbname, **mysql_ip_config.get('152'))
cursor = db.cursor()
# print 'sum_ztc'
# print sql
cursor.execute(sql, [keyword])
db.commit()
cursor.close()
db.close()
def updateItemCats():
r = redis.StrictRedis(host='223.4.5.116')
db = getDBConn('ibbd')
r.lpush('itemCats', 0)
while 1:
if r.llen('itemCats') == 0:
break
cat = r.lpop('itemCats')
metadata = json.loads(getItemCats(cat), encoding='utf-8'
)['itemcats_get_response']['item_cats']['item_cat'
]
print json.dumps(metadata, indent=4, ensure_ascii=False)
for t in metadata:
cmdStr = \
"INSERT INTO lib_topitemcats VALUES('{0}','{1}',{2},'{3}','{4}')".format(t['cid'
], escapeSQLCommand(t['name'].encode('utf-8')),
t['is_parent'], t['parent_cid'], t['sort_order'])
db.query(cmdStr)
if t['is_parent']:
r.lpush('itemCats', t['cid'])
db.commit()
db.close()
def updateItemCats():
r = redis.StrictRedis(host='223.4.5.116')
db = getDBConn('ibbd')
r.lpush('itemCats', 0)
while 1:
if r.llen('itemCats') == 0:
break
cat = r.lpop('itemCats')
metadata = json.loads(
getItemCats(cat),
encoding='utf-8')['itemcats_get_response']['item_cats']['item_cat']
print json.dumps(metadata, indent=4, ensure_ascii=False)
for t in metadata:
cmdStr = \
"INSERT INTO lib_topitemcats VALUES('{0}','{1}',{2},'{3}','{4}')".format(t['cid'
], escapeSQLCommand(t['name'].encode('utf-8')),
t['is_parent'], t['parent_cid'], t['sort_order'])
db.query(cmdStr)
if t['is_parent']:
r.lpush('itemCats', t['cid'])
db.commit()
db.close()
def saveTrades(trades):
db = getDBConn(DB)
cmdStr = 'INSERT INTO %s VALUES(%s,NOW())' % (mapping_trade['table'],
','.join(['%s' for t in mapping_trade['fields']]))
args = [[(trade[key] if trade.has_key(key) else None) for key in
mapping_trade['fields']] for trade in trades]
args = [[(field.encode('utf-8') if isinstance(field, unicode) else field)
for field in trade] for trade in args]
c = db.cursor()
rowNum = c.executemany(cmdStr, args)
orders = [[dict(order.items() + {'tid': trade['tid']}.items()) for order in
trade['orders']['order']] for trade in trades]
orders = [order[0] for order in orders]
cmdStr = 'INSERT INTO `%s` VALUES(%s,NOW())' % (mapping_order['table'],
','.join(['%s' for t in mapping_order['fields']]))
args = [[(order[key] if order.has_key(key) else None) for key in
mapping_order['fields']] for order in orders]
args = [[(field.encode('utf-8') if isinstance(field, unicode) else field)
for field in order] for order in args]
rowNum += c.executemany(cmdStr, args)
db.commit()
c.close()
db.close()
return rowNum
