SQLAlchemy IntegrityError Relationship using object already in the database

188 views Asked by At

This is my model, made with classical mapping, classes A and B are working accordingly

import sqlalchemy as sa
from sqlalchemy.orm import mapper, relationship

from domain.a import A
from domain.b import B
from app_extentions import metadata

a_table = sa.Table(
    'a', metadata,
    sa.Column('description', sa.String(30), primary_key=True), # I think this is important
    sa.Column('value_x', sa.Boolean()),
)

b_table = sa.Table(
    'b', metadata,
    sa.Column('id', sa.BigInteger, primary_key=True, autoincrement=True),
    sa.Column('description', sa.String(50), sa.ForeignKey(a_table.c.description), nullable=False),
    sa.Column('value_y', sa.String(20), nullable=True),    
)

mapper(A, a_table)
mapper(B, b_table, properties={
    'rel': relationship(
        A, primaryjoin=(a_table.c.description == b_table.c.description)
    ),
})

When I do this using pytest

obj1:A = retrieve_A_object() # A is already in the DB, I get it
obj2:B = create_B_object() # this is created now, it is brand new
obj2.rel = obj1

session = get_session()
session.add(obj2)
session.commit()

SQLAlchemy raises an error

    def do_execute(self, cursor, statement, parameters, context=None):
>       cursor.execute(statement, parameters)
E       sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "a_pkey"
E       DETAIL:  Key (description)=('MY DESCRIPTION') already exists.

I know that it's already in the db, I want to save B object How can i solve this? Why is this happening?

0

There are 0 answers