Re: [sqlalchemy] Determination of string lengths

[Mike Bayer]

On 12/10/2015 12:08 PM, Jonathan Vanasco wrote:
> `sqlalchemy.func` does not map anything.  It is a namespace for a
> factory generator.   anything you access with it becomes a function of
> that caller's name.
> 
> for example:
> 
>  filter( func.foo(table.column) > 1 )
> 
> produces
> 
>   WHERE foo(table.column) > 1
> 
> sqlalchemy generates the `foo` function dynamically though the `func`
> namespace.
> 
> In your example, `func.length` creates the sql "LENGTH()" not "LEN()".
>  It works because your backend supports "LENGTH" not "LEN".  Most
> databases use LENGTH (postgres, mysql, oracle, sqlite).  Only MsSQL uses
> LEN, and firebird has a completely different approach with CHAR_LENGTH,
> BIT_LENGTH, etc.
> 
> I don't think any more portability has ever been needed, because the
> functions are either:
> 
> * standardized across most databases due to common standards
> * highly specfiic to a single database
> 
> Trying to create a system that standardizes how every database handles
> ancillary internal function "concepts" would be overwhelming and of
> little real utility.   

Well, I can see how it would be useful for general categories of
functions - string functions, math functions, and especially date
functions, which would be a huge job as all databases handle dates very
differently.  But at the very least it would probably less confusing
if these "translated" functions were in a different namespace besides
func., because, if some of the functions in func. translate and others
don't, it's not consistent and the user has no idea what will happen.
So first off, if we added that feature, people at this point wouldn't
even know about it and it wouldn't get enough test coverage.  Then
secondly, it's of course a huge maintenance issue especially for date
functions and such.

Right now, when you really have a SQL function that you need to
translate across backends, we supply the @compiles approach.  See the
examples at
http://docs.sqlalchemy.org/en/rel_1_0/core/compiler.html#further-examples.








> 
> If you really wanted to pursue this, I'd imagine you would work on the
> SQL compliler or just inspect the db connection when you generate the
> query and conditionally use different forms.  
> 
> -- 
> 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.

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

Re: [sqlalchemy] Determination of string lengths

[Jonathan Vanasco]

`sqlalchemy.func` does not map anything.  It is a namespace for a factory 
generator.   anything you access with it becomes a function of that 
caller's name.

for example:

 filter( func.foo(table.column) > 1 )

produces

  WHERE foo(table.column) > 1

sqlalchemy generates the `foo` function dynamically though the `func` 
namespace.

In your example, `func.length` creates the sql "LENGTH()" not "LEN()".  It 
works because your backend supports "LENGTH" not "LEN".  Most databases use 
LENGTH (postgres, mysql, oracle, sqlite).  Only MsSQL uses LEN, and 
firebird has a completely different approach with CHAR_LENGTH, BIT_LENGTH, 
etc.

I don't think any more portability has ever been needed, because the 
functions are either:

* standardized across most databases due to common standards
* highly specfiic to a single database

Trying to create a system that standardizes how every database handles 
ancillary internal function "concepts" would be overwhelming and of little 
real utility.   

If you really wanted to pursue this, I'd imagine you would work on the SQL 
compliler or just inspect the db connection when you generate the query and 
conditionally use different forms.  

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

Re: [sqlalchemy] Determination of string lengths

[SF Markus Elfring]

> I don't know which database you are using,
> but in postgresql there certainly is a function called "length"

How do you think about to avoid the direct reuse of functions
which are specific for a few database software implementations?

Is there any more portability possible around string functions?

Regards,
Markus

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

Re: [sqlalchemy] Determination of string lengths

['Robert Forkel' via sqlalchemy]

