[sqlalchemy] Re: use_ansi oracle sysdate vs. current_date

[Kent]

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


How would I work that out?


Thank you in advance.



On Apr 2, 4:12 pm, Michael Bayer mike...@zzzcomputing.com wrote:
 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.

Re: [sqlalchemy] PyODBCConnector dbapi question

[Gaetan de Menten]

On Tue, Mar 30, 2010 at 19:32, Michael Bayer mike...@zzzcomputing.com wrote:
 Bo Shi wrote:
 pep 249 specifies list of tuples for fetchmany() and fetchall()

 Hrm, pep-249 seems to only specify sequence and sequence of
 sequences for the fetch*() functions, specifying list of tuples only
 as one possible example.  Perhaps the C implementation of RowProxy is
 being too strict here?  I'm surprised that pyodbc is the only dbapi
 implementation that this problem has occurred in... do all the other
 implementations subclass tuple for their rows?

 we run the tests all the time with Pyodbc, so I wasn't aware this was a
 pyodbc issue.   I'd run without the c extensions for now.   For our C
 extension to coerce into a tuple begins to add overhead and defeat the
 purpose of using the extensions in the first place, though Gaetan would
 have to answer this question.

Supporting arbitrary sequences is cheaper than I expected (you pay
more than previously only if the sequence is neither a tuple nor a
list), so now we are still fast for usual DBAPIs and hopefully don't
break on odd stuff (as long as it is a sequence).

-- 
Gaëtan de Menten

-- 
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

[Michael Bayer]

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]

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

[Kent Bower]

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

[Michael Bayer]

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

[Ian Kelly]

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.

[sqlalchemy] Re: use_ansi oracle sysdate vs. current_date

[Kent]

Just to be inconsistent, with one argument, databases handle this
differently:

postgres:
# select coalesce(0);
 coalesce
--
0
(1 row)


Oracle 10g:

select coalesce(0) from dual
   *
ERROR at line 1:
ORA-00938: not enough arguments for function




On Apr 2, 7:08 pm, Ian Kelly ian.g.ke...@gmail.com wrote:
 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.

[sqlalchemy] Re: Sharding with _BindParamClause

[George V. Reilly]

Michael Bayer wrote:
 check out r0ddd638f1d90 in mercurial.  I've added the function from the
 example below, plus support for in_op(), to the attribute_shard example.
 The old ClauseVisitor method is removed and replaced with this more robust
 method.

Very nice! Thanks, Michael.

/George

-- 
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.

[sqlalchemy] Re: Sharding with _BindParamClause

[George V. Reilly]

On Apr 2, 4:43 pm, George V. Reilly george.v.rei...@gmail.com
wrote:
 Michael Bayer wrote:
  check out r0ddd638f1d90 in mercurial.  I've added the function from the
  example below, plus support for in_op(), to the attribute_shard example.
  The old ClauseVisitor method is removed and replaced with this more robust
  method.

 Very nice! Thanks, Michael.

I blogged about this at 
http://blogs.cozi.com/tech/2010/04/sqlalchemy-sharding.html

/George

-- 
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]

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.

[sqlalchemy] Re: with_polymorphic

[Kent]

After doing more reading, this only relates to multiple class
inheritance, which is useful anytime you want two or more classes to
relate to the same record return from the database, is that an
accurate summary?

I'm trying to get of real-life feel for which situations would
benefit from multiple class inheritance so I can spot them if they
present themselves.

Any input for me or other readers at a general level?



On Apr 1, 8:26 pm, Michael Bayer mike...@zzzcomputing.com wrote:
 On Apr 1, 2010, at 8:22 PM, Kent wrote:

  What is the difference between:

  session.query(Employee).join([Engineer, Manager]).\
     filter(or_(Engineer.engineer_info=='w',
  Manager.manager_data=='q'))

  and

  session.query(Employee).with_polymorphic([Engineer, Manager]).\
     filter(or_(Engineer.engineer_info=='w',
  Manager.manager_data=='q'))

 the join to Engineer is going to create select * from employees join (select 
 * from employees join engineer ...) on ...  not what you want. this is 
 another thing I covered at the tutorial this year.   If OTOH you join to 
 Engineer.__table__ and Manager.__table__, you'll get what you want 
 (with_polymorphic is a convenience feature at this piont and not very 
 flexible).



  ?

  --
  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 
  athttp://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] array_agg() in ORM entities (0.5.8)?

