Python: How can I input a SQL query as a String without errors?

Question

I am creating a program that manipulates SQL queries. At the moment it's very simple and all I want to do is define a variable with the SQL statement inside it as a string.

E.G.

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'"

I am getting invalid syntax errors so I am clearly not defining it correctly. What is the best way to do this? For insight, the script will analyse str to identify tables and columns and return anything that matches a list.

replace each double quote with 3 single quotes, also change the parameter name str to maybe query_str, as str is already used by python. — pangyuteng
– pangyuteng, Commented Nov 20, 2018 at 0:04

Joyal Mathew · Accepted Answer · 2018-11-20 00:05:09Z

The string in multiline so you need to use triple quotes or else you get an EOL. Also you shouldn't name a variable str because it is already a function.

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'"""

Pavan Chandaka · Accepted Answer · 2018-11-20 00:18:09Z

1

Your string concatenation is incorrect.

Try something like this:

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

#output : no spaces or new lines
line1line2line3

or (but you can not indent by doing below way, if you indent, the indentation becomes part of concatenation)

str = """line1
line2
line3"""

#output : new lines
line1
line2
line3

edited Nov 20, 2018 at 0:18

answered Nov 20, 2018 at 0:04

Pavan Chandaka

13k5 gold badges35 silver badges42 bronze badges

Collectives™ on Stack Overflow

Python: How can I input a SQL query as a String without errors?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related