Re: [sqlalchemy] Calculated relationships

[Jonathan Vanasco]

On Sunday, May 22, 2016 at 11:45:23 PM UTC-4, Mike Bayer wrote:
>
>
> Anyway I approve the message and the poster and it should show up in the 
> group, I do get the released message emailed to me at least. 
>

For whatever reasons, it's only going to you.  All the "user's first post" 
threads for the past few months have been missing the first post.  Google 
must have changed something on their end, because they used to always show. 

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] return value of array at a specific index

[Brian Cherinka]

Thanks Mike.  That ARRAY_D class did the trick.  Thanks for pointing it 
out. 

On Sunday, May 22, 2016 at 11:52:11 PM UTC-4, Mike Bayer wrote:
>
>
>
> On 05/22/2016 07:12 PM, Brian Cherinka wrote: 
> > 
> > What's the proper way to return in an ORM query the value of a Postgres 
> > array attribute at a given specific index within the array? 
> > 
> > I have a db table with a column called value, which is a 2d array, 
> > defined as REAL[][]. 
> > 
> > My ModelClass is defined as 
> > 
> > | 
> > classEmLine(Base): 
> > __tablename__ ='emline' 
> > __table_args__ ={'autoload':True,'schema':'dapdb'} 
> > 
> > def__repr__(self): 
> > return' > 
> > value =Column(ARRAY(Float,dimensions=2,zero_indexes=True)) 
> > ivar =Column(ARRAY(Float,dimensions=2,zero_indexes=True)) 
> > mask =Column(ARRAY(Integer,dimensions=2,zero_indexes=True)) 
> > 
> > | 
> > 
> > Pure SQL indexing an array works just fine 
> > | 
> > selecte.value[16][17]fromdapdb.emline ase; 
> > | 
> > 
> > But SQLalchemy does not 
> > | 
> > session.query(dapdb.EmLine.value[16][17]).first() 
> > | 
> > 
> > returns the error 
> > | 
> > NotImplementedError:Operator'getitem'isnotsupported on thisexpression 
>
> this is a bug that's been fixed for 1.1.   It's detailed here: 
>
> http://docs.sqlalchemy.org/en/latest/changelog/migration_11.html#correct-sql-types-are-established-from-indexed-access-of-array-json-hstore
>  
>
> For multi-dimensional access, this can be worked around for a one-off 
> using type_coerce: 
>
>  >>> from sqlalchemy import type_coerce 
>  >>> type_coerce(c[4], ARRAY(Integer))[5] 
>
> There is also a generalized workaround created for the bug that you can 
> see at 
> https://bitbucket.org/zzzeek/sqlalchemy/issues/3487#comment-20200804 . 
> It involves creation of an ARRAY subclass that does the right thing 
> within __getitem__.   That subclass can be a drop-in replacement for 
> regular ARRAY. 
>
>
>
>
>
>
>
>
> > | 
> > 
> > I've tried defining a hybrid method/expression in my ModelClass, and 
> running 
> > | 
> > session.query(dapdb.EmLine.singleat('value',16,17)).first() 
> > | 
> > 
> >  but I'm getting the same "getitem" error 
> > 
> > | 
> > classEmLine(Base): 
> > __tablename__ ='emline' 
> > __table_args__ ={'autoload':True,'schema':'mangadapdb'} 
> > 
> > def__repr__(self): 
> > return' > 
> > value =Column(ARRAY(Float,dimensions=2,zero_indexes=True)) 
> > ivar =Column(ARRAY(Float,dimensions=2,zero_indexes=True)) 
> > mask =Column(ARRAY(Integer,dimensions=2,zero_indexes=True)) 
> > 
> > @hybrid_method 
> > defsingleat(self,name,x,y): 
> > param =self.__getattribute__(name) 
> > returnparam[x][y] 
> > 
> > @singleat.expression 
> > defsingleat(cls,name,x,y): 
> > param =cls.__getattribute__(cls,name) 
> > print(param,x,y) 
> > returnfunc.ARRAY(param)[x][y] 
> > 
> > | 
> > 
> > In my singleat expression, I've tried a variety of returns.  return 
> > func.ARRAY(param)[x][y] ;  return param[x][y].  What's the proper syntax 
> > to match the actual SQL array indexing? 
> > 
> > 
> >
>

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] return value of array at a specific index

[Mike Bayer]

On 05/22/2016 07:12 PM, Brian Cherinka wrote:


What's the proper way to return in an ORM query the value of a Postgres
array attribute at a given specific index within the array?