[David Gardner]

I was able to come up with a work-around, since this is part of a 
report, I was able to format the dates as strings

the way I was planning to do in python anyways:

qry=session.query(Task, 
func.cat(func.to_char(TaskHistory.updated,'MM/DD/YY HH:MI am')))


This works well for my needs (cat() isn't a standard Postgres aggregate, 
but instead something I wrote up in plpgsql).






Michael Bayer wrote:
oh. interesting problem, yeah. query is uniqing the values returned 
and assumes they are all hashable - it does this when any of the items 
in the row are full entities (i.e. your Task here). I don't know that 
there's a workaround for now other than using a TypeDecorator that 
turns the returned list into a tuple.



--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgard...@creatureshop.com

--
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.

[sqlalchemy] Can not access the __table__ attribute in string-based primaryjoin parameter

[Jack]

People can use string-based primaryjoin parameter to define the
sqlalchemy.orm.relation instance if people use declarative base
class. But the __table__ attribute is not available. Look at this
example.

Chassis and Blade are sub-class of System.  The sysid property
shares the same name in parent and children classes. And there is one-
to-many relationship between Chassis and Blade. So for example I have
to use Chassis.__table__.c.sysid instead of Chassis.sysid to
define join condition. The error line marked # error happens
# can cause following exception.

I know work-around, like renaming sysid, or using Python clause
instead of string in primaryjoin parameter and defining relation
outside class definition. But I wonder whether it is problem or is by
design. Anyone can help? Thx!

p.s. my sqlalchemy is latest 0.6 (changeset:   6410:09e0ec53d4d0)

##
#exception
##
  File d:\program\src\python\sqlalchemy\lib\sqlalchemy\orm
\mapper.py, line 822, in _get_property
raise sa_exc.InvalidRequestError(Mapper '%s' has no property
'%s' % (str(self), key))
InvalidRequestError: Mapper 'Mapper|Chassis|tbl_chassis' has no
property '__table__'


##
#code
##
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import (Column, ForeignKey, Integer, String)
from sqlalchemy.orm import relation


SYS_TYPE_UNKNOWN = 0
SYS_TYPE_CHASSIS = 1
SYS_TYPE_BLADE = 2

Base = declarative_base()

class System(Base):
'''Generic system'''
__tablename__ = 'tbl_system'
sysid = Column(Integer, primary_key=True, autoincrement=True)
sys_type = Column(Integer)
__mapper_args__ = {'polymorphic_on': sys_type,
'polymorphic_identity': SYS_TYPE_UNKNOWN}


class Chassis(System):
'''BladeCenter Chassis'''
__tablename__ = 'tbl_chassis'
__mapper_args__ = {'polymorphic_identity': SYS_TYPE_CHASSIS}
sysid = Column(Integer, ForeignKey('tbl_system.sysid'),
primary_key=True)

# error happens #
blades = relation('Blade', primaryjoin='Chassis.__table__.c.sysid
== Blade.chassis_sysid', backref='chassis')


class Blade(System):
'''Blade server'''
__tablename__ = 'tbl_blade'
__mapper_args__ = {'polymorphic_identity': SYS_TYPE_BLADE}
sysid = Column(Integer, ForeignKey('tbl_system.sysid'),
primary_key=True)
chassis_sysid = Column(Integer, ForeignKey('tbl_chassis.sysid'))

if __name__ == '__main__':
ch1 = Chassis()

-- 
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: Sharding with _BindParamClause

[Michael Bayer]

George V. Reilly wrote:
 I really don't like the
 isinstance(other_side,
 sqlalchemy.sql.expression._BindParamClause))
 in the middle of _check_side. Is there a cleaner way to do this?

 I found that a combination of
 * the above _check_side and two-sided visit_binary
 * doing a better job of declaring ForeignKey relationships in Columns
 * some explicit primaryjoins in calls to relation()
 cleaned up all the cases where SA wasn't providing the ids in queries

 Perhaps the sharding sample in SA 0.6 could be expanded?

