The database is OK. I found out that calling func.datetime on value in
declarative filtering fixed query and returned expected result.
query = query.filter(Invoice.InvoiceDate == func.datetime(datetime.datetime(
2007, 1, 1)))
echo:
SELECT "Invoice"."InvoiceId" AS "Invoice_InvoiceId", "Invoice"."InvoiceDate"
AS "Invoice_InvoiceDate"
FROM "Invoice"
WHERE "Invoice"."InvoiceDate" = datetime(:datetime_1)
2015-06-04 15:13:58,297 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2015-06-04 15:13:58,298 INFO sqlalchemy.engine.base.Engine SELECT "Invoice".
"InvoiceId" AS "Invoice_InvoiceId", "Invoice"."InvoiceDate" AS
"Invoice_InvoiceDate"
FROM "Invoice"
WHERE "Invoice"."InvoiceDate" = datetime(?)
2015-06-04 15:13:58,298 INFO sqlalchemy.engine.base.Engine ('2007-01-01
00:00:00.000000',)
declarative: 1
Now I'm thinking how I could make it more general for any SQLite Datetime
type.
Having read
http://docs.sqlalchemy.org/en/latest/core/custom_types.html#applying-sql-level-bind-result-processing
I'm trying with making custom type that would by default call that
func.datetime.
class SQLiteDateTime(DateTime):
def bind_expression(self, bindvalue):
return func.datetime(bindvalue, type_=self)
So far no luck but is it right approach for that problem?
Thanks,
Mike
On Thursday, June 4, 2015 at 1:50:58 PM UTC-4, Michael Bayer wrote:
>
>
>
> 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+...@googlegroups.com <javascript:>.
> To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>.
> 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.
