Storing a sql query as a single variable in Python

Question

I'm trying to write some Python code calling SQL queries in a way that will allow the SQL query to stay formatted as I want, but Python (I'm using a Spider GUI) gets to the end of line and recognizes that the string doesn't end.

Let's say my data in the table database.table looks like this:

ID Date Amount
1 2016-10-01 $3.00
1 2016-10-05 $4.99
1 2016-10-12 $1.29
2 2016-08-12 $50.13
2 2016-09-11 $27.89
2 2016-10-08 $31.13
3 2016-10-04 $4.18
3 2016-10-10 $43.99
3 2016-10-20 $62.11

If I write multi-line code and try to pass it to a single variable in Python, I run into an EOF error.

Date1 = '2016-10-01'
Date2 = '2016-10-10'

TD_Code_Snipit = str("select 
ID,
sum(Amount) as Amount
from database.tablename
group by ID
where Date >="
+Date1+
str(" and  Date < ")
+Date2+
str(";")

This allows readability in my code (the true query I want to put in is roughly 80 lines so putting it on one line of code would make it rather hard to make adjustments to on the fly.

Is this an inherently wrong way to approach this problem or is there a setting in Spider that is making it not wrap line appropriately causing the error?

this could help you: http://stackoverflow.com/questions/10660435/pythonic-way-to-create-a-long-multi-line-string — , Nov 15 '16 at 17:00

score 3 · Accepted Answer · answered Nov 15 '16 at 17:32

I think this is what you need. For multiline you need ''' and for variables you can use {} placeholders.

Date1 = '2016-10-01'
Date2 = '2016-10-10'

TD_Code_Snipit = '''select 
ID,
sum(Amount) as Amount
from database.tablename
group by ID
where Date >= {}
 and  Date < {}
;'''.format(Date1, Date2)

Storing a sql query as a single variable in Python

1 Answers1