Database

This class provides a convenient interface for managing a database to store asset price data from different exchanges and timeframes. It utilizes SQLAlchemy as the ORM and SQLite as the default database. Users can create tables for specific exchanges and timeframes, add or update data, query data, and perform basic database management tasks such as backing up and restoring the database.

Here's a high-level overview of the TorchtraderDatabase class:

classDiagram
    class TorchtraderDatabase{
        +create_table(exchange, timeframe, is_crypto) : Base
        +create_database(database_uri) : None
        +check_and_create_db(database_uri) : None
        +add_data(data, table) : None
        +add_single_datapoint(table, data, inserted_ids) : None
        +test_db() : None
        +remove_table(table) : None
        +remove_data_in_range(table, start_datetime, end_datetime) : None
        +connect_database(database_uri) : None
        +close_session() : None
        +execute_sql(sql) : None
        +backup_database(backup_path) : None
        +restore_database(backup_path) : None
        +query_data(table, filters) : List[Base]
        +update_data(table, filters, new_values) : None
    }

The TorchtraderDatabase class offers the following methods:

create_table: Creates a new table for the specified exchange and timeframe.
create_database: Creates a new SQLite database at the specified URI.
check_and_create_db: Checks if the database exists and creates it if necessary.
add_data: Adds new data to the specified table. It can handle both single datapoints and bulk data.
add_single_datapoint: Adds a single data entry to the specified table.
test_db: Runs a series of tests on the database, including creating and removing test tables.
remove_table: Removes the specified table from the database.
remove_data_in_range: Removes data from the specified table within a given datetime range.
connect_database: Connects to the specified database or creates it if it doesn't exist.
close_session: Closes the current database session.
execute_sql: Executes the specified SQL command.
backup_database: Creates a backup of the database at the specified path.
restore_database: Restores the database from the specified backup path.
query_data: Queries data from the specified table with optional filters.
update_data: Updates data in the specified table with the given filters and new values.

To use this class, you have to create an instance of TorchtraderDatabase and call the desired methods. For example, to create a new database and add some data to a table:

from torchtrader.database import TorchtraderDatabase

db = TorchtraderDatabase()
db.connect_database()
binance_table = db.create_table("binance", "1h", is_crypto=True)
data = {
    "date_time": datetime(2023, 4, 16, 12, 0, 0),
    "asset_id": "BTC",
    "base_currency": "BTC",
    "quote_currency": "USDT",
    "value": 60000,
    "exchange": "binance",
    "timeframe": "1h",
}
db.add_data(data, binance_table)
db.close_session()

In this example, we create a new TorchtraderDatabase instance, connect to the database, create a table for Binance 1-hour data, add a data entry to the table, and then close the session

This script provides a module for managing a SQLite database using SQLAlchemy ORM. The database is designed to store asset data from different exchanges and timeframes for use in the Torchtrader application.

The main class, TorchtraderDatabase, is responsible for creating, connecting, and managing the database. It also provides methods for adding, updating, querying, and removing data from the tables.

The ExchangeBase class is a base class for creating exchange-specific tables that inherit its attributes and methods.

`TorchtraderDatabase`

TorchtraderDatabase is a class for managing the SQLite database. It provides methods for creating, connecting, and managing the database as well as adding, updating, querying, and removing data from the tables.

Attributes:

Name	Type	Description
`database_uri`	`str`	The URI of the database.
`db_session`	`Session`	The database session object.
`db_engine`	`Engine`	The database engine object.

Usage

database = TorchtraderDatabase() database.connect_database() database.test_db() database.close_session()

`init(database_uri='sqlite:///torchtrader.db')`

Initializes a new TorchtraderDatabase object.

Parameters:

Name	Type	Description	Default
`database_uri`	`str`	The URI of the database. Default is "sqlite:///torchtrader.db".	`'sqlite:///torchtrader.db'`

`add_data(data, table)`

Adds data to the specified table.

Parameters:

Name	Type	Description	Default
`data`	`Union[Dict[str, any], Dict[str, Dict[str, any]]]`	The data to be added.	required
`table`	`Type[Base]`	The table class where the data will be added.	required

Raises:

Type	Description
`ValueError`	If the data is not a dictionary or a dictionary of dictionaries.

`add_single_datapoint(table, data, inserted_ids)`

Adds a single data point to the specified table.

Parameters:

Name	Type	Description	Default
`table`	`Type[Base]`	The table class where the data will be added.	required
`data`	`Dict[str, any]`	The data to be added.	required
`inserted_ids`	`List[int]`	The list of inserted primary key ids.	required

Returns:

Type	Description
`None`	List[int]: The list of inserted primary key ids.

`backup_database(backup_path)`

