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'] }