def test_normal_file(
self,
tmpdir,
json_text,
filename,
table_name,
expected_table_name,
expected_attr_names,
expected_data_matrix,
):
p_db = tmpdir.join("tmp.db")
p_json = tmpdir.join(filename)
with open(str(p_json), "w") as f:
f.write(json_text)
con = SimpleSQLite(str(p_db), "w")
con.create_table_from_json(str(p_json), table_name)
assert con.fetch_table_names() == [expected_table_name]
assert expected_attr_names == con.fetch_attr_names(expected_table_name)
result = con.select(select="*", table_name=expected_table_name)
result_matrix = result.fetchall()
assert len(result_matrix) == 3
assert result_matrix == expected_data_matrix
def test_normal_file(
self,
tmpdir,
json_text,
filename,
table_name,
expected_table_name,
expected_attr_names,
expected_data_matrix,
):
p_db = tmpdir.join("tmp.db")
p_json = tmpdir.join(filename)
with open(str(p_json), "w") as f:
f.write(json_text)
con = SimpleSQLite(str(p_db), "w")
try:
con.create_table_from_json(str(p_json), table_name)
except ImportError:
pytest.skip("requires pytablereader")
assert con.fetch_table_names() == [expected_table_name]
assert expected_attr_names == con.fetch_attr_names(expected_table_name)
result = con.select(select="*", table_name=expected_table_name)
result_matrix = result.fetchall()
assert len(result_matrix) == 3
assert result_matrix == expected_data_matrix
def main():
file_path = "sample_data_multi.json"
# create sample data file ---
with open(file_path, "w") as f:
f.write("""{
"table_a" : [
{"attr_b": 4, "attr_c": "a", "attr_a": 1},
{"attr_b": 2.1, "attr_c": "bb", "attr_a": 2},
{"attr_b": 120.9, "attr_c": "ccc", "attr_a": 3}
],
"table_b" : [
{"a": 1, "b": 4},
{"a": 2 },
{"a": 3, "b": 120.9}
]
}""")
# create table ---
con = SimpleSQLite("sample.sqlite", "w")
con.create_table_from_json(file_path)
# output ---
for table_name in con.fetch_table_names():
print("table: " + table_name)
print(con.fetch_attr_names(table_name))
result = con.select(select="*", table_name=table_name)
for record in result.fetchall():
print(record)
print()
class SimpleSqliteCilent(object):
def __init__(self):
self.con = SimpleSQLite("pandas_df.sqlite")
def createTableWithDf(self, dataframe, tableName):
self.con.create_table_from_dataframe(dataframe, tableName)
def dropTable(self, tableName):
self.con.drop_table(tableName)
def createTableWithList(self, tableName, columns):
self.con.create_table(tableName, columns)
def insertOneRow(self, tableName, columns):
self.con.insert(tableName, columns)
"""
返回的是tuple形式的结果
"""
def query(self, tableName, whereDict, operation="=", extra=None):
print len(whereDict.keys())
print "---------------"
if len(whereDict.keys()) != 1:
raise NotSupportParamError()
return self.con.select(select="*", table_name=tableName,\
where=SqlQuery.make_where(key=whereDict.keys()[0], value=whereDict[whereDict.keys()[0]],operation=operation),extra=extra).fetchall()
"""
根据where条件更新,目前仅支持1个条件
"""
def update(self, tableName, set_queryDict, whereDict):
print len(whereDict.keys())
print "---------------"
if len(whereDict.keys()) != 1:
raise NotSupportParamError()
set_queryStr = ""
for setKey, setValue in set_queryDict.items():
set_queryStr.join(setKey).join("=").join(setValue)
return self.con.update(tableName,set_query ="", \
where=SqlQuery.make_where(key=whereDict.keys()[0], \
value=whereDict[whereDict.keys()[0]])).fetchall()
"""
插入字典值形式的记录
"""
def insertMany(self, tableName, inert_dictList):
self.con.insert_many(tableName, inert_dictList)
##pass
def __del__(self):
if self.con is not None:
self.con.close()
def main():
table_name = "sample_table"
con = SimpleSQLite("sample.sqlite", "w")
data_matrix = [[1, "aaa"], [2, "bbb"]]
con.create_table_from_data_matrix(table_name, ["key", "value"],
data_matrix)
print("---- before update ----")
for record in con.select(select="*", table_name=table_name).fetchall():
print(record)
print()
con.update(table_name,
set_query="value = 'ccc'",
where=Where(key="key", value=1))
print("---- after update ----")
for record in con.select(select="*", table_name=table_name).fetchall():
print(record)
def test_normal(self, tmpdir, value, expected):
p_db = tmpdir.join("tmp.db")
con = SimpleSQLite(str(p_db), "w")
con.create_table_from_tabledata(value)
assert con.fetch_table_names() == [value.table_name]
assert con.fetch_attr_names(value.table_name) == value.headers
result = con.select(select="*", table_name=value.table_name)
result_matrix = result.fetchall()
assert result_matrix == expected
actual = con.select_as_tabledata(columns=value.headers, table_name=value.table_name)
assert actual.equals(value)
def test_normal_multi_file_same_table_same_structure(self):
db_path = "test.sqlite"
runner = CliRunner()
with runner.isolated_filesystem():
file_list = [
valid_json_multi_file_2_1(),
valid_json_multi_file_2_2()
]
result = runner.invoke(cmd, ["-o", db_path, "file"] + file_list)
print_traceback(result)
assert result.exit_code == ExitCode.SUCCESS
con = SimpleSQLite(db_path, "r")
expected_table_list = ["multij2", SourceInfo.get_table_name()]
actual_table_list = con.fetch_table_names()
print_test_result(expected=expected_table_list,
actual=actual_table_list)
assert set(actual_table_list) == set(expected_table_list)
expected_data_table = {
"multij2": [
(1, 4.0, "a"),
(2, 2.1, "bb"),
(3, 120.9, "ccc"),
(1, 4.0, "a"),
(2, 2.1, "bb"),
(3, 120.9, "ccc"),
]
}
for table in con.fetch_table_names():
if table == SourceInfo.get_table_name():
continue
expected_data = expected_data_table.get(table)
actual_data = con.select("*", table_name=table).fetchall()
message = "table={}, expected={}, actual={}".format(
table, expected_data, actual_data)
print("--- table: {} ---".format(table))
print_test_result(expected=expected_data, actual=actual_data)
assert expected_data == actual_data, message
def simpledb():
return_array = []
temp_dict = {}
print("In the simpledb function")
table_name = "sample_table"
con = SimpleSQLite("simple.sqlite", "w")
print("after con")
# create table -----
data_matrix = [[1, 1.1, "aaa", 1, 1], [2, 2.2, "bbb", 2.2, 2.2],
[3, 3.3, "ccc", 3, "ccc"]]
con.create_table_from_data_matrix(
table_name,
["attr_a", "attr_b", "attr_c", "attr_d", "attr_e"],
data_matrix,
)
con.insert(table_name,
record={
"attr_a": 4,
"attr_b": 4.4,
"attr_c": "ddd",
"attr_d": 4.44,
"attr_e": "hoge",
})
# display data type for each column in the table -----
print(con.schema_extractor.fetch_table_schema(table_name).dumps())
# display values in the table -----
print("records:")
key_array = [
"attr_a",
"attr_b",
"attr_c",
"attr_d",
"attr_e",
]
result = con.select(select="*", table_name=table_name)
print(type(result))
for record in result.fetchall():
print(record)
temp_dict = dict(zip(key_array, record))
return_array.append(temp_dict)
temp_dict = []
print("Last line of the simpledb function:")
return json.dumps(return_array)
def test_normal(self, tmpdir, value, expected):
p_db = tmpdir.join("tmp.db")
con = SimpleSQLite(str(p_db), "w")
con.create_table_from_tabledata(value)
assert con.fetch_table_names() == [value.table_name]
assert con.fetch_attr_names(value.table_name) == value.headers
result = con.select(select="*", table_name=value.table_name)
result_matrix = result.fetchall()
assert result_matrix == expected
actual = con.select_as_tabledata(columns=value.headers,
table_name=value.table_name)
assert actual.equals(value)
def test_normal_text(self, tmpdir, csv_text, table_name, attr_name_list,
expected_table_name, expected_attr_name_list,
expected_data_matrix):
p_db = tmpdir.join("tmp.db")
con = SimpleSQLite(str(p_db), "w")
con.create_table_from_csv(csv_text, table_name, attr_name_list)
assert con.get_table_name_list() == [expected_table_name]
assert expected_attr_name_list == con.get_attr_name_list(
expected_table_name)
result = con.select(select="*", table_name=expected_table_name)
result_matrix = result.fetchall()
assert len(result_matrix) == 3
assert result_matrix == expected_data_matrix
def test_normal(self, tmpdir, attr_names, data_matrix, index_attrs, expected_attr):
p = tmpdir.join("tmp.db")
con = SimpleSQLite(str(p), "w")
table_name = TEST_TABLE_NAME
con.create_table_from_data_matrix(
table_name, attr_names, data_matrix, primary_key=None, index_attrs=index_attrs
)
# check data ---
result = con.select(select=AttrList(attr_names), table_name=table_name)
result_matrix = result.fetchall()
assert len(result_matrix) == 3
print_test_result(expected=expected_attr, actual=con.fetch_attr_type(table_name))
assert con.fetch_attr_type(table_name) == expected_attr
def to_table_data(self):
from simplesqlite import SimpleSQLite
from simplesqlite.sqlquery import SqlQuery
con = SimpleSQLite(self._source_data, "r")
for table in con.get_table_name_list():
self.__table_name = table
attr_name_list = con.get_attr_name_list(table)
data_matrix = con.select(
select=",".join(SqlQuery.to_attr_str_list(attr_name_list)),
table_name=table)
yield TableData(
table, attr_name_list, data_matrix)
def main():
with open("sample_data.csv", "w") as f:
f.write("\n".join([
'"attr_a","attr_b","attr_c"', '1,4,"a"', '2,2.1,"bb"',
'3,120.9,"ccc"'
]))
# create table ---
con = SimpleSQLite("sample.sqlite", "w")
con.create_table_from_csv("sample_data.csv")
# output ---
table_name = "sample_data"
print(con.fetch_attr_names(table_name))
result = con.select(select="*", table_name=table_name)
for record in result.fetchall():
print(record)
def to_table_data(self):
from simplesqlite import SimpleSQLite
from simplesqlite.query import AttrList
con = SimpleSQLite(self._source_data, "r")
for table in con.get_table_name_list():
self.__table_name = table
attr_name_list = con.get_attr_name_list(table)
data_matrix = con.select(select=AttrList(attr_name_list),
table_name=table).fetchall()
yield TableData(table,
attr_name_list,
data_matrix,
dp_extractor=self._loader.dp_extractor)
def test_normal_multi_file_same_table_same_structure(self):
db_path = "test.sqlite"
runner = CliRunner()
with runner.isolated_filesystem():
files = [valid_json_multi_file_2_1(), valid_json_multi_file_2_2()]
result = runner.invoke(cmd, ["-o", db_path, "file"] + files)
print_traceback(result)
assert result.exit_code == ExitCode.SUCCESS
con = SimpleSQLite(db_path, "r")
expected_tables = ["multij2", SourceInfo.get_table_name()]
actual_tables = con.fetch_table_names()
print_test_result(expected=expected_tables, actual=actual_tables)
assert set(actual_tables) == set(expected_tables)
expected_data_table = {
"multij2": [
(1, 4.0, "a"),
(2, 2.1, "bb"),
(3, 120.9, "ccc"),
(1, 4.0, "a"),
(2, 2.1, "bb"),
(3, 120.9, "ccc"),
]
}
for table in con.fetch_table_names():
if table == SourceInfo.get_table_name():
continue
expected_data = expected_data_table.get(table)
actual_data = con.select("*", table_name=table).fetchall()
message = "table={}, expected={}, actual={}".format(
table, expected_data, actual_data
)
print("--- table: {} ---".format(table))
print_test_result(expected=expected_data, actual=actual_data)
assert expected_data == actual_data, message
def main():
table_name = "sample_table"
con = SimpleSQLite("sample.sqlite", "w")
# create table -----
data_matrix = [[1, 1.1, "aaa", 1, 1], [2, 2.2, "bbb", 2.2, 2.2],
[3, 3.3, "ccc", 3, "ccc"]]
con.create_table_from_data_matrix(
table_name, ["attr_a", "attr_b", "attr_c", "attr_d", "attr_e"],
data_matrix)
# display data type for each column in the table -----
print(con.schema_extractor.fetch_table_schema(table_name).dumps())
# display values in the table -----
print("records:")
result = con.select(select="*", table_name=table_name)
for record in result.fetchall():
print(record)
def test_normal_file(self, tmpdir, json_text, filename, table_name,
expected_table_name, expected_attr_name_list,
expected_data_matrix):
p_db = tmpdir.join("tmp.db")
p_json = tmpdir.join(filename)
with open(str(p_json), "w") as f:
f.write(json_text)
con = SimpleSQLite(str(p_db), "w")
con.create_table_from_json(str(p_json), table_name)
assert con.get_table_name_list() == [expected_table_name]
assert expected_attr_name_list == con.get_attr_name_list(
expected_table_name)
result = con.select(select="*", table_name=expected_table_name)
result_matrix = result.fetchall()
assert len(result_matrix) == 3
assert result_matrix == expected_data_matrix
def test_normal(self, tmpdir, attr_name_list, data_matrix, index_attr_list,
expected_attr):
p = tmpdir.join("tmp.db")
con = SimpleSQLite(str(p), "w")
table_name = TEST_TABLE_NAME
con.create_table_from_data_matrix(table_name, attr_name_list,
data_matrix, index_attr_list)
con.commit()
# check data ---
result = con.select(select=",".join(
SqlQuery.to_attr_str_list(attr_name_list)),
table_name=table_name)
result_matrix = result.fetchall()
assert len(result_matrix) == 3
print("expected: {}".format(expected_attr))
print("actual: {}".format(con.get_attr_type(table_name)))
assert con.get_attr_type(table_name) == expected_attr
def main():
table_name = "sample_table"
con = SimpleSQLite("sample.sqlite", "w")
con.create_table_from_data_matrix(
table_name, ["attr_a", "attr_b", "attr_c", "attr_d", "attr_e"], [[1, 1.1, "aaa", 1, 1]]
)
# insert namedtuple
SampleTuple = namedtuple("SampleTuple", "attr_a attr_b attr_c attr_d attr_e")
con.insert(table_name, record=[7, 7.7, "fff", 7.77, "bar"])
con.insert_many(
table_name,
records=[(8, 8.8, "ggg", 8.88, "foobar"), SampleTuple(9, 9.9, "ggg", 9.99, "hogehoge")],
)
# print
result = con.select(select="*", table_name=table_name)
for record in result.fetchall():
print(record)
def to_table_data(self):
from simplesqlite import SimpleSQLite
from simplesqlite.query import AttrList
con = SimpleSQLite(self._source_data, "r")
for table in con.fetch_table_names():
self.__table_name = table
attr_names = con.fetch_attr_names(table)
data_matrix = con.select(select=AttrList(attr_names),
table_name=table).fetchall()
yield TableData(
table,
attr_names,
data_matrix,
dp_extractor=self._loader.dp_extractor,
type_hints=self._extract_type_hints(attr_names),
)
def test_normal(self, tmpdir, attr_names, data_matrix, index_attrs,
expected_attr):
p = tmpdir.join("tmp.db")
con = SimpleSQLite(str(p), "w")
table_name = TEST_TABLE_NAME
con.create_table_from_data_matrix(table_name,
attr_names,
data_matrix,
primary_key=None,
index_attrs=index_attrs)
# check data ---
result = con.select(select=AttrList(attr_names), table_name=table_name)
result_matrix = result.fetchall()
assert len(result_matrix) == 3
print_test_result(expected=expected_attr,
actual=con.fetch_attr_type(table_name))
assert con.fetch_attr_type(table_name) == expected_attr
def main():
table_name = "sample_table"
con = SimpleSQLite("sample.sqlite", "w")
con.create_table_from_data_matrix(
table_name, ["attr_a", "attr_b", "attr_c", "attr_d", "attr_e"], [[1, 1.1, "aaa", 1, 1]]
)
con.insert(
table_name,
record={"attr_a": 4, "attr_b": 4.4, "attr_c": "ddd", "attr_d": 4.44, "attr_e": "hoge"},
)
con.insert_many(
table_name,
records=[
{"attr_a": 5, "attr_b": 5.5, "attr_c": "eee", "attr_d": 5.55, "attr_e": "foo"},
{"attr_a": 6, "attr_c": "fff"},
],
)
result = con.select(select="*", table_name=table_name)
for record in result.fetchall():
print(record)
def test_normal_text(
self,
tmpdir,
csv_text,
table_name,
attr_names,
expected_table_name,
expected_attr_names,
expected_data_matrix,
):
p_db = tmpdir.join("tmp.db")
con = SimpleSQLite(str(p_db), "w")
con.create_table_from_csv(csv_text, table_name, attr_names)
assert con.fetch_table_names() == [expected_table_name]
assert expected_attr_names == con.fetch_attr_names(expected_table_name)
result = con.select(select="*", table_name=expected_table_name)
result_matrix = result.fetchall()
assert len(result_matrix) == 3
assert result_matrix == expected_data_matrix
#!/usr/bin/env python
# encoding: utf-8
from __future__ import print_function
from simplesqlite import SimpleSQLite
from simplesqlite.query import Where
table_name = "sample_table"
con = SimpleSQLite("sample.sqlite", "w")
data_matrix = [[1, "aaa"], [2, "bbb"]]
con.create_table_from_data_matrix(table_name, ["key", "value"], data_matrix)
print("---- before update ----")
for record in con.select(select="*", table_name=table_name).fetchall():
print(record)
print()
con.update(table_name,
set_query="value = 'ccc'",
where=Where(key="key", value=1))
print("---- after update ----")
for record in con.select(select="*", table_name=table_name).fetchall():
print(record)
#!/usr/bin/env python
# encoding: utf-8
import json
from simplesqlite import SimpleSQLite
import six
table_name = "sample_table"
con = SimpleSQLite("sample.sqlite", "w")
# create table -----
data_matrix = [
[1, 1.1, "aaa", 1, 1],
[2, 2.2, "bbb", 2.2, 2.2],
[3, 3.3, "ccc", 3, "ccc"],
]
con.create_table_with_data(
table_name,
attribute_name_list=["attr_a", "attr_b", "attr_c", "attr_d", "attr_e"],
data_matrix=data_matrix)
# display values in the table -----
six.print_(con.get_attribute_name_list(table_name))
result = con.select(select="*", table_name=table_name)
for record in result.fetchall():
six.print_(record)
# display data type for each column in the table -----
six.print_(json.dumps(con.get_attr_type(table_name), indent=4))
class MysqlDataHandle(MysqlData):
def __init__(self, g, event_queue, data_handle):
self.g = g
self.event_queue = event_queue
self.data_handle = data_handle
self.dbname = 'bitmex'
self.create_table(self.dbname)
self.data = None
self.con = SimpleSQLite(self.dbname, 'a')
self.ex = cxt.bitmex({
'apiKey': self.g.apiKey,
'secret': self.g.apiSecret,
'timeout': 60000,
})
def checkData(self, symbol):
if self.OneHourDBIsZeroCount(symbol) == True:
self.insertOneHourData(symbol)
elif self.CheckLastOneHourData(symbol) == False:
self.insertOneHourData(symbol)
def on_1mbar_event(self, event):
__bar = event.dict_
# _now_time = datetime.strptime(__bar.timestamp,'%Y-%m-%dT%H:%M:%S.%fZ')
_sql = [
__bar.symbol, __bar.bar_type, __bar.open, __bar.high, __bar.low,
__bar.close, __bar.volume, __bar.trades, __bar.buy, __bar.sell,
__bar.timestamp
]
conn = sqlite3.connect(self.dbname)
cursor = conn.cursor()
cursor.execute(insert_bar_1m_sql, (_sql))
conn.commit()
cursor.close()
conn.close()
def on_1hbar_event(self, event):
__bar = event.dict_
_sql = [
__bar.symbol, __bar.bar_type, __bar.open, __bar.high, __bar.low,
__bar.close, __bar.volume, __bar.trades, __bar.buy, __bar.sell,
__bar.timestamp
]
conn = sqlite3.connect(self.dbname)
cursor = conn.cursor()
cursor.execute(insert_bar_1h_much_sql, (_sql))
conn.commit()
cursor.close()
conn.close()
def retResampleOhlcv(self, ohlcv):
data = np.array(ohlcv)
start_index = datetime.datetime.fromtimestamp(int(
str(data[0][0])[:10]))
end_index = datetime.datetime.fromtimestamp(int(str(data[-1][0])[:10]))
dateIndex = pd.date_range(start=start_index, end=end_index, freq='T')
df = pd.DataFrame(
data[:, 1:],
index=dateIndex,
columns=['Open', 'High', 'Low', 'Close', 'Volume', 'Buy', 'Sell'])
ohlc_dict = {
'Open': 'first',
'High': 'max',
'Low': 'min',
'Close': 'last',
'Volume': 'sum',
'Buy': 'sum',
'Sell': 'sum',
}
df1H = df.resample('60T', closed='left', label='left').agg(ohlc_dict)
return df1H
def OneHourDBIsZeroCount(self, symbol):
conn = sqlite3.connect(self.dbname)
cursor = conn.cursor()
cursor.execute("SELECT * FROM bitmex_bar_1h WHERE symbol = ?",
(symbol, ))
bar_1h_count = cursor.fetchall()
if len(bar_1h_count) == 0:
return True
else:
return False
def InsertNewOrder(self, order):
conn = sqlite3.connect('bitmex')
cursor = conn.cursor()
_insert_sql = '''
INSERT INTO bitmex_orderhistory
(symbol,orderid,ordertype,orderprice,side,amount,status,date)
VALUES (?,?,?,?,?,?,?,?)
'''
cursor.execute(_insert_sql, order)
conn.commit()
cursor.close()
conn.close()
def InsertManyNewOrder(self, orderlist):
conn = sqlite3.connect('bitmex')
cursor = conn.cursor()
_insert_sql = '''
INSERT INTO bitmex_orderhistory
(symbol,orderid,ordertype,orderprice,side,amount,status,date)
VALUES (?,?,?,?,?,?,?,?)
'''
cursor.executemany(_insert_sql, orderlist)
conn.commit()
cursor.close()
conn.close()
def CheckNewOrderInThreeMinute(self, symbol):
d = datetime.datetime.now().replace(
second=0, microsecond=0) + datetime.timedelta(minutes=-3)
conn = sqlite3.connect('bitmex')
cursor = conn.cursor()
cursor.execute(
"SELECT * FROM bitmex_orderhistory WHERE symbol = ? and date >= ? and ordertype= 'opening' and status = 'new' ",
(symbol, d))
_new_order = cursor.fetchall()
cursor.execute(
"SELECT * FROM bitmex_orderhistory WHERE symbol = ? and date < ? and ordertype= 'opening' and status = 'new' ",
(symbol, d))
_old_order = cursor.fetchall()
if len(_old_order) > 0:
for __old_order in _old_order:
try:
self.ex.cancel_order(__old_order[2])
except Exception as e:
print(e)
time.sleep(1)
cursor.execute(
'DELETE FROM bitmex_orderhistory WHERE orderid = ?',
(__old_order[2], ))
time.sleep(0.5)
conn.commit()
cursor.close()
conn.close()
if len(_new_order) > 0:
return True
else:
return False
def DeleteBuyOrder(self, symbol):
conn = sqlite3.connect('bitmex')
cursor = conn.cursor()
cursor.execute(
"SELECT * FROM bitmex_orderhistory WHERE symbol = ? and ordertype= 'grid' and side = 'Buy' and status = 'new' ",
(symbol, ))
_orders = cursor.fetchall()
if len(_orders) > 0:
for _order in _orders:
try:
self.ex.cancel_order(_order[2])
except Exception as e:
print(e)
time.sleep(1)
cursor.execute(
'DELETE FROM bitmex_orderhistory WHERE orderid = ?',
(_order[2], ))
time.sleep(0.5)
conn.commit()
cursor.close()
conn.close()
def UpdateOrderStatus(self, order):
ord_id = order.orderid
status = order.status
symbol = order.symbol
if status == 'filled':
conn = sqlite3.connect('bitmex')
cursor = conn.cursor()
cursor.execute(
"UPDATE bitmex_orderhistory SET status = 'filled' WHERE orderid = ? and symbol = ? ",
(ord_id, symbol))
conn.commit()
cursor.close()
conn.close()
def getOHLCDataFromDB(self, timeframe=None):
timeframe = '1h'
conn = sqlite3.connect(self.dbname)
cursor = conn.cursor()
cursor.execute(
"SELECT open,high,low,close FROM bitmex_bar_1h ORDER BY id DESC LIMIT 200"
)
bar_1h_data = cursor.fetchall()
ohlcv = np.array(bar_1h_data)[::-1]
return ohlcv
def CheckLastOneHourData(self, symbol):
d = datetime.datetime.now().replace(
second=0, microsecond=0) + datetime.timedelta(hours=-1)
conn = sqlite3.connect('bitmex')
cursor = conn.cursor()
cursor.execute(
"SELECT * FROM bitmex_bar_1h WHERE date >= ? and symbol = ? ",
(d, symbol))
__count = cursor.fetchall()
if len(__count) == 0:
cursor.execute("DELETE FROM bitmex_bar_1h WHERE symbol = ?",
(symbol, ))
conn.commit()
cursor.close()
conn.close()
return False
else:
conn.commit()
cursor.close()
conn.close()
return True
def insertOneHourData(self, symbol):
_sql = []
ohlcv = self.onehour_200_data(symbol)
for _ohlcv in ohlcv:
_date = datetime.datetime.fromtimestamp(int(str(_ohlcv[0])[:10]))
__list = [
symbol, '1h', _ohlcv[1], _ohlcv[2], _ohlcv[3], _ohlcv[4],
_ohlcv[5], _date
]
_sql.append(__list)
conn = sqlite3.connect(self.dbname)
cursor = conn.cursor()
cursor.executemany(insert_bar_1h_little_sql, _sql)
conn.commit()
cursor.close()
conn.close()
def onehour_200_data(self, symbol=None):
if symbol == 'XBTUSD':
symbol = 'BTC/USD'
elif symbol == 'ETHUSD':
symbol = 'ETH/USD'
ohlcv1h = None
limit = 201
since = self.ex.milliseconds() - limit * 60 * 60 * 1000
params = {'partial': False}
while True:
try:
ohlcv1h = self.ex.fetch_ohlcv(symbol, '1h', since, limit + 1,
params)
break
except Exception as e:
print(e)
time.sleep(1)
if ohlcv1h is not None:
return ohlcv1h
def CheckDB_by_Symbol_in_timeframe(self,
symbol=None,
condition=None,
timedelta=None):
"""
Args:
condiction:'opening','grid'
symbol:'XBTUSD','ETHUSD'
timedelta:3600 minutes
Return:
if founded conditicon db,return True,else return False.
Example:
https://github.com/thombashi/SimpleSQLite
pip install SimpleSQLite
modify sqlquery.py in sites-packages/
con = SimpleSQLite('bitmex','a')
table_name = 'bitmex_bar_1h'
where_list = And([Where("symbol", "XBTUSD"), Where("id", 1, "<=")])
result = con.select(select="symbol,close",table_name=table_name,where=where_list)
for s in result.fetchall():
print(s)
con.fetch_num_records(table_name,where_list)
con.update(table_name, set_query="symbol = 'XBTUSD',close=3300", where=where_list)
"""
d = datetime.datetime.now().replace(
second=0, microsecond=0) + datetime.timedelta(minutes=-timedelta)
where_list = And([
Where("symbol", symbol, '='),
Where("ordertype", condition, "="),
Where("date", d, ">")
])
table_name = 'bitmex_orderhistory'
ordercount_in_one_day = self.con.fetch_num_records(
table_name, where_list)
where_list = And([
Where("symbol", symbol, '='),
Where("ordertype", condition, "="),
Where("date", d, "<=")
])
orderids_before_one_day = self.con.select(select="orderid",
table_name=table_name,
where=where_list)
for orderid in orderids_before_one_day.fetchall():
while True:
try:
self.ex.cancel_order(orderid[0])
self.con.delete(table_name,
where=Where("orderid", orderid[0], "="))
break
except Exception as e:
print(e)
time.sleep(1)
return ordercount_in_one_day
def on_fill_event(self, a):
symbol = a.dict_.symbol
orderid = a.dict_.orderid
conn = sqlite3.connect('bitmex')
cursor = conn.cursor()
cursor.execute(
"UPDATE bitmex_orderhistory SET status = 'filled' WHERE orderid = ? and symbol = ? ",
(orderid, symbol))
conn.commit()
cursor.close()
conn.close()
def create_table(self, dbname):
conn = sqlite3.connect(dbname)
try:
create_bitmex_bar_1m_cmd = '''
CREATE TABLE IF NOT EXISTS bitmex_bar_1m
(id INTEGER,
symbol text,
bar_type real,
open real,
high real,
low real,
close real,
volume real,
trades real,
buy real,
sell real,
date INTEGER,
PRIMARY KEY (id));
'''
create_bitmex_bar_1h_cmd = '''
CREATE TABLE IF NOT EXISTS bitmex_bar_1h
(id INTEGER,
symbol text,
bar_type real,
open real,
high real,
low real,
close real,
volume real,
trades real,
buy real,
sell real,
date INTEGER,
PRIMARY KEY (id));
'''
create_bitmex_orderhistory_cmd = '''
CREATE TABLE IF NOT EXISTS bitmex_orderhistory
(id INTEGER,
symbol text,
orderid text,
ordertype text,
orderprice real,
side text,
amount real,
status text,
date INTEGER,
PRIMARY KEY (id));
'''
conn.execute(create_bitmex_bar_1h_cmd)
conn.execute(create_bitmex_bar_1m_cmd)
conn.execute(create_bitmex_orderhistory_cmd)
except:
print("Create table failed")
conn.commit()
conn.close()
# create sample data file ---
with open(file_path, "w") as f:
f.write(
"""{
"table_a" : [
{"attr_b": 4, "attr_c": "a", "attr_a": 1},
{"attr_b": 2.1, "attr_c": "bb", "attr_a": 2},
{"attr_b": 120.9, "attr_c": "ccc", "attr_a": 3}
],
"table_b" : [
{"a": 1, "b": 4},
{"a": 2 },
{"a": 3, "b": 120.9}
]
}"""
)
# create table ---
con = SimpleSQLite("sample.sqlite", "w")
con.create_table_from_json(file_path)
# output ---
for table_name in con.fetch_table_names():
print("table: " + table_name)
print(con.fetch_attr_names(table_name))
result = con.select(select="*", table_name=table_name)
for record in result.fetchall():
print(record)
print()
def test_normal_append(self):
db_path = "test.sqlite"
runner = CliRunner()
with runner.isolated_filesystem():
files = [valid_json_multi_file_2_1()]
table_name = "multij2"
expected_tables = [table_name, SourceInfo.get_table_name()]
# first execution without --append option (new) ---
result = runner.invoke(cmd, ["-o", db_path, "file"] + files)
print_traceback(result)
assert result.exit_code == ExitCode.SUCCESS
con = SimpleSQLite(db_path, "r")
actual_tables = con.fetch_table_names()
print_test_result(expected=expected_tables, actual=actual_tables)
assert set(actual_tables) == set(expected_tables)
actual_data = con.select("*", table_name=table_name).fetchall()
expected_data = [(1, 4.0, "a"), (2, 2.1, "bb"), (3, 120.9, "ccc")]
print_test_result(expected=expected_data, actual=actual_data)
assert expected_data == actual_data
# second execution with --append option ---
result = runner.invoke(cmd,
["-o", db_path, "--append", "file"] + files)
print_traceback(result)
assert result.exit_code == ExitCode.SUCCESS
con = SimpleSQLite(db_path, "r")
actual_tables = con.fetch_table_names()
print_test_result(expected=expected_tables, actual=actual_tables)
assert set(actual_tables) == set(expected_tables)
actual_data = con.select("*", table_name=table_name).fetchall()
expected_data = [
(1, 4.0, "a"),
(2, 2.1, "bb"),
(3, 120.9, "ccc"),
(1, 4.0, "a"),
(2, 2.1, "bb"),
(3, 120.9, "ccc"),
]
print_test_result(expected=expected_data, actual=actual_data)
assert expected_data == actual_data
# third execution without --append option (overwrite) ---
result = runner.invoke(cmd, ["-o", db_path, "file"] + files)
print_traceback(result)
assert result.exit_code == ExitCode.SUCCESS
con = SimpleSQLite(db_path, "r")
actual_tables = con.fetch_table_names()
print_test_result(expected=expected_tables, actual=actual_tables)
assert set(actual_tables) == set(expected_tables)
actual_data = con.select("*", table_name=table_name).fetchall()
expected_data = [(1, 4.0, "a"), (2, 2.1, "bb"), (3, 120.9, "ccc")]
print_test_result(expected=expected_data, actual=actual_data)
assert expected_data == actual_data
def test_normal_multi_file_different_table(self):
db_path = "test.sqlite"
runner = CliRunner()
with runner.isolated_filesystem():
files = [
valid_json_single_file(),
valid_json_single_b_file(),
valid_json_multi_file_1(),
valid_json_kv_file(),
valid_csv_file_1_1(),
valid_csv_file_2_1(),
invalid_csv_file(),
valid_tsv_file(),
invalid_tsv_file(),
valid_excel_file(),
invalid_excel_file_1(),
invalid_excel_file_2(),
valid_html_file(),
invalid_html_file(),
valid_ltsv_file(),
invalid_ltsv_file(),
valid_markdown_file(),
not_supported_format_file(),
]
result = runner.invoke(cmd, ["-o", db_path, "file"] + files)
assert result.exit_code == ExitCode.SUCCESS
con = SimpleSQLite(db_path, "r")
expected_tables = [
"2018Asset",
"singlejson",
"multij1",
"multij2",
"valid_kv",
"csv_a",
"rename_insert",
"excel_sheet_a",
"excel_sheet_c",
"excel_sheet_d",
"valid_ltsv_a",
"testtitle_tablename",
"testtitle_html2",
"tsv_a",
"valid_mdtable_markdown1",
"root",
SourceInfo.get_table_name(),
]
actual_tables = con.fetch_table_names()
print_test_result(expected=expected_tables, actual=actual_tables)
assert set(actual_tables) == set(expected_tables)
expected_data_table = {
"2018Asset": [(1, "1billion", "item_entertainment_18_212")],
"singlejson": [(1, 4.0, "a"), (2, 2.1, "bb"),
(3, 120.9, "ccc")],
"multij1": [(1, 4.0, "a"), (2, 2.1, "bb"), (3, 120.9, "ccc")],
"multij2": [(1, 4.0), (2, None), (3, 120.9)],
"valid_kv": [("json_b", "hoge"), ("json_c", "bar")],
"csv_a": [(1, 4.0, "a"), (2, 2.1, "bb"), (3, 120.9, "ccc")],
"rename_insert": [
(1, 55, "D Sam", 31, "Raven"),
(2, 36, "J Ifdgg", 30, "Raven"),
(3, 91, "K Wedfb", 28, "Raven"),
],
"excel_sheet_a": [(1.0, 1.1, "a"), (2.0, 2.2, "bb"),
(3.0, 3.3, "cc")],
"excel_sheet_c": [(1, 1.1, "a"), (2, "", "bb"), (3, 3.3, "")],
"excel_sheet_d": [(1, 1.1, "a"), (2, "", "bb"), (3, 3.3, "")],
"testtitle_tablename": [(1, 123.1, "a"), (2, 2.2, "bb"),
(3, 3.3, "ccc")],
"valid_ltsv_a": [
(1, 123.1, '"ltsv0"', 1.0, '"1"'),
(2, 2.2, '"ltsv1"', 2.2, '"2.2"'),
(3, 3.3, '"ltsv2"', 3.0, '"cccc"'),
],
"testtitle_html2": [(1, 123.1), (2, 2.2), (3, 3.3)],
"tsv_a": [(1, 4.0, "tsv0"), (2, 2.1, "tsv1"),
(3, 120.9, "tsv2")],
"valid_mdtable_markdown1": [(1, 123.1, "a"), (2, 2.2, "bb"),
(3, 3.3, "ccc")],
}
for table in con.fetch_table_names():
if table in (SourceInfo.get_table_name(), "root"):
continue
result = con.select("*", table_name=table)
expected_data = expected_data_table.get(table)
actual_data = result.fetchall()
message = "table={}, expected={}, actual={}".format(
table, expected_data, actual_data)
print("--- table: {} ---".format(table))
print_test_result(expected=expected_data, actual=actual_data)
assert sorted(expected_data) == sorted(actual_data), message
#!/usr/bin/env python
# encoding: utf-8
from __future__ import print_function
from simplesqlite import SimpleSQLite
from simplesqlite.query import Where
table_name = "sample_table"
con = SimpleSQLite("sample.sqlite", "w")
data_matrix = [[1, "aaa"], [2, "bbb"]]
con.create_table_from_data_matrix(table_name, ["key", "value"], data_matrix)
print("---- before update ----")
for record in con.select(select="*", table_name=table_name).fetchall():
print(record)
print()
con.update(table_name, set_query="value = 'ccc'", where=Where(key="key", value=1))
print("---- after update ----")
for record in con.select(select="*", table_name=table_name).fetchall():
print(record)
def test_normal_multi_file_different_table(self):
db_path = "test.sqlite"
runner = CliRunner()
with runner.isolated_filesystem():
files = [
valid_json_single_file(),
invalid_json_single_file(),
valid_json_multi_file_1(),
valid_json_kv_file(),
valid_csv_file_1_1(),
valid_csv_file_2_1(),
invalid_csv_file(),
valid_tsv_file(),
invalid_tsv_file(),
valid_excel_file(),
invalid_excel_file_1(),
invalid_excel_file_2(),
valid_html_file(),
invalid_html_file(),
valid_ltsv_file(),
invalid_ltsv_file(),
valid_markdown_file(),
not_supported_format_file(),
]
result = runner.invoke(cmd, ["-o", db_path, "file"] + files)
assert result.exit_code == ExitCode.SUCCESS
con = SimpleSQLite(db_path, "r")
expected_tables = [
"singlejson",
"multij1",
"multij2",
"valid_kv",
"csv_a",
"rename_insert",
"excel_sheet_a",
"excel_sheet_c",
"excel_sheet_d",
"valid_ltsv_a",
"testtitle_tablename",
"testtitle_html2",
"tsv_a",
"valid_mdtable_markdown1",
SourceInfo.get_table_name(),
]
actual_tables = con.fetch_table_names()
print_test_result(expected=expected_tables, actual=actual_tables)
assert set(actual_tables) == set(expected_tables)
expected_data_table = {
"singlejson": [(1, 4.0, "a"), (2, 2.1, "bb"), (3, 120.9, "ccc")],
"multij1": [(1, 4.0, "a"), (2, 2.1, "bb"), (3, 120.9, "ccc")],
"multij2": [(1, 4.0), (2, None), (3, 120.9)],
"valid_kv": [("json_b", "hoge"), ("json_c", "bar")],
"csv_a": [(1, 4.0, "a"), (2, 2.1, "bb"), (3, 120.9, "ccc")],
"rename_insert": [
(1, 55, "D Sam", 31, "Raven"),
(2, 36, "J Ifdgg", 30, "Raven"),
(3, 91, "K Wedfb", 28, "Raven"),
],
"excel_sheet_a": [(1.0, 1.1, "a"), (2.0, 2.2, "bb"), (3.0, 3.3, "cc")],
"excel_sheet_c": [(1, 1.1, "a"), (2, "", "bb"), (3, 3.3, "")],
"excel_sheet_d": [(1, 1.1, "a"), (2, "", "bb"), (3, 3.3, "")],
"testtitle_tablename": [(1, 123.1, "a"), (2, 2.2, "bb"), (3, 3.3, "ccc")],
"valid_ltsv_a": [
(1, 123.1, u'"ltsv0"', 1.0, u'"1"'),
(2, 2.2, u'"ltsv1"', 2.2, u'"2.2"'),
(3, 3.3, u'"ltsv2"', 3.0, u'"cccc"'),
],
"testtitle_html2": [(1, 123.1), (2, 2.2), (3, 3.3)],
"tsv_a": [(1, 4.0, "tsv0"), (2, 2.1, "tsv1"), (3, 120.9, "tsv2")],
"valid_mdtable_markdown1": [(1, 123.1, "a"), (2, 2.2, "bb"), (3, 3.3, "ccc")],
}
for table in con.fetch_table_names():
if table == SourceInfo.get_table_name():
continue
result = con.select("*", table_name=table)
expected_data = expected_data_table.get(table)
actual_data = result.fetchall()
message = "table={}, expected={}, actual={}".format(
table, expected_data, actual_data
)
print("--- table: {} ---".format(table))
print_test_result(expected=expected_data, actual=actual_data)
assert sorted(expected_data) == sorted(actual_data), message
def test_normal_append(self):
db_path = "test.sqlite"
runner = CliRunner()
with runner.isolated_filesystem():
files = [valid_json_multi_file_2_1()]
table_name = "multij2"
expected_tables = [table_name, SourceInfo.get_table_name()]
# first execution without --append option (new) ---
result = runner.invoke(cmd, ["-o", db_path, "file"] + files)
print_traceback(result)
assert result.exit_code == ExitCode.SUCCESS
con = SimpleSQLite(db_path, "r")
actual_tables = con.fetch_table_names()
print_test_result(expected=expected_tables, actual=actual_tables)
assert set(actual_tables) == set(expected_tables)
actual_data = con.select("*", table_name=table_name).fetchall()
expected_data = [(1, 4.0, "a"), (2, 2.1, "bb"), (3, 120.9, "ccc")]
print_test_result(expected=expected_data, actual=actual_data)
assert expected_data == actual_data
# second execution with --append option ---
result = runner.invoke(cmd, ["-o", db_path, "--append", "file"] + files)
print_traceback(result)
assert result.exit_code == ExitCode.SUCCESS
con = SimpleSQLite(db_path, "r")
actual_tables = con.fetch_table_names()
print_test_result(expected=expected_tables, actual=actual_tables)
assert set(actual_tables) == set(expected_tables)
actual_data = con.select("*", table_name=table_name).fetchall()
expected_data = [
(1, 4.0, "a"),
(2, 2.1, "bb"),
(3, 120.9, "ccc"),
(1, 4.0, "a"),
(2, 2.1, "bb"),
(3, 120.9, "ccc"),
]
print_test_result(expected=expected_data, actual=actual_data)
assert expected_data == actual_data
# third execution without --append option (overwrite) ---
result = runner.invoke(cmd, ["-o", db_path, "file"] + files)
print_traceback(result)
assert result.exit_code == ExitCode.SUCCESS
con = SimpleSQLite(db_path, "r")
actual_tables = con.fetch_table_names()
print_test_result(expected=expected_tables, actual=actual_tables)
assert set(actual_tables) == set(expected_tables)
actual_data = con.select("*", table_name=table_name).fetchall()
expected_data = [(1, 4.0, "a"), (2, 2.1, "bb"), (3, 120.9, "ccc")]
print_test_result(expected=expected_data, actual=actual_data)
assert expected_data == actual_data