On 6/4/15 3:22 PM, mdob wrote:
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-0415:13:58,297INFO sqlalchemy.engine.base.EngineBEGIN(implicit)
2015-06-0415:13:58,298INFO sqlalchemy.engine.base.EngineSELECT
"Invoice"."InvoiceId"AS "Invoice_InvoiceId","Invoice"."InvoiceDate"AS
"Invoice_InvoiceDate"
FROM "Invoice"
WHERE "Invoice"."InvoiceDate"=datetime(?)
2015-06-0415:13:58,298INFO sqlalchemy.engine.base.Engine('2007-01-01
00:00:00.000000',)
declarative:1
|
oh, missed the quoting not being there, sure.
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.
|
classSQLiteDateTime(DateTime):
defbind_expression(self,bindvalue):
returnfunc.datetime(bindvalue,type_=self)
|
So far no luck but is it right approach for that problem?
that actually is the right approach, whenever this type is referred to
in SQL it will be surrounded by that function.
However you might want to use a TypeDecorator around DateTime, which
tends to be the easiest way to make a custom type. Not sure, I'd have
to play with it.
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/
<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
<http://groups.google.com/group/sqlalchemy>.
For more options, visit https://groups.google.com/d/optout
<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
<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.
