def fetch_stock_info_to_csv_db():
import csv
data = []
twse_file = "../data_fetch/twse_info_list.csv"
with open(twse_file, 'w+') as f:
csv_reader = csv.reader(f)
for i, row in enumerate(csv_reader, 1):
if i == 1:
continue
data.append([row[0], row[1], row[2], row[3], '1', '0'])
# print (row[0], row[1], row[2], row[3])
otc_file = "../data_fetch/otc_info_list.csv"
with open(otc_file, 'w+') as f:
csv_reader = csv.reader(f)
for i, row in enumerate(csv_reader, 1):
if i == 1:
continue
data.append([row[0], row[1], row[2], row[3], '0', '1'])
# print (row[0], row[1], row[2], row[3])
print("stock update finished...")
from database import mysql_manager
manager = mysql_manager.MysqlManager()
manager.insert_stock_info(data)
def fetch_all_warrant_to_db(twse=False, otc=False):
from database import mysql_manager
from data_fetch.warrant_fetch import twse_warrant_fetch
from data_fetch.warrant_fetch import otc_warrant_fetch
mysql_manager = mysql_manager.MysqlManager()
start_dt = datetime.datetime.now()
# otc自民國96年7月起開始提供
# twse自民國93年2月11日起提供
# 從民國97年1月1號開始收集 = 西元2008/01/01
start_date = datetime.date(2008, 1, 1)
end_date = datetime.date.today()
day = datetime.timedelta(days=1)
while start_date <= end_date:
date = start_date.strftime('%Y/%m/%d')
data = None
# 擷取當日otc全部權證資料
if twse:
print("twse warrant , date:{} start loading".format(date))
data = twse_warrant_fetch(date)
elif otc:
print("otc warrant , date:{} start loading".format(date))
data = otc_warrant_fetch(date)
if data is not None:
# 匯入至資料庫
mysql_manager.insert_warrant(date, data)
start_date = start_date + day
end_dt = datetime.datetime.now()
dif = end_dt - start_dt
print("started at ", start_dt)
print("finished at ", end_dt)
print("all time = ", dif)
# -*- coding: utf-8 -*-
"""
Created on 2018/3/15 13:29
@author: demonickrace
# 第一版
"""
import csv
import time
import os
from os import listdir
from database import mysql_manager
manager = mysql_manager.MysqlManager()
from data_fetch import stock
from datetime import datetime
from data_fetch import log
log = log.Log()
wait_seconds = 5
report_dir = os.path.dirname(os.path.realpath(__file__)) + "/" + "report"
target_date = "2018-03-31"
pass_old_data = False
def get_price(stock_no, date):
sql_query = "SELECT stock.close FROM stock.stock where stock_no = '{}' and date = '{}';".format(
def fetch_all_warrant_info_to_db():
from database import mysql_manager
from data_fetch import warrant_fetch
manager = mysql_manager.MysqlManager()
# 從第幾個開始
twse_target_no = 0
otc_target_no = 0
# 每50筆在匯入資料庫
count = 50
n = count
twse_data = []
# 取得全部上市權證代號
all_twse_warrant_no = manager.select_all_warrant_no(twse=True)
for i, no in enumerate(all_twse_warrant_no, 1):
if i >= twse_target_no:
data = warrant_fetch.warrant_info_fetch(no[0], twse=True)
print(data)
if data is None:
print("twse warrant_no:{} loaded fail, {}/{}...".format(
no[0], i, len(all_twse_warrant_no)))
else:
twse_data.append(data)
print("twse warrant_no:{} was load, {}/{}...".format(
no[0], i, len(all_twse_warrant_no)))
if i >= n:
manager.insert_warrant_info(twse_data)
twse_data = []
n += count
print("\n")
manager.insert_warrant_info(twse_data)
print("all twse warrant info was loaded, {} rows was inserted".format(
len(all_twse_warrant_no)))
n = count
otc_data = []
# 取得全部上櫃權證代號
all_otc_warrant_no = manager.select_all_warrant_no(otc=True)
for i, no in enumerate(all_otc_warrant_no, 1):
if i >= otc_target_no:
data = warrant_fetch.warrant_info_fetch(no[0], otc=True)
print(data)
if data is None:
print("otc warrant_no:{} loaded fail, {}/{}...".format(
no[0], i, len(all_otc_warrant_no)))
else:
otc_data.append(data)
print("otc warrant_no:{} was load, {}/{}...".format(
no[0], i, len(all_otc_warrant_no)))
if i >= n:
manager.insert_warrant_info(otc_data)
otc_data = []
n += count
print("\n")
manager.insert_warrant_info(otc_data)
print("all otc warrant info was loaded, {} rows was inserted".format(
len(all_otc_warrant_no)))