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()

​x
 
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'

 
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))

 
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)

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

Advertisement

Answer