Preventing duplicate child entries in an ORM relationship

user_10272912 0
Viewed 133

Basically I have a service that reads from a spreadsheet and inserts into database. In SQLAlchemy I have the following relationship

class Customer(Base):
 __tablename__ = 'customers'
 id = Column(Integer, primary_key=True)
 name = Column(String)
 children = relationship('Email', backref=('customer')

class Email(Base):
 __tablename__ = 'emails'
 id = Column(Integer, primary_key=True)
 customer = Column(Integer, ForeignKey('customer.id')) 
 email = Column(String)
 primary = Column(Boolean)

Is it possible for SQLAlchemy to check for a duplicate entry between a fetched resource and one created in the ORM? For example let's say customer 123 has an email some_email, and we try to add it again:

email_object = Email(customer=123, email='some_email', primary=True)
cust = connection.query(Customer).options(joinedload(Customer.emails)).filter_by(
        id=123).first()
cust.emails.append(email_object)

Ideally I would like SQLAlchemy to either notice that such a combination exists and merge/ignore it, or throw some kind of exception.

But instead I'm getting the following result if I print out cust.emails

[<Email(id=1, email=some_email, primary=True, customer=123>), 
<Email(customer=192071, email='some_email', primary=True, customers=<Employee(id=123, name='John', emails=['some_email', 'some_email']>>)]

and doing a merge and commit just seems to add an extra identical row in the database (except for the pk). I think maybe it has to do with the unused primary key in Emails, but that is autogenerated when committing to the DB. Any ideas? Lemme know if I need to clarify anything.

sqlalchemy orm python
1 Answers

Setting the Email class to have two primary keys doesn't seem to make SQLAlchemy stop from appending the extra email

That's correct. Using a composite primary key on (customer_id, email) does not prevent SQLAlchemy from trying to insert a new object that essentially duplicates an existing email — although it will warn you if an object with the same primary key already exists in the identity map — and the INSERT will fail (throw an exception and be rolled back) because of the duplicate PK, thus preventing the duplicate child record.

If you want to check whether an email exists before trying to add it you can either use session.get() …

with Session(engine) as session:
    # retrieve John's object
    john = (
        session.execute(select(Customer).where(Customer.name == "John"))
        .scalars()
        .one()
    )
    print(john)  # <Customer(id=123, name='John')>

    # check if email already exists using .get()
    email = session.get(Email, (john.id, "some_email"))
    if email:
        print(f"email already exists: {email}")
        # email already exists: <Email(customer_id=123, email='some_email')>
    else:
        print("email does not already exist")

… or a relationship in Customer could provide the existing emails, allowing you to search for the one you want to add

    # alternative (less efficient) method: check via relationship
    e_list = [e for e in john.emails if e.email == "some_email"]
    if e_list:  # list not empty
        print("email already exists")
    else:
        print("email does not already exist")
user_21443900
Related