CODEWITHSUNDEEP
pythonsqlalchemy
Kingz
Kingz

Reputation: 1717

Identify what values in a list doesn't exist in a Table column using SQLAlchemy

I have a list cities = ['Rome', 'Barcelona', 'Budapest', 'Ljubljana']

Then,

I have a sqlalchemy model as follows -

class Fly(Base):

    __tablename__ = 'fly'

    pkid = Column('pkid', INTEGER(unsigned=True), primary_key=True, nullable=False)
    city = Column('city', VARCHAR(45), unique=True, nullable=False)
    country = Column('country', VARCHAR(45))
    flight_no = Column('Flight', VARCHAR(45))

I need to check if ALL the values in given cities list exists in fly table or not using sqlalchemy. Return true only if ALL the cities exists in table. Even if a single city doesn't exist in table, I need to return false and list of cities that doesn't exist. How to do that? Any ideas/hints/suggestions? I'm using MYSQL

Upvotes: 1

Views: 500

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52949

One way would be to create a (temporary) relation based on the given list and take the set difference between it and the cities from the fly table. In other words create a union of the values from the list1:

from sqlalchemy import union, select, literal

cities_union = union(*[select([literal(v)]) for v in cities])

Then take the difference:

sq = cities_union.select().except_(select([Fly.city]))

and check that no rows are left after the difference:

res = session.query(~exists(sq)).scalar()

For a list of cities lacking from fly table omit the (NOT) EXISTS:

res = session.execute(sq).fetchall()

1 Other database vendors may offer alternative methods for producing relations from arrays, such as Postgresql and its unnest().

Upvotes: 3

Related Questions