def season_records():
# check today to appoint duration
yesterday = datetime.date.today() - datetime.timedelta(1)
year = yesterday.strftime("%Y")
# set moongosdb and postgres connection
client = mon.mongo_connection('linode1', 'mongo')
conn_pos = pos.postgres_connection('linode1', 'postgres', 'stock')
cursor_pos = pos.make_cursor(connection=conn_pos)
# stock id
for item in allStockID.all_stock_id():
stock_id = item['_id']
for item in allStockID.all_stock_id():
stock_id = item['_id']
# create table if not exists with table name like month+stock_id
sql = f"""
create table if not exists year{stock_id} (
ID varchar(8) primary key,
duration char(4),
sum_volumn decimal,
avg_price decimal,
avg_open decimal,
avg_high decimal,
avg_low decimal,
avg_close decimal,
avg_change decimal,
sum_trade decimal
);
"""
pos.createTable(connection=conn_pos, cursor=cursor_pos, sql=sql)
# iterately insert data into table
for year in range(2010, 2021):
dur_records = duration_records.get_year_record_mongo(
stock_id, year)
if dur_records:
docs = compute_records.compute_records(dur_records)
# 將計算好的結果存入postgres
print(f"{stock_id}: {year}")
print(docs)
query = f"""
INSERT INTO year{stock_id}
(ID, duration, sum_volumn, sum_trade, avg_price,avg_open, avg_high, avg_low, avg_close, avg_change)
VALUES
('{stock_id+year}', '{year}', {docs['sumVolume']}, {docs['sumTrades']},
{docs['avgPrice']}, {docs['avgOpen']}, {docs['avgHigh']},{docs['avgLow']}, {docs['avgClose']}, {docs['avgChange']})
"""
if not conn_pos:
conn_pos = pos.postgres_connection('linode1', 'postgres',
'stock')
cursor_pos = pos.make_cursor(connection=conn_pos)
pos.insertTable(connection=conn_pos,
cursor=cursor_pos,
query=query,
exceptionfile='year')
pos.close_connection(connection=conn_pos)
mon.close_connection(client=client)
def season_records():
# check today to appoint duration
yesterday = datetime.date.today() - datetime.timedelta(1)
year = yesterday.strftime("%Y")
month = yesterday.strftime("%m")
season_dict = {'3': '01', '6': '02', '9': '03', '12': '04'}
season = season_dict[month]
# set moongosdb and postgres connection
client = mon.mongo_connection('linode1', 'mongo')
conn_pos = pos.postgres_connection('linode1', 'postgres', 'stock')
cursor_pos = pos.make_cursor(connection=conn_pos)
# stock id
for item in allStockID.all_stock_id():
stock_id = item['_id']
# create table if not exists with table name like month+stock_id
sql = f"""
create table if not exists season{stock_id} (
ID varchar(10) primary key,
duration char(6),
sum_volumn decimal,
avg_price decimal,
avg_open decimal,
avg_high decimal,
avg_low decimal,
avg_close decimal,
avg_change decimal,
sum_trade decimal
);
"""
pos.createTable(connection=conn_pos, cursor=cursor_pos, sql=sql)
dur_records = duration_records.get_season_record_mongo(
stock_id, year, season)
if dur_records:
# print('dur', dur_records)
docs = compute_records.compute_records(dur_records)
# 將計算好的結果存入postgres
print(f"{stock_id}: {year}, season: {season}")
print(docs)
query = f"""
INSERT INTO season{stock_id}
(ID, duration, sum_volumn, sum_trade, avg_price, avg_open, avg_high, avg_low, avg_close, avg_change)
VALUES
('{stock_id+year+season}', '{year+season.zfill(2)}', {docs['sumVolume']}, {docs['sumTrades']},
{docs['avgPrice']}, {docs['avgOpen']}, {docs['avgHigh']},{docs['avgLow']}, {docs['avgClose']}, {docs['avgChange']})
"""
if not conn_pos:
conn_pos = pos.postgres_connection('linode1', 'postgres',
'stock')
cursor_pos = pos.make_cursor(connection=conn_pos)
pos.insertTable(connection=conn_pos,
cursor=cursor_pos,
query=query,
exceptionfile='season')
pos.close_connection(connection=conn_pos)
mon.close_connection(client=client)
def month_records():
# set moongosdb and postgres connection
client = mon.mongo_connection('linode1', 'mongo')
conn_pos = pos.postgres_connection('linode1', 'postgres', 'stock')
cursor_pos = pos.make_cursor(connection=conn_pos)
# stock id
for item in allStockID.all_stock_id():
stock_id = item['_id']
# create table if not exists with table name like month+stock_id
sql = f"""
create table if not exists month{stock_id} (
ID varchar(10) primary key,
duration char(6),
sum_volumn decimal,
avg_price decimal,
avg_open decimal,
avg_high decimal,
avg_low decimal,
avg_close decimal,
avg_change decimal,
sum_trade decimal
);
"""
pos.createTable(connection=conn_pos, cursor=cursor_pos, sql=sql)
# iterately insert data into table
for year in range(2010, 2021):
for month in range(1, 13):
dur_records = duration_records.get_month_record_mongo(
stock_id, year, month)
if dur_records:
docs = compute_records.compute_records(dur_records)
# 將計算好的結果存入postgres
print(f"{stock_id}: {str(year)+str(month).zfill(2)}")
print(docs)
query = f"""
INSERT INTO month{stock_id}
(ID, duration, sum_volumn, sum_trade, avg_price, avg_open, avg_high, avg_low, avg_close, avg_change)
VALUES
('{stock_id+str(year)+str(month).zfill(2)}', '{str(year)+str(month).zfill(2)}', {docs['sumVolume']}, {docs['sumTrades']},
{docs['avgPrice']}, {docs['avgOpen']}, {docs['avgHigh']},{docs['avgLow']}, {docs['avgClose']}, {docs['avgChange']})
"""
if not conn_pos:
conn_pos = pos.postgres_connection(
'linode1', 'postgres', 'stock')
cursor_pos = pos.make_cursor(connection=conn_pos)
pos.insertTable(connection=conn_pos,
cursor=cursor_pos,
query=query,
exceptionfile='oldmonth')
pos.close_connection(connection=conn_pos)
mon.close_connection(client=client)
def check_records_exist():
client = mon.mongo_connection('linode1', 'mongo')
for content in allStockID.all_stock_id():
stock_id = content['_id']
# print(stock_id)
coll_stock = mon.mongo_collection(client, 'stocks', f"stock{stock_id}")
# stocks_con = list(coll_stock.find(
# {"trade_date": {"$regex": "2020"}}, {"trade_date": 1}))
records_count = coll_stock.find({
"trade_date": {
"$regex": "202102"
}
}, {
"trade_date": 1
}).count()
if records_count < 5:
print(stock_id, records_count)
wcsv.writeToCsv("double_check_stock", [stock_id])
def crawler_daily():
counts = 0
# notify daily updation starts
goo.main('stock_crawler', 'Stocks Daily Updation Starts!')
# start time
t1 = datetime.datetime.now()
# set daily status zero for default
client = mon.mongo_connection('linode1', 'mongo')
coll_stockInfo = mon.mongo_collection(client, 'stocks', 'stockInfo')
coll_stockInfo.update_many({}, {'$set': {'dailyStatus': 0}})
# today
today = datetime.date.today() #-datetime.timedelta(1)
year = today.strftime("%Y")
month = today.strftime("%m")
day = today.strftime("%d")
# get all stocks' id
for content in allStockID.all_stock_id():
stock_id = content['_id']
print(stock_id)
retry = 0
url = f"""https://www.twse.com.tw/exchangeReport/STOCK_DAY?response=html&date={year}{month}01&stockNo={stock_id}"""
coll_stock = mon.mongo_collection(client, 'stocks', f"stock{stock_id}")
while retry < 3:
try:
contents = crawler.crawler(url)
# print(contents)
for item in contents:
# daily record to mongo
mon.insert_document(coll_stock, item)
# crawlering and writing to mongo done, set daily status as datetime
coll_stockInfo.update_one(
{'_id': stock_id},
{'$set': {
'dailyStatus': f"{year+month+day}"
}})
counts += 1
time.sleep(10)
break
except Exception as e:
print(e)
time.sleep(10)
retry += 1
if retry == 3:
# sent notify with googlebot
goo.main('stock_crawler',
f"{stock_id}, {year,month,day} Wrong: {e}")
wcsv.writeToCsv(
f'./dataStore/DailyCrawlerException_{today}',
[stock_id, year, month, day])
continue
# check daily update done
if coll_stockInfo.find({
'dailyStatus': {
'$ne': f"{year+month+day}"
}
}, {
'_id': 1
}).count() != 0:
crawler_daily()
# notify daily updation done
cost_time = datetime.datetime.now() - t1
goo.main(
'stock_crawler',
f"{datetime.date.today()}: Daily Updation Finished!\nCheck amount of stock: {counts}, except: {938-counts}\nCost_time: {cost_time}"
)
return
