Creating a Foreign Key in MySQL Using SQLAlchemy ORM in Python

Question

I have the following class which creates 3 tables in a MySQL database, I would like the tables to be connected with primary key for the first table and foreign keys in the other tables that link to that primary key.

I've constructed this using SQLAlchemy in Python

class Database:
    def __init__(self):
        self.engine = sqlalchemy.create_engine(
            'mysql+mysqldb://{}:{}@{}/{}'.format(self.USER, self.PASSWORD,
                                                 self.HOST, self.DB_NAME))
        self.create_strategies_table()
        self.create_renko_bars_table()
        self.create_trades_table()

    def create_strategies_table(self):
        meta = MetaData()
        self.strategies = Table(
            'strategies',
            meta,

            # PRIMARY KEY
            Column('id', CHAR(32), primary_key=True, nullable=False),

            Column('start_time', DATETIME, nullable=False),
            Column('symbol', TEXT(), nullable=False),
            Column('brick_size', DOUBLE(), nullable=False),
            Column('method', TEXT(), nullable=False),
            Column('entry_points', TEXT(), nullable=True),
            Column('exit_points', TEXT(), nullable=True),
            Column('take_profit', INTEGER(), nullable=True),
            Column('stop_loss', INTEGER(), nullable=True),
            Column('balance', DOUBLE(), nullable=True),
            Column('trades', INTEGER(), nullable=True),
        )
        meta.create_all(self.engine, tables=[self.strategies], checkfirst=True)

    def create_renko_bars_table(self):
        meta = MetaData()
        self.renko_bars = Table(
            'renko_bars',
            meta,

            # ADD FOREIGN KEY HERE -> strategies.id
            Column(CHAR(32), ForeignKey('strategies.id'), nullable=False),

            Column('price_last', DOUBLE(), nullable=False),
            Column('price_renko', DOUBLE(), nullable=False),
            Column('price_min', DOUBLE(), nullable=False),
            Column('price_max', DOUBLE(), nullable=False),
            Column('dt_start', DATETIME(), nullable=False),
            Column('dt_end', DATETIME(), nullable=False),
            Column('trend', INTEGER(), nullable=False),
            Column('volume', BIGINT(), nullable=False),
            Column('count', INTEGER(), nullable=False),
            Column('cons_up', INTEGER(), nullable=False),
            Column('cons_down', INTEGER(), nullable=False),
        )
        meta.create_all(self.engine, tables=[self.renko_bars], checkfirst=True)

    def create_trades_table(self):
        meta = MetaData()
        self.trades = Table(
            'trades',
            meta,

            # ADD FOREIGN KEY HERE -> strategies.id
            Column(CHAR(32), ForeignKey('strategies.id'), nullable=False),

            Column('symbol', TEXT(), nullable=False),
            Column('side', INTEGER(), nullable=False),
            Column('size', BIGINT(), nullable=False),
            Column('entry_time', DATETIME(), nullable=False),
            Column('exit_time', DATETIME(), nullable=False),
            Column('entry_price', DOUBLE(), nullable=False),
            Column('exit_price', DOUBLE(), nullable=False),
            Column('entry_reason', TEXT(), nullable=True),
            Column('exit_reason', TEXT(), nullable=True),
            Column('pal', DOUBLE(), nullable=False),
            Column('net_pal', DOUBLE(), nullable=False),
            Column('commissions', DOUBLE(), nullable=False),
        )
        meta.create_all(self.engine, tables=[self.trades], checkfirst=True)

I've noticed in other threads that others are using SQLAlchemy to build a model (a class) to construct the tables, I was not sure how you'd do it in the way I am using it.

What would be the proper way to implement said foreign keys here?

Thank you!

Creating a Foreign Key in MySQL Using SQLAlchemy ORM in Python

0 Answers0