python_files/cppc_python脚本/服务器脚本应急/update_goods_hashV1.py

# -*- codeing = utf-8 -*-
# @Time : 2021/5/15 12:55
# @Author : Clown
# @File : update_goods_hash.py
# @Software : PyCharm
import pymysql
import time
from datetime import timedelta, datetime
import sys
import os


def linkTomySql(host,passwd,db_name):
    '''连接至数据库返回【db】,v2新增local_infile=1 打开文件导入权限'''
    try:
        #本地连接为：localhost  服务器连接为：
        db = pymysql.connect(
                        host=host, user="root",
                        passwd=passwd,
                        db=db_name,
                        charset='utf8mb4',
                        local_infile=1)
        print('\nconnect to mysql server 成功')
        print('---------------------------------------')

    except:
        print("\ncould not connect to mysql server")
        db = "连接失败"
    return db

def update_scriptrunninglogs(log_date,log_time,running_Time,operation_results,table_name,db):
    '''将脚本执行情况发送至【脚本执行日志】表
	\n	配套内容
	\n	from datetime import datetime
	\n	import sys
	\n	import traceback
	\n	time_begin = time.time()
	\n	log_date = datetime.now().strftime("%Y-%m-%d")  # 日志记录日期
	\n	log_time = datetime.now().strftime('%H:%M:%S')  # 日志记录时间
	\n	df_name = '数据库表名'
	\n	try:
	\n		operation_results = '执行成功'
	\n	except Exception as e:
	\n		operation_results = traceback.format_exc()
	\n	running_Time = time.time()  - time_begin  # 脚本运行时长
    '''
    try:
        sql = "INSERT INTO scriptrunninglogs VALUES (%s,%s,%s,%s,%s,%s,%s)"#date, time, running_Time, script_name, operation_results, table_name, id
        script_name = os.path.basename(sys.argv[0])
        value = (log_date,log_time,running_Time,script_name,operation_results,table_name,0)
        cursor = db.cursor()
        cursor.execute(sql, value)  # 执行sql语句
        db.commit()
        cursor.close()
    except:
        result = "数据库连接失败"
        print(result)

def update_goods_hash(db,formgoods_table_name,brand_name,date_min_h,data_max_h):
	'''此函数用于更新goods_hash表，更新内容为：
	1.新增goods_hash中不存在的【商品】信息
	\n 2.刷新【商品】【关联品牌】字段中品牌名
	\n 3.字段注解
	\n [db]:数据库实例
	\n [formgoods_table_name]：订单商品明细表名称
	\n [brand_name]：品牌名拼音首字母大写
	\n [date_min_h]：时间筛选维度起始时间
	\n [data_max_h]：时间筛选维度结束时间
	'''
	time_count = time.time()
	cursor_hash = db.cursor()
	# 筛选出订单商品明细表中，相应日期内的【商品】数据/对【商品】相关信息进行去重取唯一/判断【商品】数据在goods_hash表中是否存在/输出数据
	sql_hash_new = "SELECT DISTINCT "\
					"t1.`平台商品名称`,"\
					"t1.`标准商品名称`" \
			   " FROM "\
				   "( SELECT *" \
					f" FROM {formgoods_table_name}" \
					f" WHERE {formgoods_table_name}.`日期` >= "+str(date_min_h)+\
					f" AND {formgoods_table_name}.`日期` <= "+str(data_max_h)+" ) t1 "\
			   " WHERE "\
					"t1.`标准商品名称` NOT IN ( SELECT goods_hash.`标准商品名称` FROM goods_hash )"
	# 导入数据至goods_hash表中
	sql_in_hash = '''INSERT INTO goods_hash VALUES(%s,%s,%s,'')'''

	cursor_hash.execute(sql_hash_new)
	results_h = cursor_hash.fetchall()
	good_in_count = 0
	for result_h in results_h:
		value_h = (result_h[0], result_h[1], brand_name)
		cursor_hash.execute(sql_in_hash, value_h)
		db.commit()
		good_in_count += 1

	# 更新原有的商品关联品牌信息
	sql_hash_old = "SELECT DISTINCT " \
				   "t1.`平台商品名称`," \
				   "t1.`标准商品名称`" \
				" FROM " \
				   "( SELECT *" \
				   f" FROM {formgoods_table_name}" \
				   f" WHERE {formgoods_table_name}.`日期` >= " + str(date_min_h) + \
				   f" AND {formgoods_table_name}.`日期` <= " + str(data_max_h) + " ) t1 " \
				" WHERE " \
				   	"t1.`标准商品名称` IN ( SELECT goods_hash.`标准商品名称` FROM goods_hash )"

	cursor_hash.execute(sql_hash_old)
	results_h = cursor_hash.fetchall()
	good_up_count = 0
	for result_h in results_h:
		sql = f'''SELECT goods_hash.`关联品牌` FROM goods_hash WHERE goods_hash.`标准商品名称` = '{result_h[1]}';'''
		cursor_hash.execute(sql)
		result = str(cursor_hash.fetchall()[0][0])

		if result == 'None':
			brand_cent = brand_name + ','
			sql_up_hash = f"UPDATE goods_hash SET goods_hash.`关联品牌` = '{brand_cent}' WHERE goods_hash.`标准商品名称` = '{result_h[1]}';"
			cursor_hash.execute(sql_up_hash)
			db.commit()
			good_up_count += 1

		elif brand_name not in result:
			brand_cent = brand_name + ','
			sql_up_hash = f"UPDATE goods_hash SET goods_hash.`关联品牌` = CONCAT(goods_hash.`关联品牌`,'{brand_cent}') WHERE goods_hash.`标准商品名称` = '{result_h[1]}';"
			cursor_hash.execute(sql_up_hash)
			db.commit()
			good_up_count += 1

	cursor_hash.close()
	print(f"{brand_name},goods_hash导入用时",time.time()-time_count,"秒****","已录入",good_in_count,"个新增品名，","已更新",good_up_count,"个原有品名")