I think what's needed here is a little more savvy to what the visitors
package provides.   ClauseVisitor provides support for some of the hefty
transforming objects SQLA has internally in the sql util package, but for
finding things you're much better off using the functional API, which is
a lot more flexible.   Anytime you find yourself using isinstance(),
which is in fact sometimes necessary, you can instead try to get the
visitor dispatch system to give you that type instead.   Below I've
adapted a recipe that is in the beaker example to also include columns and
operators from binary expressions.   We can certainly add this function to
the sharding example.  Ideally the sharding API would come with some
helper functions, if we could identify some that are of very general use.

from sqlalchemy.sql import visitors

def get_comparisons(query):

binds = {}
columns = set()
result = []
def visit_bindparam(bind):
value = query._params.get(bind.key, bind.value)

# lazyloader may dig a callable in here, intended
# to late-evaluate params after autoflush is called.
# convert to a scalar value.
if callable(value):
value = value()

binds[bind] = value

def visit_column(column):
columns.add(column)

def visit_binary(binary):
if binary.left in columns and binary.right in binds:
result.append((binary.left, binary.operator,
binds[binary.right]))

elif binary.left in binds and binary.right in columns:
result.append((binary.right, binary.operator,
binds[binary.left]))

if query._criterion is not None:
visitors.traverse_depthfirst(query._criterion, {},
{'bindparam':visit_bindparam,
'binary':visit_binary,
'column':visit_column
}
)
return result

if __name__ == '__main__':
from sqlalchemy import *
from sqlalchemy.orm import *


metadata = MetaData()

users = Table('users', metadata,
  Column('id', Integer, primary_key=True, ),
  Column('name', String(30), nullable=False),
)

addresses = Table('addresses', metadata,
  Column('id', Integer, primary_key=True, ),
  Column('user_id', None, ForeignKey('users.id')),
  Column('email_address', String(50), nullable=False)
  )

class User(object):
pass
class Address(object):
pass

mapper(Address, addresses, properties={
'user':relationship(User)
})
mapper(User, users)
sess = create_session()


q = sess.query(User).\
filter(User.id==Address.user_id).\
filter(User.name=='foo').\
filter(Address.email_address.like(bindparam('foo'))).\
params(foo='edward')

for col, op, value in get_comparisons(q):
print col.name, op, value





 --
 /George V. Reilly, Seattle

 --
 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] Before I send

[Michael Bayer]

Michael Mileusnich wrote:
 Hello,

 I know there is documentation on this but I am still fuzzy on certain
 practices when using the session.  In my application I have a function
 that
 returns a new non scoped session.  In one method (method a) I get object o
 from this session.  I call another method (method b) that needs to use o
 and
 potentially make changes to it.  Is it best practice to pass the session
 and
 the object as a parameter to method b or should should I pass only the
 object and use session = Session.object_session(o) to grab that session?
 Is there some other option (like re-fetching that object every time which
 is
 what I am trying to avoid).

usually there's a ScopedSession per application that everyone can call
upon to give the session that they want.If I had an application that
had multiple session configurations, I might use decorators to reconfigure
the ScopedSession global as needed, such as:

@uses_session('write_master')
def do_something(x, y, z, ...)

@uses_session('read_slave')
def do_something(q, p, r, ...)


The case for object_session() is usually for ad-hoc per-object activities,
like a query method on the object itself which wants to say
object_session(self).query(...).

