JJFord3 JJFord3 - 25 days ago 10
Python Question

Storing a sql query as a single variable in Python

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?

Answer

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)