I have a db table with a column called value, which is a 2d array,
defined as REAL[][].

My ModelClass is defined as

|
classEmLine(Base):
__tablename__ ='emline'
__table_args__ ={'autoload':True,'schema':'dapdb'}

def__repr__(self):
return'

this is a bug that's been fixed for 1.1.   It's detailed here: 
http://docs.sqlalchemy.org/en/latest/changelog/migration_11.html#correct-sql-types-are-established-from-indexed-access-of-array-json-hstore


For multi-dimensional access, this can be worked around for a one-off 
using type_coerce:


>>> from sqlalchemy import type_coerce
>>> type_coerce(c[4], ARRAY(Integer))[5]

There is also a generalized workaround created for the bug that you can 
see at 
https://bitbucket.org/zzzeek/sqlalchemy/issues/3487#comment-20200804 . 
It involves creation of an ARRAY subclass that does the right thing 
within __getitem__.   That subclass can be a drop-in replacement for 
regular ARRAY.










|

I've tried defining a hybrid method/expression in my ModelClass, and running
|
session.query(dapdb.EmLine.singleat('value',16,17)).first()
|

 but I'm getting the same "getitem" error

|
classEmLine(Base):
__tablename__ ='emline'
__table_args__ ={'autoload':True,'schema':'mangadapdb'}

def__repr__(self):
return'mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
.
Visit this group at https://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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Calculated relationships

[Mike Bayer]

On 05/21/2016 02:06 PM, Jonathan Vanasco wrote:



On Saturday, May 21, 2016 at 12:30:26 PM UTC-4, Andrew Pashkin wrote:

It's strange, why I don't see the first post in the thread?


this has happened a few times this week.  any chance this is from a
moderation feature, mike?


Google Groups about one every two weeks selects a totally random post 
from any user (even users who have posted lots) and decides it might be 
spam, holds it in a moderation queue and sends me an annoying email. 
I've allowed many hundreds of these over the years and not one ever was 
actually spam.  Whereas we do occasionally get blindingly obvious spam 
posts a few times a year as well.


Anyway I approve the message and the poster and it should show up in the 
group, I do get the released message emailed to me at least.







--
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 https://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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: FAILED: No 'script_location' key found in configuration

[Mike Bayer]

This depends on how you are sending Alembic its configuration. If you 
are running with a plain alembic.ini file, "script_location" should be 
present in the file itself, see the example in 
http://alembic.readthedocs.io/en/latest/tutorial.html#editing-the-ini-file


Otherwise if your setup is not using a simple .ini file and is instead 
providing configuration to Alembic in some other way, this key must be 
present in the config object.  If you were using the Config object 
directly, details on how that looks is here: 
http://alembic.readthedocs.io/en/latest/api/config.html .  There's a 
simple programmatic example a few paragraphs down which illustrates 
script_location being applied.




On 05/21/2016 01:59 PM, Drake wrote:

I've had alembic running, but now when I try and add some updates to my
database I run

alembic revision -m "Add permissions table"

I'm getting the following error

FAILED: No 'script_location' key found in configuration

Do you know how I can fix this?

Thanks.

--
You received this message because you are subscribed to the Google
Groups "sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy-alembic+unsubscr...@googlegroups.com
.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Dialect not replacing ? with parameter

[Mike Bayer]

On 05/20/2016 08:53 AM, John Omernik wrote:

Awesome, I am making some progress, I realized that the visit_join I had
was specific to Microsoft access, so I commented it out to see what
happened... that function was somehow not using the ?. When I commented
it out I got "better" sql:



OK so the example of the "access" dialect is referred to in order to 
illustrate the layout of files for a third-party dialect, such that it 
plugs into SQLAlchemy smoothly.


However, the "access" dialect from a "works really well with MS Access" 
standpoint is not very good at all, as MS Access is not really well 
supported by the odbc driver, or PyODBC, or as far as providing a robust 
SQL dialect that we can make good use of.  So it's not great as an 
example of how to use the internal dialect APIs.


To learn about the hooks and flags that Dialect subclasses really make 
use of, you should look in the dialects that are *included* with 
SQLAlchemy since they really cover the span of the kinds of things that 
are needed.  Look in the source under lib/sqlalchemy/dialects and 
compare and contrast Postgresql, MySQL, SQLite, Oracle, SQL server.  I 
think looking at these is how most people learn the basics of getting a 
dialect to work.





Access visit_join:
def visit_join(self, join, asfrom=False, **kwargs):

