SQLAlchemy:添加自引用分层对象——导致 sqlalchemy.orm.exc.UnmappedInstanceError [英] SQLAlchemy: Add Self-referential Hierarchical Object -- Causing sqlalchemy.orm.exc.UnmappedInstanceError

问题描述

对 Python 相当陌生 &来自 C#/EF 世界的 SQLAlchemy；尝试在后者中做一些非常简单的事情，所以我认为至少在前者中它必须是可能的.

Fairly new to Python & SQLAlchemy from C#/EF world; attempting to do something which is pretty simple in the latter, so I assume it must at least be possible in the former.

问题:从 JSON 数据加载表，但表由具有自引用层次结构的对象定义，如下所示:

Problem: loading table from JSON data, but table is defined by an object which has a self-referential hierarchy, like so:

class TableMixin(object):
    def __init__(self, jsonData = None):
        if(jsonData is not None):
            self.__dict__ = json.loads(jsonData)

    @declared_attr
    def __tablename__(self):
        return self.__name__

    id = Column(Integer, primary_key = True)

class HierarchyMixin(object):
    @declared_attr
    def predecessor_id(self):
        return Column(Integer, ForeignKey(self.__name__ + '.id'))

    @declared_attr
    def successors(self):
        return relationship(self.__name__)

class Something(TableMixin, HierarchyMixin, Base):
    a_property = Column(String)
    b_property = Column(String)

然后我有一些 JSON 数据，例如:

And then I have some JSON data, like:

{
  "a_property":"some value",
  "b_property":"some other value",
  "successors":[
    {
      "a_property":"some value 1",
      "b_property":"some other value 1"
    },
    {
      "a_property":"some value 2",
      "b_property":"some other value 2"
      "successors":[
      {
        "a_property":"some value 3",
        "b_property":"some other value 3"
      }
    }
  ]
}

我的目标是将其加载到我的数据库中.为此，我有:

And my goal is to load that to my database. For that I have:

import json
from MyEntities import Something
from sqlalchemy import Table, MetaData, Column, Integer, String, ForeignKey, create_engine
from sqlalchemy.orm import mapper, sessionmaker

db_engine = create_engine('sqlite:///my.db', echo=True)
db_conn = db_engine.connect()
Session = sessionmaker(bind = db_engine)
db_sesn = Session()

with open('MyJsonData.json', encoding='utf-8-sig') as json_data_file:
    data = Something(json_data_file.read())

db_sesn.add(data)
db_sesn.commit()

它不起作用.我得到:

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

我四处探索，发现一切似乎都很好，因为所有数据都将加载并创建对象层次结构.一旦创建，我可以通过访问该数据"对象的各种成员来确认.在我尝试添加到数据库时出现错误.

I've poked around and found that everything seems to be okay, in the sense that all the data will load and create and object hierarchy. I can confirm by accessing various members of that "data" object, once created. The error arises at the time I attempt the add to the database.

据我所知，这不应该是我使用 SQLAlchemy 所能做的范围之外的事情.但就我的生活而言，我找不到一个具体的例子来涵盖我的情况.

From everything I can tell, this ought not be something simply outside the scope of what I can do with SQLAlchemy. But for the life of me I cannot find a specific example to cover the case I have.

我会请求暂时原谅一些可能是非pythonic"的做事模式——我还在学习:-)

I'll ask to be excused for the time-being for what might be a few "unpythonic" modes of doing things -- I'm still learning :-)

推荐答案

我认为只是我对 Python 和 SqlAlchemy 的陌生让我无法立即看到解决方案，这实际上非常简单.

I think it's only my newness to Python and SqlAlchemy which prevented me from seeing the solution to this right away, which is actually pretty simple.

坚持我最初的代码示例，我做到了:

Sticking with my initial code example, I did:

class HierarchyMixin(object):    
    def __init__(self, data = None):
        if data is not None:
            if 'successors' in data:
                self.successors = list(map(self.__class__, data['successors']))
            if 'a_property' in data:
                self.a_property = data['a_property']
            if 'b_property' in data:
                self.b_property = data['b_property']

    @declared_attr
    def predecessor_id(self):
        return Column(Integer, ForeignKey(self.__name__ + '.id'))

    @declared_attr
    def successors(self):
        return relationship(self.__name__)

    a_property = Column(String)
    b_property = Column(String)

class Something(TableMixin, HierarchyMixin, Base):
    pass

我真正在做的就是为 successors 属性分配一个列表，该列表将每个成员映射到类的一个新实例.而且……嗯，差不多就是这样.

All I really am doing, there, is assigning a list to the successors property, and that list maps each member to a new instance of the class. And... well, that's pretty-much it.

然后，对于 Json 数据文件，我只是:

And then, with the Json data file, I just:

with open('MyJsonData.json', encoding = 'utf-8-sig') as json_data:
    not_nothing = Something(json.loads(json_data.read()))

这就是全部.分层数据结构作为关系导入，并正确设置了所有的前驱_id 键.

And that's all there is to it. The hierarchical data structure imports as relational, with all the predecessor_id keys properly set.

我对自己没有立即看到如此明显简单的事情感到有点恼火，但我想这可以归结为学习一门新语言而不是获得 Python.变化很快:-)

I'm just kinda annoyed with myself for not seeing such an obviously simple thing right off the bat, but I guess chalk it up to learning a new language and not quite getting Python. That's changing fast :-)

这篇关于SQLAlchemy:添加自引用分层对象——导致 sqlalchemy.orm.exc.UnmappedInstanceError的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！