def toutiaoprice(data, item):
decide_sql = "select id,source,platformPriceNameID from PlatformIPAccountPrice where iPAcctountID = '%s' and source = 14 and platformPriceNameID = 1" % (
data)
conn = connect_db()
cursor = conn.cursor()
cursor.execute(decide_sql)
conn.commit()
decide = cursor.fetchall()
if decide:
if item['multi_top_original_writing'] != 0:
updateprice_sql = "update PlatformIPAccountPrice set price='%s',isOriginal=1 where id='%s'" % (
item['multi_top_original_writing'], decide[0][0])
#更新数据头条价格等于多图文第一条原创加发布价格，原创=1
elif item['multi_top_original_writing'] == 0 and item['multi_top'] != 0:
updateprice_sql = "update PlatformIPAccountPrice set price='%s',isOriginal=0 where id='%s'" % (
item['multi_top'], decide[0][0])
#更新数据头条价格等于多图文第一条发布价格，原创=0
elif item['multi_top_original_writing'] == 0 and item[
'multi_top'] == 0 and item['single_original_writing'] != 0:
updateprice_sql = "update PlatformIPAccountPrice set price='%s',isOriginal=1 where id='%s'" % (
item['single_original_writing'], decide[0][0])
#更新数据头条价格等于单图文原创加发布价格， 原创=1
elif item['multi_top_original_writing'] == 0 and item[
'multi_top'] == 0 and item[
'single_original_writing'] == 0 and item['single'] != 0:
updateprice_sql = "update PlatformIPAccountPrice set price='%s',isOriginal=0 where id='%s'" % (
item['single'], decide[0][0])
#更新数据头条价格等于单图文发布价格， 原创=0
else:
updateprice_sql = "update PlatformIPAccountPrice set price='%s',isOriginal=0 where id='%s'" % (
0, decide[0][0])
#更新数据头条价格等于0， 原创=0
save_data(updateprice_sql)
else:
if item['multi_top_original_writing'] != 0:
insertprice_sql = "insert into PlatformIPAccountPrice(iPAcctountID,price,platformPriceNameID,statusIndex,source,createdTime,isOriginal) VALUES ('%s','%s',1,1,14,'%s',1)" % (
data, item['multi_top_original_writing'],
item['last_update_time'])
#插入数据头条价格等于多图文第一条原创加发布价格，原创=1
elif item['multi_top_original_writing'] == 0 and item['multi_top'] != 0:
insertprice_sql = "insert into PlatformIPAccountPrice(iPAcctountID,price,platformPriceNameID,statusIndex,source,createdTime,isOriginal) VALUES ('%s','%s',1,1,14,'%s',0)" % (
data, item['multi_top'], item['last_update_time'])
#插入数据头条价格等于多图文第一条发布价格，原创=0
elif item['multi_top_original_writing'] == 0 and item[
'multi_top'] == 0 and item['single_original_writing'] != 0:
insertprice_sql = "insert into PlatformIPAccountPrice(iPAcctountID,price,platformPriceNameID,statusIndex,source,createdTime,isOriginal) VALUES ('%s','%s',1,1,14,'%s',1)" % (
data, item['single_original_writing'],
item['last_update_time'])
#插入数据头条价格等于单图文原创加发布价格， 原创=1
elif item['multi_top_original_writing'] == 0 and item[
'multi_top'] == 0 and item[
'single_original_writing'] == 0 and item['single'] != 0:
insertprice_sql = "insert into PlatformIPAccountPrice(iPAcctountID,price,platformPriceNameID,statusIndex,source,createdTime,isOriginal) VALUES ('%s','%s',1,1,14,'%s',0)" % (
data, item['single'], item['last_update_time'])
#插入数据头条价格等于单图文发布价格， 原创=0
else:
insertprice_sql = "insert into PlatformIPAccountPrice(iPAcctountID,price,platformPriceNameID,statusIndex,source,createdTime,isOriginal) VALUES ('%s','%s',1,1,14,'%s',0)" % (
data, 0, item['last_update_time'])
#插入数据头条价格等于0， 原创=0
save_data(insertprice_sql)
def decide(num):
conn = connect_db()
cursor = conn.cursor()
# 设置client、connection、results编码方式为utf8mb4
cursor.execute("SET NAMES utf8mb4")
cursor.execute("SET CHARACTER SET utf8mb4")
cursor.execute("SET character_set_connection = utf8mb4")
findsql = "SELECT * from CrawlWeiboWeixinInfo where ID=%s" % (num)
cursor.execute(findsql)
conn.commit()
res = cursor.fetchall()
if res:
return 'ok'
else:
return 'error'
def get_insertid():
"""获取需要插入数据的id"""
# 获取昨天入库的数据
conn = connect_db()
cursor = conn.cursor()
# 设置client、connection、results编码方式为utf8mb4
cursor.execute("SET NAMES utf8mb4")
cursor.execute("SET CHARACTER SET utf8mb4")
cursor.execute("SET character_set_connection = utf8mb4")
sql = "select id, platformID, accountID, `name`, indexUrl from PlatformIPAccount " \
" where platformID in (1,2,3,10,12,13)"
cursor.execute(sql)
weixindatas = cursor.fetchall()
conn.close()
return weixindatas
def save_data(sql):
conn = connect_db()
cursor = conn.cursor()
# 设置client、connection、results编码方式为utf8mb4
cursor.execute("SET NAMES utf8mb4")
cursor.execute("SET CHARACTER SET utf8mb4")
cursor.execute("SET character_set_connection = utf8mb4")
try:
cursor.execute(sql)
print(sql)
except pymysql.IntegrityError:
print('数据已存在', sql)
except Exception as e:
logger.error('执行SQL发生异常 原因是：%s' % e)
logger.error('SQL为：%s' % sql)
else:
conn.commit()
finally:
conn.close()
def forward_price(data,item):
decide_sql = "select id,source,platformPriceNameID from PlatformIPAccountPrice where iPAcctountID = '%s' and source = 14 and platformPriceNameID = 7"%(data)
conn = connect_db()
cursor = conn.cursor()
cursor.execute(decide_sql)
conn.commit()
decide = cursor.fetchall()
if decide:
if item['soft_retweet_price'] != 0:
updataprice_sql = "update PlatformIPAccountPrice set price='%s',isOriginal=0 where id='%s'"%(item['retweet_price'],decide[0][0])
elif item['soft_retweet_price'] == 0 and item['retweet_price'] != 0:
updataprice_sql = "update PlatformIPAccountPrice set price='%s',isOriginal=0 where id='%s'"%(item['soft_retweet_price'],decide[0][0])
else:
updataprice_sql = "update PlatformIPAccountPrice set price='%s',isOriginal=0 where id='%s'"%(0,decide[0][0])
save_data(updataprice_sql)
else:
if item['soft_retweet_price'] != 0:
insertprice_sql = "insert into PlatformIPAccountPrice(iPAcctountID,price,platformPriceNameID,statusIndex,source,createdTime,isOriginal) VALUES ('%s','%s',7,1,14,'%s',0)"%(data,item['retweet_price'],item['last_update_time'])
# print(insertprice_sql)
elif item['soft_retweet_price'] == 0 and item['retweet_price'] != 0:
insertprice_sql = "insert into PlatformIPAccountPrice(iPAcctountID,price,platformPriceNameID,statusIndex,source,createdTime,isOriginal) VALUES ('%s','%s',7,1,14,'%s',0)"%(data,item['soft_retweet_price'],item['last_update_time'])
else:
insertprice_sql = "insert into PlatformIPAccountPrice(iPAcctountID,price,platformPriceNameID,statusIndex,source,createdTime,isOriginal) VALUES ('%s','%s',7,1,14,'%s',0)"%(data,0,item['last_update_time'])
save_data(insertprice_sql)
def shortvideosql(data, item):
conn = connect_db()
cursor = conn.cursor()
existsql = "select iPAcctountID from PlatformIPAccountPrice where iPAcctountID = '%s' and source = 14" % (
data[0])
cursor.execute(existsql)
conn.commit()
decide = cursor.fetchall()
if decide:
if 'original_release' in item.keys():
#更新原创视频+发布的价格
updateoriginal = "update PlatformIPAccountPrice set price = '%s',isOriginal = '%s' where iPAcctountID = '%s' and platformPriceNameID = 3"\
%(item['original_release'],1,decide[0][0])
save_data(updateoriginal)
#更新活动现场直播
updateevents = "update PlatformIPAccountPrice set price = '%s', isOriginal = '%s' where iPAcctountID = '%s' and platformPriceNameID = 5"\
%(item['filed_events'],0,decide[0][0])
save_data(updateevents)
#更新原创视频价格
if item['original_release'] != 0 and item['video_release'] != 0:
originalvideo = item['original_release'] - item['video_release']
updatevideo = "update PlatformIPAccountPrice set price = '%s', isOriginal = '%s' where iPAcctountID = '%s' and platformPriceNameID = 4"\
%(originalvideo,1,decide[0][0])
else:
updatevideo = "update PlatformIPAccountPrice set price = '%s', isOriginal = '%s' where iPAcctountID = '%s' and platformPriceNameID = 4" \
%(0,0,decide[0][0])
save_data(updatevideo)
else:
#更新原创视频+发布的价格
updateoriginal = "update PlatformIPAccountPrice set price = '%s',isOriginal = '%s' where iPAcctountID = '%s' and platformPriceNameID = 3" \
%(item['price_video_release'],0,decide[0][0])
save_data(updateoriginal)
#更新活动现场直播
updateevents = "update PlatformIPAccountPrice set price = '%s', isOriginal = '%s' where iPAcctountID = '%s' and platformPriceNameID = 5" \
%(0,0,decide[0][0])
save_data(updateevents)
#更新原创视频价格
updatevideo = "update PlatformIPAccountPrice set price = '%s', isOriginal = '%s' where iPAcctountID = '%s' and platformPriceNameID = 4" \
%(0,0,decide[0][0])
save_data(updatevideo)
else:
#有三种报价的字典结构
if 'original_release' in item.keys():
#插入原创视频+发布
insertoriginal = "insert into PlatformIPAccountPrice(iPAcctountID, price, platformPriceNameID,statusIndex,source,createdTime,isOriginal) " \
"VALUES('%s','%s','%s','%s','%s','%s','%s')"%(data[0],item['original_release'],3,1,14,item['createtime'],1)
save_data(insertoriginal)
#插入活动现场直播
insertevents = "insert into PlatformIPAccountPrice(iPAcctountID, price, platformPriceNameID,statusIndex,source,createdTime,isOriginal) " \
"VALUES('%s','%s','%s','%s','%s','%s','%s')"%(data[0],item['filed_events'],5,1,14,item['createtime'],0)
save_data(insertevents)
#当原创视频+发布价格和视频发布价格都不为空时，插入原创视频价格。
if item['original_release'] != 0 and item['video_release'] != 0:
originalvideo = item['original_release'] - item['video_release']
insertvideo = "insert into PlatformIPAccountPrice(iPAcctountID, price, platformPriceNameID,statusIndex,source,createdTime,isOriginal) " \
"VALUES('%s','%s','%s','%s','%s','%s','%s')"%(data[0],originalvideo,4,1,14,item['createtime'],1)
else:
insertvideo = "insert into PlatformIPAccountPrice(iPAcctountID, price, platformPriceNameID,statusIndex,source,createdTime,isOriginal) " \
"VALUES('%s','%s','%s','%s','%s','%s','%s')"%(data[0],0,4,1,14,item['createtime'],0)
save_data(insertvideo)
#只有一种报价的结构
else:
#插入视频发布
insertoriginal = "insert into PlatformIPAccountPrice(iPAcctountID, price, platformPriceNameID,statusIndex,source,createdTime,isOriginal) " \
"VALUES('%s','%s','%s','%s','%s','%s','%s')"%(data[0],item['price_video_release'],3,1,14,item['createtime'],0)
save_data(insertoriginal)
#插入活动现场直播
insertevents = "insert into PlatformIPAccountPrice(iPAcctountID, price, platformPriceNameID,statusIndex,source,createdTime,isOriginal) " \
"VALUES('%s','%s','%s','%s','%s','%s','%s')"%(data[0],0,5,1,14,item['createtime'],0)
save_data(insertevents)
#这种情况原创视频价格必定为0
insertvideo = "insert into PlatformIPAccountPrice(iPAcctountID, price, platformPriceNameID,statusIndex,source,createdTime,isOriginal) " \
"VALUES('%s','%s','%s','%s','%s','%s','%s')"%(data[0],0,4,1,14,item['createtime'],0)
save_data(insertvideo)
def contentprice(data, item):
decide_sql = "select id,source,platformPriceNameID from PlatformIPAccountPrice where iPAcctountID = '%s' and source = 14 and platformPriceNameID = 2" % (
data)
conn = connect_db()
cursor = conn.cursor()
cursor.execute(decide_sql)
conn.commit()
decide = cursor.fetchall()
if decide:
if item['multi_top_original_writing'] != 0 and item['multi_top'] != 0:
#多图文第一条原创+发布，多图文第一条发布都存在时，原创内容价格=（多图文第一条原创+发布）-（多图文第一条发布）
originalprice = item['multi_top_original_writing'] - item[
'multi_top']
updateprice_sql = "update PlatformIPAccountPrice set price='%s',isOriginal=1 where id='%s'" % (
originalprice, decide[0][0])
elif item['multi_top_original_writing'] == 0 and item[
'multi_top'] == 0 and item[
'single_original_writing'] != 0 and item['single'] != 0:
#多图文第一条原创+发布，多图文第一条发布都不存在时，且单图文原创+发布，单图文发布都存在时，原创内容价格 = （单图文原创+发布）-（单图文发布）
originalprice = item['single_original_writing'] - item['single']
updateprice_sql = "update PlatformIPAccountPrice set price='%s',isOriginal=1 where id='%s'" % (
originalprice, decide[0][0])
elif item['multi_top_original_writing'] == 0 and item[
'multi_top'] == 0 and item[
'single_original_writing'] == 0 and item[
'single'] == 0 and item[
'multi_second_original_writing'] != 0 and item[
'multi_second'] != 0:
#多图文第一条原创+发布，多图文第一条发布都不存在时，且单图文原创+发布，单图文发布都不存在时.多图文第二条原创+发布，多图文第二条发布都存在时，原创内容价格 = （多图文第二条原创+发布）-（多图文第二条发布）
originalprice = item['multi_second_original_writing'] - item[
'multi_second']
updateprice_sql = "update PlatformIPAccountPrice set price='%s',isOriginal=1 where id='%s'" % (
originalprice, decide[0][0])
else:
#剩下的其他情况，原创内容皆为0
originalprice = 0
updateprice_sql = "update PlatformIPAccountPrice set price='%s',isOriginal=1 where id='%s'" % (
originalprice, decide[0][0])
save_data(updateprice_sql)
else:
# global insertprice_sql
if item['multi_top_original_writing'] != 0 and item['multi_top'] != 0:
sign = 1
originalprice = item['multi_top_original_writing'] - item[
'multi_top']
insertprice_sql = "insert into PlatformIPAccountPrice(iPAcctountID,price,platformPriceNameID,statusIndex,source,createdTime,isOriginal) VALUES ('%s','%s',2,1,14,'%s',1)" % (
data, originalprice, item['last_update_time'])
save_data(insertprice_sql)
elif item['multi_top_original_writing'] == 0 and item[
'multi_top'] == 0 and item[
'single_original_writing'] != 0 and item['single'] != 0:
sign = 1
originalprice = item['single_original_writing'] - item['single']
insertprice_sql = "insert into PlatformIPAccountPrice(iPAcctountID,price,platformPriceNameID,statusIndex,source,createdTime,isOriginal) VALUES ('%s','%s',2,1,14,'%s',1)" % (
data, originalprice, item['last_update_time'])
elif item['multi_top_original_writing'] == 0 and item[
'multi_top'] == 0 and item[
'single_original_writing'] == 0 and item[
'single'] == 0 and item[
'multi_second_original_writing'] != 0 and item[
'multi_second'] != 0:
sign = 1
originalprice = item['multi_second_original_writing'] - item[
'multi_second']
insertprice_sql = "insert into PlatformIPAccountPrice(iPAcctountID,price,platformPriceNameID,statusIndex,source,createdTime,isOriginal) VALUES ('%s','%s',2,1,14,'%s',1)" % (
data, originalprice, item['last_update_time'])
else:
sign = 0
#剩下其他情况皆为原创内容为0，且不做任何操作
# print(insertprice_sql)
if sign == 1:
save_data(insertprice_sql)