On 6/4/15 11:57 AM, mdob wrote:
Hi,
It seems I have different result when filtering sqlite database on
datetime column.
I don't get any results when filtering declarative way
|
Invoice.InvoiceDate==datetime.datetime(2007,01,01)
|
same when hen executing raw query with engine.execute
|
'select * from Invoice where InvoiceDate = "2007-01-01"'
|
but it works when using sqlite datetime function in sql statement
|
'select * from Invoice where InvoiceDate = datetime("2007-01-01") '
|
My objective is to be able to filter declarative way and I'm not sure
either I am missing something or there's an issue with declarative
filtering. Any Ideas?
the literal string 'datetime("2007-01-01")' is probably what is directly
present in the database. I'd guess some prior version of the
application or other weird event is responsible for this, I'd log into
the database using sqlite3 directly just to see if this is the case, and
then look into repairing that data.
Below some testing code and here's link to chinook
databases http://chinookdatabase.codeplex.com/
Any help much appreciated.
Mike
|
import datetime
from sqlalchemy import create_engine, text, Column, Integer, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class Invoice(Base):
__tablename__ = 'Invoice'
InvoiceId = Column(Integer, primary_key=True)
InvoiceDate = Column(DateTime)
def __repr__(self):
return 'Invoice {}'.format(self.InvoiceId)
engine = create_engine('sqlite:////home/mike/chinook.sqlite')
def declarative():
Session = sessionmaker(bind=engine)
session = Session()
query = session.query(Invoice)
query = query.filter(Invoice.InvoiceDate == datetime.datetime(2007,
01, 01))
result = query.all()
for invoice in result:
print invoice.InvoiceId, invoice.InvoiceDate
print 'declarative:', len(result)
def core():
sql = text('select * from Invoice where InvoiceDate = "2007-01-01"')
query = engine.execute(sql)
result = query.fetchall()
print 'core: ', len(result)
def core_fun():
sql = text('select * from Invoice where InvoiceDate =
datetime("2007-01-01") ')
query = engine.execute(sql)
result = query.fetchall()
print 'core with datetime fun: ', len(result)
declarative()
core()
core_fun()
|
Result
declarative: 0
core: 0
core with datetime fun: 1
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
