Query with date in sql includes the day in question?

Question

Query with date in sql includes the day in question?

Navigation

#1 by (1 votes)

1

I have a question about the exact days that SQL includes, and my system once per 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 query:

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

and at the end it will update last_date for next execution

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

Well the code looks 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 wondering if the way I actually did this date manipulation is right, because I do not know if sql includes the data that was included in the date in question.

Maybe someone will suggest I search in just one day created_data = hoje , but as sometimes I might not run in one day due to maintenance or something of the sort, when the script re-runs it should recover those days lost.

sql datetime python

asked by anonymous 22.02.2018 / 16:27

1 answer

Write numeric topic (1 1.2, 1.2.1) in MySQL with PHP Update command to remove recorded data accents?

score 1 · Accepted Answer

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

Now - this is not the best way to do a search by date - are some points you can hit - your code will be easier to write, easier to maintain, and less susceptible to 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 operators % and .format - then concatenate values of variables closing the string, using + is something that it was never necessary. I'm not going to give an example already because for formatting queries in SQL we do not actually use that either.

Second, SQL Queries made with data historically are a major vulnerability vectors for calls "sql injections" - this particular case, the dates comes from a file under his control and the system itself, so probably are not vectors of attack. but in the case of data entered by the user, sanitizing the escapes that may allow the insertion of another "clandestine" query within the query has some edge cases, and may be non-trivial. So in Python all 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 votes of the data. The only thing is that depending on the driver, the syntax used for replacement may vary - check the session paramstyle in ' link and the SQL driver documentation you are using (since you do not mention which e)

The third thing is that most SQL drivers accept date objects from Python itself - so you do not have to worry about what format the dates will be represented if they are strings. Incidentally, depending on the SQL database, dates are comaparadas internamnete as strings (think almost everyone) and the %m/%d/%Y American format is nothing suitable for this type of comparison - since the day of the month will influence more than year. (So, 10/3/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 date

After all this, 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()))

(compare the line containing the "SELECT" with your example, and see the number of symbols less to enter inside the query)

And the output 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 because I used 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 an object "date" even when I do a select, and not a string)