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