How can I rewrite the following sql statement with sqlalchemy in python. I have been searching for 30 mins but still couldn't find any solutions.
DATEADD(NOW(), INTERVAL 1 DAY)
or
INSERT INTO dates (expire)
VALUES(DATEADD(NOW(), INTERVAL 1 DAY))
Thanks in advance
For completeness sake, here is how you'd generate that exact SQL with using sqlalchemy.sql.func:
from sqlalchemy.sql import func
from sqlalchemy.sql.expression import bindparam
from sqlalchemy import Interval
tomorrow = func.dateadd(func.now(), bindparam('tomorrow', timedelta(days=1), Interval()))
which results in:
>>> from sqlalchemy.sql import func
>>> func.dateadd(func.now(), bindparam('tomorrow', timedelta(days=1), Interval(native=True)))
<sqlalchemy.sql.expression.Function at 0x100f559d0; dateadd>
>>> str(func.dateadd(func.now(), bindparam('tomorrow', timedelta(days=1), Interval(native=True))))
'dateadd(now(), :tomorrow)'
Alternatively you could use a text() object to specify the interval instead:
from sqlalchemy.sql import func
from sqlalchemy.sql.expression import text
tomorrow = func.dateadd(func.now(), text('interval 1 day'))
Interval only works with certain DBs & the text version may be required for those.
DATEADD(NOW(), INTERVAL 1 DAY), i.e. MySQL, but SQLAlchemy does not because the adapter has issues with type.SQLAlchemy dates automagically map to Python datetime objects, so you should just be able to do:
from sqlalchemy import Table, Column, MetaData, DateTime
from datetime import datetime, timedelta
metadata = MetaData()
example = Table('users', metadata,
Column('expire', DateTime)
)
tomorrow = datetime.now() + timedelta(days=1)
ins = example.insert().values(expire=tomorrow)
now() isn't the same as SQL's now(). It matters on which machine you take the timestamp.Doobeh beat me to it while I was typing, here's a flask-sqlalchemy example I was going to post though (to compliment the plain sqlalchemy example):
from flask.ext.sqlalchemy import SQLAlchemy
from datetime import datetime, timedelta
db = SQLAlchemy()
class Thing(db.Model):
id = db.Column(db.Integer, primary_key=True)
created = db.Column(db.DateTime)
c = Thing(created = datetime.utcnow() + timedelta(days=1))
print repr(c.created)
# datetime.datetime(2013, 3, 23, 15, 5, 48, 136583)
You can pass default as a callable too:
from flask.ext.sqlalchemy import SQLAlchemy
from datetime import datetime, timedelta
db = SQLAlchemy()
def tomorrow():
return datetime.utcnow() + timedelta(days=1)
class Thing(db.Model):
id = db.Column(db.Integer, primary_key=True)
publish_date = db.Column(db.DateTime, default=tomorrow)
You may use MySQL timestampadd insted dateadd.
More detail http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_timestampadd
from datetime import datetime, timedelta
from dateutil import tz
new_date = datetime.now(tz=tz.tzlocal()) + timedelta(days=1)
new_item = Expire(expire=new_date)
session.save(new_item)
session.commit()
Specifying model default value like this worked for me. SQLAlchemy, however, expects you to pass a value to the attribute for the validator to be triggered.
from uuid import uuid4, UUID
from datetime import datetime, timedelta
from sqlalchemy.sql import func
from sqlalchemy.orm import (
validates,
declarative_base,
)
from sqlalchemy import (
Column,
DateTime,
)
Base = declarative_base()
class SomeModel(Base):
__tablename__ = "some_model"
id = Column(UUID, primary_key=True, default=lambda: uuid4().hex)
created = Column(DateTime, server_default=func.now())
expires = Column(DateTime)
@validates("expires")
def set_expiration(self, key, value):
return datetime.utcnow() + timedelta(days=1)
SomeModel(expires=None)
