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)
]