return ( self.process(join.left, asfrom=True) + \
(join.isouter and " LEFT OUTER JOIN " or " INNER JOIN ") + \
self.process(join.right, asfrom=True) + " ON " + \
self.process(join.onclause) )
SELECT field1 AS field1, COUNT(*) AS cnt
FROM table INNER JOIN (SELECT field1 AS __field1
FROM table GROUP BY field1 ORDER BY COUNT(*) DESC
 LIMIT ?) AS anon_1 ON field1 = __field1 GROUP BY field1 ORDER BY cnt DESC
 LIMIT 5000


No visit_join

SELECT field1 AS field1, COUNT(*) AS cnt
FROM table JOIN (SELECT field1 AS __field1
FROM table GROUP BY field1 ORDER BY COUNT(*) DESC
LIMIT 50) AS anon_1 ON field1 = __field1 GROUP BY field1 ORDER BY cnt DESC
LIMIT 5000




This is better! Now, the only question I have now is how I can create a
visit_join (or perhaps this goes elsewhere) where the "ON field1 =
__field1" can actually be ON field1 = anon_1.__field1  Drill doesn't
like field1 = __field1, says its ambiguous.   Is there an easy way to do
this rather than trying to manually rewrite the statement. (For example,
anon_1 is being put in by something, and it's non-obvious to me, but I
don't want hard code anon_1 in a dialect...

Thanks for your help thus far in pointing me in the right direction.

John







On Thu, May 19, 2016 at 7:50 PM, John Omernik > wrote:

Hey Mike, thanks for the reply. I feel really bad in that I am
struggling on this, a little background, I am trying to get Caravel,
which uses SQL Alchemy to play nice with Apache Drill, this means I
am learning about dialects through a crash course of feeling really
dumb, I've had some success (with help) and have some aspects
working great.  One of the things I am struggling with is the "How"
to trouble shoot when, for example Caravel is issuing a Query that
should have a limit, but instead comes through like this:

SELECT myfield AS myfield, COUNT(*) AS cnt
FROM mytable INNER JOIN (SELECT myfield AS __myfield
FROM mytable GROUP BY myfield ORDER BY COUNT(*) DESC
 LIMIT ?) AS anon_1 ON myfield = __myfield GROUP BY myfield ORDER BY cnt 
DESC
 LIMIT 5000


So this hos Drill receives the query and it fails. There are two
things wrong here, first is the limits. Now, why the query is build
this way is beyond me (I can't tell if its Caravel building it that
way or SQL Alchemy, but the the self join makes no sense to me... it
seems redudant, but I digress.)  There are two LIMITS, the first is
the ? which I am assuming should be the parameter from Caravel, and
then LIMIT 5000. I am guessing that the limit 5000 is put on by
CARAVEL as that is the max row count... but why this is failing is
beyond me, it doesn't work on my drill/pyodbc setup, but when it's
the sqllite dialect on a sqllite test database, that works fine...


The second issue is the join. Drill finds the myfield =
__myfield ambiguous.  I just need to find how I could tell
SQLAlchemy to qualify the __myfield with the anon_1. I can't tell if
that is  SQL Alchemy thing or a caravel thing. I am still digging,
and will continue to, but if you or anyone here could help me debug
and/or point me in the right direction, I figured it would be good
to articulate my thoughts.





On Thu, May 19, 2016 at 6:31 PM, Mike Bayer
> wrote:


a SQL query that uses parameters will use ? if the DBAPI uses
"qmark" paramstyle which is very common.  The actual value that
lines up with the ? is part of the "parameters" sequence.   

Re: [sqlalchemy] How to map class to multiple tables (for sharding purpose)

[Mike Bayer]

On 05/20/2016 05:16 AM, Михаил Доронин wrote:

The first link you gave have exactly the thing I said I don't want to do.
The second one is however looks very promising.



oh well when you said "map(klass, *tables)", that's pretty much exactly 
what entity name does.  But I guess you don't like having to qualify 
each Query with the subclass you want to deal with.




Have I understand correctly that I need to create custom Session class
and custom Query class for this session that would somehow produce
correct from clause (with correct table name) from a statement like that

|
session.query(ShardedEntity).filter(ShardedEntity.some_unique_name=='foo')
|

If so, than could you give a little more guidance on it? Like what
methods from query do I need do override etc?


to make this "automatic" is very tricky.  it means that the SQL the 
query is going to produce has to be intercepted at some point and 
rewritten.   We have a long term plan to add a new API to Query to make 
this kind of thing feasible at the point at which the Query is to begin 
producing a core select() object, that API is only a single event so far 
and is described here: 
http://docs.sqlalchemy.org/en/latest/orm/events.html#sqlalchemy.orm.events.QueryEvents 
.  Altering the Query to hit a different table for a certain entity is 
not that easy, though.


There's another recipe i just remembered for this kind of thing which 
hits the SELECT statement way at the end after it's already a string. 
You replace the table name out based on the fact that the original 
Table() object has some very easy-to-identity name.   This might be just 
what you're asking for and that recipe is here: 
https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/SessionModifiedSQL.





On Thursday, 19 May 2016 18:30:51 UTC+3, Mike Bayer wrote:



Well if you want to see some examples of on-the-fly mappers for
different databases that nonetheless map to the same class take a look
at
https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/EntityName
.
The kind of thing you're doing has been done before but it's not
totally easy.


Along with the entity name thing, you can customize Session as well as
how it generates a query.  If you look at the source to the horizontal
sharding extension

http://docs.sqlalchemy.org/en/latest/orm/extensions/horizontal_shard.html?highlight=horizontal#module-sqlalchemy.ext.horizontal_shard



and maybe also my post at

http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/



you can get some ideas for how to game things on the Session side.

Those are the two tools you'd be working with here so hopefully you can
find a way to combine them in a way that works for you.




>
> I haven't created the issue because I didn't know if solution (not a
> workaround like mentioned in stackoverflow) already possible with
> sqlalchemy. If not it would be beneficial to add I think.
>
> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it,
send
> an email to sqlalchemy+...@googlegroups.com 
> .
> To post to this group, send email to sqlal...@googlegroups.com

> .
> Visit this group at https://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 https://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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Create Table DDL options before "("

[Mike Bayer]

OK so we'll probably just add the hook you've proposed.

I've not had anytime to work on a computer for like five days straight 
which puts me super behind, ill try to get to your PR soon.



On 05/19/2016 01:08 PM, Mark Sandan wrote:

Sure, the Teradata database has create table ddl where you can specify
certain options that are separated by commas. We have a reference manual
available online specifying the full DDL here

 (see
page 383 for the full syntax).  Here is some example DDL:

|
CREATE TABLE t1 ,FALLBACK ,
 NO BEFORE JOURNAL,
 NO AFTER JOURNAL,
 CHECKSUM = DEFAULT,
 DEFAULT MERGEBLOCKRATIO
 (
  c1 VARCHAR(128) NOT NULL,
  Id BYTE(4) NOT NULL,
  OwnerId BYTE(4) NOT NULL
 )
   UNIQUE PRIMARY INDEX ( c1 )
 UNIQUE INDEX ( Id );
|



On Thursday, May 19, 2016 at 8:31:49 AM UTC-7, Mike Bayer wrote:

saw your pull request, just curious what database / DDL is this?  Just
like to see the finished product that you're going for.



On 05/18/2016 09:19 PM, Mark Sandan wrote:
> Hi, I'm implementing a dialect for sqlalchemy and would like to add
> options before the '(' but after the table name in visit_create. I
know
> I can just subclass visit_create in my ddl compiler but it seems
kind of
> silly since I'd be making a small change. Something like the
following:
> |
>
>
> defvisit_create_table(self,create):
>   table =create.element
>   preparer =self.preparer
>
>   text ="\nCREATE "
>   iftable._prefixes:
>  text +=" ".join(table._prefixes)+" "
>   text +="TABLE "+preparer.format_table(table)+"
"+table_options(table)+" ("
>
> |
>
> table_options would be a function in the ddl compiler provided by the
> dialect that takes dialect specific keywords and simply appends comma
> delimited values. I essentially need something like the 'prefixes'
> keyword in Table but for after the table name and before the left
> parens. Any ideas?
>
> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it,
send
> an email to sqlalchemy+...@googlegroups.com 
> .
> To post to this group, send email to sqlal...@googlegroups.com

> .
> Visit this group at https://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 https://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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] return value of array at a specific index

[Brian Cherinka]

What's the proper way to return in an ORM query the value of a Postgres 
array attribute at a given specific index within the array?

I have a db table with a column called value, which is a 2d array, defined 
as REAL[][]. 

My ModelClass is defined as 

class EmLine(Base):
__tablename__ = 'emline'
__table_args__ = {'autoload': True, 'schema': 'dapdb'}

def __repr__(self):
return 'https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.