Python SQLITE3 SELECT query with calculated datetime string not working

Question

I have an SQLite3 DB with a table named TEST_TABLE that looks like this:

 ("ID" TEXT,"DATE_IN" DATE,"WEEK_IN" number); 

There are 2 entries in the table:

 1|2012-03-25|13 2|2013-03-25|13 

I am trying to write a query that returns an ID for the 13th week of this year. I want to use the program again next year, so I can not specify "2013" as the year.

I used datetime to compute the value this year by creating a datetime.date object with this content: "2013-01-01". Then I converted this to a string:

 this_year = (datetime.date(datetime.date.today().isocalendar()[0], 1, 1)) test2 = ("'"+str(this_year)+"'") 

Then I requested SQLite DB:

 cursr = con.cursor() con.text_factory = str cursr.execute("""select ID from TEST_TABLE where WEEK_IN = 13 and DATE_IN > ? """,[test2]) result = cursr.fetchall() print result [('1',), ('2',)] 

This returns identifiers 1 and 2, but this is not good, because ID 1 has "2012" as the year.

It is strange if I do not use datetime for a string, but I create var manually, IT WORKS CORRECTLY.

 test2 = ('2013-01-01') cursr.execute("""select ID from TEST_TABLE where WEEK_IN = 13 and DATE_IN > ? """,[test2]) result = cursr.fetchall() print result [('2',)] 

So, why the query will not work correctly when I create a row via datetime? A string is a string, right? So what am I missing here?

Answer 1

Instead of converting this_year to a string, just leave it as a datetime.date object:

 this_year = DT.date(DT.date.today().year,1,1) 

---

 import sqlite3 import datetime as DT this_year = (DT.date(DT.date.today().isocalendar()[0], 1, 1)) # this_year = ("'"+str(this_year)+"'") # this_year = DT.date(DT.date.today().year,1,1) with sqlite3.connect(':memory:') as conn: cursor = conn.cursor() sql = '''CREATE TABLE TEST_TABLE ("ID" TEXT, "DATE_IN" DATE, "WEEK_IN" number) ''' cursor.execute(sql) sql = 'INSERT INTO TEST_TABLE(ID, DATE_IN, WEEK_IN) VALUES (?,?,?)' cursor.executemany(sql, [[1,'2012-03-25',13],[2,'2013-03-25',13],]) sql = 'SELECT ID FROM TEST_TABLE where WEEK_IN = 13 and DATE_IN > ?' cursor.execute(sql, [this_year]) for row in cursor: print(row) 

gives

 (u'2',) 

---

The sqlite3 database adapter will provide arguments for you when you write parameterized SQL and use a form with two cursor.execute arguments. Therefore, you do not need (or do not want) to give arguments manually yourself.

So

 this_year = str(this_year) 

instead

 this_year = ("'"+str(this_year)+"'") 

also works, but as shown above, both rows are not needed since sqlite3 will accept datetime objects as arguments.

also works.

Since sqlite3 automatically quotes arguments when you manually add quotes, the last argument gets two sets of quotes. SQL finishes comparison

 In [59]: '2012-03-25' > "'2013-01-01'" Out[59]: True 

therefore, both rows were erroneously returned.

Answer 2

I believe in this because of how you create your date in the variable test2 .

In the first example, when you use the datetime module , you accidentally enter extra quotation marks:

 >>> import datetime >>> this_year = datetime.date(datetime.date.today().isocalendar()[0], 1, 1) >>> test2 = "'" + str(this_year) + "'" >>> print test2 "'2013-01-01'" 

However, in your second example, you set test2 to the date that worked.

 >>> test2 = '2013-01-01' '2013-01-01' 

To fix this, just change your first example like this:

 this_year = datetime.date(datetime.date.today().isocalendar()[0], 1, 1) test2 = str(this_year) 

As a note, note that I removed the parentheses around your variables as they were redundant.