python - 通过 sqlalchemy 运行 sql 表达式

Question

我收到错误，不确定如何编写可由 sqlalchemy 解释的多行 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)

我收到此错误：

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)

我究竟做错了什么？

标签： pythonsqlalchemy

Answer 1

实际上，您正在撰写错误的陈述，例如，您可以在异常中看到这一点：j.interview_id = os.interview_event_idwhere正确的陈述可能在哪里j.interview_id = os.interview_event_id where。

更改引用系统以保留换行符作为 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)