CODEWITHSUNDEEP
pythonsqlalchemy
susumanin
susumanin

Reputation: 3

sqlalchemy one-to-many ORM update error

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

# 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')}]

Can't find any idea why this sql_user_id is 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).

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

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

Upvotes: 0

Views: 549

Answers (1)

univerio
univerio

Reputation: 20508

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.

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

Upvotes: 1

Related Questions