SqlAlchemy adding "string" as a column in results

Question

candidates = db_session.query(
    Jobs.candidate_id, ## is there a way to eliminate this?
    'Good' ## How do I add this as a column for every person?
    Resumes.resume
).outerjoin(
    (Resumes, Resumes.candidate_id == Jobs.candidate_id),
).outerjoin(
    (Candidate, Candidate.candidate_id == Jobs.candidate_id)

Is there a way I can add Good as the first column to every result?

Additionally, can I eliminate the Jobs category from the Query without breaking the outerjoin?

Update -

can = db_session.query(
    sqlalchemy.sql.expression.literal_column("Good"),
    Jobs.candidate_id

I get this error:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: Good

I'm using SQLite.

Adding the string literal: https://stackoverflow.com/questions/7533146/how-do-i-select-literal-values-in-an-sqlalchemy-query — Ilja Everilä, May 11 '19 at 10:24
@IljaEverilä still getting an error. I added what I changed — Morgan Allen, May 11 '19 at 14:17
`literal_column()` puts the string *as is* in to the query, so in that case it's up to you to quote it correctly. If you want to produce an SQL literal from a Python value, use `literal()` instead. — Ilja Everilä, May 11 '19 at 15:58

score 1 · Answer 1 · answered Sep 23 '22 at 17:21

You were very close in your update: use literal_column, but make sure to put the value in single quotes.

In your case:

sqlalchemy.sql.expression.literal_column("Good"),

Should be:

sqlalchemy.sql.expression.literal_column("'Good'"),

Your entire query would then look like this:

candidates = db_session.query(
    Jobs.candidate_id, ## is there a way to eliminate this?
    sqlalchemy.sql.expression.literal_column("'Good'").label("optional_column_name"),
    Resumes.resume
).outerjoin(
    (Resumes, Resumes.candidate_id == Jobs.candidate_id),
).outerjoin(
    (Candidate, Candidate.candidate_id == Jobs.candidate_id)

SqlAlchemy adding "string" as a column in results

1 Answers1