How to filter by date with sqlalchemy

Question

After I import the modules:

import datetime, uuid
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy import Column, ForeignKey, Integer, String, DateTime, create_engine, literal

I proceed by connecting to db:

Base = declarative_base()
session = scoped_session(sessionmaker())
engine = create_engine('mysql://root:pass@localhost/testdb', echo=False)
session.remove()
session.configure(bind=engine, autoflush=False, expire_on_commit=False)

I define and create MyObject's table:

class MyObject(Base):
    __tablename__ = 'myobjects'
    uuid = Column(String(64), primary_key=True, unique=True)
    created = Column(DateTime, nullable=False) 

    def __init__ (self, *args, **kwargs):
        self.uuid = str(uuid.uuid4()).replace('-','').lower()
        self.created = datetime.datetime.utcnow()

Base.metadata.create_all(engine)

I create 10 MyObject entities:

for i in range(10):
    obj = MyObject()
    session.add(obj)
    session.commit()

I query all the entities by printing their date created:

objects = session.query(MyObject).all()
for obj in objects:
    print obj.created

prints:

2017-01-05 17:22:21
2017-01-05 17:22:21
.
.
.
2017-01-05 17:22:43

Question:

How to query by filtering only those entities that were not created on exactly '2017-01-05 17:22:43'?

I have tried this:

result = session.query(MyObject).filter(MyObject.created!='2017-01-05 17:22:43').all()

But it returns all the objects filtering nothing...

By default, utcnow() includes microseconds.. are you sure those aren't getting stored in the database? What does the raw record look like? — dizzyf
– dizzyf, Commented Jan 5, 2017 at 17:47
Could you post the result of print str(query) so we can see what SQL is being generated? — ACV
– ACV, Commented Jan 5, 2017 at 19:22

user378704 · Accepted Answer · 2017-01-05 17:42:17Z

2

To compare datetimes you will need to convert your string to datetime objects. For e.g. to convert the string 2016-10-20 to datetime you can do so:

from datetime import datetime
print datetime.strptime('2016-10-20', '%Y-%m-%d')

For your question if you are trying to check if the column created doesn't match the time 2017-01-05 17:22:43 then we first convert to datetime object in our query:

from datetime import datetime
search_created = datetime.strptime('2017-01-05 17:22:43', '%Y-%m-%d %H:%M:%S')
result = session.query(MyObject).filter(MyObject.created!=search_created).all()

You can look up more information on strftime and strptime over here.

answered Jan 5, 2017 at 17:42

user378704

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

3 Comments

dizzyf Over a year ago

This is incorrect. Datetime comparisons can be made using date strings... So long as they match the format in the database field.

Piotr Dawidiuk Over a year ago

@dizzyf Are you sure it's wrong? Kiran.koduru compares datetimes, not strings

dizzyf Over a year ago

@PiotrDawidiuk In python, comparing datetimes requires datetime objects. But to filter by datetimes in sqlalchemy, string comparisons work just fine... it's not necessary to cast them to an actual python datetime object first. This answer is misleading.

Collectives™ on Stack Overflow

How to filter by date with sqlalchemy

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related