Can’t write SqlAlchemy query with contains operation

Let’s say that we have a two tables: Movie and Genre. Genre has an attribute genres which is a PostgreSQL ARRAY of genre ids. I have written a query to get all movie ids with genre names.

session.query(Movie.id, Genre.name).join(Genre, Movie.genres.contains([Genre.id])).all()

But sadly I am getting:

ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'InstrumentedAttribute'

So, how can I rewrite my query to get what I want?

Answer

Remove the square brackets [] and use any:

session.query(Movie.id, Genre.name).join(Genre, Movie.genres.any(Genre.id))

Generated SQL:

SELECT movie.id AS movie_id, genre.name AS genre_name 
FROM movie 
JOIN genre ON genre.id = ANY (movie.genres)