def update_goods_hash_activeDate(db,date):
	cursor = db.cursor()

	sql = f"UPDATE goods_hash SET goods_hash.`最近一次活跃时间` = '{date}' WHERE goods_hash.`关联品牌` != 'other' AND (goods_hash.`标准商品名称` IN ( SELECT DISTINCT( order_formsgoods.`标准商品名称` ) FROM order_formsgoods WHERE order_formsgoods.`日期` = {int(date)}) OR goods_hash.`标准商品名称` IN ( SELECT DISTINCT( order_formsgoods_zzx.`标准商品名称` ) FROM order_formsgoods_zzx WHERE order_formsgoods_zzx.`日期` = {int(date)}) OR goods_hash.`标准商品名称` IN (SELECT DISTINCT( order_formsgoods_lls.`标准商品名称` ) FROM order_formsgoods_lls WHERE order_formsgoods_lls.`日期` = {int(date)}))"
	try:
		cursor.execute(sql)
		db.commit()
		print(f'已完成【{date}】goods时间更新')
	except:
		db.rollback()
		print(f'【{date}】更新存在错误')

if __name__ == '__main__':

	#日志参数
	time_begin = time.time()
	log_date = datetime.now().strftime("%Y-%m-%d")  # 日志记录日期
	log_time = datetime.now().strftime('%H:%M:%S')  # 日志记录时间
	#通用参数
	host = 'localhost'
	passwd = '111???clown'
	db_name = 'hexingxing'
	table_name = 'goods_hash'

	#时间参数，用于限制数据扫描范围，默认为昨日
	yesterday = (datetime.now() + timedelta(days=-1)).strftime("%Y%m%d")
	date_min_h = yesterday
	data_max_h = yesterday

	#订单商品信息对照表，表名及对应品牌名
	formgoods_table_names = [{'formgoods_table_name':'order_formsgoods','brand_name':'JXB'},
							 {'formgoods_table_name':'order_formsgoods_zzx','brand_name':'ZXX'},
							 {'formgoods_table_name':'order_formsgoods_lls','brand_name':'LLS'}]
	operation_results = '执行成功'

	db = linkTomySql(host,passwd,db_name)

	for formgoods_table in formgoods_table_names:
		formgoods_table_name = formgoods_table['formgoods_table_name']
		brand_name = formgoods_table['brand_name']
		try:
			update_goods_hash(db,formgoods_table_name,brand_name,date_min_h,data_max_h)
		except Exception as e:
			operation_results = e

	update_goods_hash_activeDate (db, yesterday)

	#将此脚本执行结果返回至log中
	running_Time = time.time() - time_begin  # 脚本运行时长
	db = linkTomySql(host, passwd, db_name)
	update_scriptrunninglogs(log_date,log_time,running_Time,operation_results,table_name,db)