How to set up a table with recursive foreign key and relationships declaratively in SQLAlchemy?

Question

Suppose I have a “nodes” table where I store a tree. Each node has a primary key identifier and a parent_id column. Of course, I want to access the parent attribute of each node instance, that is, the relationship. You can also try:

import sqlalchemy, sqlalchemy.orm, sqlalchemy.ext.declarative
engine = sqlalchemy.create_engine('sqlite:///PATHTOMYDATABASE', echo=True)
Base = sqlalchemy.ext.declarative.declarative_base()
class Node(Base):
    __tablename__ = "nodes"
    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True) 
    parent_id = sqlalchemy.Column(sqlalchemy.Integer, sqlalchemy.ForeignKey("nodes.id"))
    parent = sqlalchemy.orm.relationship("Node", backref=sqlalchemy.orm.backref("childs"))
Base.metadata.create_all(engine)

But when I do this, I get an error message:

sqlalchemy.exc.InvalidRequestError: Table 'nodes' is already defined for this MetaData instance. Specify 'useexisting=True' to redefine options and columns on an existing Table object.

I do not understand at what point I could install this option 'useexisting=True'. Is it correct?

EDIT: actually the error is indirectly coming from another part of the original script. If you replace the database path with a temporary database :memory:, it works without problems. Thanks TokenMacGuy.

Thus, the above example can be considered as a working example.

Answer 1

- , , (, ). , python; sqlalchemy , ( MetaData). , ( python , ). , , - , - reload(), sys.modules.

, useexisting=True __table_args__:

class Node(Base):
    __tablename__ = "nodes"
    __table_args__ = {"useexisting": True}

, , , python.

, , , .