Suppose I have a table "nodes" where I store a tree. Each node has a primary key id and a column parent_id. Of course, I want to access a parent attribute of each node instance, that is, a relation. One might 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 that, I get an error:
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 don't understand at what point I could set that option 'useexisting=True'
. Is this the right way?
EDIT: In fact, the error comes indirectly from another part of the original script. If one replaces the database path by the temporary database :memory:
, it works without problems. Thanks to TokenMacGuy.
So the above example can be considered as a working example.