def data_csv2db(self, symbol: str, exchange: Exchange, interval: Interval,
start_dt: datetime, end_dt: datetime):
csv_file_name = self.make_csvfile_name(symbol=symbol,
exchange=exchange,
interval=interval,
start_dt=start_dt,
end_dt=end_dt)
if not os.path.exists(csv_file_name):
print(csv_file_name + "不存在，错误")
return None
engine = CsvLoaderEngine(None, None)
engine.load_by_handle(
open(csv_file_name, "r"),
symbol=symbol,
exchange=exchange,
interval=interval,
datetime_head="datetime",
open_head=exchange.value + '.' + symbol + ".open",
close_head=exchange.value + '.' + symbol + ".close",
low_head=exchange.value + '.' + symbol + ".low",
high_head=exchange.value + '.' + symbol + ".high",
volume_head=exchange.value + '.' + symbol + ".volume",
datetime_format="%Y-%m-%d %H:%M:%S.000000000",
)
engine.close()
def setUp(self) -> None:
self.engine = CsvLoaderEngine(None, None) # no engine is necessary for CsvLoader
class TestCsvLoader(unittest.TestCase):
def setUp(self) -> None:
self.engine = CsvLoaderEngine(None, None) # no engine is necessary for CsvLoader
def test_load(self):
data = """"Datetime","Open","High","Low","Close","Volume"
2010-04-16 09:16:00,3450.0,3488.0,3450.0,3468.0,489
2010-04-16 09:17:00,3468.0,3473.8,3467.0,3467.0,302
2010-04-16 09:18:00,3467.0,3471.0,3466.0,3467.0,203
2010-04-16 09:19:00,3467.0,3468.2,3448.0,3448.0,280
2010-04-16 09:20:00,3448.0,3459.0,3448.0,3454.0,250
2010-04-16 09:21:00,3454.0,3456.8,3454.0,3456.8,109
"""
with tempfile.TemporaryFile("w+t") as f:
f.write(data)
f.seek(0)
self.engine.load_by_handle(
f,
symbol="1",
exchange=Exchange.BITMEX,
interval=Interval.MINUTE,
datetime_head="Datetime",
open_head="Open",
close_head="Close",
low_head="Low",
high_head="High",
volume_head="Volume",
datetime_format="%Y-%m-%d %H:%M:%S",
)
def test_load_duplicated(self):
data = """"Datetime","Open","High","Low","Close","Volume"
2010-04-16 09:16:00,3450.0,3488.0,3450.0,3468.0,489
2010-04-16 09:17:00,3468.0,3473.8,3467.0,3467.0,302
2010-04-16 09:18:00,3467.0,3471.0,3466.0,3467.0,203
2010-04-16 09:19:00,3467.0,3468.2,3448.0,3448.0,280
2010-04-16 09:20:00,3448.0,3459.0,3448.0,3454.0,250
2010-04-16 09:21:00,3454.0,3456.8,3454.0,3456.8,109
"""
with tempfile.TemporaryFile("w+t") as f:
f.write(data)
f.seek(0)
self.engine.load_by_handle(
f,
symbol="1",
exchange=Exchange.BITMEX,
interval=Interval.MINUTE,
datetime_head="Datetime",
open_head="Open",
close_head="Close",
low_head="Low",
high_head="High",
volume_head="Volume",
datetime_format="%Y-%m-%d %H:%M:%S",
)
with tempfile.TemporaryFile("w+t") as f:
f.write(data)
f.seek(0)
self.engine.load_by_handle(
f,
symbol="1",
exchange=Exchange.BITMEX,
interval=Interval.MINUTE,
datetime_head="Datetime",
open_head="Open",
close_head="Close",
low_head="Low",
high_head="High",
volume_head="Volume",
datetime_format="%Y-%m-%d %H:%M:%S",
)
# 下载从 2018-05-01凌晨0点 到 2018-06-01凌晨0点 的 T1809 盘口Tick数据
# download_tasks["T_tick"] = DataDownloader(api, symbol_list=["SHFE.cu1801"], dur_sec=0,
# start_dt=datetime(2017, 1, 1), end_dt=datetime(2018, 1, 1),
# csv_file_name="SHFE.cu1801_tick.csv")
# 使用with closing机制确保下载完成后释放对应的资源
with closing(api):
while not all([v.is_finished() for v in download_tasks.values()]):
api.wait_update()
print("progress: ", {
k: ("%.2f%%" % v.get_progress())
for k, v in download_tasks.items()
})
print("下载完成，开始写入数据库")
engine = CsvLoaderEngine(None, None)
engine.load_by_handle(
open(set_csv_file_name, "r"),
symbol=set_symbol,
exchange=Exchange.SHFE,
interval=Interval.MINUTE,
datetime_head="datetime",
open_head=set_exchange + '.' + set_symbol + ".open",
close_head=set_exchange + '.' + set_symbol + ".close",
low_head=set_exchange + '.' + set_symbol + ".low",
high_head=set_exchange + '.' + set_symbol + ".high",
volume_head=set_exchange + '.' + set_symbol + ".volume",
datetime_format="%Y-%m-%d %H:%M:%S.000000000",
)
engine.close()
print("写入数据库完成")
def __init__(self, class_task: Type[Model],
class_task_detail: Type[Model]):
self.class_task = class_task
self.class_task_detail = class_task_detail
self.tq_down = TQDownload()
self.engine = CsvLoaderEngine(None, None)
class TaskManager(ABC):
def __init__(self, class_task: Type[Model],
class_task_detail: Type[Model]):
self.class_task = class_task
self.class_task_detail = class_task_detail
self.tq_down = TQDownload()
self.engine = CsvLoaderEngine(None, None)
def create_task(self,
symbol: str,
exchange: str,
maintype: MainType = MainType.SINGLE,
interval: int = 60,
startdate: datetime = FUTURE_START,
enddate: datetime = datetime.now(),
dateauto: bool = False):
if dateauto:
if int(symbol[-4:-2]) < 70:
year = int('20' + symbol[-4:-2])
else:
year = int('19' + symbol[-4:-2])
month = int(symbol[-2:])
if month == 12:
enddate = datetime(year + 1, 1, 1)
else:
enddate = datetime(year, month + 1, 1)
if enddate > datetime.now():
enddate = datetime.now()
startdate = datetime(year - 1, month, 1)
self.class_task.insert(
maintype=maintype.value,
symbol=symbol,
exchange=exchange,
interval=interval,
startdate=startdate,
enddate=enddate,
status=TaskStatus.INIT.value,
).on_conflict_replace().execute()
def get_task(self) -> Sequence[DownloadTaskData]:
s = (self.class_task.select().where(
(self.class_task.status == TaskStatus.INIT.value)).order_by(
self.class_task.processdate))
data = [db_task.to_task() for db_task in s]
return data
def get_task_detail(self) -> Sequence[DownloadTaskDetailData]:
s = (self.class_task_detail.select().where(
(self.class_task_detail.status == TaskStatus.INIT.value)
| (self.class_task_detail.status == TaskStatus.SUSPEND.value)).
order_by(self.class_task_detail.detaildate))
data = [class_task_detail.to_task_detail() for class_task_detail in s]
return data
def update_task(self, maintype: MainType, symbol: str, exchange: Exchange,
interval: int, startdate: datetime, enddate: datetime,
status: TaskStatus):
self.class_task.update(
status=status.value, processdate=datetime.now()).where(
(self.class_task.maintype == maintype.value)
& (self.class_task.symbol == symbol)
& (self.class_task.exchange == exchange.value)
& (self.class_task.interval == interval)
& (self.class_task.startdate == startdate)
& (self.class_task.enddate == enddate)).execute()
def gen_task_detail(self, objs: Sequence["DownloadTaskData"]):
task_detail_list = []
for t in objs:
startdate = datetime(t.startdate.year, t.startdate.month,
t.startdate.day, 9)
enddate = datetime(t.enddate.year, t.enddate.month, t.enddate.day,
16)
daydate = startdate
while daydate < enddate:
if daydate.weekday() not in [5, 6]:
task_detail = DownloadTaskDetailData(
maintype=MainType(t.maintype),
symbol=t.symbol,
exchange=Exchange(t.exchange),
interval=t.interval,
detaildate=daydate,
status=TaskStatus.INIT,
processdate=datetime.now(),
breakpointdate=datetime.now(),
)
task_detail_list.append(task_detail)
daydate += timedelta(days=1)
self.update_task(t.maintype, t.symbol, t.exchange, t.interval,
t.startdate, t.enddate, TaskStatus.FINISHED)
ds = [
self.class_task_detail.from_task_detail(i)
for i in task_detail_list
]
self.class_task_detail.save_all(ds)
def update_task_detail(self, maintype: MainType, symbol: str,
exchange: Exchange, interval: int,
detaildate: datetime, status: TaskStatus):
self.class_task_detail.update(
status=status.value, processdate=datetime.now()).where(
(self.class_task_detail.maintype == maintype.value)
& (self.class_task_detail.symbol == symbol)
& (self.class_task_detail.exchange == exchange.value)
& (self.class_task_detail.interval == interval)
& (self.class_task_detail.detaildate == detaildate)).execute()
def make_csv_file(self, t: DownloadTaskDetailData):
path = FUTURE_PATH + t.maintype.value + "_" + t.symbol + "_" + t.exchange.value + "/" + str(
t.interval)
isExists = os.path.exists(path)
if not isExists:
os.makedirs(path)
task_name = t.maintype.value + '_' + t.symbol + '_' + t.exchange.value + '_' + str(
t.interval) + '_' + t.detaildate.strftime("%Y%m%d")
csv_file_name = path + '/' + task_name + ".csv"
return csv_file_name
def load_csv_to_db(self, t: DownloadTaskDetailData, csv_file_name: str):
interval = convert_interval(t.interval)
if t.maintype != MainType.SINGLE:
head = MainType.convert2str(
t.maintype) + t.exchange.value + '.' + t.symbol
symbol = t.symbol + t.maintype.value
else:
head = t.exchange.value + '.' + t.symbol
symbol = t.symbol
self.engine.load_by_handle(
open(csv_file_name, "r"),
symbol=symbol,
exchange=t.exchange,
interval=interval,
datetime_head="datetime",
open_head=head + ".open",
close_head=head + ".close",
low_head=head + ".low",
high_head=head + ".high",
volume_head=head + ".volume",
datetime_format="%Y-%m-%d %H:%M:%S.000000000",
)
def download_from_detail(self):
data = self.get_task_detail()
sum = len(data)
for n, t in enumerate(data):
time.sleep(1)
csv_file_name = self.make_csv_file(t)
self.tq_down.tq_download_task(t, csv_file_name)
self.load_csv_to_db(t, csv_file_name)
end_dt = datetime(t.detaildate.year, t.detaildate.month,
t.detaildate.day, 16, 0, 0)
if datetime.now() < end_dt:
self.update_task_detail(t.maintype, t.symbol, t.exchange,
t.interval, t.detaildate,
TaskStatus.SUSPEND)
else:
self.update_task_detail(t.maintype, t.symbol, t.exchange,
t.interval, t.detaildate,
TaskStatus.FINISHED)
print(f"完成 {n}/{sum}, 文件：{csv_file_name}")
def clean(self, symbol: str):
self.class_task.delete().where(
self.class_task.symbol == symbol).execute()
self.class_task_detail.delete().where(
self.class_task_detail.symbol == symbol).execute()
print(df)
i = i + 1
# check
for instrument in stockList:
if os.path.getsize("./stock_data/%s.csv" % instrument[0]) / 1024 < 100:
print(f'数据 {instrument[0]} 错误！文件大小太小，可能是空文件。')
lastModifiedTime = os.path.getmtime("./stock_data/%s.csv" % instrument[0])
currTime = time.time()
if currTime - lastModifiedTime > 40 * 60:
print(f'数据 {instrument[0]} 错误！今日文件未改变，请手动检查。')
# save data to database use csv loader
engine = CsvLoaderEngine(None, None)
with open('stock_pool.csv', 'r') as f:
reader = csv.reader(f)
stockList = list(reader)
for instrument in stockList:
f = ("./stock_data/%s.csv" % instrument[0])
if instrument[0][-2:] == 'SH':
temp_exchange = Exchange.SSE
elif instrument[0][-2:] == 'SZ':
temp_exchange = Exchange.SZSE
else:
print(f'股票{instrument[0]}的交易所填写错误，请手动终止程序，并检查股票池文件！')
os.system("pause")
engine.load(
from vnpy.app.csv_loader import CsvLoaderEngine
from vnpy.trader.constant import Exchange, Interval
import os
import csv
engine = CsvLoaderEngine(None, None)
engine.load_by_handle(
open("cu1802_min.csv", "r"),
symbol="cu1802",
exchange=Exchange.SHFE,
interval=Interval.MINUTE,
datetime_head="datetime",
open_head="SHFE.cu1802.open",
close_head="SHFE.cu1802.close",
low_head="SHFE.cu1802.low",
high_head="SHFE.cu1802.high",
volume_head="SHFE.cu1802.volume",
#datetime_format=None
datetime_format="%Y-%m-%d %H:%M:%S.000000000",
)
engine.close()