Re: [sqlalchemy] Re: use_ansi oracle sysdate vs. current_date
Kent wrote: Along the same lines, is there something we can do about nvl() (oracle) versus coalesce() (ansi)? They aren't exactly the same, unfortunately (nvl takes exactly 2 arguments, no more), so maybe there is nothing 'official' you can do, but can you help me work it out for my project? I assume it is very similar to what you helped me out with above...something like this: from sqlalchemy.sql.expression import ColumnElement from sqlalchemy.ext.compiler import compiles from sqlalchemy.types import DateTime, Date class current_date(ColumnElement): type = Date() @compiles(current_date) def _compiler_dispatch(element, compiler, **kw): if compiler.dialect.name == 'oracle': if compiler.dialect.use_ansi: return trunc(current_date) else: return trunc(sysdate) else: # assume postgres return current_date But the main difference is it takes arguments. Is there a clever way to return the appropriate function, something like this: def if compiler.dialect.name == 'oracle': return func.nvl else: # assume postgres return func.coalesce I will add this to the docs: from sqlalchemy import * from sqlalchemy.ext.compiler import compiles from sqlalchemy.sql.expression import FunctionElement class coalesce(FunctionElement): name = 'coalesce' @compiles(coalesce) def compile(element, compiler, **kw): return coalesce(%s) % compiler.process(element.clauses) @compiles(coalesce, 'oracle') def compile(element, compiler, **kw): if len(element.clauses) 2: raise TypeError(coalesce only supports two arguments on Oracle) return nvl(%s) % compiler.process(element.clauses) print coalesce(5, 6) from sqlalchemy.dialects import oracle print coalesce(5, 6).compile(dialect=oracle.dialect()) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Re: use_ansi oracle sysdate vs. current_date
Thanks very much. On 4/2/2010 5:41 PM, Michael Bayer wrote: Kent wrote: Along the same lines, is there something we can do about nvl() (oracle) versus coalesce() (ansi)? They aren't exactly the same, unfortunately (nvl takes exactly 2 arguments, no more), so maybe there is nothing 'official' you can do, but can you help me work it out for my project? I assume it is very similar to what you helped me out with above...something like this: from sqlalchemy.sql.expression import ColumnElement from sqlalchemy.ext.compiler import compiles from sqlalchemy.types import DateTime, Date class current_date(ColumnElement): type = Date() @compiles(current_date) def _compiler_dispatch(element, compiler, **kw): if compiler.dialect.name == 'oracle': if compiler.dialect.use_ansi: return trunc(current_date) else: return trunc(sysdate) else: # assume postgres return current_date But the main difference is it takes arguments. Is there a clever way to return the appropriate function, something like this: def if compiler.dialect.name == 'oracle': return func.nvl else: # assume postgres return func.coalesce I will add this to the docs: from sqlalchemy import * from sqlalchemy.ext.compiler import compiles from sqlalchemy.sql.expression import FunctionElement class coalesce(FunctionElement): name = 'coalesce' @compiles(coalesce) def compile(element, compiler, **kw): return coalesce(%s) % compiler.process(element.clauses) @compiles(coalesce, 'oracle') def compile(element, compiler, **kw): if len(element.clauses) 2: raise TypeError(coalesce only supports two arguments on Oracle) return nvl(%s) % compiler.process(element.clauses) print coalesce(5, 6) from sqlalchemy.dialects import oracle print coalesce(5, 6).compile(dialect=oracle.dialect()) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Re: use_ansi oracle sysdate vs. current_date
As an aside, more recent Oracles support ansi coalesce, so it is probably more appropriate to only use nvl() for older, non-ansi Oracle versions, but if this is only for illustration then that is not a big deal (unless you don't want people writing to you saying coalesce does support more than 2 arguments. In my case, I'd rather rework the nvl() function to work on other engines besides Oracle as an alias to coalesce(), seems less ambiguous. Thanks again. On 4/2/2010 5:41 PM, Michael Bayer wrote: Kent wrote: Along the same lines, is there something we can do about nvl() (oracle) versus coalesce() (ansi)? They aren't exactly the same, unfortunately (nvl takes exactly 2 arguments, no more), so maybe there is nothing 'official' you can do, but can you help me work it out for my project? I assume it is very similar to what you helped me out with above...something like this: from sqlalchemy.sql.expression import ColumnElement from sqlalchemy.ext.compiler import compiles from sqlalchemy.types import DateTime, Date class current_date(ColumnElement): type = Date() @compiles(current_date) def _compiler_dispatch(element, compiler, **kw): if compiler.dialect.name == 'oracle': if compiler.dialect.use_ansi: return trunc(current_date) else: return trunc(sysdate) else: # assume postgres return current_date But the main difference is it takes arguments. Is there a clever way to return the appropriate function, something like this: def if compiler.dialect.name == 'oracle': return func.nvl else: # assume postgres return func.coalesce I will add this to the docs: from sqlalchemy import * from sqlalchemy.ext.compiler import compiles from sqlalchemy.sql.expression import FunctionElement class coalesce(FunctionElement): name = 'coalesce' @compiles(coalesce) def compile(element, compiler, **kw): return coalesce(%s) % compiler.process(element.clauses) @compiles(coalesce, 'oracle') def compile(element, compiler, **kw): if len(element.clauses) 2: raise TypeError(coalesce only supports two arguments on Oracle) return nvl(%s) % compiler.process(element.clauses) print coalesce(5, 6) from sqlalchemy.dialects import oracle print coalesce(5, 6).compile(dialect=oracle.dialect()) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Re: use_ansi oracle sysdate vs. current_date
Kent Bower wrote: As an aside, more recent Oracles support ansi coalesce, so it is probably more appropriate to only use nvl() for older, non-ansi Oracle versions, but if this is only for illustration then that is not a big deal (unless you don't want people writing to you saying coalesce does support more than 2 arguments. In my case, I'd rather rework the nvl() function to work on other engines besides Oracle as an alias to coalesce(), seems less ambiguous. ultimately the oracle dialect should do the same thing we want it to do for now() here. return nvl() on oracle 8. Thanks again. On 4/2/2010 5:41 PM, Michael Bayer wrote: Kent wrote: Along the same lines, is there something we can do about nvl() (oracle) versus coalesce() (ansi)? They aren't exactly the same, unfortunately (nvl takes exactly 2 arguments, no more), so maybe there is nothing 'official' you can do, but can you help me work it out for my project? I assume it is very similar to what you helped me out with above...something like this: from sqlalchemy.sql.expression import ColumnElement from sqlalchemy.ext.compiler import compiles from sqlalchemy.types import DateTime, Date class current_date(ColumnElement): type = Date() @compiles(current_date) def _compiler_dispatch(element, compiler, **kw): if compiler.dialect.name == 'oracle': if compiler.dialect.use_ansi: return trunc(current_date) else: return trunc(sysdate) else: # assume postgres return current_date But the main difference is it takes arguments. Is there a clever way to return the appropriate function, something like this: def if compiler.dialect.name == 'oracle': return func.nvl else: # assume postgres return func.coalesce I will add this to the docs: from sqlalchemy import * from sqlalchemy.ext.compiler import compiles from sqlalchemy.sql.expression import FunctionElement class coalesce(FunctionElement): name = 'coalesce' @compiles(coalesce) def compile(element, compiler, **kw): return coalesce(%s) % compiler.process(element.clauses) @compiles(coalesce, 'oracle') def compile(element, compiler, **kw): if len(element.clauses) 2: raise TypeError(coalesce only supports two arguments on Oracle) return nvl(%s) % compiler.process(element.clauses) print coalesce(5, 6) from sqlalchemy.dialects import oracle print coalesce(5, 6).compile(dialect=oracle.dialect()) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Re: use_ansi oracle sysdate vs. current_date
On Fri, Apr 2, 2010 at 3:41 PM, Michael Bayer mike...@zzzcomputing.comwrote: Kent wrote: Along the same lines, is there something we can do about nvl() (oracle) versus coalesce() (ansi)? They aren't exactly the same, unfortunately (nvl takes exactly 2 arguments, no more), so maybe there is nothing 'official' you can do, but can you help me work it out for my project? I assume it is very similar to what you helped me out with above...something like this: from sqlalchemy.sql.expression import ColumnElement from sqlalchemy.ext.compiler import compiles from sqlalchemy.types import DateTime, Date class current_date(ColumnElement): type = Date() @compiles(current_date) def _compiler_dispatch(element, compiler, **kw): if compiler.dialect.name == 'oracle': if compiler.dialect.use_ansi: return trunc(current_date) else: return trunc(sysdate) else: # assume postgres return current_date But the main difference is it takes arguments. Is there a clever way to return the appropriate function, something like this: def if compiler.dialect.name == 'oracle': return func.nvl else: # assume postgres return func.coalesce I will add this to the docs: from sqlalchemy import * from sqlalchemy.ext.compiler import compiles from sqlalchemy.sql.expression import FunctionElement class coalesce(FunctionElement): name = 'coalesce' @compiles(coalesce) def compile(element, compiler, **kw): return coalesce(%s) % compiler.process(element.clauses) @compiles(coalesce, 'oracle') def compile(element, compiler, **kw): if len(element.clauses) 2: raise TypeError(coalesce only supports two arguments on Oracle) return nvl(%s) % compiler.process(element.clauses) print coalesce(5, 6) from sqlalchemy.dialects import oracle print coalesce(5, 6).compile(dialect=oracle.dialect()) Might this work as a more complete solution for Oracle? @compiles(coalesce, 'oracle') def compile(element, compiler, **kw): sql = nvl(%s) for i in xrange(len(element.clauses) - 2): sql %= %s, nvl(%%s) % compiler.process(element.clauses[i:i+1]) return sql % compiler.process(element.clauses[-2:]) Ian -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Re: use_ansi oracle sysdate vs. current_date
By the way, Unless a ClauseList is subscriptable in 0.6, I had problems the way it was. Here is what I did: @compiles(coalesce, 'oracle') def compile(element, compiler, **kw): sql = nvl(%s) clauses = map(compiler.process, element.clauses) for i in xrange(len(clauses) - 2): sql %= %s, nvl(%%s) % clauses[i] return sql % , .join(clauses[-2:]) On 4/2/2010 7:08 PM, Ian Kelly wrote: On Fri, Apr 2, 2010 at 3:41 PM, Michael Bayer mike...@zzzcomputing.com mailto:mike...@zzzcomputing.com wrote: Kent wrote: Along the same lines, is there something we can do about nvl() (oracle) versus coalesce() (ansi)? They aren't exactly the same, unfortunately (nvl takes exactly 2 arguments, no more), so maybe there is nothing 'official' you can do, but can you help me work it out for my project? I assume it is very similar to what you helped me out with above...something like this: from sqlalchemy.sql.expression import ColumnElement from sqlalchemy.ext.compiler import compiles from sqlalchemy.types import DateTime, Date class current_date(ColumnElement): type = Date() @compiles(current_date) def _compiler_dispatch(element, compiler, **kw): if compiler.dialect.name http://compiler.dialect.name == 'oracle': if compiler.dialect.use_ansi: return trunc(current_date) else: return trunc(sysdate) else: # assume postgres return current_date But the main difference is it takes arguments. Is there a clever way to return the appropriate function, something like this: def if compiler.dialect.name http://compiler.dialect.name == 'oracle': return func.nvl else: # assume postgres return func.coalesce I will add this to the docs: from sqlalchemy import * from sqlalchemy.ext.compiler import compiles from sqlalchemy.sql.expression import FunctionElement class coalesce(FunctionElement): name = 'coalesce' @compiles(coalesce) def compile(element, compiler, **kw): return coalesce(%s) % compiler.process(element.clauses) @compiles(coalesce, 'oracle') def compile(element, compiler, **kw): if len(element.clauses) 2: raise TypeError(coalesce only supports two arguments on Oracle) return nvl(%s) % compiler.process(element.clauses) print coalesce(5, 6) from sqlalchemy.dialects import oracle print coalesce(5, 6).compile(dialect=oracle.dialect()) Might this work as a more complete solution for Oracle? @compiles(coalesce, 'oracle') def compile(element, compiler, **kw): sql = nvl(%s) for i in xrange(len(element.clauses) - 2): sql %= %s, nvl(%%s) % compiler.process(element.clauses[i:i+1]) return sql % compiler.process(element.clauses[-2:]) Ian -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Re: use_ansi oracle sysdate vs. current_date
Kent wrote: Any chance you plan to make func.now consider oracle and use_ansi=False (and return sysdate)? the use_ansi flag should be removed as far as public API since we can detect server version now. we can also make now() return sysdate() unconditionally on oracle, you should give us a trac ticket for the latter on milestone 0.6. On Feb 3, 4:44 pm, Michael Bayer mike...@zzzcomputing.com wrote: Kent wrote: Any chance SQLAlchemy has a mechanism to switch to or fromsysdatevs. current_datebased on the database dialect (and maybe use_ansi)? It would be really nice if I could program in sqla not knowing which database type I am connected to and still could select the current date from the database... can I? func.now() does do this (well, it returns CURRENT_TIMESTAMP, isCURRENT_DATEdifferent?). it doesn't take use_ansi into account though (though that would be an easy patch to theOracledialect). Aside from all that, this is also easy enough to roll yourself: from sqlalchemy.sql.expression import ColumnElement from sqlalchemy.ext.compiler import compiles classcurrent_date(ColumnElement): type = sa.DateTime() @compiler.compiles(current_date) def _compiler_dispatch(element, compiler, **kw): if compiler.dialect.name == 'oracle': if not compiler.dialect.use_ansi: return sysdate else: return current_date else: # etc ... then just saycurrent_date() to get the expression. you could also throw @compiles onto sqlalchemy.sql.functions.now if you wanted to augment what func.now() returns. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.