running a sql expression through sqlalchemy

Question

I am getting errors and not sure on how to write a multi-line sql expression that can be interpreted by sqlalchemy

x = text("select j.candidate_id, j.level, j.family_id, j.interview_type, j.interview_result, os.interview_event_id from onsite_data as os"
"left join jobs as j on j.interview_id = os.interview_event_id"

"where (j.level = '5' or j.level = '6')"
"and j.family_id = 'SDE'"
"and interview_result = 'NOT_INCLINED'"

"and (j.internal_title LIKE '%SDE%' OR j.internal_title LIKE '%software%' OR j.internal_title LIKe '%front%' OR j.internal_title LIKE '%full%')"
"AND j.internal_title NOT LIKE '%embedded%'"
"AND j.internal_title NOT LIKE '%intern%'"
"AND j.internal_title NOT LIKE '%manager%'"
"AND j.internal_title NOT like '%test%'"
"AND j.internal_title NOT LIKE '%SDET%'"
"AND j.candidate_id not in (select candidate_id from jobs where interview_type = 'IN_HOUSE' and interview_result = 'INCLINED')"
"group by interview_event_id"
"having"
"    COUNT(CASE WHEN os.vote_type = 'INCLINED' THEN 1 ELSE NULL end) > COUNT(CASE WHEN os.vote_type = 'NOT_INCLINED' THEN 1 ELSE NULL end)")

db_session.execute(x)

I am getting this error:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "(": syntax error [SQL: "select j.candidate_id, j.level, j.family_id, j.interview_type, j.interview_result, os.interview_event_id from onsite_data as osleft join jobs as j on j.interview_id = os.interview_event_idwhere (j.level = '5' or j.level = '6')and j.family_id = 'SDE'and interview_result = 'NOT_INCLINED'and (j.internal_title LIKE '%SDE%' OR j.internal_title LIKE '%software%' OR j.internal_title LIKe '%front%' OR j.internal_title LIKE '%full%')AND j.internal_title NOT LIKE '%embedded%'AND j.internal_title NOT LIKE '%intern%'AND j.internal_title NOT LIKE '%manager%'AND j.internal_title NOT like '%test%'AND j.internal_title NOT LIKE '%SDET%'AND j.candidate_id not in (select candidate_id from jobs where interview_type = 'IN_HOUSE' and interview_result = 'INCLINED')group by interview_event_idhaving    COUNT(CASE WHEN os.vote_type = 'INCLINED' THEN 1 ELSE NULL end) > COUNT(CASE WHEN os.vote_type = 'NOT_INCLINED' THEN 1 ELSE NULL end)"] (Background on this error at: http://sqlalche.me/e/e3q8)

What am I doing wrong?

Juan Diego Godoy Robles · Accepted Answer · 2019-05-20 13:04:46Z

Actually you are composing a wrong statement, for example you can see this in the exception: j.interview_id = os.interview_event_idwhere where a correct one could be j.interview_id = os.interview_event_id where.

Change the quoting system to preserve line breaks as separators between the elements of the sql:

x = text("""select j.candidate_id, j.level, j.family_id, j.interview_type, j.interview_result, os.interview_event_id from onsite_data as os
left join jobs as j on j.interview_id = os.interview_event_id

where (j.level = '5' or j.level = '6')
and j.family_id = 'SDE'
and interview_result = 'NOT_INCLINED'

and (j.internal_title LIKE '%SDE%' OR j.internal_title LIKE '%software%' OR j.internal_title LIKe '%front%' OR j.internal_title LIKE '%full%')
AND j.internal_title NOT LIKE '%embedded%'
AND j.internal_title NOT LIKE '%intern%'
AND j.internal_title NOT LIKE '%manager%'
AND j.internal_title NOT like '%test%'
AND j.internal_title NOT LIKE '%SDET%'
AND j.candidate_id not in (select candidate_id from jobs where interview_type = 'IN_HOUSE' and interview_result = 'INCLINED')
group by interview_event_id
having
    COUNT(CASE WHEN os.vote_type = 'INCLINED' THEN 1 ELSE NULL end) > COUNT(CASE WHEN os.vote_type = 'NOT_INCLINED' THEN 1 ELSE NULL end))"""

db_session.execute(x)

Collectives™ on Stack Overflow

running a sql expression through sqlalchemy

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related