CODEWITHSUNDEEP
pythonpython-3.xentity-frameworkormsqlalchemy
Michael Doleman
Michael Doleman

Reputation: 552

Python + SqlAlchemy: Add Parent/Child Records When Both Parent & Child are New

I've asked a similar question already, but I think it would be much clearer if, rather than provide samples of what I've already coded, I simply state the goal of the case I have. Here goes:

I have a table that is in a self-referential one-to-many relationship. I.e., it is a hierarchical table in the classic parent/child set-up. Nothing fancy about it, just a basic parent has many children, child has one parent.

I can create the table just fine in ORM. From there, however, I want to load the table with data from a JSON file. My JSON, of course, represents such that there are top-level "parent" entries which then, each, have n-number of "children," and each of those my have children, also, etc. For all those children records, I obviously can't refer back to a specific "parent.id," because parent is not yet persisted in the DB. And, also obviously, this problem exists in any case I want to add a wholly new graph of parent/child records (i.e., not just for the load-from-JSON example).

Accomplishing this feat in Entity Framework has always been pretty simple. So long as the relationships are correctly defined, it simply works, with all the proper sequencing of record adds.

In SqlAlchemy, it also seems to be entirely possible, but there's something wrong (I think) with the way I've been defining the relationships. In any way I've tried thus far, what I get is this error:

Class 'MyTableClass' is mapped, but this instance lacks instrumentation. This occurs when the instance is created before sqlalchemy.orm.mapper(MyTableClass) was called.

I don't know precisely how to read that, but what I think it's telling me is that it's trying to create child objects before parent is created. But I don't know, and it doesn't help me discover where my relationships are ill-defined.

Any guidance appreciated!

Upvotes: 2

Views: 7908

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123664

Based on the answers and comments from this question the following seems to work for me:

from sqlalchemy import create_engine, Column, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relation, sessionmaker

connection_url = "mssql+pyodbc://@localhost,49242/myDb?driver=ODBC+Driver+17+for+SQL+Server"
engine = create_engine(connection_url)

Base = declarative_base()


class Person(Base):
    __tablename__ = 'person'

    id = Column(Integer, autoincrement=True, primary_key=True)
    name = Column(String(50))
    _parent_id = Column(Integer, ForeignKey('person.id'))
    parent = relation('Person', remote_side=[id])
    children = relation('Person', remote_side=[_parent_id], uselist=True)

    def __repr__(self):
        return f"<Person(id={self.id}, name='{self.name}'>)"


Base.metadata.drop_all(engine, checkfirst=True)
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

parent = Person(name='Homer')
child = Person(name='Bart')
parent.children.append(child)
child = Person(name='Lisa')
parent.children.append(child)

print(f"{parent.name}'s (unpersisted) children: {parent.children}")
# Homer's (unpersisted) children: [<Person(id=None, name='Bart'>), <Person(id=None, name='Lisa'>)]

session.add(parent)
session.commit()

print(f"{parent.name}'s (persisted) children: {parent.children}")
# Homer's (persisted) children: [<Person(id=2, name='Bart'>), <Person(id=3, name='Lisa'>)]

child = session.query(Person).filter(Person.name=='Lisa').first()
print(f"{child.name}'s parent: {child.parent}")
# Lisa's parent: <Person(id=1, name='Homer'>)

with engine.begin() as conn:
    print(conn.execute("SELECT * FROM person").fetchall())
    # [(1, 'Homer', None), (2, 'Bart', 1), (3, 'Lisa', 1)]

Upvotes: 2

Related Questions