The other options are passing the Session around to all functions, which
could be appropriate in some cases.   Calling object_session(o) on the
passed object is kind of similar to that, i.e. instead of passing o to
your functions you might pass a variable like context which references
the Session as well as the object or objects in question for a particular
operation.  At least that way you give yourself room for expansion.



 --
 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: with_polymorphic

[Michael Bayer]

Kent wrote:
 After doing more reading, this only relates to multiple class
 inheritance, which is useful anytime you want two or more classes to
 relate to the same record return from the database, is that an
 accurate summary?

I wouldn't say its accurate.   SQLA doesn't support any pattern of
multiple inheritance as represented in the database, so a single record
would not correspond to two classes at the same time, unless one class is
a direct superclass of the other.

with_polymorphic() does imply that multiple class identities are 
significant for the overall result set, however, in that you are
expressing criterion that references elements from more than one class.


-- 
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] Can not access the __table__ attribute in string-based primaryjoin parameter

[Michael Bayer]

Jack wrote:
 People can use string-based primaryjoin parameter to define the
 sqlalchemy.orm.relation instance if people use declarative base
 class. But the __table__ attribute is not available. Look at this
 example.

its a limited subset of behaviors which are allowed within the evaluated
strings.  In this case, there is a check to ensure you aren't trying to
use a relationship() or other construct in your string, but it wasn't
prepared for an attribute that isn't mapped at all.  An informative error
message for that has been committed in r6143041d8c4a.

When two tables have the same column you need to name them with different
attributes in order to reference both.   The declarative docs certainly
need an example of this since it is a common use case:

class System(Base):
   ...
   sysid = Column(Integer, primary_key=True, autoincrement=True)
   ...

class Chassis(System):
...
local_sysid = Column(sysid, Integer,
ForeignKey('tbl_system.sysid'),primary_key=True)
blades = relation('Blade',
primaryjoin=Chassis.local_sysid==Blade.chassis_sysid,
backref='chassis')
...


-- 
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: Sharding with _BindParamClause

[Michael Bayer]

Michael Bayer wrote:
 George V. Reilly wrote:
 I really don't like the
 isinstance(other_side,
 sqlalchemy.sql.expression._BindParamClause))
 in the middle of _check_side. Is there a cleaner way to do this?

 I found that a combination of
 * the above _check_side and two-sided visit_binary
 * doing a better job of declaring ForeignKey relationships in Columns
 * some explicit primaryjoins in calls to relation()
 cleaned up all the cases where SA wasn't providing the ids in queries

 Perhaps the sharding sample in SA 0.6 could be expanded?

check out r0ddd638f1d90 in mercurial.  I've added the function from the
example below, plus support for in_op(), to the attribute_shard example. 
The old ClauseVisitor method is removed and replaced with this more robust
method.





 I think what's needed here is a little more savvy to what the visitors
 package provides.   ClauseVisitor provides support for some of the hefty
 transforming objects SQLA has internally in the sql util package, but for
 finding things you're much better off using the functional API, which is
 a lot more flexible.   Anytime you find yourself using isinstance(),
 which is in fact sometimes necessary, you can instead try to get the
 visitor dispatch system to give you that type instead.   Below I've
 adapted a recipe that is in the beaker example to also include columns and
 operators from binary expressions.   We can certainly add this function to
 the sharding example.  Ideally the sharding API would come with some
 helper functions, if we could identify some that are of very general use.

 from sqlalchemy.sql import visitors

 def get_comparisons(query):

 binds = {}
 columns = set()
 result = []
 def visit_bindparam(bind):
 value = query._params.get(bind.key, bind.value)

 # lazyloader may dig a callable in here, intended
 # to late-evaluate params after autoflush is called.
 # convert to a scalar value.
 if callable(value):
 value = value()

 binds[bind] = value

 def visit_column(column):
 columns.add(column)

 def visit_binary(binary):
 if binary.left in columns and binary.right in binds:
 result.append((binary.left, binary.operator,
 binds[binary.right]))

 elif binary.left in binds and binary.right in columns:
 result.append((binary.right, binary.operator,
 binds[binary.left]))

 if query._criterion is not None:
 visitors.traverse_depthfirst(query._criterion, {},
 {'bindparam':visit_bindparam,
 'binary':visit_binary,
 'column':visit_column
 }
 )
 return result

 if __name__ == '__main__':
 from sqlalchemy import *
 from sqlalchemy.orm import *


 metadata = MetaData()

 users = Table('users', metadata,
   Column('id', Integer, primary_key=True, ),
   Column('name', String(30), nullable=False),
 )

 addresses = Table('addresses', metadata,
   Column('id', Integer, primary_key=True, ),
   Column('user_id', None, ForeignKey('users.id')),
   Column('email_address', String(50), nullable=False)
   )

 class User(object):
 pass
 class Address(object):
 pass

 mapper(Address, addresses, properties={
 'user':relationship(User)
 })
 mapper(User, users)
 sess = create_session()


 q = sess.query(User).\
 filter(User.id==Address.user_id).\
 filter(User.name=='foo').\
 filter(Address.email_address.like(bindparam('foo'))).\
 params(foo='edward')

 for col, op, value in get_comparisons(q):
 print col.name, op, value





 --
 /George V. Reilly, Seattle

 --
 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.

