1

I've got a issue passing a string literal parameter to a SQL function using peewee's fn construct. I've got an object defined as:

class User(BaseModel):
    computingID = CharField()
    firstName = CharField()
    lastName = CharField()
    role = ForeignKeyField(Role)
    lastLogin = DateTimeField()
    class Meta:
        database = database

I'm attempting to use the mySQL timestampdiff function in a select to get the number of days since the last login. The query should look something like this:

SELECT t1.`id`, t1.`computingID`, t1.`firstName`, t1.`lastName`, t1.`role_id`, t1.`lastLogin`, timestampdiff(day, t1.`lastLogin`, now()) AS daysSinceLastLogin FROM `user` AS t1

Here's the python peewee code I'm trying to use:

bob = User.select(User, fn.timestampdiff('day', User.lastLogin, fn.now()).alias('daysSinceLastLogin'))
result = bob[0].daysSinceLastLogin

But when I execute this code, I get an error:

ProgrammingError: (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''day', t1.lastLogin, now()) AS daysSinceLastLogin FROM user AS t1' at line 1")

Judging from this message, it looks like the quote marks around the 'day' parameter are being retained in the SQL that peewee is generating. And mySQL doesn't like quotes around the parameter. I obviously can't leave off the quotes in the python code, so can someone tell me what I'm doing wrong please?

Update: I have my query working as intended by using the SQL() peewee command to add the DAY parameter, sans quote marks:

User.select(User, fn.timestampdiff(SQL('day'), User.lastLogin, fn.now()).alias('daysSinceLastLogin'))

But I'm not sure why I had to use SQL() in this situation. Am I missing anything, or is this the right answer?

BridgetG
  • 113
  • 1
  • 14

1 Answers1

0

Is there a reason you need to use an SQL function to do this?

In part because I'm not very comfortable with SQL functions, I would probably do something like this:

import datetime as dt
bob = user.get(User = "Bob") #or however you want to get the User instance
daysSinceLastLogin = (dt.datetime.now() - bob.lastLogin).days
Alex S
  • 4,726
  • 7
  • 39
  • 67
  • I probably oversimplified my example -- I really need the daysSinceLogin for each user, as a column in the results. Your example makes a lot of sense, and I'd probably do it that way if I needed just one user's info. – BridgetG Mar 04 '14 at 15:21
  • Hmm I see, sorry I can't help you if that's the issue. An ugly workaround might be to include an "age" column in the user class, and then run a script daily to put in the new age of the account for every user. Not ideal I know, but if you just need to get it working might be an option. – Alex S Mar 06 '14 at 05:31