def insert_table(base_time, instrument, con, table_type, count):
if decideMarket(base_time):
sql = "select insert_time from %s_%s_TABLE where insert_time = \'%s\'" % (instrument, table_type, base_time)
response = con.select_sql(sql)
if len(response) == 0:
if table_type == "1m":
granularity = "M1"
elif table_type == "5m":
granularity = "M5"
elif table_type == "1h":
granularity = "H1"
elif table_type == "3h":
granularity = "H3"
elif table_type == "8h":
granularity = "H8"
elif table_type == "day":
granularity = "D"
start_time = (base_time - timedelta(hours=14)).strftime("%Y-%m-%dT%H:%M:%S")
params = {
"from": start_time,
"granularity": granularity,
"price": "ABM",
"count": count
}
req = instruments.InstrumentsCandles(instrument=instrument, params=params)
client.request(req)
response = req.response
if len(response) == 0:
pass
else:
for candle in response["candles"]:
open_ask_price = candle["ask"]["o"]
open_bid_price = candle["bid"]["o"]
close_ask_price = candle["ask"]["c"]
close_bid_price = candle["bid"]["c"]
high_ask_price = candle["ask"]["h"]
high_bid_price = candle["bid"]["h"]
low_ask_price = candle["ask"]["l"]
low_bid_price = candle["bid"]["l"]
insert_time = candle["time"]
insert_time = insert_time.split(".")[0]
insert_time = insert_time + ".000000Z"
#print(insert_time)
insert_time = iso_jp(insert_time)
insert_time = insert_time.strftime("%Y-%m-%d %H:%M:%S")
sql = "select insert_time from %s_%s_TABLE where insert_time = \'%s\'" % (instrument, table_type, insert_time)
response = con.select_sql(sql)
if len(response) == 0:
sql = "insert into %s_%s_TABLE(open_ask, open_bid, close_ask, close_bid, high_ask, high_bid, low_ask, low_bid, insert_time) values(%s, %s, %s, %s, %s, %s, %s, %s, \'%s\')" % (instrument, table_type, open_ask_price, open_bid_price, close_ask_price, close_bid_price, high_ask_price, high_bid_price, low_ask_price, low_bid_price, insert_time)
print(sql)
try:
con.insert_sql(sql)
except Exception as e:
print(traceback.format_exc())
def decide_term(base_time, currency, con):
count = 1
granularity = "D"
start_time = base_time.strftime("%Y-%m-%d %H:%M:%S")
start_time = jp_utc(start_time)
start_time = start_time.strftime("%Y-%m-%dT%H:%M:%S")
response = oanda.get_history(instrument=currency,
start=start_time,
granularity=granularity,
count=count)
today = response["candles"][0]["time"]
today = iso_jp(today)
today = today.strftime("%Y-%m-%d %H:%M:%S")
today = datetime.strptime(today, "%Y-%m-%d %H:%M:%S")
term = None
if today.hour == 6:
term = "summer"
elif today.hour == 7:
term = "winter"
else:
raise
return term
def insert_table(base_time, currency, con, table_type):
count = 5000
if table_type == "1m":
granularity = "M1"
elif table_type == "5m":
granularity = "M5"
elif table_type == "1h":
granularity = "H1"
elif table_type == "3h":
granularity = "H3"
elif table_type == "8h":
granularity = "H8"
elif table_type == "day":
granularity = "D"
start_time = base_time.strftime("%Y-%m-%d %H:%M:%S")
start_time = jp_utc(start_time)
start_time = start_time.strftime("%Y-%m-%dT%H:%M:%S")
response = oanda.get_history(instrument=currency,
start=start_time,
granularity=granularity,
count=count)
for candle in response["candles"]:
open_ask_price = candle["openAsk"]
open_bid_price = candle["openBid"]
close_ask_price = candle["closeAsk"]
close_bid_price = candle["closeBid"]
high_ask_price = candle["highAsk"]
high_bid_price = candle["highBid"]
low_ask_price = candle["lowAsk"]
low_bid_price = candle["lowBid"]
insert_time = candle["time"]
insert_time = iso_jp(insert_time)
insert_time = insert_time.strftime("%Y-%m-%d %H:%M:%S")
sql = "insert into %s_%s_TABLE(open_ask, open_bid, close_ask, close_bid, high_ask, high_bid, low_ask, low_bid, insert_time) values(%s, %s, %s, %s, %s, %s, %s, %s, \'%s\')" % (
currency, table_type, open_ask_price, open_bid_price,
close_ask_price, close_bid_price, high_ask_price, high_bid_price,
low_ask_price, low_bid_price, insert_time)
print(sql)
try:
con.insert_sql(sql)
except Exception as e:
print(e.args)
return insert_time
def return_sql(response):
for candle in response["candles"]:
open_ask_price = candle["openAsk"]
open_bid_price = candle["openBid"]
close_ask_price = candle["closeAsk"]
close_bid_price = candle["closeBid"]
high_ask_price = candle["highAsk"]
high_bid_price = candle["highBid"]
low_ask_price = candle["lowAsk"]
low_bid_price = candle["lowBid"]
insert_time = candle["time"]
print(insert_time)
insert_time = iso_jp(insert_time)
insert_time = insert_time.strftime("%Y-%m-%d %H:%M:%S")
sql = "insert into %s_%s_TABLE(open_ask, open_bid, close_ask, close_bid, high_ask, high_bid, low_ask, low_bid, insert_time) values(%s, %s, %s, %s, %s, %s, %s, %s, \'%s\')" % (
instrument, table_type, open_ask_price, open_bid_price,
close_ask_price, close_bid_price, high_ask_price, high_bid_price,
low_ask_price, low_bid_price, insert_time)
return sql
def decide_term(base_time, instrument, con):
count = 1
granularity = "D"
#start_time = base_time.strftime("%Y-%m-%d %H:%M:%S")
#start_time = jp_utc(start_time)
#start_time = start_time.strftime("%Y-%m-%dT%H:%M:%S")
start_time = (base_time -
timedelta(hours=14)).strftime("%Y-%m-%dT%H:%M:%S")
params = {
"from": start_time,
"granularity": granularity,
"price": "ABM",
"count": count
}
req = instruments.InstrumentsCandles(instrument=instrument, params=params)
client.request(req)
response = req.response
today = response["candles"][0]["time"]
today = today.split(".")[0]
today = today + ".000000Z"
#print(response)
today = iso_jp(today)
today = today.strftime("%Y-%m-%d %H:%M:%S")
#print(today)
today = datetime.strptime(today, "%Y-%m-%d %H:%M:%S")
term = None
if today.hour == 6:
term = "summer"
elif today.hour == 7:
term = "winter"
else:
raise
return term
def return_sqlv20(response):
for candle in response["candles"]:
open_ask_price = candle["ask"]["o"]
open_bid_price = candle["bid"]["o"]
close_ask_price = candle["ask"]["c"]
close_bid_price = candle["bid"]["c"]
high_ask_price = candle["ask"]["h"]
high_bid_price = candle["bid"]["h"]
low_ask_price = candle["ask"]["l"]
low_bid_price = candle["bid"]["l"]
insert_time = candle["time"]
insert_time = insert_time.split(".")[0]
insert_time = insert_time + ".000000Z"
print(insert_time)
insert_time = iso_jp(insert_time)
insert_time = insert_time.strftime("%Y-%m-%d %H:%M:%S")
sql = "insert into %s_%s_TABLE(open_ask, open_bid, close_ask, close_bid, high_ask, high_bid, low_ask, low_bid, insert_time) values(%s, %s, %s, %s, %s, %s, %s, %s, \'%s\')" % (
instrument, table_type, open_ask_price, open_bid_price,
close_ask_price, close_bid_price, high_ask_price, high_bid_price,
low_ask_price, low_bid_price, insert_time)
return sql
def insert_table(base_time, instrument, con, table_type, count):
debug_logger.info("%s: Start insert_table logic" % (base_time))
if table_type == "5s":
granularity = "S5"
elif table_type == "1m":
granularity = "M1"
elif table_type == "5m":
granularity = "M5"
elif table_type == "15m":
granularity = "M15"
elif table_type == "30m":
granularity = "M30"
elif table_type == "1h":
granularity = "H1"
elif table_type == "3h":
granularity = "H3"
elif table_type == "8h":
granularity = "H8"
elif table_type == "day":
granularity = "D"
start_time = (base_time -
timedelta(hours=14)).strftime("%Y-%m-%dT%H:%M:%S")
params = {
"from": start_time,
"granularity": granularity,
"price": "ABM",
"count": count
}
req = instruments.InstrumentsCandles(instrument=instrument, params=params)
client.request(req)
response = req.response
time.sleep(1)
print("###################")
if len(response) == 0:
pass
else:
#print(len(response["candles"]))
for candle in response["candles"]:
open_ask_price = candle["ask"]["o"]
open_bid_price = candle["bid"]["o"]
close_ask_price = candle["ask"]["c"]
close_bid_price = candle["bid"]["c"]
high_ask_price = candle["ask"]["h"]
high_bid_price = candle["bid"]["h"]
low_ask_price = candle["ask"]["l"]
low_bid_price = candle["bid"]["l"]
insert_time = candle["time"]
insert_time = insert_time.split(".")[0]
insert_time = insert_time + ".000000Z"
##print(insert_time)
insert_time = iso_jp(insert_time)
insert_time = insert_time.strftime("%Y-%m-%d %H:%M:%S")
print(insert_time)
sql = "select insert_time from %s_%s_TABLE where insert_time = \'%s\'" % (
instrument, table_type, insert_time)
#print(sql)
response = con.select_sql(sql)
if len(response) == 0:
sql = "insert into %s_%s_TABLE(open_ask, open_bid, close_ask, close_bid, high_ask, high_bid, low_ask, low_bid, insert_time) values(%s, %s, %s, %s, %s, %s, %s, %s, \'%s\')" % (
instrument, table_type, open_ask_price, open_bid_price,
close_ask_price, close_bid_price, high_ask_price,
high_bid_price, low_ask_price, low_bid_price, insert_time)
else:
sql = "update %s_%s_TABLE set open_ask=%s, open_bid=%s, close_ask=%s, close_bid=%s, high_ask=%s, high_bid=%s, low_ask=%s, low_bid=%s, insert_time=\'%s\' where insert_time=\'%s\'" % (
instrument, table_type, open_ask_price, open_bid_price,
close_ask_price, close_bid_price, high_ask_price,
high_bid_price, low_ask_price, low_bid_price, insert_time,
insert_time)
try:
con.insert_sql(sql)
except Exception as e:
debug_logger.info(traceback.format_exc())
return insert_time
def insert_table(base_time, currency, con, table_type):
hour = base_time.hour
minutes = base_time.minute
seconds = base_time.second
if mode == "test":
count = 5000
elif mode == "production":
count = 1
flag = False
if table_type == "1m":
if seconds < 10:
granularity = "M1"
start_time = base_time - timedelta(minutes=1)
flag = True
elif table_type == "5m":
if minutes % 5 == 0 and seconds < 10:
granularity = "M5"
start_time = base_time - timedelta(minutes=5)
flag = True
elif table_type == "1h":
if minutes == 0 and seconds < 10:
granularity = "H1"
start_time = base_time - timedelta(hours=1)
flag = True
elif table_type == "day":
if hour == 7 and minutes == 0 and seconds < 10:
granularity = "D"
start_time = base_time - timedelta(days=1)
flag = True
if flag:
start_time = start_time.strftime("%Y-%m-%d %H:%M:%S")
start_time = jp_utc(start_time)
start_time = start_time.strftime("%Y-%m-%dT%H:%M:%S")
response = oanda.get_history(instrument=currency,
start=start_time,
granularity=granularity,
count=count)
for candle in response["candles"]:
open_ask_price = candle["openAsk"]
open_bid_price = candle["openBid"]
close_ask_price = candle["closeAsk"]
close_bid_price = candle["closeBid"]
high_ask_price = candle["highAsk"]
high_bid_price = candle["highBid"]
low_ask_price = candle["lowAsk"]
low_bid_price = candle["lowBid"]
insert_time = candle["time"]
insert_time = iso_jp(insert_time)
insert_time = insert_time.strftime("%Y-%m-%d %H:%M:%S")
sql = "insert into %s_%s_TABLE(open_ask, open_bid, close_ask, close_bid, high_ask, high_bid, low_ask, low_bid, insert_time) values(%s, %s, %s, %s, %s, %s, %s, %s, \'%s\')" % (
currency, table_type, open_ask_price, open_bid_price,
close_ask_price, close_bid_price, high_ask_price,
high_bid_price, low_ask_price, low_bid_price, insert_time)
print(sql)
con.insert_sql(sql)
if flag == False:
insert_time = base_time
else:
insert_time = datetime.strptime(insert_time, "%Y-%m-%d %H:%M:%S")
if flag:
if table_type == "1m":
if seconds < 10:
insert_time = insert_time + timedelta(minutes=1)
elif table_type == "5m":
if minutes % 5 == 0 and seconds < 10:
insert_time = insert_time + timedelta(minutes=5)
elif table_type == "1h":
if minutes == 0 and seconds < 10:
insert_time = insert_time + timedelta(hours=1)
elif table_type == "day":
if hour == 7 and minutes == 0 and seconds < 10:
insert_time = insert_time + timedelta(days=1)
return insert_time
else:
print(len(response["candles"]))
for candle in response["candles"]:
open_ask_price = candle["ask"]["o"]
open_bid_price = candle["bid"]["o"]
close_ask_price = candle["ask"]["c"]
close_bid_price = candle["bid"]["c"]
high_ask_price = candle["ask"]["h"]
high_bid_price = candle["bid"]["h"]
low_ask_price = candle["ask"]["l"]
low_bid_price = candle["bid"]["l"]
insert_time = candle["time"]
insert_time = insert_time.split(".")[0]
insert_time = insert_time + ".000000Z"
#print(insert_time)
insert_time = iso_jp(insert_time)
insert_time = insert_time.strftime("%Y-%m-%d %H:%M:%S")
sql = "select insert_time from %s_%s_TABLE where insert_time = \'%s\'" % (instrument, table_type, insert_time)
print(sql)
response = con.select_sql(sql)
if len(response) == 0:
sql = "insert into %s_%s_TABLE(open_ask, open_bid, close_ask, close_bid, high_ask, high_bid, low_ask, low_bid, insert_time) values(%s, %s, %s, %s, %s, %s, %s, %s, \'%s\')" % (instrument, table_type, open_ask_price, open_bid_price, close_ask_price, close_bid_price, high_ask_price, high_bid_price, low_ask_price, low_bid_price, insert_time)
debug_logger.info("%s: insert record = %s" % (base_time, sql))
print(sql)
try:
con.insert_sql(sql)
except Exception as e:
print(traceback.format_exc())