Python SQLITE3 SELECT query with datetime calculated string not working
Question: I have a SQLite3 DB with a table named `TEST_TABLE`, which 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'm trying to write a query that returns the ID for week 13 of this year. I
want to use the program again next year, so I cannot hardcode "2013" as the
year.

I used datetime to calculate a value for this year, creating a `datetime.date`
object with content like this: "2013-01-01". I then converted this to a
string:

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

Then I queried the 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 the IDs 1 and 2, but this is no good, because ID 1 has '2012' as
the year.

The strange thing is, if I don't use datetime for the string, but create the
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 won't the query work correctly when I create the string via datetime? A
string is a string, right? So what am I missing here?


Answer: Instead of converting `this_year` into 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)
    

yields

    
    
    (u'2',)
    

* * *

The sqlite3 database adapter will quote arguments for you when you write
parametrized SQL and use the 2-argument form of `cursor.execute`. So you do
not need (or want) to quote arguments manually yourself.

So

    
    
    this_year = str(this_year)
    

instead of

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

also works, but as shown above, both lines are unnecessary, since `sqlite3`
will accept `datetime` objects as arguments as well.

also works.

Since sqlite3 automatically quotes arguments, when you manually add quotes,
the final argument gets two sets of quotes. The SQL ends up comparing

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

which is why both rows were (erroneously) returned.

