Your answer: The "between" operator is inclusive - both the maximum and minimum value are included in the search range.Now - this one is not the best way to do a search by date -
are some points that you can hit - your code will get easier to write, easier to maintain and less susceptible to invasion by sql injection.First: Python always had a number of options to interpolar data in strings. Now with Python 3.6, we have the "f strings" that make it even easier - but even before we had the operators % and .format - then concatenate variable values by closing the string, using the + is something that has never been necessary. I'm just not going to give an example because for formatting queries in SQL we don't actually use that.Second: SQL queries composed with data historically are one of the main vectors of vulnerability for so-called "sql injections" - In this specific case, the dates come from a file under its control and system itself, so they are probably not attack vectors. but in the case of data entered by the user, sanitize the "\'" and exhausts that can allow the insertion of another query "clandestina" within the query has some edge cases, and may be non-trivial. So in Python, all the database drivers have a scheme to enter the parameters in query for you. This is done automatically, and the driver even inserts the ' in data votla. The only thing is that depending on the driver, the syntax used for replacement can vary
- check the session paramstyle in https://www.python.org/dev/peps/pep-0249/ and the documentation of the SQL driver you are using (since you do not mention what it is)The third thing is that most SQL drivers accept objects date Python itself - so you don't have to worry about which format the dates will be represented if they are strings.
Alias, depending on the SQL bank,dates are compared internalmnete as strings (I think almost all) and the format %m/%d/%Y American is not conducive to this type of comparison - since the day of the month will influence more than the year. (So, 03/10/2017 comes after 03/15/2014). In t.i. and databases the most usual representation of dates is yyyy-mm-dd, since in this case the comparison as string maintains the same order as the comparison as dateThat being said, I create a table with dates in SQLITE and create a query using these style guides, for example:from datetime import date
import sqlite3
from pprint import pprint
conn = sqlite3.connect(":")
cursor = conn.cursor()
cursor.execute("""CREATE TABLE teste (id INT PRIMARY KEY, nome VARCHAR, data DATE)""")
for i in range(1, 5):
cursor.execute("INSERT INTO teste VALUES (?, ?, ?)", (i, "nome de teste %d" % i, date(2017, 8, i)))
cursor.execute("SELECT * FROM teste WHERE data BETWEEN ? and ?", (date(2017, 8, 2), date(2017, 8, 4)))
pprint(list(cursor.fetchall()))
(buy the line that contains the "SELECT" with your example, and see the amount of symbols less to type inside the query)And the exit is:[(2, u'nome de teste 2', u'2017-08-02'),
(3, u'nome de teste 3', u'2017-08-03'),
(4, u'nome de teste 4', u'2017-08-04')]
(this is why I used the sqlite, in which the driver only simulates a date column that does not exist internally in the bank. In mysql, postgresql, and others will return a "date" object also when I make a select, not a string)
