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