Re: [sqlalchemy] PyODBCConnector dbapi question

[Michael Bayer]

Michael Bayer wrote:
 Bo Shi wrote:
 pep 249 specifies list of tuples for fetchmany() and fetchall()

 Hrm, pep-249 seems to only specify sequence and sequence of
 sequences for the fetch*() functions, specifying list of tuples only
 as one possible example.  Perhaps the C implementation of RowProxy is
 being too strict here?  I'm surprised that pyodbc is the only dbapi
 implementation that this problem has occurred in... do all the other
 implementations subclass tuple for their rows?

 we run the tests all the time with Pyodbc, so I wasn't aware this was a
 pyodbc issue.   I'd run without the c extensions for now.   For our C
 extension to coerce into a tuple begins to add overhead and defeat the
 purpose of using the extensions in the first place, though Gaetan would
 have to answer this question.

Looks like we have a fix as of r3b40ceca28d3, cextensions now look for
sequence instead of tuple.










 On Mon, Mar 29, 2010 at 8:03 PM, Michael Bayer
 mike...@zzzcomputing.com
 wrote:
 how come the strack trace shows beta2 as the version number in the
 path ?   did you mean to say between beta1 and beta2 ?   it looks
 specific to the C rewrite of RowProxy.   basically the rows returned by
 fetchone(), fetchall() etc. are expected to be tuples.   pep 249
 specifies list of tuples for fetchmany() and fetchall() though is
 less
 specific for fetchone(), though I'm pretty sure it intends tuples there
 as well.


 On Mar 29, 2010, at 7:43 PM, Bo Shi wrote:

 Also, dunno if it's helpful or not, but this is a regression in
 0.6beta3.  My dialect plugin works as is when using 0.6beta2.

 On Mon, Mar 29, 2010 at 7:41 PM, Bo Shi bs1...@gmail.com wrote:
 Thanks, explicitly assigning self.dbapi in my dialect constructor
 seems to get around the exception.

 I do, however, encounter a new exception:

  File test_vertica.py, line 57, in testTransactionIsolation
    _, iso_level = e.execute('SHOW TRANSACTION_ISOLATION').fetchone()
  File
 /home/vmc/ENV/lib/python2.6/site-packages/SQLAlchemy-0.6beta2-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py,
 line 2204, in fetchone
    return self.process_rows([row])[0]
  File
 /home/vmc/ENV/lib/python2.6/site-packages/SQLAlchemy-0.6beta2-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py,
 line 2163, in process_rows
    for row in rows]
 TypeError: row must be a tuple


 Any idea what's going on?  The stack trace isn't very informative,
 I'm
 afraid.

 On Mon, Mar 29, 2010 at 6:05 PM, Michael Bayer
 mike...@zzzcomputing.com wrote:
 Bo Shi wrote:
 Hello,

 I had a custom dialect based on the PyODBC functionality that was
 working with SQLA SVN-6738.  Upgrading to beta 3, my tests no
 longer
 pass, so I've begun the process updating - on_connect() was easy,
 now
 I'm stumped on connect(...).  I've gotten to the point where, when
 using my dialect, connect() fails because it attempts to run
 self.dbapi.connect(...) but the PyODBC connector seems to implement
 it
 as a classmethod:


 Taking the following from the connector in revision control:

 9     class PyODBCConnector(Connector):

 27       �...@classmethod
 28        def dbapi(cls):
 29            return __import__('pyodbc')

 84        def initialize(self, connection):
 85            # determine FreeTDS first.   can't issue SQL easily
 86            # without getting unicode_statements/binds set up.
 87
 88            pyodbc = self.dbapi
 89
 90            dbapi_con = connection.connection
 91
 92            self.freetds = bool(re.match(r.*libtdsodbc.*\.so,
              dbapi_con.getinfo(pyodbc.SQL_DRIVER_NAME)))


 If dbapi is implemented as a class method, then wouldn't the call
 on
 line 92 fail?  Indeed, that's what I'm seeing.  So is self.dbapi
 getting assigned somewhere else?

 yeah there's a slight misfortune in that naming scheme - the
 @classmethod
 should have some different name, probably import_dbapi.   the
 reassignment takes place on line 102 of
 sqlalchemy/engine/default.py.
 this naming scheme is also present in 0.5 - it was just the
 PyODBCConnector that somehow didn't catch up until recently.






 Thanks,
 Bo

 --
 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.





 --
 Bo Shi
 617-942-1744




 --
 Bo Shi
 617-942-1744

 --
 You received this message because you are subscribed to the Google
 Groups sqlalchemy group.
 To post to this group, send email to 

