Python + SqlAlchemy:当父级和子级都添加时，添加父级/子级记录孩子是新来的 [英] 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.

我可以在ORM中创建表.但是，我要从那里使用JSON文件中的数据加载表.当然，我的JSON表示有一些顶级父"条目，然后每个条目都有n个子级"，而我的每个子级也都有子级，依此类推.对于所有这些子级记录，我显然不能引用特定的"parent.id"，因为父级尚未持久保存在数据库中.而且，显然，无论如何我都想添加一个父/子记录的全新图形(即，不仅仅用于从JSON加载示例)，还存在这个问题.

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).

在Entity Framework中实现这一壮举始终非常简单.只要正确定义了关系，它就可以起作用，并且对记录添加进行所有适当的排序.

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.

在SqlAlchemy中，这似乎也是完全可能的，但是我定义关系的方式存在某些问题(我认为).到目前为止，无论如何，我都会得到以下错误信息:

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.

任何指导表示赞赏！

推荐答案

基于此问题的答案和评论以下似乎对我有用:

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)

try:
    engine.execute(f"DROP TABLE person")
except:
    pass

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.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)]

这篇关于Python + SqlAlchemy:当父级和子级都添加时，添加父级/子级记录孩子是新来的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！