SQLAlchemy: insert record if certain record and relationship data does not already exist

user_7812800 0
Viewed 616

I want to know how to query data from multiple tables with multiple conditions.

My example db has the following tables:

class Location(Base):
    __tablename__ = "location"

    id = Column('id', Integer, primary_key=True)
    location = Column('Location', String)

class Person(Base):
    __tablename__ = "person"

    id = Column('id', Integer, primary_key=True)
    name = Column('Name', String, unique=True)
    profession = Column('Profession', String)
    location_id = Column(Integer, ForeignKey('location.id'))
    location = relationship(Location)

We have in this database a person with a specific location. My goal is to write a query where I can check conditions of the Location table and the Person table.

A person with the name Eric lives in Houston. Now I want to know if I already have an Eric from Houston in my database.

The following query doesn't work.

new_location = Location(location='Houston')
obj = Person(name='Eric', profession='Teacher', location=new_location)

if session.query(Person).filter(Person.name == obj.name,
Person.profession == obj.profession,
Person.location_id == obj.location.id).first() == None:
session.add(obj)
session.commit()
print("Insert sucessful")

The problem in my query is the last line where I check the location but I don't know how to solve it. Maybe someone has a working example with the SQLAlchemy method exists()?

filtering exists sqlalchemy python-3.x python
1 Answers

You can do something like the following to join Person and Location and filter for any record where the name and location are the same as the new person instance you have created. The query will either return the record or None, so you can use the result in your if (remember that indentation matters - maybe the code example in your question just copied incorrectly).

new_location = Location(location='Houston')
new_person = Person(name='Eric', profession='Teacher', location=new_location)

person_location_exists = session.query(Person).\
    join(Location).\
    filter(Person.name == new_person.name).\
    filter(Location.location == new_location.location).\
    first()

if not person_location_exists:
    session.add(new_person)
    session.commit()
    print("Insert successful")

You could use exists() to accomplish the same thing, but I think the above is a bit simpler.

user_99589420
Related