def save_crypto_exchange_to_db(exchange):
if not enable_persist_data_db:
return True
db = __create_connection()
if db is None:
return False
try:
cursor = db.cursor()
__try_create_exchange_table(cursor)
query = "INSERT OR REPLACE INTO exchanges(id,name,currency,amount) VALUES(?, ?, ?, ?)"
values = (exchange.exchange_id, exchange.name, exchange.currency, exchange.amount)
cursor.execute(query, values)
db.commit()
return True
except sqlite3.Error as e:
log_error(e)
finally:
db.close()
return False
def load_crypto_currencies(exchange_id):
if not enable_persist_data_db:
return {}
db = __create_connection()
if db is None:
return None
try:
cursor = db.cursor()
__try_create_crypto_exchanges_table(cursor)
if exchange_id is None:
cursor.execute("SELECT * FROM crypto_currencies")
else:
cursor.execute("SELECT * FROM crypto_currencies WHERE exchange_id=" + str(exchange_id))
records = cursor.fetchall()
crypto_exchanges = {}
for row in records:
crypto_exchanges[row[0]] = CryptoCurrency(row[3], row[1], row[2], row[4], row[0])
return crypto_exchanges
except sqlite3.Error as e:
log_error(e)
finally:
db.close()
return None
def load_trades_from_db():
if not enable_persist_data_db:
return {}
db = __create_connection()
if db is None:
return None
try:
cursor = db.cursor()
__try_create_trades_table(cursor)
cursor.execute("SELECT * FROM trades")
records = cursor.fetchall()
trades = []
for row in records:
trades.append(Trade(row[1], row[2], row[3], row[4], row[5], row[6]))
return trades
except sqlite3.Error as e:
log_error(e)
finally:
db.close()
return None
def save_trade_to_db(trade):
if not enable_persist_data_db:
return True
db = __create_connection()
if db is None:
return False
try:
cursor = db.cursor()
__try_create_trades_table(cursor)
query = "INSERT INTO trades (amount, amount_out, currency_in, currency_out, exchange_id, timestamp) " \
"VALUES (?, ?, ?, ?, ? ,?)"
values = (
trade.amount, trade.amount_out, trade.currency_in, trade.currency_out, trade.exchange_id, trade.timestamp)
cursor.execute(query, values)
db.commit()
return True
except sqlite3.Error as e:
log_error(e)
finally:
db.close()
return False
def load_crypto_exchanges_from_db():
if not enable_persist_data_db:
return {}
db = __create_connection()
if db is None:
return None
try:
cursor = db.cursor()
__try_create_exchange_table(cursor)
cursor.execute("SELECT * FROM exchanges")
crypto_exchanges = {}
records = cursor.fetchall()
for row in records:
crypto_exchange = CryptoExchange(row[0], row[1], row[2], row[3])
crypto_exchanges[row[0]] = crypto_exchange
return crypto_exchanges
except sqlite3.Error as e:
log_error(e)
finally:
db.close()
return None
def __create_connection():
""" create a database connection to a SQLite database """
conn = None
try:
conn = sqlite3.connect("crypto_exchanges_db.db")
conn.commit()
except sqlite3.Error as e:
log_error(e)
return conn
def download_exchange_rate_for_currency(currency_in, currency_out):
if not download_exchange_rate_enabled:
return 1
try:
currencies = currency_in + "_" + currency_out
url = EXCHANGE_RATE_FOR_CURRENCY_SERVER.format(currencies)
res = requests.get(url)
exchange_rate = json.loads(res.content)[currencies]["val"]
return exchange_rate
except Exception as e:
log_error(e)
return 1 # return default value
def download_exchange_rate_for_crypto_currency(currency, crypto_currency):
if not download_exchange_rate_enabled:
return 1
url = EXCHANGE_RATE_FOR_CRYPTO_CURRENCY_SERVER.format(crypto_currency, currency)
# key for getting exchange rates
headers = {'X-CoinAPI-Key': '5DB08D8A-C2DD-4592-AD7E-B7C7F4F875E2'}
try:
res = requests.get(url, headers=headers)
data = json.loads(res.content)["rate"]
print("Price: " + str(data))
return data
except Exception as e:
log_error(e)
return 1 # default value
def remove_all_data():
db = __create_connection()
if db is None:
return None
try:
cursor = db.cursor()
cursor.execute("DROP TABLE exchanges;")
cursor.execute("DROP TABLE crypto_currencies;")
cursor.execute("DROP TABLE trades;")
except sqlite3.Error as e:
log_error(e)
finally:
db.close()
return None
def get_trades(self,
exchange_id=0,
offset=0,
limit=-1,
search='',
date_from=None,
date_to=None):
# if data is not loaded from db, will be loaded
if SqlLiteDbHelper.enable_persist_data_db and not self.__data_loaded_from_db:
self.trades_storage = load_trades_from_db()
self.__data_loaded_from_db = True
trades = []
try:
# convert time to timestamp
date_from_timestamp = self.__convert_date_to_timestamp(date_from)
date_to_timestamp = self.__convert_date_to_timestamp(date_to)
for trade in self.trades_storage:
if self.__validate_trade_depend_on_conditions(
trade, exchange_id, date_from_timestamp,
date_to_timestamp, search):
trades.append(trade)
if isinstance(offset, int) and offset > 0:
if offset > len(trades):
trades = []
else:
trades = trades[offset:len(trades) - 1]
if isinstance(limit, int) and limit > 0:
if limit < len(trades):
trades = trades[0:limit]
except Exception as e:
log_error(e)
return trades
def remove_crypto_currency_to_db(currency_id):
if not enable_persist_data_db:
return True
db = __create_connection()
if db is None:
return False
try:
cursor = db.cursor()
query = "DELETE FROM crypto_currencies WHERE id = ?"
values = (currency_id,)
cursor.execute(query, values)
db.commit()
return True
except sqlite3.Error as e:
log_error(e)
finally:
db.close()
return False
