How could i get the sqlite date delta in SQLAlchemy

I have a sqlite table with a column final_date in INTEGER type (Use as date). I want to calculate number of days from this column to now. In sqlite, i can run SQL with help of function (julianday) as following.

select (julianday(final_date) - julianday('now')) AS days from bond where days > 0
109.407374872826
482.407374872826
488.407374872826
....

However, in SQLAlchemy, code as following can not get the accurate number of days.

today = datetime.today().date()

query = session.query( Bond.final_date - today )

I wonder whether there is an effective way to apply function julianday to SQLAlchemy code or whether there is an alternative native approach in SQLAlchemy to accomplish this.

Answers


If you have control on database and data - you may want to update schema to define column as sqlalchemy.types.DateTime. That use datetime module to make calculations with dates.

Or you can use sqlalchemy.sql.func to access julianday() sqlite function.

from sqlalchemy.sql import func
q = session.query( func.julianday( Bond.final_date) - func.julianday("now"))
for row in q.all():
    print row[0]

Please note that the second approach uses sqlite specific function and won't work with other database systems.


Need Your Help

Call to undefined function curl_init() - with WAMP

php wamp

I'm trying to get cUrl to work, and I'm stumped.

junit testSuite: ClassNotFoundException

java ant junit

I am getting follwing error code for running junit target in ant. EshopCoreTestSuite is a framework as:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.