Slava Tutushkin wrote:
>
>
>
> On Sep 8, 6:18 pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote:
>> "julianday" being a SQLite function ? i.e. you'd like SQLite to do the
>> date arithmetic from the ISO value for you ? or you'd like your custom
>> type to provide the "julianday" numeric value ?
>>
>> better yet, to ensure perfect clarity here, provide a plain pysqlite
>> script that illustrates what API/sqlite features you'd like to use.
>
> Yes, julianday is sqlite function. It accepts ISO string and returns
> julianday number. SQLite's datetime stuff description can be found
> here: http://sqlite.org/lang_datefunc.html
>
> I'll try to explain what I want to achieve.
> I have the table description:
>
> dates_table = Table('dates', md,
> Column('id', Integer, primary_key=True),
> Column('dt', sqlalchemy.DateTime),
> )
>
>
> session.add(Record(datetime.utcnow()))
right so, just as I told someone the other day about oracle geo types,
TypeEngines don't support embedding of SQL expressions right now, they
only deal with values sent as bind arguments to cursor.execute() and
cursor.executemany(). So the answer is the same as that which I gave to
that person, you have to create/utilize a custom clauseelement:
from sqlalchemy.sql import ClauseElement
from sqlalchemy.ext.compiler import compiles
class julianday(ClauseElement):
def __init__(self, date):
self.date = date
@compiles(julianday)
def compile_julianday(compiler, element, **kw):
return "julianday(....)" % element.date
julianday returns a numeric value (right ?) so you need to use the Numeric
type for your column (i.e. not DateTime, which is hardwired to load in the
SLDateTime that isn't going to do what you want). You then likely want
to use TypeDecorator around the Numeric as I stated earlier so that you
can override the result_processor() to return a datetime object (or
whatever kind of value you'd like to get back).
you then need to specify julianday(mydate) whenever sending a date into an
expression. It should work transparently for inserts/selects/ORM
operations.
I also experimented with overriding compilation for "bindparam" directly,
this approach only works for selects so far, so this may or may not be
helpful:
from sqlalchemy import *
from sqlalchemy.types import TypeDecorator
from sqlalchemy.sql.expression import _BindParamClause
from sqlalchemy.ext.compiler import compiles
@compiles(_BindParamClause)
def compile_bind(element, compiler, **kw):
if isinstance(element.type, MyType):
return "my_func(%s)" % compiler.visit_bindparam(element, **kw)
else:
return compiler.visit_bindparam(element, **kw)
class MyType(TypeDecorator):
impl = Numeric
m = MetaData()
t = Table('t1', m,
Column('id', Integer, primary_key=True),
Column('data', MyType),
)
print t.select().where(t.c.data=="some data")
For the column selection side of things, you probably want to map your ORM
object to the expression of your choice using column_property() - that
approach would only take place at the ORM level though and it wouldn't
work for writes (you'd have to use the "real" column-mapped attribute for
writes).
For a more transparent approach, you'd have a bigger job - its possible to
use a custom Column subclass in conjunction with another @compiles
decorator, but you might have to carefully mimic what the compiler's
current visit_column() method does as far as the "result_map" variable in
order for this to work correctly - and you'd also have to detect, using
the presence of the same "result_map" attribute, when the column is the
"top level" column in the "columns" clause of a select, versus being used
in the WHERE clause in a comparison or in the columns clause of an INSERT,
UPDATE, etc.
since two people have asked for this functionality in the past two days,
I've opened ticket #1534 to address this feature. it's targeted at an
eventual 0.6 version.
>
> So only textual modifications to the queries are needed. Values to and
> from the query shall be passed as it is now, as ISO strings.
as an aside, the DateTime types expect Python datetime objects, not
strings. I thought that would be raising an error for the current sqlite
implementation if you passed a string.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