Creates a backup of the database.

Parameters:

Name	Type	Description	Default
`backup`	`backup_path (str`	The path where the backup will be saved.	required

`check_and_create_db(database_uri='sqlite:///torchtrader.db')`

Checks if the specified database exists, and if not, creates it.

Parameters:

Name	Type	Description	Default
`database_uri`	`str`	The URI of the database. Default is "sqlite:///torchtrader.db".	`'sqlite:///torchtrader.db'`

`close_session()`

Closes the current database session.

`connect_database(database_uri='sqlite:///torchtrader.db')`

Connects to the specified database or creates it if it doesn't exist.

Parameters:

Name	Type	Description	Default
`database_uri`	`str`	The URI of the database. Default is "sqlite:///torchtrader.db".	`'sqlite:///torchtrader.db'`

`create_database(database_uri='sqlite:///torchtrader.db')`

Creates a new SQLite database.

Parameters:

Name	Type	Description	Default
`database_uri`	`str`	The URI of the database. Default is "sqlite:///torchtrader.db".	`'sqlite:///torchtrader.db'`

`create_table(exchange, timeframe, is_crypto=True)` `staticmethod`

Creates a new table class for the specified exchange and timeframe.

Parameters:

Name	Type	Description	Default
`exchange`	`str`	The name of the exchange.	required
`timeframe`	`str`	The timeframe for the data.	required
`is_crypto`	`bool`	Whether the asset is a cryptocurrency. Default is True.	`True`

Returns:

Name	Type	Description
`Base`	`Base`	The newly created table class.

Raises:

Type	Description
`AssertionError`	If the specified timeframe is not in the list of allowed timeframes.

`execute_sql(sql)`

Executes the specified SQL command.

Parameters:

Name	Type	Description	Default
`sql`	`str`	The SQL command to be executed.	required

`query_data(table, filters=None)`

Queries data from the specified table with optional filters.

Parameters:

Name	Type	Description	Default
`table`	`Type[Base]`	The table class to query data from.	required
`filters`	`Dict[str, any]`	A dictionary of filters to apply. Default is None.	`None`

Returns:

Type	Description
`None`	List[Base]: A list of rows matching the filters.

`remove_data_in_range(table, start_datetime, end_datetime)`

Removes data from the specified table within a given datetime range.

Parameters:

Name	Type	Description	Default
`table`	`Type[Base]`	The table class where data will be removed.	required
`start_datetime`	`datetime`	The start datetime of the range.	required
`end_datetime`	`datetime`	The end datetime of the range.	required

`remove_table(table)`

Removes the specified table from the database.

Parameters:

Name	Type	Description	Default
`table`	`Type[Base]`	The table class to be removed.	required

`restore_database(backup_path)`

Restores the database from the specified backup.

Parameters:

Name	Type	Description	Default
`backup_path`	`str`	The path of the backup file.	required

`test_db()`

Tests the database by adding and removing sample data.

`update_data(table, filters, new_values)`

Updates data in the specified table with the given filters and new values.

Parameters:

Name	Type	Description	Default
`table`	`Type[Base]`	The table class to update data in.	required
`filters`	`Dict[str, any]`	A dictionary of filters to apply.	required
`new_values`	`Dict[str, any]`	A dictionary of new values to update the data with.	required

Database

TorchtraderDatabase

__init__(database_uri='sqlite:///torchtrader.db')

add_data(data, table)

add_single_datapoint(table, data, inserted_ids)

backup_database(backup_path)

check_and_create_db(database_uri='sqlite:///torchtrader.db')

close_session()

connect_database(database_uri='sqlite:///torchtrader.db')

create_database(database_uri='sqlite:///torchtrader.db')

create_table(exchange, timeframe, is_crypto=True) staticmethod

execute_sql(sql)

query_data(table, filters=None)

remove_data_in_range(table, start_datetime, end_datetime)

remove_table(table)

restore_database(backup_path)

test_db()

update_data(table, filters, new_values)

`TorchtraderDatabase`

`init(database_uri='sqlite:///torchtrader.db')`

`add_data(data, table)`

`add_single_datapoint(table, data, inserted_ids)`

`backup_database(backup_path)`

`check_and_create_db(database_uri='sqlite:///torchtrader.db')`

`close_session()`

`connect_database(database_uri='sqlite:///torchtrader.db')`

`create_database(database_uri='sqlite:///torchtrader.db')`

`create_table(exchange, timeframe, is_crypto=True)` `staticmethod`

`execute_sql(sql)`

`query_data(table, filters=None)`

`remove_data_in_range(table, start_datetime, end_datetime)`

`remove_table(table)`

`restore_database(backup_path)`

`test_db()`

`update_data(table, filters, new_values)`