sqlalchemy一对多ORM更新错误

I have two tables: Eca_users and Eca_user_emails, one user can have many emails. I recive json with users and their emails. And I wont to load them into MS SQL database. Users can update their emails, so in this json I can get the same users with new (or changed) emails. My code

我有两个表:Eca_users和Eca_user_emails,一个用户可以有很多电子邮件。我用用户和他们的电子邮件回忆了json。我不会将它们加载到MS SQL数据库中。用户可以更新他们的电子邮件,因此在这个json中我可以通过新的(或更改的)电子邮件获得相同的用户。我的代码

# some import here
Base = declarative_base()

class Eca_users(Base):
    __tablename__ = 'eca_users'
    sql_id = sqlalchemy.Column(sqlalchemy.Integer(), primary_key = True)
    first_id = sqlalchemy.Column(sqlalchemy.String(15))
    name = sqlalchemy.Column(sqlalchemy.String(200))
    main_email = sqlalchemy.Column(sqlalchemy.String(200))
    user_emails = relationship("Eca_user_emails", backref=backref('eca_users'))

class Eca_user_emails(Base):
    __tablename__ = 'user_emails'
    sql_id = sqlalchemy.Column(sqlalchemy.Integer(), primary_key = True)
    email_address = Column(String(200), nullable=False)
    status = Column(String(10), nullable=False)
    active = Column(DateTime, nullable=True)
    sql_user_id = Column(Integer, ForeignKey('eca_users.sql_id'))

def main()
    engine = sqlalchemy.create_engine('mssql+pymssql://user:pass/ECAusers?charset=utf8')
    Session = sessionmaker()
    Session.configure(bind = engine)
    session = Session()

    #then I get my json, parse it and...
    query = session.query(Eca_users).filter(Eca_users.first_id == str(user_id))
    if query.count() == 0:
        # not interesting now
    else:
        for exstUser in query:
            exstUser.name = name  #update user info
            exstUser.user_emails = [:] # empty old emails
            # creating new Email obj
            newEmail = Eca_user_emails(email_address = email_record['email'],
                                       status = email_record['status'],
                                       active = active_date)
            exstUser.user_emails.append(newEmail) # and I get error here because autoflush


    session.commit()


if __name__ == '__main__':
    main()

Error message: sqlalchemy.exc.IntegrityError: ... [SQL: 'UPDATE user_emails SET sql_user_id=%(sql_user_id)s WHERE user_emails.sql_id = %(user_emails_sql_id)s'] [parameters: {'sql_user_id': None, 'user_emails_sql_id': Decimal('1')}]

错误消息:sqlalchemy.exc.IntegrityError:... [SQL:'UPDATE user_emails SET sql_user_id =%(sql_user_id)s WHERE user_emails.sql_id =%(user_emails_sql_id)s'] [参数:{'sql_user_id':无,'user_emails_sql_id ':十进制('1')}]

Can't find any idea why this sql_user_id is None :(

无法找到为什么这个sql_user_id是None :(

When I chek exstUser and newEmail objects in debugger - it looks like everething fine. I mean all the reference is OK. The session obj and it's dirty attribute looks also OK in the debugger (sql_user_id is set for Eca_user_emails obj).

当我在调试器中检查exstUser和newEmail对象时 - 它看起来很好。我的意思是所有参考都没关系。会话obj和它的脏属性在调试器中看起来也没问题(sql_user_id是为Eca_user_emails obj设置的)。

And what is most strange for me - this code worked absolutely fine when it was without a main function, just all code after the classes declaration. But after I wrote main declaration and put all code here I started to get this error. I am completely new to Python so maybe this is one of stupid mistakes... Any ideas how to fix it and what is the reason? Thanks for reading this :)

对我来说最奇怪的是 - 这个代码在没有main函数时完全正常,只是在类声明之后的所有代码。但在我写完主要声明并将所有代码放在这里后,我开始得到这个错误。我是Python的新手,所以也许这是一个愚蠢的错误...任何想法如何解决它,是什么原因?谢谢你读这个:)

By the way: Python 3.4, sqlalchemy 1.0, SQL Server 2012

顺便说一下:Python 3.4,sqlalchemy 1.0,SQL Server 2012

1 个解决方案

#1

sql_user_id is None because by default SQLAlchemy clears out the foreign key when you delete a child object across a relationship, that is, when you clear exstUser.user_emails SQLAlchemy sets sql_user_id to None for all those instances. If you want SQLAlchemy to issue DELETEs for Eca_user_emails instances when they are detached from Eca_users, you need to add delete-orphan cascade option to the user_emails relationship. If you want SQLAlchemy to issue DELETEs for Eca_user_emails instances when a Eca_users instance is deleted, you need to add the delete cascade option to the user_emails relationship.

sql_user_id为None,因为默认情况下SQLAlchemy在关系中删除子对象时清除外键,也就是说,当您清除exstUser.user_emails时,SQLAlchemy会将sql_user_id设置为None以用于所有这些实例。如果您希望SQLAlchemy在从Eca_users分离时为Eca_user_emails实例发出DELETE,则需要在user_emails关系中添加delete-orphan cascade选项。如果希望SQLAlchemy在删除Eca_users实例时为Eca_user_emails实例发出DELETE,则需要将delete cascade选项添加到user_emails关系中。

user_emails = relationship("Eca_user_emails", backref=backref('eca_users'), cascade="save-update, merge, delete, delete-orphan")

You can find more information about cascades in the SQLAlchemy docs

您可以在SQLAlchemy文档中找到有关级联的更多信息