Sqlalchemy utilize foreign key in table creation

Question

I've began learning sqlalchemy and the script below shows how far I've gotten. I've created a class to start the database, a class that creates a table containing games and their ID's and a class that is used to create tables for each individual external data source.

I realized I need to incorporate foreign keys but upon doing so I get the error below. It's very confusing because I'm fairly certain the MLBGamelist table was created. Any help on this wuld be greatly appreciated.

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'CaesarsGamelist.game_ref' could not find table 'MLBGamelist' with which to generate a foreign key to target column 'game_ref'

class DBControl:
    def __init__(self,mem):
        print('>>>> [MAIN]: INITIALIZING MAIN DATABASE CONNECTION')
        self.engine = create_engine(memory[mem], echo=False)
        self.inspector = inspect(self.engine)
        self.db_connection = self.engine.connect()
        self.create_session = sessionmaker(bind=self.engine)


class GamelistMLBControl:
    def __init__(self,book,db_control):
        self.table_name = f'{book}Gamelist'
        self.db_control = db_control
            
    def commit_entry(self,site_data):
        write_session = scoped_session(self.db_control.create_session)
        insert_stmt = insert(self.check_table()).values(site_data)
        write_session.execute(insert_stmt)
        write_session.commit()
        write_session.remove()

    def check_table(self):
        metadata = MetaData(bind=self.db_control.engine)
        if self.table_name not in self.db_control.inspector.get_table_names():
            table_name = Table(
                str(self.table_name),
                metadata,
                Column("event_id", Integer, primary_key=True),
                Column("game_ref", String),
                Column("game_datetime", Integer),
                Column("book", String),
            )
    
            metadata.create_all(self.db_control.db_connection)
            print(f'> [{self.table_name}]: Table created')
        else:
            metadata.reflect(self.db_control.engine)
            print(f'> [{self.table_name}]: Table exists')

        return Table(table_name, metadata, autoload=True)

    
class GamelistControl:
    def __init__(self,book,db_control):
        self.table_name = f'{book}Gamelist'
        self.db_control = db_control
            
    def commit_entry(self,site_data):
        write_session = scoped_session(self.db_control.create_session)
        insert_stmt = insert(self.check_table()).values(site_data)
        write_session.execute(insert_stmt)
        write_session.commit()
        write_session.remove()

    def check_table(self):
        metadata = MetaData(bind=self.db_control.engine)
        if self.table_name not in self.db_control.inspector.get_table_names():
            table_name = Table(
                str(self.table_name),
                metadata,
                Column("event_id", Integer, primary_key=True),
                Column("game_ref", String, ForeignKey('MLBGamelist.game_ref')),
                Column("game_datetime", Integer, ForeignKey('MLBGamelist.game_datetime')),
                Column("book", String),
            )
    
            metadata.create_all(self.db_control.db_connection)
            print(f'> [{self.table_name}]: Table created')
        else:
            metadata.reflect(self.db_control.engine)
            print(f'> [{self.table_name}]: Table exists')

        return Table(table_name, metadata, autoload=True)

You can use create_engine(…, echo=True) to check if the parent table is being created before the child table. — Gord Thompson
– Gord Thompson, Commented Aug 17, 2022 at 15:01
@GordThompson I just did that and the parent table was created. Very confused. — Nick
– Nick, Commented Aug 17, 2022 at 15:19

snakecharmerb · Accepted Answer · 2022-08-18 07:52:53Z

2

SQLAlchemy stores details of tables in MetaData objects when a table is created or reflected. Binding an engine to a MetaData object does not in itself cause any table details to be stored. In the code in the question, each class creates a separate MetaData instance, hence the error occurs because the Metadata instance created in GamelistControl doesn't have details of the table created in GamelistMLBControl.

To solve the problem, either call metadata.reflect() after creating the MetaData instance in GamelistControl or create a single MetaData as an attribute of DBControl and use that to create tables in both classes.

answered Aug 18, 2022 at 7:52

snakecharmerb

57.2k13 gold badges137 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nick Over a year ago

This makes perfect sense! Thank you!

Gord Thompson Over a year ago

Note also that MetaData(bind=engine) is deprecated in SQLAlchemy 1.4 and will go away in 2.0.

Collectives™ on Stack Overflow

Sqlalchemy utilize foreign key in table creation

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related