Re: [sqlalchemy] PyODBCConnector dbapi question

[Bo Shi]

Awesome!  Thanks!

We work with large amounts of time series data so we have high hopes
for the c extension.

Bo

On Fri, Apr 2, 2010 at 2:38 PM, Michael Bayer mike...@zzzcomputing.com wrote:
 Michael Bayer wrote:
 Bo Shi wrote:
 pep 249 specifies list of tuples for fetchmany() and fetchall()

 Hrm, pep-249 seems to only specify sequence and sequence of
 sequences for the fetch*() functions, specifying list of tuples only
 as one possible example.  Perhaps the C implementation of RowProxy is
 being too strict here?  I'm surprised that pyodbc is the only dbapi
 implementation that this problem has occurred in... do all the other
 implementations subclass tuple for their rows?

 we run the tests all the time with Pyodbc, so I wasn't aware this was a
 pyodbc issue.   I'd run without the c extensions for now.   For our C
 extension to coerce into a tuple begins to add overhead and defeat the
 purpose of using the extensions in the first place, though Gaetan would
 have to answer this question.

 Looks like we have a fix as of r3b40ceca28d3, cextensions now look for
 sequence instead of tuple.










 On Mon, Mar 29, 2010 at 8:03 PM, Michael Bayer
 mike...@zzzcomputing.com
 wrote:
 how come the strack trace shows beta2 as the version number in the
 path ?   did you mean to say between beta1 and beta2 ?   it looks
 specific to the C rewrite of RowProxy.   basically the rows returned by
 fetchone(), fetchall() etc. are expected to be tuples.   pep 249
 specifies list of tuples for fetchmany() and fetchall() though is
 less
 specific for fetchone(), though I'm pretty sure it intends tuples there
 as well.


 On Mar 29, 2010, at 7:43 PM, Bo Shi wrote:

 Also, dunno if it's helpful or not, but this is a regression in
 0.6beta3.  My dialect plugin works as is when using 0.6beta2.

 On Mon, Mar 29, 2010 at 7:41 PM, Bo Shi bs1...@gmail.com wrote:
 Thanks, explicitly assigning self.dbapi in my dialect constructor
 seems to get around the exception.

 I do, however, encounter a new exception:

  File test_vertica.py, line 57, in testTransactionIsolation
    _, iso_level = e.execute('SHOW TRANSACTION_ISOLATION').fetchone()
  File
 /home/vmc/ENV/lib/python2.6/site-packages/SQLAlchemy-0.6beta2-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py,
 line 2204, in fetchone
    return self.process_rows([row])[0]
  File
 /home/vmc/ENV/lib/python2.6/site-packages/SQLAlchemy-0.6beta2-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py,
 line 2163, in process_rows
    for row in rows]
 TypeError: row must be a tuple


 Any idea what's going on?  The stack trace isn't very informative,
 I'm
 afraid.

 On Mon, Mar 29, 2010 at 6:05 PM, Michael Bayer
 mike...@zzzcomputing.com wrote:
 Bo Shi wrote:
 Hello,

 I had a custom dialect based on the PyODBC functionality that was
 working with SQLA SVN-6738.  Upgrading to beta 3, my tests no
 longer
 pass, so I've begun the process updating - on_connect() was easy,
 now
 I'm stumped on connect(...).  I've gotten to the point where, when
 using my dialect, connect() fails because it attempts to run
 self.dbapi.connect(...) but the PyODBC connector seems to implement
 it
 as a classmethod:


 Taking the following from the connector in revision control:

 9     class PyODBCConnector(Connector):

 27       �...@classmethod
 28        def dbapi(cls):
 29            return __import__('pyodbc')

 84        def initialize(self, connection):
 85            # determine FreeTDS first.   can't issue SQL easily
 86            # without getting unicode_statements/binds set up.
 87
 88            pyodbc = self.dbapi
 89
 90            dbapi_con = connection.connection
 91
 92            self.freetds = bool(re.match(r.*libtdsodbc.*\.so,
              dbapi_con.getinfo(pyodbc.SQL_DRIVER_NAME)))


 If dbapi is implemented as a class method, then wouldn't the call
 on
 line 92 fail?  Indeed, that's what I'm seeing.  So is self.dbapi
 getting assigned somewhere else?

 yeah there's a slight misfortune in that naming scheme - the
 @classmethod
 should have some different name, probably import_dbapi.   the
 reassignment takes place on line 102 of
 sqlalchemy/engine/default.py.
 this naming scheme is also present in 0.5 - it was just the
 PyODBCConnector that somehow didn't catch up until recently.






 Thanks,
 Bo

 --
 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.





 --
 Bo Shi
 

