s = text('SELECT customers.id,WHERE customers.end_date IS NULL or customers.end_date >= '+ "2018-05-01"+ 'AND customers.end_date <='+"2018-05-31" + 'ORDER BY customers.id;')
This throws syntax errors. How exactly do I pass the date values in the above statement? How to foramte the date string? Using sqlalchemy core version 1.0.8 only, python 2.7
There are multiple potential syntax errors. The first is the extra comma at the end of the SELECT item list, before the WHERE clause. The second (and third) the missing quotes around the literal that you're comparing against. The missing spaces also alter how the query is parsed. After string concatenation the result looks like:
In [2]: s
Out[2]: 'SELECT customers.id,WHERE customers.end_date IS NULL or customers.end_date >= 2018-05-01AND customers.end_date <=2018-05-31ORDER BY customers.id;'
which is clearly wrong.
As always, don't pass values to SQL queries with string concatenation or formatting, unless they're static, in which case they're a part of your query to begin with. If you do, you might expose yourself to SQL injection. The driver you're using knows how to handle different data types, quoting etc. better than you – probably. Use placeholders:
s = text('''SELECT customers.id
WHERE customers.end_date IS NULL
OR customers.end_date >= :end_date_low
AND customers.end_date <= :end_date_high
ORDER BY customers.id''')
low = "2018-05-01"
high = "2018-05-31"
# engine, connection, or session
conn.execute(s, end_date_low=low, end_date_high=high)
In addition you could use the SQL operator BETWEEN here:
s = text('''SELECT customers.id
WHERE customers.end_date IS NULL
OR customers.end_date BETWEEN :end_date_low AND :end_date_high
ORDER BY customers.id''')
try this:
s = text('SELECT customers.id WHERE customers.end_date IS NULL or
customers.end_date >= \'2018-05-01\' AND customers.end_date
<= \'2018-05-31\' ORDER BY customers.id;')
Be careful when concatenating strings, you might miss some spaces. For example:
"2018-05-01"+ 'AND customers.end_date <=' => "2018-05-01AND customers.end_date <='.
Etc.
The other thing is adding quotes around date inside query.
In your case quotes around are not part of query.
So you could write instead:
"'2018-05-01'"+ ' AND customers.end_date <='.
Or see the full example by @Dataichou
