SQL strftime with python and sqlite3 returns None

Question

I am with a problem like in this topic. and I've tried 'unixepoch', but even so, my recordset returns None

running the sql below directly in sqliteman, i have as return, the value that I expected

SELECT sum(value)
FROM customers
WHERE customer_id = 1
    AND devolution = 'No'
    AND strftime('%Y', delivery_date) IN ('2016')

but, when in python, I get None as return from the print self.rec[0]

def get_total_from_last_year(self):   
    self.now = datetime.datetime.now()
    self.lastYear = self.now.year -1

    self.con = sqlite3.connect(database.name)
    self.cur = self.con.cursor()
    self.sql = """SELECT sum(value) 
        FROM customers 
        WHERE customer_id=?
        AND devolution='No' 
        AND strftime('%%Y',delivery_date) IN(%s) """ %(str(self.lastYear))
    self.cur.execute(self.sql,  str(customerID))
    self.rs = self.cur.fetchall()

    for self.rec in self.rs:
        print self.rec[0]

Someone can help me? Thank you very much.

Alberto · Accepted Answer · 2017-02-11 22:43:10Z

1

Your query string in python is wrong:

You don't need double % for the Year.
The year in the condition "IN" must be quoted if you want to add the param as you are doing.
You should never apply params that way, but let sqlite to get the type for you, as you did for customerId.

This should work now:

self.cur.execute("SELECT sum(value) \
    FROM customers \
    WHERE customer_id=? \
    AND devolution='No' \
    AND strftime('%Y',delivery_date) IN (?)", (customerID, self.lastYear))

You should not need to convert customerID to str(), sqlite3 should be able to do it for you.

Please note I'm adding \'s at the end of the lines for the sake of clarity (as I'm breaking the lines), you might not need them.

answered Feb 11, 2017 at 22:43

Alberto

7277 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Schwern Over a year ago

Check your quotes around "No". You'll want to keep the triple-quotes.

Alberto Over a year ago

Done, noticed after posting the answer. Thanks for the tip ;-)

Juliano Gomes Over a year ago

Now it worked, with single-quotes, and converting customerID and self.lastYear to str(). Thanks!

Alberto Over a year ago

It didn't work without converting customerID and self.lastYear to str()?? My best guess is your conversion to str() is not really necessary, but it forces sqlite3 to add the quotes in the query for you.

Collectives™ on Stack Overflow

SQL strftime with python and sqlite3 returns None

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related