I don't know which database you are using, but in postgresql there
certainly is a function called "length" (see
http://www.postgresql.org/docs/9.1/static/functions-string.html)

On Thu, Dec 10, 2015 at 2:04 PM, SF Markus Elfring <
elfr...@users.sourceforge.net> wrote:

> > As stated in the docs:
> > "Note that any name not known to func generates the function name as is
> > - there is no restriction on what SQL functions can be called,
> > known or unknown to SQLAlchemy, built-in or user defined."
>
> I get an error message like "Function len(text) does not exist." if I try
> something out like the following on a string field.
>
> …
>   for length, \
>   incidence in session.query(func.len(position.label),
>  func.count(position.label)
> ).group_by(func.len(position.label)) \
>  .order_by(func.len(position.label)):
> …
>
>
> Is it interesting that the following approach seems to work instead?
>
> …
>   for length, \
>   incidence in session.query(func.length(position.label),
>  func.count(position.label)
>
> ).group_by(func.length(position.label)) \
>
>  .order_by(func.length(position.label)):
> …
>
>
> How is the function "length" mapped to the SQL function "LEN"?
>
> Regards,
> Markus
>
> --
> 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.
>

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

Re: [sqlalchemy] Determination of string lengths

[SF Markus Elfring]

> As stated in the docs:
> "Note that any name not known to func generates the function name as is
> - there is no restriction on what SQL functions can be called,
> known or unknown to SQLAlchemy, built-in or user defined."

I get an error message like "Function len(text) does not exist." if I try
something out like the following on a string field.

…
  for length, \
  incidence in session.query(func.len(position.label),
 func.count(position.label)
).group_by(func.len(position.label)) \
 .order_by(func.len(position.label)):
…


Is it interesting that the following approach seems to work instead?

…
  for length, \
  incidence in session.query(func.length(position.label),
 func.count(position.label)
).group_by(func.length(position.label)) \
 .order_by(func.length(position.label)):
…


How is the function "length" mapped to the SQL function "LEN"?

Regards,
Markus

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

Re: [sqlalchemy] Determination of string lengths

['Robert Forkel' via sqlalchemy]

As stated in the docs:

"Note that any name not known to func

generates the function name as is - there is no restriction on what SQL
functions can be called, known or unknown to SQLAlchemy, built-in or user
defined."

I guess there won't be a waiting queue (or better descriptions) for this
category of SQL functions in the sqla docs.


On Thu, Dec 10, 2015 at 12:06 PM, SF Markus Elfring <
elfr...@users.sourceforge.net> wrote:

> Thanks for the link to information I was looking for:
>
> http://stackoverflow.com/questions/15743121/how-to-filter-in-sqlalchemy-by-string-length#answer-15743220
>
> Why is the method "length" not mentioned in the documentation chapter
> "SQL and Generic Functions" so far?
> http://docs.sqlalchemy.org/en/rel_1_1/core/functions.html
>
> Are there any related functions for which a better description is also
> in a waiting queue?
>
> Regards,
> Markus
>
> --
> 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.
>

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

Re: [sqlalchemy] Determination of string lengths

[SF Markus Elfring]

Thanks for the link to information I was looking for:
http://stackoverflow.com/questions/15743121/how-to-filter-in-sqlalchemy-by-string-length#answer-15743220

Why is the method "length" not mentioned in the documentation chapter
"SQL and Generic Functions" so far?
http://docs.sqlalchemy.org/en/rel_1_1/core/functions.html

Are there any related functions for which a better description is also
in a waiting queue?

Regards,
Markus

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

Re: [sqlalchemy] Determination of string lengths

[Jeff Widman]

This is probably the fastest answer:
http://stackoverflow.com/questions/15743121/how-to-filter-in-sqlalchemy-by-string-length
ᐧ

On Sat, Dec 5, 2015 at 1:34 AM, SF Markus Elfring <
elfr...@users.sourceforge.net> wrote:

> Hello,
>
> I would like to reuse the standard function "len" for the determination
> of string lengths from specific database fields in a query.
> http://www.dailyfreecode.com/code/len-function-296.aspx
>
> Which interface does provide this functionality for the software
> "SQLAlchemy"?
>
> Regards,
> Markus
>
> --
> 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.
>



-- 

*Jeff Widman*
jeffwidman.com  | 740-WIDMAN-J (943-6265)
<><

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

[sqlalchemy] Determination of string lengths

[SF Markus Elfring]

Hello,

I would like to reuse the standard function "len" for the determination
of string lengths from specific database fields in a query.
http://www.dailyfreecode.com/code/len-function-296.aspx

Which interface does provide this functionality for the software "SQLAlchemy"?

Regards,
Markus

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