CODEWITHSUNDEEP
pythonmysqlsqlalchemyforeign-keys
orie
orie

Reputation: 571

SQLAlchemy Error Trying To Add Foreign Key

I have the following script which attempts to create 3 tables if they don't exist yet in my database.

I am using MySQL as database engine.

    class Journal:
        USER = '******'
        PASSWORD = '******'
        HOST = '******'
        DB_NAME = 'trades_test'
    
        def __init__(self):
            self.engine = create_engine(
                f'mysql+mysqlconnector://{self.USER}:{self.PASSWORD}@{self.HOST}/{self.DB_NAME}'
            )
            self.create_openings_table()
            self.create_closings_table()
            self.create_adjustments_table()
    
        def create_openings_table(self):
            meta = MetaData(self.engine)
            self.openings = Table(
                'openings',
                meta,
                Column('trade_id',
                       INTEGER(unsigned=True),
                       primary_key=True,
                       autoincrement=True),
                Column('opened_at', DATE(), nullable=False),
                Column('underlying', VARCHAR(5), nullable=False),
                Column('underlying_price', FLOAT(2), nullable=False),
                Column('iv_rank', SMALLINT(), nullable=False),
                Column('strategy', VARCHAR(20), nullable=False),
                Column('quantity', SMALLINT(), nullable=False),
                Column('expiration_date', DATE(), nullable=False),
                Column('option_types', JSON()),
                Column('strikes', JSON(), nullable=False),
                Column('premium', FLOAT(2), nullable=False),
                Column('prob_of_profit', FLOAT(2), nullable=False),
                Column('margin', FLOAT(2), nullable=False),
                Column('notes', TEXT()))
            meta.create_all()
    
        def create_closings_table(self):
            meta = MetaData(self.engine)
            self.closings = Table(
                'closings',
                meta,
                Column('id',
                       INTEGER(unsigned=True),
                       primary_key=True,
                       autoincrement=True),
                # FOREIGN KEY - fk_closings_trade_id
                Column('trade_id', ForeignKey('openings.trade_id')),
                Column('closed_at', DATE(), nullable=False),
                Column('underlying_price', FLOAT(2), nullable=False),
                Column('iv_rank', SMALLINT(), nullable=False),
                Column('premium', FLOAT(2), nullable=False),
                Column('margin', FLOAT(2), nullable=False),
                Column('notes', TEXT()),
            )
            meta.create_all()
    
        def create_adjustments_table(self):
            meta = MetaData(self.engine)
            self.adjustments = Table(
                'adjustments',
                meta,
                Column('id',
                       INTEGER(unsigned=True),
                       primary_key=True,
                       autoincrement=True),
                # FOREIGN KEY - fk_adj_trade_id
                Column('trade_id', ForeignKey('openings.trade_id')),
                Column('adjusted_at', DATE(), nullable=False),
                Column('underlying_price', FLOAT(2), nullable=False),
                Column('iv_rank', SMALLINT(), nullable=False),
                Column('quantity', SMALLINT()),
                Column('premium', FLOAT(2)),
                Column('option_types', JSON()),
                Column('strikes', JSON()),
                Column('expiration_date', DATE()),
                Column('margin', FLOAT(2)),
                Column('notes', TEXT()),
            )
            meta.create_all()

This code produces this error:

Traceback (most recent call last):
  File "/Users/or/Desktop/Or/Options/journal/journal.py", line 105, in <module>
    Journal()
  File "/Users/or/Desktop/Or/Options/journal/journal.py", line 16, in __init__
    self.create_closings_table()
  File "/Users/or/Desktop/Or/Options/journal/journal.py", line 61, in create_closings_table
    meta.create_all()
  File "/Users/or/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 4744, in create_all
    bind._run_ddl_visitor(
  File "/Users/or/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3008, in _run_ddl_visitor
    conn._run_ddl_visitor(visitorcallable, element, **kwargs)
  File "/Users/or/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2016, in _run_ddl_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "/Users/or/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 483, in traverse_single
    return meth(obj, **kw)
  File "/Users/or/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 822, in visit_metadata
    collection = sort_tables_and_constraints(
  File "/Users/or/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 1286, in sort_tables_and_constraints
    dependent_on = fkc.referred_table
  File "/Users/or/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 3671, in referred_table
    return self.elements[0].column.table
  File "/Users/or/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 1093, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/Users/or/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 2376, in column
    raise exc.NoReferencedTableError(
sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'closings.trade_id' could not find table 'openings' with which to generate a foreign key to target column 'trade_id'

I would like the primary key of the first table (trade_id) to serve as a foreign key in the other two tables.

I've also seen other ways of constructing the tables, mainly combined with Flask, that create subclasses of a Model class and fill out the details of the table there, what would be the more correct way to construct a small database application like this?

Upvotes: 1

Views: 969

Answers (1)

rfkortekaas
rfkortekaas

Reputation: 6474

The issue with the primary key happens because the MetaData object is a storage object for a series of tables. When defining a foreign key it looks into the MetaData object for the relevant table to map to. As you are redefining the MetaData object in the different create functions they are all stored in a different MetaData object. Due to this the foreign key cannot be looked up during the creation of the second table with a relation to the first table.

The solution is to define the MetaData object once and refer each Table object to this MetaData object.

See Working with Database Metadata for more information about this.

Also you don't have to call create_all in each create function but it can be called once at the end of __init__.

Regarding your last question about the different methods is that your method is mainly SQLAlchemy Core. When using subclasses of Base you are getting into SQLAlchemy ORM. Here the differences are explained a little bit more: What is the difference between SQLAlchemy Core and ORM

Upvotes: 2

Related Questions