def update_ip_access_unnormal_address(): ip_list = session.query(WEB_ANALYSIS_IP_ACCESS_UNNORMAL).all() for ip in ip_list: ip_json = get_ip_by_api(ip.access_ip) data = {"country": ip_json['country'], "city": ip_json['city']} session.query(WEB_ANALYSIS_IP_ACCESS_UNNORMAL).filter( WEB_ANALYSIS_IP_ACCESS_UNNORMAL.access_ip == ip.access_ip).update( data)
def hk_hold_into_db(runday): print(f"hk_hold_into_db=>{runday}") delete_table(runday) #check_date trade_date = session.query(SHARE_TRADE_DATE).filter_by( trade_date=int(runday)).first() if not trade_date: return "today no trade" try: df = ak.stock_em_hsgt_stock_statistics(symbol="北向持股", start_date=str(runday), end_date=str(runday)) except ValueError: return "today no trade" columns = { '持股日期': 'date_id', '股票代码': 'code', '股票简称': 'name', '当日收盘价': 'price', '当日涨跌幅': 'amplitude', '持股数量': 'numbers', '持股市值': 'market_value', '持股数量占发行股百分比': 'share_percent', '持股市值变化-1日': 'change_one_days', '持股市值变化-5日': 'change_five_days', '持股市值变化-10日': 'change_ten_days', } df.rename(columns=columns, inplace=True) df['date_id'] = df['date_id'].apply( lambda x: int(x[0:10].replace("-", ""))) df['price'] = df['price'].apply(lambda x: 0 if x == '-' else x) df['amplitude'] = df['amplitude'].apply(lambda x: 0 if x == '-' else x) df.to_sql("hk_hold", engine, if_exists="append", index=False) #print(df) return "success"
def delete(cls, session, id): tbl = cls.classes.replace_word target_record = session.query(tbl).filter_by(id=id).first() session.delete(target_record) try: session.commit() except InvalidRequestError as e: return target_record return target_record
def compare_with_strategy(analysis_date, data=None): analysis_dates = session.query(distinct(HK_HOLD.hddate)).filter( HK_HOLD.hddate >= analysis_date).order_by(HK_HOLD.hddate.asc()).all() analysis_dates = [ v[0] for k, v in enumerate(analysis_dates) if k in (0, 1, 3, 5, 7) ] query = session.query(HK_HOLD).filter( HK_HOLD.hddate.in_(analysis_dates)).order_by(HK_HOLD.hddate.asc()) df = pd.read_sql( query.statement, query.session.bind, ) for i, d in enumerate(analysis_dates): if i == 0: series_df = df[df.hddate == d].loc[:, ['hddate', 'scode', 'closeprice']] series_df.rename(columns={"closeprice": f"closeprice_{i}"}, inplace=True) if i > 0: tmp_df = df[df.hddate == d].loc[:, ['scode', 'closeprice']] tmp_df.rename(columns={"closeprice": f"closeprice_{i}"}, inplace=True) series_df = pd.merge(series_df, tmp_df, on='scode') df = pd.merge(data, series_df, on='scode') data = {} data['date'] = analysis_date print(df) data['percent_1'] = df[ (df.closeprice_1 - df.closeprice_0) / df.closeprice_0 > 0].scode.count() * 100 / df.scode.count() data['percent_2'] = df[ (df.closeprice_2 - df.closeprice_0) / df.closeprice_0 > 0].scode.count() * 100 / df.scode.count() data['percent_3'] = df[ (df.closeprice_3 - df.closeprice_0) / df.closeprice_0 > 0].scode.count() * 100 / df.scode.count() data['percent_4'] = df[ (df.closeprice_4 - df.closeprice_0) / df.closeprice_0 > 0].scode.count() * 100 / df.scode.count() return pd.DataFrame([data])
def analysis(analysis_date, analysis_days=10): analysis_dates = session.query(distinct( HK_HOLD.hddate)).filter(HK_HOLD.hddate <= analysis_date).order_by( HK_HOLD.hddate.desc()).all()[0:analysis_days] analysis_dates = [i[0] for i in analysis_dates] query = session.query(HK_HOLD).filter( HK_HOLD.hddate.in_(analysis_dates)).order_by(HK_HOLD.hddate.asc()) df = pd.read_sql( query.statement, query.session.bind, ) for i, d in enumerate(analysis_dates): if i == 0: series_df = df[df.hddate == d].loc[:, ['hddate', 'scode', 'sharesrate']] series_df.rename(columns={"sharesrate": f"sharesrate_{i}"}, inplace=True) if i > 0: tmp_df = df[df.hddate == d].loc[:, ['scode', 'sharesrate']] tmp_df.rename(columns={"sharesrate": f"sharesrate_{i}"}, inplace=True) series_df = pd.merge(series_df, tmp_df, on='scode') # 1.连续3日加仓 数量较多 constant_up = series_df[ (series_df.sharesrate_0 > series_df.sharesrate_1) & (series_df.sharesrate_1 > series_df.sharesrate_2) & ((series_df.sharesrate_0 - series_df.sharesrate_2) > 1)] # 2.3日均线>5日均线>10日均线 if constant_up.empty: return constant_up, constant_up series_df["daysavg10"] = series_df.apply(lambda row: get_row_avg(row, 10), axis=1) series_df["daysavg5"] = series_df.apply(lambda row: get_row_avg(row, 5), axis=1) series_df["daysavg3"] = series_df.apply(lambda row: get_row_avg(row, 3), axis=1) stable_up = series_df[(series_df.daysavg3 > series_df.daysavg5) & (series_df.daysavg5 > series_df.daysavg10)] return constant_up, stable_up
def backend_info_analysis(): conf = SparkConf() spark = SparkSession.builder.config(conf=conf).master("local").config( "hive.metastore.uris", "thrift://baidu:9083").config( "hive.exec.dynamic.partition.mode", "nonstrict").enableHiveSupport().getOrCreate() spark.sparkContext.setLogLevel("ERROR") #truncate mysql by sqlalchemy session.query(WEB_ANALYSIS_API_ACCESS_NORMAL).delete() session.query(WEB_ANALYSIS_API_ACCESS_UNNORMAL).delete() session.query(WEB_ANALYSIS_IP_ACCESS_UNNORMAL).delete() log = """ select log_level,log_date,log_time,access_ip,access_type,access_api,access_code,access_time from web.backend_info """ LOG = spark.sql(log) LOG.persist() #jdbc mysql info data = { "user": "******", "password": "******", "driver": "com.mysql.cj.jdbc.Driver", } #正常接口访问排名 DF = LOG.filter("access_code = '200'") DF = DF.groupBy('access_api').count().orderBy(desc('count')).limit(15) DF.repartition(1).write.jdbc("jdbc:mysql://tencent:3306/DWDB", "web_analysis_api_access_normal", "append", data) #异常接口访问排名 DF = LOG.filter("access_code != '200'") DF = DF.groupBy('access_api').count().orderBy(desc('count')) DF.repartition(1).write.jdbc("jdbc:mysql://tencent:3306/DWDB", "web_analysis_api_access_unnormal", "append", data) #异常IP访问排名 DF = LOG.filter("access_code != '200'") DF = DF.groupBy('access_ip').count().orderBy(desc('count')).limit(20) DF = DF.withColumn("country", lit("unkown")) DF = DF.withColumn("city", lit("unkown")) DF.repartition(1).write.jdbc("jdbc:mysql://tencent:3306/DWDB", "web_analysis_ip_access_unnormal", "append", data) LOG.unpersist() return True
def get(id: UUID4) -> Union[None, NotesModel]: note = session.query(NotesModel).filter_by(id=id).first() return note
def get_all() -> List[NotesModel]: all_notes = session.query(NotesModel).all() return all_notes
def delete_table(): session.query(SHARE_TRADE_DATE).delete()
def all(cls, session): """ fetch all records """ tbl = cls.classes.replace_word return session.query(tbl).all()
def delete_table(runday): session.query(HK_HOLD).filter_by(date_id=int(runday)).delete()
#!/usr/bin/env python import datetime import os import sys from db.connection import engine from db.model import Site, Item, Component from db.session import session if len(sys.argv) < 2: sys.exit('Usage: %s repository-name' % sys.argv[0]) repository = sys.argv[1] site = session.query(Site).filter_by(name=repository).first() if site is None: sys.exit('repository not exists.') now = datetime.datetime.now().strftime("%Y%m%d") output = os.path.join(os.getcwd(), "data", "seeds", "{}-{}.seeds").format(now, repository) if len(site.items) != 0: print output out_file = open(output, "wb") for item in site.items: for component in item.components: out_file.write(component.url + "\n") item.status = "seeded"
from db.database import (Course, Department, Employee, Student, Grade, Online, Onsite, Instructor) from db.session import session from datetime import datetime, date def separator(): print('-' * 30) print() if __name__ == '__main__': print('!!!!! Display all students data (repr) !!!!!!') for student in session.query(Student): print(student) separator() print( '!!!!! Display courses data: id, name and sort by price from he most expensive !!!!!' ) for curse in session.query(Course.id, Course.name, Course.price).order_by(Course.price.desc()): print(curse) separator() print('!!!!! Display departments with budget higher than 500 000 !!!!!') [ print(dep) for dep in session.query(Department).filter(Department.budget > 500000) ]