Geek asks and answers

SQLAlchemy error when adding literal_column

I use Flask-SQLAlchemy and Postgres, and I have 2 classes FacebookPostand TwitterPostof which I want to query 2 columns, add a third column post_typeto indicate which class they consist of, combine the results, and then sort them.

When I create two subqueries (without a column post_type) and combine them like this

t1 = FacebookPost.__table__
fbq = db.session.query(t1.c.provider_post_id, t1.c.created_at).\
    join(FacebookPage).join((Artist,FacebookPage.artists)).\
    filter(Artist.id == 1).order_by(FacebookPost.created_at.desc()).\
    limit(5)
t2 = TwitterPost.__table__
twq = db.session.query(t2.c.provider_post_id, t2.c.created_at)\
    .join(TwitterUser).join((Artist,TwitterUser.artists)).\
    filter(Artist.id == 1).order_by(TwitterPost.created_at.desc()).\
    limit(5)
u = union(fbq.subquery().select(), twq.subquery().select())
db.session.query('provider_post_id', 'created_at').select_from(u).\
    order_by("created_at desc").all()

This works just fine and as you would expect. As soon as I update my two queries to include post_typeso that

fbq = db.session.query(t1.c.provider_post_id, t1.c.created_at,\
    literal("facebook_post", type_=db.String).label("post_type")).\
    join(FacebookPage).join((Artist,FacebookPage.artists)).\
    filter(Artist.id == 1).order_by(FacebookPost.created_at.desc()).\
    limit(5)
twq = db.session.query(t2.c.provider_post_id, t2.c.created_at,\
    literal("twitter_post", type_=db.String).label("post_type")).\
    join(TwitterUser).join((Artist,TwitterUser.artists)).\
    filter(Artist.id == 1).order_by(TwitterPost.created_at.desc()).\
    limit(5)

requests work individually, i.e.

fbq.all()

gives a list of results, each of which has an attribute post_typethat is facebook_post. The same thing works for twq.all(). But as soon as I call

u = union(fbq.subquery().select(), twq.subquery().select())
db.session.query('provider_post_id', 'created_at', 'post_type').select_from(u).\
    order_by("created_at desc").all()

I get the following error:

InternalError: (InternalError) failed to find conversion function from unknown to text

literal_column literal . , , literal, , , . .

+3
1

@RedBaron , postgres specific, . cast literal db.String:

fbq = db.session.query(t1.c.provider_post_id, t1.c.created_at,\
    cast(literal("facebook_post"), db.String).label("post_type")).\
    join(FacebookPage).join((Artist,FacebookPage.artists)).\
     filter(Artist.id == 1).order_by(FacebookPost.created_at.desc()).\
    limit(5)
twq = db.session.query(t2.c.provider_post_id, t2.c.created_at,\
    cast(literal("twitter_post"), db.String).label("post_type")).\
    join(TwitterUser).join((Artist,TwitterUser.artists)).\
    filter(Artist.id == 1).order_by(TwitterPost.created_at.desc()).\
    limit(5)
u = union(fbq.subquery().select(), twq.subquery().select())
db.session.query('provider_post_id', 'created_at', 'post_type').select_from(u).\
order_by("created_at desc").all()

!

+2

More articles:


All Articles