Python：如何将SQL查询作为字符串输入而不出错？

Question

我正在创建一个处理SQL查询的程序。目前它非常简单，我只想定义一个变量，其中的SQL语句是一个字符串。你知道吗

例如

str = "SELECT DISTINCT 
pv.project_id,
gps.period_name,
gps.period_year,
rbse.resource_source_id,
rbse.alias as resource_name,
DECODE(pjo_plan_version_utils.get_time_phased_code(pv.plan_version_id), 'G', 
       pld.quantity, pjo_plan_version_utils.spread_amount('L', pld.start_date, pld.end_date, gps.start_date, gps.end_date, pld.quantity)
) as hours,
(131.4-pld.quantity)/131.4 unallocated_percentage
FROM
    pjf_rbs_elements rbse,
    gl_period_statuses gps,
    pjo_plan_line_details pld,
    pjo_planning_elements pe,
    pjo_plan_versions_vl pv,
    pjo_plan_types_vl pt
WHERE
    1=1
    AND pe.rbs_element_id = rbse.rbs_element_id 
    AND pld.planning_element_id = pe.planning_element_id 
    AND pv.plan_version_id = pe.plan_version_id
    AND pv.current_plan_status_flag = 'Y'
    AND ((gps.start_date <= pld.end_date)
    AND (gps.end_date >= pld.start_date))
    AND gps.adjustment_period_flag = 'N'
    AND gps.application_id = 10037
    AND pv.plan_type_id = pt.plan_type_id
    AND pt.plan_type_code = 'PROJECT_PLAN'"

我得到无效的语法错误，所以我显然没有正确定义它。最好的方法是什么？为了深入了解，该脚本将分析str以识别表和列，并返回与列表匹配的任何内容。你知道吗

Answer 1

多行中的字符串，因此需要使用三个引号，否则会得到EOL。另外，您不应该命名变量str，因为它已经是一个函数了。你知道吗

string = """SELECT DISTINCT 
pv.project_id,
gps.period_name,
gps.period_year,
rbse.resource_source_id,
rbse.alias as resource_name,
DECODE(pjo_plan_version_utils.get_time_phased_code(pv.plan_version_id), 'G', 
       pld.quantity, pjo_plan_version_utils.spread_amount('L', pld.start_date, pld.end_date, gps.start_date, gps.end_date, pld.quantity)
) as hours,
(131.4-pld.quantity)/131.4 unallocated_percentage
FROM
    pjf_rbs_elements rbse,
    gl_period_statuses gps,
    pjo_plan_line_details pld,
    pjo_planning_elements pe,
    pjo_plan_versions_vl pv,
    pjo_plan_types_vl pt
WHERE
    1=1
    AND pe.rbs_element_id = rbse.rbs_element_id 
    AND pld.planning_element_id = pe.planning_element_id 
    AND pv.plan_version_id = pe.plan_version_id
    AND pv.current_plan_status_flag = 'Y'
    AND ((gps.start_date <= pld.end_date)
    AND (gps.end_date >= pld.start_date))
    AND gps.adjustment_period_flag = 'N'
    AND gps.application_id = 10037
    AND pv.plan_type_id = pt.plan_type_id
    AND pt.plan_type_code = 'PROJECT_PLAN'"""

Answer 2

字符串串联不正确。你知道吗

尝试以下操作：

str = "line1" \
      "line2" \
      "line3" 

#output : no spaces or new lines
line1line2line3

或（但不能按以下方式缩进，如果缩进，缩进将成为串联的一部分）

str = """line1
line2
line3"""

#output : new lines
line1
line2
line3