Does query with date in sql include the day in question?

Question

I have a doubt in a matter of the exact days that SQL include, and my system once a day performs the following query on the system:

SELECT id,nome FROM products WHERE 
    created_date between \'"+last_date+"\' and \'" + now+"\'

Where

today’s date:

now = date.fromordinal(date.today().toordinal()).strftime("%m/%d/%y")

date of last time the consultation took place:

last_date = open('/home/data_query','r').read().replace('\n',' ')

and at the end it will update the last_date for the next execution

open('/home/data_query','w').write(now)

well the code went like this:

def atualiza():
    now = date.fromordinal(date.today().toordinal()).strftime("%m/%d/%y")
    last_date = open('/home/data_query','r').read().replace('\n',' ')
    cur.execute("SELECT id, nome FROM products WHERE created_date between \'"
                 +last_date+"\' and \'" + now+"\'")
    rows = fetchall()
    products = []
    for row in rows:
        products.append([row[0],row[1]])

    open('/home/data_query','w').write(now)

    return products

But I’m doubtful if really the way I did this date manipulation is right, because I don’t know if sql includes the data that was included in the date in question.

Maybe someone will suggest I research in just one day created_data = hoje, but as sometimes I may not run in a day due to maintenance or something like that, when the script runs again it should recover those lost days.

Answer 1

Your answer: The "between" operator is inclusive - both the maximum and minimum value are included in the search range.

Now - this is not the best way to do a data search - are some points you can hit - your code will become easier to write, easier to maintain and less susceptible to invasion by sql Injection.

First: Python has always had a number of options for interpolating 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 the .format - then concatenate variable values by closing the string, using the + is something that was never needed. I’m just not going to give an example as for formatting queries in SQL we don’t actually use either that.

Second: Historically composed SQL queries are one of the major vulnerability vectors for the so-called "sql injections" - In this particular case, the dates come from a file under your control and from the system itself, so they are probably not attack vectors. but in the case of data entered by the user, sanitize the " '" and escapes that may allow the insertion of another "clandestine" query within the query have some edge cases, and may be non-trivial. Therefore, in Python, all the database drivers have a scheme to insert the parameters in the 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 substitution may vary - check the session paramstyle in `https://www.python.org/dev/peps/pep-0249/ and the SQL driver documentation you’re using (since you don’t mention which one is)

The third thing is that most SQL drivers accept objects date from Python itself - so you don’t have to worry about which format dates will be represented if they are strings. Alias, depending on the SQL database, dates are internalized as strings (I think in 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 keeps the same order as the comparison as date

That said, create a table with dates in SQLITE and create a query using these style tabs, 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()))

(compare the line that contains the "SELECT" with your example, and see the number of less symbols to enter 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 because I used sqlite, where the driver only simulates a date column that does not exist internally in the database. In mysql, postgresql, and others will return a "date" object also when I make a select, not a string)