SQLAlchemy MySQL Timestamp column value

Question

I need to execute a query which compares only the year and month value from TIMESTAMP column where the records look like this:

2015-01-01 08:33:06

The SQL Query is very simple (the interesting part is the year(timestamp) and month(timestamp) which extracts the year and the month so I can use them for comparison:

SELECT model, COUNT(model) AS count 
FROM log.logs 

WHERE SOURCE = "WEB"
AND year(timestamp) = 2015
AND month(timestamp) = 01
AND account = "TEST"
AND brand = "Nokia"

GROUP BY model
ORDER BY count DESC limit 10

Now the problem:

This is my SQLAlchemy Query:

devices = (db.session.query(Logs.model, Logs.timestamp,
func.count(Logs.model).label('count'))

       .filter_by(source=str(source))
       .filter_by(account=str(acc))
       .filter_by(brand=str(brand))
       .filter_by(year=year)
       .filter_by(month=month)
       .group_by(Logs.model)
       .order_by(func.count(Logs.model).desc()).all())

The part:

 .filter_by(year=year)
 .filter_by(month=month)

is not the same as

AND year(timestamp) = 2015
AND month(timestamp) = 01

and my SQLAchemy query is not working. It seems like year and month are MySQL functions that extract the values from a timestamp column.

My DB Model looks like this:

class Logs(db.Model):

    id = db.Column(db.Integer, primary_key=True)
    timestamp = db.Column(db.TIMESTAMP, primary_key=False)
    .... other attributes

It is interesting to mention that when I select and print Logs.timestamp it is in the following format:

(datetime.datetime(2013, 7, 11, 12, 47, 28))

How should this part be written in SQLAlchemy if I want my SQLAlchemy query to compare by the DB Timestamp year and month ?

 .filter_by(year=year)  #MySQL - year(timestamp)
 .filter_by(month=month) #MySQL- month(timestamp)

I tried .filter(Logs.timestamp == year(timestamp) and similar variations but no luck. Any help will be greatly appreciated.

van · Accepted Answer · 2015-04-15 21:13:33Z

2

Simply replace:

 .filter_by(year=year)
 .filter_by(month=month)

with:

from sqlalchemy.sql.expression import func
# ...

 .filter(func.year(Logs.timestamp) == year)
 .filter(func.month(Logs.timestamp) == month)

Read more on this in SQL and Generic Functions section of documentation.

answered Apr 15, 2015 at 21:13

van

77.5k13 gold badges174 silver badges179 bronze badges

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

Comments

lrnzcig · Accepted Answer · 2015-04-15 21:21:50Z

You can use custom constructs if you want to use functions that are specific to your database, like the year function you mention for MySQL. However I don't use MySQL and cannot give you some tested code (I did not even know about this function, by the way).

This would be an simple and useless example for Oracle (which is tested). I hope from this one you can quite easily deduce yours.

from sqlalchemy.sql import expression
from sqlalchemy.ext.compiler import compiles
from sqlalchemy import Date

class get_current_date(expression.FunctionElement):
    type = Date()

@compiles(get_current_date, 'oracle')
def ora_get_current_date(element, compiler, **kw):
    return "CURRENT_DATE"

session = schema_mgr.get_session()
q = session.query(sch.Tweet).filter(sch.Tweet.created_at == get_current_date())
tweets_today = pd.read_sql(q.statement, session.bind)

However I don't need to mention that this way you make your highly portable SQLAlchemy code a bit less portable.

Hope it helps.

Collectives™ on Stack Overflow

SQLAlchemy MySQL Timestamp column value

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related