def save_to_sql(self, sql, centence, db="baoersqlerp", is_log=False): conn = pymysql.connect(host=configure.echo(sql)["config"]["host"], user=configure.echo(sql)["config"]["user"], passwd=configure.echo(sql)["config"]["passwd"], db=db, port=configure.echo(sql)["config"]["port"], charset=configure.echo(sql)["config"]["charset"], ) cur = conn.cursor() if not is_log: log = self.create_log(sql=sql, db=db) try: cur.execute(centence) conn.commit() if not is_log: log["result"] = "OK!" except Exception as e: print(e) if not is_log: log["result"] = str(e)[:254] finally: cur.close() conn.close() if not is_log: return log
def run(mode="shops.query", msg=False, default=True, file_name="jst_private", sandbox=False, **kwargs):
env = {
'sandbox': sandbox, # 是否为沙盒环境
'partner_id': configure.echo(file_name)["config"]["partner_id"],
'partner_key': configure.echo(file_name)["config"]["partner_key"],
'token': configure.echo(file_name)["config"]["token"],
'taobao_appkey': configure.echo(file_name)["config"]["taobao_appkey"],
'taobao_secret': configure.echo(file_name)["config"]["taobao_secret"],
}
if default == True:
params = {
# 'shop_id': '10127848', # 店铺ID
'modified_begin': delta_now,
'modified_end': now,
'page_size': 10,
'page_index': 1,
# 'so_ids': ['111902591572215654'] # 订单号
}
else:
params = {}
if msg:
print("mode:<{}>\nfile_name:<{}>\nmsg:<{}>\nsandbox:<{}>\nkwargs:<{}>\n".format(mode, file_name, msg, sandbox, kwargs))
params.update(kwargs)
cfg = Config(**env) # 配置参数
service = BaseService(cfg, msg) # 运行服务
return service.run(mode, params)
def save_to_mysql_mode2(msg, items, table, sql_list=["localhost"]):
sql_insert = ""
for item in items:
sql_value = ""
sql_columns = str(tuple(item.keys())).replace("'", "`")
for i in item:
sql_value += "'" + item[i] + "',"
sql_values = "(" + sql_value[:-1] + ")"
sql_insert += "INSERT INTO " + table + sql_columns + " VALUES " + sql_values + ";"
for sql in sql_list:
conn = pymysql.connect(
host=configure.echo(sql)["config"]["host"],
port=configure.echo(sql)["config"]["port"],
user=configure.echo(sql)["config"]["user"],
passwd=configure.echo(sql)["config"]["passwd"],
charset=configure.echo(sql)["config"]["charset"],
db="baoersqlbs")
try:
cursor = conn.cursor()
cursor.execute(sql_insert)
conn.commit()
except pymysql.err.InternalError as e:
pass
except pymysql.err.IntegrityError as e:
code, message = e.args
print("* 错误：", message[-17:])
except Exception as e:
prompt_message(mode=2, msg=msg, detail=1)
print("* 错误：", e)
print(type(e))
finally:
cursor.close()
conn.close()
return table
def detector_mode4(date, table, sql_list):
for sql in sql_list:
for category in MODE4_PERMIT:
print("#" * 70)
print(" 日期：", date, "\t\t类目：", category, "\t\t终端：", sql)
print("#" * 70)
for attribute, form in MODE4_PERMIT[category].items():
sql_select = "SELECT max(热销排名),count(*) from "+table+" WHERE 类目='"+category+"' " \
"AND 类型='"+form+"' AND 属性='"+attribute+"' AND 日期='"+date+"' GROUP BY 类目,类型,属性,日期;"
conn = pymysql.connect(
host=configure.echo(sql)["config"]["host"],
port=configure.echo(sql)["config"]["port"],
user=configure.echo(sql)["config"]["user"],
passwd=configure.echo(sql)["config"]["passwd"],
charset=configure.echo(sql)["config"]["charset"],
db="baoersqlbasic")
try:
cursor = conn.cursor()
cursor.execute(sql_select)
result = cursor.fetchall()
print("类型：", form, "\t属性：", attribute, "\t\t最大：",
result[0][0], " \t条目数：", result[0][1])
except:
print("类型：", form, "\t属性：", attribute,
"\t\t最大：- \t\t条目数： -")
finally:
cursor.close()
conn.close()
def validation_exists2(id, date, sql_list=["localhost"]):
sql = sql_list[-1]
conn = pymysql.connect(host=configure.echo(sql)["config"]["host"],
port=configure.echo(sql)["config"]["port"],
user=configure.echo(sql)["config"]["user"],
passwd=configure.echo(sql)["config"]["passwd"],
charset=configure.echo(sql)["config"]["charset"],
db="baoersqlbs")
cursor = conn.cursor()
result = cursor.execute("SELECT * FROM bc_commodity_items WHERE `日期`='" +
date + "' and `id`='" + id + "';")
conn.commit()
cursor.close()
conn.close()
if result:
print("* 忽略：该条信息已经存在！")
return 1
def write_to_sql(self, filenames, sql_list=SQL_LIST_PATTERN):
import configure
for filename in filenames:
sql_insert = ""
df = pd.read_excel(filename, header=3)
print(filename)
for r in range(len(df)):
basename = os.path.basename(filename)
sql_colunms = "(日期,类目,渠道,字段,"
sql_values = "('" + basename[8:18] + "','" + basename[
29:32] + "','" + basename[39:43] + "','" + basename[
33:38] + "',"
for s in df.columns:
if s != df.columns[-1]:
if s == "词均支付转化率":
sql_colunms += "支付转化率,"
sql_values += "'" + str(df.loc[r, s]).replace(
"'", "`") + "',"
elif s == "词均点击率":
sql_colunms += "点击率,"
sql_values += "'" + str(df.loc[r, s]).replace(
"'", "`") + "',"
else:
sql_colunms += str(s) + ","
sql_values += "'" + str(df.loc[r, s]).replace(
"'", "`") + "',"
else:
sql_colunms += str(s)
sql_values += "'" + str(df.loc[r, s]).replace(
"'", "`") + "'"
if "热搜" in filename:
sql_insert += "insert into bc_searchwords_hotwords " + sql_colunms + ") values" + sql_values + ");"
elif "飙升" in filename:
sql_insert += "insert into bc_searchwords_risewords " + sql_colunms + ") values" + sql_values + ");"
for sql in sql_list:
sql_msg = configure.echo(sql)
conn = pymysql.connect(host=sql_msg["config"]["host"],
port=int(sql_msg["config"]["port"]),
user=sql_msg["config"]["user"],
passwd=sql_msg["config"]["passwd"],
charset=sql_msg["config"]["charset"],
db="baoersqlbs")
cursor = conn.cursor()
try:
cursor.execute(sql_insert)
conn.commit()
print(" * 写入 {} 成功！".format(sql))
except Exception as e: # 加入异常判定
conn.rollback() # 进行回滚
print(" * 写入 {} 错误：{}".format(sql, e))
finally:
cursor.close()
conn.close()
shutil.move(
filename,
os.path.dirname(filename) + os.path.sep + "searchwords" +
os.path.sep + os.path.basename(filename))
def return_shop_list(sql="xiaobaods_w"):
import pymysql
import configure
conn = pymysql.connect(host=configure.echo(sql)["config"]["host"],
port=configure.echo(sql)["config"]["port"],
user=configure.echo(sql)["config"]["user"],
passwd=configure.echo(sql)["config"]["passwd"],
charset=configure.echo(sql)["config"]["charset"],
db="baoersqlerp")
try:
cursor = conn.cursor()
cursor.execute(
"SELECT `brand` FROM `shops.query` WHERE `operator` is not null ;")
conn.commit()
data = cursor.fetchall()
finally:
cursor.close()
conn.close()
result = [i[0] for i in data]
return result
def request_to_sql(self, sql, centence, db="baoersqlerp"):
data = None
conn = pymysql.connect(
host=configure.echo(sql)["config"]["host"],
user=configure.echo(sql)["config"]["user"],
passwd=configure.echo(sql)["config"]["passwd"],
db=db,
port=configure.echo(sql)["config"]["port"],
charset=configure.echo(sql)["config"]["charset"],
)
cur = conn.cursor()
try:
cur.execute(centence)
conn.commit()
data = cur.fetchall()
except Exception as e:
print(e)
finally:
cur.close()
conn.close()
return data
def save_to_mysql_mode1(mode, msg, items, sql_list=["localhost"]):
sql_insert = ""
if mode == 1 or mode == 3:
sql_insert = ""
table1 = {1: "brand", 3: "attribute"}
table2 = {"热销商品榜": "sales", "流量商品榜": "visitor"}
table = "bc_{0}_granularity_{1}".format(table1[mode],
table2[msg.get('head')])
elif mode == 4:
table = "bc_category_granularity"
for item in items:
sql_value = ""
sql_columns = str(tuple(item.keys())).replace("'", "`")
# sql_columns = str(tuple(item.keys()))
for i in item:
sql_value += "'" + item[i] + "',"
sql_values = "(" + sql_value[:-1] + ")"
sql_insert += "insert into " + table + \
sql_columns + " values " + sql_values + ";"
for sql in sql_list:
conn = pymysql.connect(
host=configure.echo(sql)["config"]["host"],
port=configure.echo(sql)["config"]["port"],
user=configure.echo(sql)["config"]["user"],
passwd=configure.echo(sql)["config"]["passwd"],
charset=configure.echo(sql)["config"]["charset"],
db="baoersqlbs")
try:
cursor = conn.cursor()
cursor.execute(sql_insert)
conn.commit()
print("# ", sql, ":Write successfully")
except Exception as e:
prompt_message(mode=1, msg=msg, detail=1)
print("* 错误：", e)
finally:
cursor.close()
conn.close()
return table
def call_sql(self, sql, sentence):
# print("sentence: ", sentence)
conn = pymysql.connect(
host=configure.echo(sql)["config"]["host"],
port=configure.echo(sql)["config"]["port"],
user=configure.echo(sql)["config"]["user"],
passwd=configure.echo(sql)["config"]["passwd"],
charset=configure.echo(sql)["config"]["charset"],
db="baoersqlbs")
try:
cursor = conn.cursor()
cursor.execute(sentence)
conn.commit()
data = cursor.fetchall()
finally:
cursor.close()
conn.close()
if data:
result = [i[0] for i in data]
else:
result = "Completed Saved!"
return result
def __init__(self, **kwargs):
self.sql = configure.echo(kwargs.get("sql", "xiaobaods_r"))["config"]
# 基于数据库configure的选择
self.sql["db"] = "baoersqlbasic"
self.line_b = kwargs.get("line_b", 0) # 最终输出条数起始
self.line_f = kwargs.get("line_f", 20) # 最终输出条数结束
if self.line_b > self.line_f:
self.line_b, self.line_f = self.line_f, self.line_b
self.date = kwargs.get("date", datetime.datetime.strftime( \
datetime.datetime.today().date() -datetime.timedelta(1), "%Y-%m-%d"))
self.date_range = kwargs.get("date_range", datetime.datetime.strftime(\
datetime.datetime.today().date() - datetime.timedelta(1), "%Y-%m-%d"))
self.date = parse(self.date).date() # 抽出数据筛选日期
self.date_range = parse(self.date_range).date() # 抽出数据筛选日期
self.category = kwargs.get("category", "牛仔裤") # 抽出数据筛选“类目”
self.classification = kwargs.get("classification", "款式") # 分类
self.attributes = kwargs.get("attributes", "铅笔裤") # 二级分类
self.length = kwargs.get("length", 7) # 针对二次筛选需求的上溯天数
self.table = kwargs.get("table", "") # 具体的数据表，具体函数需指定
self.variable = kwargs.get("variable", "热销排名") # 二次筛选需求的显示变量
self.fillna = kwargs.get("fillna", " ") # 填充空值
self.debug = kwargs.get("debug", 0) # 选择输出项目
self.path = kwargs.get("path", "") # debug=9,输出csv的路径指定
self.choice = kwargs.get("choice", "热搜核心词") # xiaobaods_w 中的表单选择
if not self.path:
self.path = os.path.join(os.path.expanduser("~"), 'Desktop')
self.keyword = kwargs.get("keyword", "日期：")
self.cid = kwargs.get("cid", "")
# 替换的关键词（防止显示数字在前的异常排序）
self.rankl = kwargs.get("rankl", 0)
self.rankm = kwargs.get("rankm", 500)
self.titler = kwargs.get("titler", "")
self.storer = kwargs.get("storer", "")
self.v1l = kwargs.get("v1l", 9999999999)
self.v1m = kwargs.get("v1m", -9999999999)
self.v2l = kwargs.get("v2l", 9999999999)
self.v2m = kwargs.get("v2m", -9999999999)
self.v3l = kwargs.get("v3l", 9999999999)
self.v3m = kwargs.get("v3m", -9999999999)
self.v4l = kwargs.get("v4l", 9999999999)
self.v4m = kwargs.get("v4m", -9999999999)
self.v5l = kwargs.get("v5l", 9999999999)
self.v5m = kwargs.get("v5m", -9999999999)
# 算法部分
self.alg = kwargs.get("alg", "")
self.alpha = kwargs.get("alpha", 1.2)
self.beta = kwargs.get("beta", 0.2)
self.gamma = kwargs.get("gamma", 10)
self.delta = kwargs.get("delta", 0.05)
self.epsilon = kwargs.get("epsilon", 350)
self.zeta = kwargs.get("zeta", 0)
def sql_answer(self, sql, sql_centence, db="baoersqlos"):
data = ""
conn = pymysql.connect(
host=configure.echo(sql)["config"]["host"],
port=configure.echo(sql)["config"]["port"],
user=configure.echo(sql)["config"]["user"],
passwd=configure.echo(sql)["config"]["passwd"],
charset=configure.echo(sql)["config"]["charset"],
db=db)
try:
cursor = conn.cursor()
cursor.execute(sql_centence)
conn.commit()
data = cursor.fetchall()
if not data:
data = "写入完毕" # 返回一个标记
except Exception as e:
conn.rollback()
data = str(e)
finally:
cursor.close()
conn.close()
return data
def __init__(self, **kwargs):
self.sql = configure.echo(kwargs.get("sql", "xiaobaods_w"))["config"]
self.sql["db"] = "baoersqlaftertreatment"
self.line_b = kwargs.get("line_b", 1)
self.line_f = kwargs.get("line_f", 5)
self.fillna = kwargs.get("fillna", "")
self.debug = kwargs.get("debug", 0)
self.path = kwargs.get("path", "") # debug=9,输出csv的路径指定
self.category = kwargs.get("category", "牛仔裤")
self.date = kwargs.get("date", datetime.datetime.strftime( \
datetime.datetime.today().date() -datetime.timedelta(1), "%Y-%m-%d"))
self.alg = kwargs.get("alg", "Dec")
if not self.path:
self.path = os.path.join(os.path.expanduser("~"), 'Desktop')
def operation_pymysql(SQL_select="", SQL="xiaobaods_w"):
SQL_msg = configure.echo(SQL)["config"]
conn = pymysql.connect(host=SQL_msg["host"],
port=int(SQL_msg["port"]),
user=SQL_msg["user"],
passwd=SQL_msg["passwd"],
charset=SQL_msg["charset"],
db="baoersqlerp")
cursor = conn.cursor()
cursor.execute(SQL_select)
conn.commit()
return_variable = cursor.fetchall()
cursor.close()
conn.close()
return return_variable
def save_to_sql(self,
sql,
sentence,
length=-1,
add_mode="",
db="baoersqlerp",
is_log=False):
'''
length 专为日志文件写入文件长度
'''
conn = pymysql.connect(
host=configure.echo(sql)["config"]["host"],
user=configure.echo(sql)["config"]["user"],
passwd=configure.echo(sql)["config"]["passwd"],
db=db,
port=configure.echo(sql)["config"]["port"],
charset=configure.echo(sql)["config"]["charset"],
)
cur = conn.cursor()
if not is_log:
log = self.create_log(sql=sql, db=db, add_mode=add_mode)
log["length"] = length
try:
cur.execute(sentence)
conn.commit()
if not is_log:
log["result"] = "OK!"
except Exception as e:
print(e)
if not is_log:
log["result"] = str(e)[:254]
finally:
cur.close()
conn.close()
if not is_log:
return log
def __init__(self, variable="交易时间", date=""):
# 调用函数，读取 MySQL ERP 中的值
print(" - 表单初始化中...")
time_start = time.time()
self._date = datetime.date.today() - datetime.timedelta(
1) # datetime.date()
self._sql = configure.echo("xiaobaods_r")["config"]
self._df, self._date = request_df(self._sql, self._date, variable)
# 添加日期列
self._df["日期"] = self._df["交易时间"].map(lambda s: str(s).split()[0])
# 提示表单上下限日期
print(" - 表单准备完成，日期为：{} - {}，共 {} 行。".format(min(self._df[variable]),
max(self._df[variable]),
len(self._df)))
# 格式化为 str的末日时间：
self.date = datetime.datetime.strftime(self._date, "%Y-%m-%d")
time_end = time.time()
print(' - 表单准备耗时：{} s'.format(format(time_end - time_start, ".2f")))
def __init__(self, **kwargs):
self.sql = configure.echo("xiaobaods_r")["config"]
self.sql["db"] = "kpi_datebase"
self.max_weight = kwargs.get("max_weight", 100)
self.date = kwargs.get(
"date",
datetime.datetime.strftime(
datetime.datetime.now().date() - datetime.timedelta(30),
"%Y-%m-01"))
if self.date.split("-")[-1] != "01":
self.date = self.date[:-2] + "01"
self.department = kwargs.get("department", "customer_service")
self.table = department_table[self.department]
self.position = kwargs.get("position", "售前")
self.kpi_index = kwargs.get(
"kpi_index",
kpi_index_default[self.department + "#" + self.position])
# 验证 Kpi
self.validation_data()
def __init__(self, data_sources="caibaoshuo"):
import configure
if data_sources=="caibaoshuo":
self._api_token = configure.echo(data_sources)["config"]["api"]
else:
self.sql = configure.echo(data_sources)["config"]
# -*- coding: utf-8 -*-
from jst.apis.base_services import BaseService
from jst.config import Config
import configure
file_name = "jst_private"
env = {
'sandbox': False, # 是否为沙盒环境
'partner_id': configure.echo(file_name)["config"]["partner_id"],
'partner_key': configure.echo(file_name)["config"]["partner_key"],
'token': configure.echo(file_name)["config"]["token"],
'taobao_appkey': configure.echo(file_name)["config"]["taobao_appkey"],
'taobao_secret': configure.echo(file_name)["config"]["taobao_secret"],
}
if __name__ == '__main__':
cfg = Config(**env) # 配置参数
service = BaseService(cfg, msg=True) # 运行服务
params = {
'shop_id': '10127848',
'modified_begin': '2018-04-18 00:00:00',
'modified_end': '2018-04-18 12:00:00',
'page_size': 10,
'page_index': 1,
# 'so_ids': ['111902591572215654']
}