def api_payrate_zhexian():
now_time, seven_days_time = get_now_and_7days_time()
if 'provinceid' in request.args and 'cityid' in request.args and "areaid" in request.args and "shopid" not in request.args:
sql=get_sql("sql","sql_payrate_zhexian1")\
.replace("get_provinced_id",request.args['provinceid'])\
.replace("get_city_id",request.args['cityid'])\
.replace("get_area_id",request.args['areaid'])\
.replace("seven_days_time","'"+seven_days_time+"'")\
.replace("now_time","'"+now_time+"'")
print (sql)
cur.execute(sql)
df = as_pandas(cur)
res = trans_array(df)
return res
if 'provinceid' in request.args and 'cityid' in request.args and "shopid" not in request.args:
sql = get_sql("sql", "sql_payrate_zhexian2") \
.replace("get_provinced_id", request.args['provinceid']) \
.replace("get_city_id", request.args['cityid']) \
.replace("seven_days_time", "'"+seven_days_time+"'") \
.replace("now_time", "'"+now_time+"'")
#print (sql)
cur.execute(sql)
df = as_pandas(cur)
res = trans_array(df)
return res
if 'provinceid' in request.args and "shopid" not in request.args:
sql = get_sql("sql", "sql_payrate_zhexian3") \
.replace("get_provinced_id", request.args['provinceid']) \
.replace("seven_days_time", "'"+seven_days_time+"'") \
.replace("now_time", "'"+now_time+"'")
#print (sql)
cur.execute(sql)
df = as_pandas(cur)
res = trans_array(df)
return res
if 'shopid' in request.args :
sql = get_sql("sql", "sql_payrate_zhexian4") \
.replace("shop__", request.args['shopid']) \
.replace("seven_days_time", "'" + seven_days_time + "'") \
.replace("now_time", "'" + now_time + "'")
#print (sql)
cur.execute(sql)
df = as_pandas(cur)
res = trans_array(df)
return res
else:
sql = get_sql("sql", "sql_payrate_zhexian5") \
.replace("seven_days_time", "'"+seven_days_time+"'") \
.replace("now_time", "'"+now_time+"'")
print (sql)
cur.execute(sql)
df = as_pandas(cur)
res = trans_array(df)
return res
def api_payrate_bing():
yes_time = get_yes_time()
if 'provinceid' in request.args and 'cityid' in request.args and "areaid" in request.args and "shopid" not in request.args:
sql=get_sql("sql","sql_payrate1")\
.replace("province_",request.args['provinceid'])\
.replace("city_",request.args['cityid'])\
.replace("area_",request.args['areaid']) \
.replace("yes_time", "'" + yes_time + "'")
#print (sql)
cur.execute(sql)
df = as_pandas(cur)
res = trans_array3(df)
return res
if 'provinceid' in request.args and 'cityid' in request.args and "shopid" not in request.args:
sql = get_sql("sql", "sql_payrate2") \
.replace("province_", request.args['provinceid']) \
.replace("city_", request.args['cityid']) \
.replace("yes_time", "'" + yes_time + "'")
#print (sql)
cur.execute(sql)
df = as_pandas(cur)
res = trans_array3(df)
return res
if 'provinceid' in request.args and "shopid" not in request.args:
sql = get_sql("sql", "sql_payrate3") \
.replace("province_", request.args['provinceid']) \
.replace("yes_time", "'" + yes_time + "'")
#print (sql)
cur.execute(sql)
df = as_pandas(cur)
res = trans_array3(df)
return res
if 'shopid' in request.args :
sql = get_sql("sql", "sql_payrate4") \
.replace("shop__", request.args['shopid']) \
.replace("yes_time", "'" + yes_time + "'")
#print (sql)
cur.execute(sql)
df = as_pandas(cur)
res = trans_array3(df)
return res
else:
sql = get_sql("sql", "sql_payrate5") \
.replace("yes_time", "'" + yes_time + "'")
#print (sql)
cur.execute(sql)
df = as_pandas(cur)
res = trans_array3(df)
return res
def main(env):
# Load configuration
config = utils.load_config(env)
mysql_config = config["mysql"]
# Print DB info
print(f"Environment : {env}")
print(f"MySQL host : {mysql_config['host']}:{mysql_config['port']}")
print(f"MySQL database : {mysql_config['db']}")
# Connect to database
db_connection = utils.connect(mysql_config)
# Get a cursor
db_cursor = db_connection.cursor()
# Run multiple statements
for statement in utils.get_sql("db_init.sql").split("==="):
if len(statement) > 0:
# print(statement)
db_cursor.execute(statement)
# List database tables
db_cursor.execute("SHOW TABLES")
for table in db_cursor:
print(table)
# Close the cursor
db_cursor.close()
# Close the connection to database
db_connection.close()
def api_shop():
yes_time = get_yes_time()
sql = get_sql("sql","sql_shop")\
.replace("long1",str(float(request.args['longitude1'])*1000000)) \
.replace("long2", str(float(request.args['longitude2'])*1000000))\
.replace("lati1",str(float(request.args['latitude1'])*1000000))\
.replace("lati2",str(float(request.args['latitude2'])*1000000))\
.replace("yes_time",yes_time)
print (sql)
cur.execute(sql)
df = as_pandas(cur)
print (len(df))
res=trans_array2(df)
return res
def get_area(cityid):
tmpsql = get_sql("sql","sql_area") + cityid
cur.execute(tmpsql)
df = as_pandas(cur)
return json.dumps(df.to_dict(orient='records'), ensure_ascii=False)
def get_city(provinceid):
tmpsql = get_sql("sql","sql_city")+provinceid
cur.execute(tmpsql)
df = as_pandas(cur)
return json.dumps(df.to_dict(orient='records'), ensure_ascii=False)
def get_province():
sql_pro = get_sql("sql", "sql_pro")
cur.execute(sql_pro)
df = as_pandas(cur)
return json.dumps(df.to_dict(orient='records'), ensure_ascii=False)
# копирование всех скриптов
client.put_all(config.BASE_DIR, '/tmp')
DIRNAME = os.path.split(config.BASE_DIR)[1]
client.run('. /u/dba/venv/python_dba/bin/activate; python /tmp/'
+ DIRNAME + '/cdb.py -s ' + args.sid + ' -v ' + str(args.ver)
+ ' -o ' + args.oradata + ' -n ' + args.nls_characterset + cdb + archive_mode + ' -l ' + args.logging)
client.close()
else:
# к абсолюдному пути еще добавляется sid. Путь согласно стандартизации
# https://bftman.ftc.ru:8443/pages/viewpage.action?pageId=30867462
oradata = os.path.join(args.oradata, args.sid)
oracle_version = 'o' + str(args.ver)
orautils.oraenv(oracle_version)
db1 = db.LocalDb(oracle_version)
db1.create_from_tpl(args.sid, oradata, nls=args.nls_characterset, archive_mode=args.archive_mode, cdb=args.cdb)
if args.type == 'cft':
log_adapter.info('create tablespaces for cft platform')
for sql in utils.get_sql(config.SQL_DIR, 'create_tablespaces.sql'):
db1.cur().ddl_execute(sql)
log_adapter.info('create user ibs, add grants to ibs')
cmd = '{}/bin/sqlplus -S / as sysdba @{}/init_ibs_schema.sql'.format(db1.oracle_home, config.SQL_DIR)
db1._run_cmd(cmd, stdoutDisable=True)
# change system options
db1.cur().ddl_execute('alter system set filesystemio_options=setall scope=spfile')
db1.shut_immediate()
db1.open()