Re: [sqlalchemy] PyODBCConnector dbapi question

[Bo Shi]

Hrm, some different errors pop up.  I'll move the dialog to the ticket
in question.

http://www.sqlalchemy.org/trac/ticket/1757

On Fri, Apr 2, 2010 at 2:40 PM, Bo Shi bs1...@gmail.com wrote:
 Awesome!  Thanks!

 We work with large amounts of time series data so we have high hopes
 for the c extension.

 Bo

 On Fri, Apr 2, 2010 at 2:38 PM, Michael Bayer mike...@zzzcomputing.com 
 wrote:
 Michael Bayer wrote:
 Bo Shi wrote:
 pep 249 specifies list of tuples for fetchmany() and fetchall()

 Hrm, pep-249 seems to only specify sequence and sequence of
 sequences for the fetch*() functions, specifying list of tuples only
 as one possible example.  Perhaps the C implementation of RowProxy is
 being too strict here?  I'm surprised that pyodbc is the only dbapi
 implementation that this problem has occurred in... do all the other
 implementations subclass tuple for their rows?

 we run the tests all the time with Pyodbc, so I wasn't aware this was a
 pyodbc issue.   I'd run without the c extensions for now.   For our C
 extension to coerce into a tuple begins to add overhead and defeat the
 purpose of using the extensions in the first place, though Gaetan would
 have to answer this question.

 Looks like we have a fix as of r3b40ceca28d3, cextensions now look for
 sequence instead of tuple.










 On Mon, Mar 29, 2010 at 8:03 PM, Michael Bayer
 mike...@zzzcomputing.com
 wrote:
 how come the strack trace shows beta2 as the version number in the
 path ?   did you mean to say between beta1 and beta2 ?   it looks
 specific to the C rewrite of RowProxy.   basically the rows returned by
 fetchone(), fetchall() etc. are expected to be tuples.   pep 249
 specifies list of tuples for fetchmany() and fetchall() though is
 less
 specific for fetchone(), though I'm pretty sure it intends tuples there
 as well.


 On Mar 29, 2010, at 7:43 PM, Bo Shi wrote:

 Also, dunno if it's helpful or not, but this is a regression in
 0.6beta3.  My dialect plugin works as is when using 0.6beta2.

 On Mon, Mar 29, 2010 at 7:41 PM, Bo Shi bs1...@gmail.com wrote:
 Thanks, explicitly assigning self.dbapi in my dialect constructor
 seems to get around the exception.

 I do, however, encounter a new exception:

  File test_vertica.py, line 57, in testTransactionIsolation
    _, iso_level = e.execute('SHOW TRANSACTION_ISOLATION').fetchone()
  File
 /home/vmc/ENV/lib/python2.6/site-packages/SQLAlchemy-0.6beta2-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py,
 line 2204, in fetchone
    return self.process_rows([row])[0]
  File
 /home/vmc/ENV/lib/python2.6/site-packages/SQLAlchemy-0.6beta2-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py,
 line 2163, in process_rows
    for row in rows]
 TypeError: row must be a tuple


 Any idea what's going on?  The stack trace isn't very informative,
 I'm
 afraid.

 On Mon, Mar 29, 2010 at 6:05 PM, Michael Bayer
 mike...@zzzcomputing.com wrote:
 Bo Shi wrote:
 Hello,

 I had a custom dialect based on the PyODBC functionality that was
 working with SQLA SVN-6738.  Upgrading to beta 3, my tests no
 longer
 pass, so I've begun the process updating - on_connect() was easy,
 now
 I'm stumped on connect(...).  I've gotten to the point where, when
 using my dialect, connect() fails because it attempts to run
 self.dbapi.connect(...) but the PyODBC connector seems to implement
 it
 as a classmethod:


 Taking the following from the connector in revision control:

 9     class PyODBCConnector(Connector):

 27       �...@classmethod
 28        def dbapi(cls):
 29            return __import__('pyodbc')

 84        def initialize(self, connection):
 85            # determine FreeTDS first.   can't issue SQL easily
 86            # without getting unicode_statements/binds set up.
 87
 88            pyodbc = self.dbapi
 89
 90            dbapi_con = connection.connection
 91
 92            self.freetds = bool(re.match(r.*libtdsodbc.*\.so,
              dbapi_con.getinfo(pyodbc.SQL_DRIVER_NAME)))


 If dbapi is implemented as a class method, then wouldn't the call
 on
 line 92 fail?  Indeed, that's what I'm seeing.  So is self.dbapi
 getting assigned somewhere else?

 yeah there's a slight misfortune in that naming scheme - the
 @classmethod
 should have some different name, probably import_dbapi.   the
 reassignment takes place on line 102 of
 sqlalchemy/engine/default.py.
 this naming scheme is also present in 0.5 - it was just the
 PyODBCConnector that somehow didn't catch up until recently.






 Thanks,
 Bo

 --
 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 

[sqlalchemy] Re: use_ansi oracle sysdate vs. current_date

[Kent]

Any chance you plan to make func.now consider oracle and
use_ansi=False (and return sysdate)?



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.

Re: [sqlalchemy] Re: use_ansi oracle sysdate vs. current_date

[Michael Bayer]

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.