def test_migrate_mid_state(mocker, default_conf, fee, caplog):
"""
Test Database migration (starting with new pairformat)
"""
caplog.set_level(logging.DEBUG)
amount = 103.223
create_table_old = """CREATE TABLE IF NOT EXISTS "trades" (
id INTEGER NOT NULL,
exchange VARCHAR NOT NULL,
pair VARCHAR NOT NULL,
is_open BOOLEAN NOT NULL,
fee_open FLOAT NOT NULL,
fee_close FLOAT NOT NULL,
open_rate FLOAT,
close_rate FLOAT,
close_profit FLOAT,
stake_amount FLOAT NOT NULL,
amount FLOAT,
open_date DATETIME NOT NULL,
close_date DATETIME,
open_order_id VARCHAR,
PRIMARY KEY (id),
CHECK (is_open IN (0, 1))
);"""
insert_table_old = """INSERT INTO trades (exchange, pair, is_open, fee_open, fee_close,
open_rate, stake_amount, amount, open_date)
VALUES ('binance', 'ETC/BTC', 1, {fee}, {fee},
0.00258580, {stake}, {amount},
'2019-11-28 12:44:24.000000')
""".format(fee=fee.return_value,
stake=default_conf.get("stake_amount"),
amount=amount
)
engine = create_engine('sqlite://')
mocker.patch('freqtrade.persistence.models.create_engine', lambda *args, **kwargs: engine)
# Create table using the old format
engine.execute(create_table_old)
engine.execute(insert_table_old)
# Run init to test migration
init_db(default_conf['db_url'], default_conf['dry_run'])
assert len(Trade.query.filter(Trade.id == 1).all()) == 1
trade = Trade.query.filter(Trade.id == 1).first()
assert trade.fee_open == fee.return_value
assert trade.fee_close == fee.return_value
assert trade.open_rate_requested is None
assert trade.close_rate_requested is None
assert trade.is_open == 1
assert trade.amount == amount
assert trade.stake_amount == default_conf.get("stake_amount")
assert trade.pair == "ETC/BTC"
assert trade.exchange == "binance"
assert trade.max_rate == 0.0
assert trade.stop_loss == 0.0
assert trade.initial_stop_loss == 0.0
assert trade.open_trade_value == trade._calc_open_trade_value()
assert log_has("trying trades_bak0", caplog)
assert log_has("Running database migration for trades - backup: trades_bak0", caplog)
def test_init_dryrun_db(default_conf, tmpdir):
filename = f"{tmpdir}/freqtrade2_prod.sqlite"
assert not Path(filename).is_file()
default_conf.update({'dry_run': True, 'db_url': f'sqlite:///{filename}'})
init_db(default_conf['db_url'], default_conf['dry_run'])
assert Path(filename).is_file()
def start_show_trades(args: Dict[str, Any]) -> None:
"""
Show trades
"""
import json
from freqtrade.persistence import Trade, init_db
config = setup_utils_configuration(args, RunMode.UTIL_NO_EXCHANGE)
if 'db_url' not in config:
raise OperationalException("--db-url is required for this command.")
logger.info(f'Using DB: "{config["db_url"]}"')
init_db(config['db_url'], clean_open_orders=False)
tfilter = []
if config.get('trade_ids'):
tfilter.append(Trade.id.in_(config['trade_ids']))
trades = Trade.get_trades(tfilter).all()
logger.info(f"Printing {len(trades)} Trades: ")
if config.get('print_json', False):
print(json.dumps([trade.to_json() for trade in trades], indent=4))
else:
for trade in trades:
print(trade)
def test_init_custom_db_url(default_conf, mocker):
# Update path to a value other than default, but still in-memory
default_conf.update({'db_url': 'sqlite:///tmp/freqtrade2_test.sqlite'})
create_engine_mock = mocker.patch('freqtrade.persistence.models.create_engine', MagicMock())
init_db(default_conf['db_url'], default_conf['dry_run'])
assert create_engine_mock.call_count == 1
assert create_engine_mock.mock_calls[0][1][0] == 'sqlite:///tmp/freqtrade2_test.sqlite'
def test_stoploss_reinitialization(default_conf, fee):
init_db(default_conf['db_url'])
trade = Trade(
pair='ETH/BTC',
stake_amount=0.001,
fee_open=fee.return_value,
open_date=arrow.utcnow().shift(hours=-2).datetime,
amount=10,
fee_close=fee.return_value,
exchange='bittrex',
open_rate=1,
max_rate=1,
)
trade.adjust_stop_loss(trade.open_rate, 0.05, True)
assert trade.stop_loss == 0.95
assert trade.stop_loss_pct == -0.05
assert trade.initial_stop_loss == 0.95
assert trade.initial_stop_loss_pct == -0.05
Trade.session.add(trade)
# Lower stoploss
Trade.stoploss_reinitialization(0.06)
trades = Trade.get_open_trades()
assert len(trades) == 1
trade_adj = trades[0]
assert trade_adj.stop_loss == 0.94
assert trade_adj.stop_loss_pct == -0.06
assert trade_adj.initial_stop_loss == 0.94
assert trade_adj.initial_stop_loss_pct == -0.06
# Raise stoploss
Trade.stoploss_reinitialization(0.04)
trades = Trade.get_open_trades()
assert len(trades) == 1
trade_adj = trades[0]
assert trade_adj.stop_loss == 0.96
assert trade_adj.stop_loss_pct == -0.04
assert trade_adj.initial_stop_loss == 0.96
assert trade_adj.initial_stop_loss_pct == -0.04
# Trailing stoploss (move stoplos up a bit)
trade.adjust_stop_loss(1.02, 0.04)
assert trade_adj.stop_loss == 0.9792
assert trade_adj.initial_stop_loss == 0.96
Trade.stoploss_reinitialization(0.04)
trades = Trade.get_open_trades()
assert len(trades) == 1
trade_adj = trades[0]
# Stoploss should not change in this case.
assert trade_adj.stop_loss == 0.9792
assert trade_adj.stop_loss_pct == -0.04
assert trade_adj.initial_stop_loss == 0.96
assert trade_adj.initial_stop_loss_pct == -0.04
def test_init_prod_db(default_conf, mocker):
default_conf.update({'dry_run': False})
default_conf.update({'db_url': constants.DEFAULT_DB_PROD_URL})
create_engine_mock = mocker.patch('freqtrade.persistence.models.create_engine', MagicMock())
init_db(default_conf['db_url'], default_conf['dry_run'])
assert create_engine_mock.call_count == 1
assert create_engine_mock.mock_calls[0][1][0] == 'sqlite:///tradesv3.sqlite'
def test_init_custom_db_url(default_conf, tmpdir):
# Update path to a value other than default, but still in-memory
filename = f"{tmpdir}/freqtrade2_test.sqlite"
assert not Path(filename).is_file()
default_conf.update({'db_url': f'sqlite:///{filename}'})
init_db(default_conf['db_url'], default_conf['dry_run'])
assert Path(filename).is_file()
def load_trades_from_db(db_url: str, strategy: Optional[str] = None) -> pd.DataFrame:
"""
Load trades from a DB (using dburl)
:param db_url: Sqlite url (default format sqlite:///tradesv3.dry-run.sqlite)
:param strategy: Strategy to load - mainly relevant for multi-strategy backtests
Can also serve as protection to load the correct result.
:return: Dataframe containing Trades
"""
init_db(db_url, clean_open_orders=False)
filters = []
if strategy:
filters.append(Trade.strategy == strategy)
trades = trade_list_to_dataframe(Trade.get_trades(filters).all())
return trades
def load_trades_from_db(db_url: str,
strategy: Optional[str] = None) -> pd.DataFrame:
"""
Load trades from a DB (using dburl)
:param db_url: Sqlite url (default format sqlite:///tradesv3.dry-run.sqlite)
:param strategy: Strategy to load - mainly relevant for multi-strategy backtests
Can also serve as protection to load the correct result.
:return: Dataframe containing Trades
"""
init_db(db_url, clean_open_orders=False)
columns = [
"pair", "open_date", "close_date", "profit", "profit_percent",
"open_rate", "close_rate", "amount", "trade_duration", "sell_reason",
"fee_open", "fee_close", "open_rate_requested", "close_rate_requested",
"stake_amount", "max_rate", "min_rate", "id", "exchange", "stop_loss",
"initial_stop_loss", "strategy", "timeframe"
]
filters = []
if strategy:
filters.append(Trade.strategy == strategy)
trades = pd.DataFrame(
[(t.pair, t.open_date.replace(tzinfo=timezone.utc),
t.close_date.replace(tzinfo=timezone.utc) if t.close_date else None,
t.calc_profit(), t.calc_profit_ratio(), t.open_rate, t.close_rate,
t.amount, (round(
(t.close_date.timestamp() - t.open_date.timestamp()) /
60, 2) if t.close_date else None), t.sell_reason, t.fee_open,
t.fee_close, t.open_rate_requested, t.close_rate_requested,
t.stake_amount, t.max_rate, t.min_rate, t.id, t.exchange,
t.stop_loss, t.initial_stop_loss, t.strategy, t.timeframe)
for t in Trade.get_trades(filters).all()],
columns=columns)
return trades
def test_migrate_new(mocker, default_conf, fee, caplog):
"""
Test Database migration (starting with new pairformat)
"""
caplog.set_level(logging.DEBUG)
amount = 103.223
# Always create all columns apart from the last!
create_table_old = """CREATE TABLE IF NOT EXISTS "trades" (
id INTEGER NOT NULL,
exchange VARCHAR NOT NULL,
pair VARCHAR NOT NULL,
is_open BOOLEAN NOT NULL,
fee FLOAT NOT NULL,
open_rate FLOAT,
close_rate FLOAT,
close_profit FLOAT,
stake_amount FLOAT NOT NULL,
amount FLOAT,
open_date DATETIME NOT NULL,
close_date DATETIME,
open_order_id VARCHAR,
stop_loss FLOAT,
initial_stop_loss FLOAT,
max_rate FLOAT,
sell_reason VARCHAR,
strategy VARCHAR,
ticker_interval INTEGER,
stoploss_order_id VARCHAR,
PRIMARY KEY (id),
CHECK (is_open IN (0, 1))
);"""
insert_table_old = """INSERT INTO trades (exchange, pair, is_open, fee,
open_rate, stake_amount, amount, open_date,
stop_loss, initial_stop_loss, max_rate, ticker_interval,
open_order_id, stoploss_order_id)
VALUES ('binance', 'ETC/BTC', 1, {fee},
0.00258580, {stake}, {amount},
'2019-11-28 12:44:24.000000',
0.0, 0.0, 0.0, '5m',
'buy_order', 'stop_order_id222')
""".format(fee=fee.return_value,
stake=default_conf.get("stake_amount"),
amount=amount)
engine = create_engine('sqlite://')
mocker.patch('freqtrade.persistence.models.create_engine',
lambda *args, **kwargs: engine)
# Create table using the old format
engine.execute(create_table_old)
engine.execute("create index ix_trades_is_open on trades(is_open)")
engine.execute("create index ix_trades_pair on trades(pair)")
engine.execute(insert_table_old)
# fake previous backup
engine.execute("create table trades_bak as select * from trades")
engine.execute("create table trades_bak1 as select * from trades")
# Run init to test migration
init_db(default_conf['db_url'], default_conf['dry_run'])
assert len(Trade.query.filter(Trade.id == 1).all()) == 1
trade = Trade.query.filter(Trade.id == 1).first()
assert trade.fee_open == fee.return_value
assert trade.fee_close == fee.return_value
assert trade.open_rate_requested is None
assert trade.close_rate_requested is None
assert trade.is_open == 1
assert trade.amount == amount
assert trade.amount_requested == amount
assert trade.stake_amount == default_conf.get("stake_amount")
assert trade.pair == "ETC/BTC"
assert trade.exchange == "binance"
assert trade.max_rate == 0.0
assert trade.min_rate is None
assert trade.stop_loss == 0.0
assert trade.initial_stop_loss == 0.0
assert trade.sell_reason is None
assert trade.strategy is None
assert trade.timeframe == '5m'
assert trade.stoploss_order_id == 'stop_order_id222'
assert trade.stoploss_last_update is None
assert log_has("trying trades_bak1", caplog)
assert log_has("trying trades_bak2", caplog)
assert log_has(
"Running database migration for trades - backup: trades_bak2", caplog)
assert trade.open_trade_price == trade._calc_open_trade_price()
assert trade.close_profit_abs is None
assert log_has("Moving open orders to Orders table.", caplog)
orders = Order.query.all()
assert len(orders) == 2
assert orders[0].order_id == 'buy_order'
assert orders[0].ft_order_side == 'buy'
assert orders[1].order_id == 'stop_order_id222'
assert orders[1].ft_order_side == 'stoploss'
def test_migrate_old(mocker, default_conf, fee):
"""
Test Database migration(starting with old pairformat)
"""
amount = 103.223
create_table_old = """CREATE TABLE IF NOT EXISTS "trades" (
id INTEGER NOT NULL,
exchange VARCHAR NOT NULL,
pair VARCHAR NOT NULL,
is_open BOOLEAN NOT NULL,
fee FLOAT NOT NULL,
open_rate FLOAT,
close_rate FLOAT,
close_profit FLOAT,
stake_amount FLOAT NOT NULL,
amount FLOAT,
open_date DATETIME NOT NULL,
close_date DATETIME,
open_order_id VARCHAR,
PRIMARY KEY (id),
CHECK (is_open IN (0, 1))
);"""
insert_table_old = """INSERT INTO trades (exchange, pair, is_open, open_order_id, fee,
open_rate, stake_amount, amount, open_date)
VALUES ('BITTREX', 'BTC_ETC', 1, '123123', {fee},
0.00258580, {stake}, {amount},
'2017-11-28 12:44:24.000000')
""".format(fee=fee.return_value,
stake=default_conf.get("stake_amount"),
amount=amount)
insert_table_old2 = """INSERT INTO trades (exchange, pair, is_open, fee,
open_rate, close_rate, stake_amount, amount, open_date)
VALUES ('BITTREX', 'BTC_ETC', 0, {fee},
0.00258580, 0.00268580, {stake}, {amount},
'2017-11-28 12:44:24.000000')
""".format(fee=fee.return_value,
stake=default_conf.get("stake_amount"),
amount=amount)
engine = create_engine('sqlite://')
mocker.patch('freqtrade.persistence.models.create_engine',
lambda *args, **kwargs: engine)
# Create table using the old format
engine.execute(create_table_old)
engine.execute(insert_table_old)
engine.execute(insert_table_old2)
# Run init to test migration
init_db(default_conf['db_url'], default_conf['dry_run'])
assert len(Trade.query.filter(Trade.id == 1).all()) == 1
trade = Trade.query.filter(Trade.id == 1).first()
assert trade.fee_open == fee.return_value
assert trade.fee_close == fee.return_value
assert trade.open_rate_requested is None
assert trade.close_rate_requested is None
assert trade.is_open == 1
assert trade.amount == amount
assert trade.amount_requested == amount
assert trade.stake_amount == default_conf.get("stake_amount")
assert trade.pair == "ETC/BTC"
assert trade.exchange == "bittrex"
assert trade.max_rate == 0.0
assert trade.stop_loss == 0.0
assert trade.initial_stop_loss == 0.0
assert trade.open_trade_price == trade._calc_open_trade_price()
assert trade.close_profit_abs is None
assert trade.fee_open_cost is None
assert trade.fee_open_currency is None
assert trade.fee_close_cost is None
assert trade.fee_close_currency is None
assert trade.timeframe is None
trade = Trade.query.filter(Trade.id == 2).first()
assert trade.close_rate is not None
assert trade.is_open == 0
assert trade.open_rate_requested is None
assert trade.close_rate_requested is None
assert trade.close_rate is not None
assert pytest.approx(trade.close_profit_abs) == trade.calc_profit()
assert trade.sell_order_status is None
# Should've created one order
assert len(Order.query.all()) == 1
order = Order.query.first()
assert order.order_id == '123123'
assert order.ft_order_side == 'buy'
def test_init_invalid_db_url(default_conf):
# Update path to a value other than default, but still in-memory
default_conf.update({'db_url': 'unknown:///some.url'})
with pytest.raises(OperationalException,
match=r'.*no valid database URL*'):
init_db(default_conf['db_url'], default_conf['dry_run'])
def test_init_create_session(default_conf):
# Check if init create a session
init_db(default_conf['db_url'], default_conf['dry_run'])
assert hasattr(Trade, 'session')
assert 'scoped_session' in type(Trade.session).__name__
def test_migrate_new(mocker, default_conf, fee, caplog):
"""
Test Database migration (starting with new pairformat)
"""
caplog.set_level(logging.DEBUG)
amount = 103.223
# Always create all columns apart from the last!
create_table_old = """CREATE TABLE IF NOT EXISTS "trades" (
id INTEGER NOT NULL,
exchange VARCHAR NOT NULL,
pair VARCHAR NOT NULL,
is_open BOOLEAN NOT NULL,
fee FLOAT NOT NULL,
open_rate FLOAT,
close_rate FLOAT,
close_profit FLOAT,
stake_amount FLOAT NOT NULL,
amount FLOAT,
open_date DATETIME NOT NULL,
close_date DATETIME,
open_order_id VARCHAR,
stop_loss FLOAT,
initial_stop_loss FLOAT,
max_rate FLOAT,
sell_reason VARCHAR,
strategy VARCHAR,
ticker_interval INTEGER,
stoploss_order_id VARCHAR,
PRIMARY KEY (id),
CHECK (is_open IN (0, 1))
);"""
insert_table_old = """INSERT INTO trades (exchange, pair, is_open, fee,
open_rate, stake_amount, amount, open_date,
stop_loss, initial_stop_loss, max_rate, ticker_interval,
open_order_id, stoploss_order_id)
VALUES ('binance', 'ETC/BTC', 1, {fee},
0.00258580, {stake}, {amount},
'2019-11-28 12:44:24.000000',
0.0, 0.0, 0.0, '5m',
'buy_order', 'stop_order_id222')
""".format(fee=fee.return_value,
stake=default_conf.get("stake_amount"),
amount=amount
)
engine = create_engine('sqlite://')
mocker.patch('freqtrade.persistence.models.create_engine', lambda *args, **kwargs: engine)
# Create table using the old format
with engine.begin() as connection:
connection.execute(text(create_table_old))
connection.execute(text("create index ix_trades_is_open on trades(is_open)"))
connection.execute(text("create index ix_trades_pair on trades(pair)"))
connection.execute(text(insert_table_old))
# fake previous backup
connection.execute(text("create table trades_bak as select * from trades"))
connection.execute(text("create table trades_bak1 as select * from trades"))
# Run init to test migration
init_db(default_conf['db_url'], default_conf['dry_run'])
assert len(Trade.query.filter(Trade.id == 1).all()) == 1
trade = Trade.query.filter(Trade.id == 1).first()
assert trade.fee_open == fee.return_value
assert trade.fee_close == fee.return_value
assert trade.open_rate_requested is None
assert trade.close_rate_requested is None
assert trade.is_open == 1
assert trade.amount == amount
assert trade.amount_requested == amount
assert trade.stake_amount == default_conf.get("stake_amount")
assert trade.pair == "ETC/BTC"
assert trade.exchange == "binance"
assert trade.max_rate == 0.0
assert trade.min_rate is None
assert trade.stop_loss == 0.0
assert trade.initial_stop_loss == 0.0
assert trade.sell_reason is None
assert trade.strategy is None
assert trade.timeframe == '5m'
assert trade.stoploss_order_id == 'stop_order_id222'
assert trade.stoploss_last_update is None
assert log_has("trying trades_bak1", caplog)
assert log_has("trying trades_bak2", caplog)
assert log_has("Running database migration for trades - backup: trades_bak2", caplog)
assert trade.open_trade_value == trade._calc_open_trade_value()
assert trade.close_profit_abs is None
assert log_has("Moving open orders to Orders table.", caplog)
orders = Order.query.all()
assert len(orders) == 2
assert orders[0].order_id == 'buy_order'
assert orders[0].ft_order_side == 'buy'
assert orders[1].order_id == 'stop_order_id222'
assert orders[1].ft_order_side == 'stoploss'
caplog.clear()
# Drop latest column
with engine.begin() as connection:
connection.execute(text("alter table orders rename to orders_bak"))
inspector = inspect(engine)
with engine.begin() as connection:
for index in inspector.get_indexes('orders_bak'):
connection.execute(text(f"drop index {index['name']}"))
# Recreate table
connection.execute(text("""
CREATE TABLE orders (
id INTEGER NOT NULL,
ft_trade_id INTEGER,
ft_order_side VARCHAR NOT NULL,
ft_pair VARCHAR NOT NULL,
ft_is_open BOOLEAN NOT NULL,
order_id VARCHAR NOT NULL,
status VARCHAR,
symbol VARCHAR,
order_type VARCHAR,
side VARCHAR,
price FLOAT,
amount FLOAT,
filled FLOAT,
remaining FLOAT,
cost FLOAT,
order_date DATETIME,
order_filled_date DATETIME,
order_update_date DATETIME,
PRIMARY KEY (id),
CONSTRAINT _order_pair_order_id UNIQUE (ft_pair, order_id),
FOREIGN KEY(ft_trade_id) REFERENCES trades (id)
)
"""))
connection.execute(text("""
insert into orders ( id, ft_trade_id, ft_order_side, ft_pair, ft_is_open, order_id, status,
symbol, order_type, side, price, amount, filled, remaining, cost, order_date,
order_filled_date, order_update_date)
select id, ft_trade_id, ft_order_side, ft_pair, ft_is_open, order_id, status,
symbol, order_type, side, price, amount, filled, remaining, cost, order_date,
order_filled_date, order_update_date
from orders_bak
"""))
# Run init to test migration
init_db(default_conf['db_url'], default_conf['dry_run'])
assert log_has("trying orders_bak1", caplog)
orders = Order.query.all()
assert len(orders) == 2
assert orders[0].order_id == 'buy_order'
assert orders[0].ft_order_side == 'buy'
assert orders[1].order_id == 'stop_order_id222'
assert orders[1].ft_order_side == 'stoploss'
