CODEWITHSUNDEEP
pythonmysqldatesqlalchemy
user4554133
user4554133

Reputation: 75

SQLAlchemy MySQL Timestamp column value

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.

Upvotes: 2

Views: 4787

Answers (2)

lrnzcig
lrnzcig

Reputation: 3947

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.

Upvotes: 0

van
van

Reputation: 77082

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.

Upvotes: 2

Related Questions