def testFactor():
current_date = "2020-07-22"
sql_cmd = "SELECT * FROM stock_adjustfactor where date>='" + current_date + "'"
db = create_engine(common.db_path_sqlalchemy)
factor = pd.read_sql(sql=sql_cmd, con=db)
print(factor)
for i in range(factor.shape[0]):
ticker = factor.loc[i, 'code']
print(ticker)
sql_cmd = "SELECT * FROM stock_day_k_noadjust where code='" + ticker + "' order by date desc limit 0,300"
daily = pd.read_sql(sql=sql_cmd, con=db)
print(daily.head(50))
sql_cmd = "SELECT * FROM stock_adjustfactor where code='" + ticker + "' order by date desc"
adjustfactor = pd.read_sql(sql=sql_cmd, con=db)
print(adjustfactor)
common.calculateDayKWithAdjustFactor(daily, adjustfactor)
print(daily.head(50))
break
def refresh_all_stock_day_k_first_time(start_date="2020-03-27",
end_date="2020-05-08",
current_date="2020-05-08"):
bs.login()
stock_rs = bs.query_all_stock(day=current_date)
stock_df = stock_rs.get_data()
db = create_engine(common.db_path_sqlalchemy)
number = 0
data_list = []
for ticker in stock_df["code"]:
print("adjust " + ticker)
sql_cmd = "SELECT * FROM stock_day_k_noadjust where code='" + ticker + "' order by date desc limit 0,300"
daily = pd.read_sql(sql=sql_cmd, con=db)
if ticker.startswith("sh.6") | ticker.startswith(
"sz.00") | ticker.startswith("sz.300"):
sql_cmd = "SELECT * FROM stock_adjustfactor where code='" + ticker + "' order by date desc"
factor = pd.read_sql(sql=sql_cmd, con=db)
common.calculateDayKWithAdjustFactor(daily, factor)
data = daily.values.tolist()
for d in data:
data_list.append(d)
number += 1
if number == 100:
if len(data_list) > 0:
db.execute(
r'''
INSERT OR REPLACE INTO stock_day_k VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
''', data_list)
data_list = []
number = 0
if number != 100:
if len(data_list) > 0:
db.execute(
r'''
INSERT OR REPLACE INTO stock_day_k VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
''', data_list)
data_list = []
number = 0
bs.logout()
def refresh_all_stock_day_k(start_date="2020-03-27",
current_date="2020-03-30"):
bs.login()
stock_rs = bs.query_all_stock(day=current_date)
stock_df = stock_rs.get_data()
data_list = []
for code in stock_df["code"]:
k_rs = bs.query_history_k_data_plus(
code,
"date,code,open,high,low,close,preclose,volume,amount,adjustflag,turn,tradestatus,pctChg,peTTM,pbMRQ,psTTM,pcfNcfTTM,isST",
start_date=start_date,
end_date='',
frequency="d",
adjustflag="2")
while (k_rs.error_code == '0') & k_rs.next():
data_list.append(k_rs.get_row_data())
print('query_history_k_data_fore code:' + code)
bs.logout()
db_conn = create_engine(common.db_path_sqlalchemy)
db_conn.execute(
r'''
INSERT OR REPLACE INTO stock_day_k VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
''', data_list)
sql_cmd = "SELECT * FROM stock_adjustfactor where date>='" + start_date + "'"
factor = pd.read_sql(sql=sql_cmd, con=db_conn)
for i in range(factor.shape[0]):
ticker = factor.loc[i, 'code']
print("adjust the dayK fore" + ticker)
sql_cmd = "SELECT * FROM stock_day_k_noadjust where code='" + ticker + "' order by date desc limit 0,300"
daily = pd.read_sql(sql=sql_cmd, con=db_conn)
sql_cmd = "SELECT * FROM stock_adjustfactor where code='" + ticker + "' order by date desc"
adjustfactor = pd.read_sql(sql=sql_cmd, con=db_conn)
common.calculateDayKWithAdjustFactor(daily, adjustfactor)
data = daily.values.tolist()
sql_cmd = "DELETE from stock_day_k where code='" + ticker + "'"
db_conn.execute(sql_cmd)
db_conn.execute(
r'''
INSERT OR REPLACE INTO stock_day_k VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
''', data)