Re: can't modify a column name

[Ofir Herzas]

It seems that I am wrong since I do see all column names in uppercase using 
sql developer (all but reserved words) so I don't know how this happened 
since my models are all lowercase (could it be Base.metadata.create_all?) 
but that is indeed the problem.
Using 'SCOPE' in the alter method does solve the issue (I just hope it 
wouldn't introduce problems with mysql...)

Thanks.

On Monday, July 11, 2016 at 6:40:09 PM UTC+3, Ofir Herzas wrote:
>
> For sure the table was not created using capital letters or quotes but 
> I'll try your suggestion, thanks.
> On Jul 11, 2016 6:02 PM, "Mike Bayer" <mike...@zzzcomputing.com> wrote:
>
>>
>>
>> On 07/11/2016 10:48 AM, Ofir Herzas wrote:
>>
>>> Alembic 0.8.6
>>> cx-Oracle 5.1.3
>>> SQLAlchemy 1.0.14
>>>
>>>
>>> I have a column named "scope" (without the double quotes) with an
>>> existing type of SmallInt and I'm trying to change it to an Integer with
>>> the following line:
>>> |
>>>
>>> op.alter_column('t_rule','scope',existing_type=sa.SmallInteger(),type_=sa.Integer())
>>> |
>>>
>>> Unfortunately, this results with the following error:
>>> |
>>> sqlalchemy.exc.DatabaseError:(cx_Oracle.DatabaseError)ORA-00904::invalid
>>> identifier
>>>  [SQL:'ALTER TABLE t_rule MODIFY scope INTEGER']
>>> |
>>>
>>> I have found out that the following syntax does work:
>>> |
>>> ALTER TABLE t_rule MODIFY "SCOPE"INTEGER;
>>> |
>>>
>>> (Notice the caption of "scope" and the double quotes)
>>>
>>> I'm not sure why this happens since /scope /doesn't seem to be a
>>> reserved word.
>>>
>>> Is it a bug or am I doing something wrong?
>>>
>>
>> If "scope" is not a reserved word, then I'd suggest that the table was 
>> created using quoted "SCOPE" as the column name here, so that it is 
>> case-sensitive and will only match if quoted and uppercased.   If you 
>> passed the name as all uppercase SCOPE to alter_column it should trip the 
>> "case sensitive" flag and quote it.  There are more direct ways to turn on 
>> the quoting if that doesn't work.
>>
>>
>>
>>
>>
>>> --
>>> 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
>>> <mailto:sqlalchemy-alembic+unsubscr...@googlegroups.com>.
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>
>> -- 
>> You received this message because you are subscribed to a topic in the 
>> Google Groups "sqlalchemy-alembic" group.
>> To unsubscribe from this topic, visit 
>> https://groups.google.com/d/topic/sqlalchemy-alembic/FwVRUgcdVtg/unsubscribe
>> .
>> To unsubscribe from this group and all its topics, 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: can't modify a column name

[Ofir Herzas]

For sure the table was not created using capital letters or quotes but I'll
try your suggestion, thanks.
On Jul 11, 2016 6:02 PM, "Mike Bayer" <mike...@zzzcomputing.com> wrote:

>
>
> On 07/11/2016 10:48 AM, Ofir Herzas wrote:
>
>> Alembic 0.8.6
>> cx-Oracle 5.1.3
>> SQLAlchemy 1.0.14
>>
>>
>> I have a column named "scope" (without the double quotes) with an
>> existing type of SmallInt and I'm trying to change it to an Integer with
>> the following line:
>> |
>>
>> op.alter_column('t_rule','scope',existing_type=sa.SmallInteger(),type_=sa.Integer())
>> |
>>
>> Unfortunately, this results with the following error:
>> |
>> sqlalchemy.exc.DatabaseError:(cx_Oracle.DatabaseError)ORA-00904::invalid
>> identifier
>>  [SQL:'ALTER TABLE t_rule MODIFY scope INTEGER']
>> |
>>
>> I have found out that the following syntax does work:
>> |
>> ALTER TABLE t_rule MODIFY "SCOPE"INTEGER;
>> |
>>
>> (Notice the caption of "scope" and the double quotes)
>>
>> I'm not sure why this happens since /scope /doesn't seem to be a
>> reserved word.
>>
>> Is it a bug or am I doing something wrong?
>>
>
> If "scope" is not a reserved word, then I'd suggest that the table was
> created using quoted "SCOPE" as the column name here, so that it is
> case-sensitive and will only match if quoted and uppercased.   If you
> passed the name as all uppercase SCOPE to alter_column it should trip the
> "case sensitive" flag and quote it.  There are more direct ways to turn on
> the quoting if that doesn't work.
>
>
>
>
>
>> --
>> 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
>> <mailto:sqlalchemy-alembic+unsubscr...@googlegroups.com>.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
> --
> You received this message because you are subscribed to a topic in the
> Google Groups "sqlalchemy-alembic" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy-alembic/FwVRUgcdVtg/unsubscribe
> .
> To unsubscribe from this group and all its topics, 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.

can't modify a column name

[Ofir Herzas]

Alembic 0.8.6
cx-Oracle 5.1.3
SQLAlchemy 1.0.14


I have a column named "scope" (without the double quotes) with an existing 
type of SmallInt and I'm trying to change it to an Integer with the 
following line:
op.alter_column('t_rule', 'scope', existing_type=sa.SmallInteger(), type_=sa
.Integer())

Unfortunately, this results with the following error:
sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00904: : 
invalid identifier
 [SQL: 'ALTER TABLE t_rule MODIFY scope INTEGER']

I have found out that the following syntax does work:
ALTER TABLE t_rule MODIFY "SCOPE" INTEGER;

(Notice the caption of "scope" and the double quotes)

I'm not sure why this happens since *scope *doesn't seem to be a reserved 
word.

Is it a bug or am I doing something wrong?

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

[sqlalchemy] naming convention

[Ofir Herzas]

I want to start using the naming convention but have several questions 
regarding this feature:

   1. Does the naming convention support Sequences? If not, what's the 
   proper way of handling them?
   2. Are Booleans going to be supported in the near future or will I have 
   to name them separately?
   3. How can I migrate my existing DBs to the naming convention?
   
Thanks,
Ofir

-- 
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: Alter boolean column on Oracle

[Ofir Herzas]

Hi Mike,
I've just checked my code and I noticed that I already use existing_type 
(not sure what was wrong with the documentation in that aspect).

Can you elaborate a bit on specifying custom rules as you mentioned?
I tried issuing a drop_constraint before calling the alter_column but that 
doesn't stop alter_column from trying to remove the check constraint by 
itself, leading to the same exception.
On the other hand, I didn't see any parameter in the alter_column method 
that accepts a constraint name

Thanks,
Ofir


On Thursday, December 10, 2015 at 3:39:42 PM UTC+2, Ofir Herzas wrote:
>
> I'm trying to run an alter_column operation from a Boolean type column to 
> SmallInteger on Oracle.
>
> According to the documentation: "...Type changes which are against the 
> SQLAlchemy “schema” types Boolean 
> <http://www.sqlalchemy.org/docs/core/type_basics.html#sqlalchemy.types.Boolean>
>  and Enum 
> <http://www.sqlalchemy.org/docs/core/type_basics.html#sqlalchemy.types.Enum> 
> may 
> also add or drop constraints which accompany those types on backends that 
> don’t support them natively. The existing_server_default argument is used 
> in this case as well to remove a previous constraint"
>
> Does this mean that the alter column should work out-of-the-box or do I 
> have to remove the constraint myself because I'm getting the following 
> error on alembic 0.8.3 (sqlalchemy 1.0.9):
>
> sqlalchemy.exc.CompileError: Can't emit DROP CONSTRAINT for constraint 
> CheckConstraint( 0x28a9b10>, name='_unnamed_', table=Table('t_message', 
> MetaData(bind=None), Column('is_bulletin', Boolean(), table=), 
> schema=None), 
> _create_rule= 0x289ac68>, _type_bound=True); it has no name
>
>
> And on a previous version (alembic 0.6.5, sqlalchemy 0.9.7) I got the 
> following error:
>
> sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-02443: Cannot drop 
> constraint  - nonexistent constraint
>  'ALTER TABLE t_message DROP CONSTRAINT None' {}
>
>
> Your help will be appreciated...
>

-- 
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: Alter boolean column on Oracle

[Ofir Herzas]

Thanks Mike. It seems that I'm in the worst case :)

I've started using Sqlalchemy before the naming convention feature was 
introduced and so I didn't use it.
A while back when I wanted to port my code to using it, I didn't find an easy 
way to do it and so I decided not to do it.
My application uses 45 tables with 115 foreign keys on multiple platforms 
(mainly Mysql and Oracle) - Is there a way I can automate the porting through a 
script using inspection or something similar? Do you have a recipe for 
something of that sort?

Thanks again,
Ofir


I'll try 

-Original Message-
From: sqlalchemy-alembic@googlegroups.com 
[mailto:sqlalchemy-alembic@googlegroups.com] On Behalf Of Mike Bayer
Sent: Thursday, December 10, 2015 19:24
To: sqlalchemy-alembic@googlegroups.com
Subject: Re: Alter boolean column on Oracle



On 12/10/2015 08:39 AM, Ofir Herzas wrote:
> I'm trying to run an alter_column operation from a Boolean type column
> to SmallInteger on Oracle.
> 
> According to the documentation: "...Type changes which are against the
> SQLAlchemy “schema” types |Boolean|
> <http://www.sqlalchemy.org/docs/core/type_basics.html#sqlalchemy.types.Boolean>
>  and |Enum|
> <http://www.sqlalchemy.org/docs/core/type_basics.html#sqlalchemy.types.Enum> 
> may
> also add or drop constraints which accompany those types on backends
> that don’t support them natively. The |existing_server_default| argument
> is used in this case as well to remove a previous constraint"

OK that document is wrong.  At least in 0.8 and maybe previously, that
should be "existing_type", because the current source code is looking at
existing_type to determine what previous type-bound constraints need to
be dropped.  I've committed that and it should be on readthedocs shortly.


> 
> Does this mean that the alter column should work out-of-the-box or do I
> have to remove the constraint myself because I'm getting the following
> error on alembic 0.8.3 (sqlalchemy 1.0.9):

Well, the constraint needs to have a name.  At least in the
existing_type that you send, for Boolean you'd need to also include the
"name" field that is the name of the constraint.

If this is an existing production DB, the constraint already has some
name and on Oracle it will be something like SYS12345.  But
unfortunately that name is not portable so this depends on if you are
targeting a lot of databases, or just one.

If you're just working out how these migrations would run from start to
finish, the real way you'd want to address this is at the very least
ensuring that any Boolean or Enum type you use where you've also used
create_constraint=True (the default) also has a "name".If you want
that to be automatic, you can use naming conventions.  The whole story
here is documented at http://alembic.readthedocs.org/en/latest/naming.html.

In the worst case, you in fact have production DBs, there are multiple,
you didn't give the constraints a specific name up front, and this
constraint probably has a different name on each one.Your options
there are either to manually rename constraints to something predictable
on these DBs and then use the full naming approach, *or* you'd need to
write custom rules in your migrations that do a live search of the
Oracle catalog tables, locate these constraints dynamically, and drops them.





> |
> sqlalchemy.exc.CompileError:Can't emit DROP CONSTRAINT for constraint
> CheckConstraint( 0x28a9b10>, name='_unnamed_', table=Table('t_message',
> MetaData(bind=None), Column('is_bulletin', Boolean(),
> table=), schema=None),
> _create_rule= at 0x289ac68>, _type_bound=True); it has no name
> |
> 
> 
> And on a previous version (alembic 0.6.5, sqlalchemy 0.9.7) I got the
> following error:
> 
> |
> sqlalchemy.exc.DatabaseError:(DatabaseError)ORA-02443:Cannotdrop
> constraint  -nonexistent constraint
>  'ALTER TABLE t_message DROP CONSTRAINT None'{}
> |
> 
> 
> Your help will be appreciated...
> 
> -- 
> 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
> <mailto:sqlalchemy-alembic+unsubscr...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

-- 
You received this message because you are subscribed to a topic in the Google 
Groups "sqlalchemy-alembic" group.
To unsubscribe from this topic, visit 
https://groups.google.com/d/topic/sqlalchemy-alembic/OPctuividc0/unsubscribe.
To unsubscribe from this group and all its topics, 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 subscri

RE: Alter boolean column on Oracle

[Ofir Herzas]

BTW, A little bit off topic but how does the naming convention support 
character limitations such as the 30 characters max length for the foreign keys 
in Oracle? Does it provide any hooks?


-Original Message-
From: sqlalchemy-alembic@googlegroups.com 
[mailto:sqlalchemy-alembic@googlegroups.com] On Behalf Of Mike Bayer
Sent: Thursday, December 10, 2015 19:24
To: sqlalchemy-alembic@googlegroups.com
Subject: Re: Alter boolean column on Oracle



On 12/10/2015 08:39 AM, Ofir Herzas wrote:
> I'm trying to run an alter_column operation from a Boolean type column
> to SmallInteger on Oracle.
> 
> According to the documentation: "...Type changes which are against the
> SQLAlchemy “schema” types |Boolean|
> <http://www.sqlalchemy.org/docs/core/type_basics.html#sqlalchemy.types.Boolean>
>  and |Enum|
> <http://www.sqlalchemy.org/docs/core/type_basics.html#sqlalchemy.types.Enum> 
> may
> also add or drop constraints which accompany those types on backends
> that don’t support them natively. The |existing_server_default| argument
> is used in this case as well to remove a previous constraint"

OK that document is wrong.  At least in 0.8 and maybe previously, that
should be "existing_type", because the current source code is looking at
existing_type to determine what previous type-bound constraints need to
be dropped.  I've committed that and it should be on readthedocs shortly.


> 
> Does this mean that the alter column should work out-of-the-box or do I
> have to remove the constraint myself because I'm getting the following
> error on alembic 0.8.3 (sqlalchemy 1.0.9):

Well, the constraint needs to have a name.  At least in the
existing_type that you send, for Boolean you'd need to also include the
"name" field that is the name of the constraint.

If this is an existing production DB, the constraint already has some
name and on Oracle it will be something like SYS12345.  But
unfortunately that name is not portable so this depends on if you are
targeting a lot of databases, or just one.

If you're just working out how these migrations would run from start to
finish, the real way you'd want to address this is at the very least
ensuring that any Boolean or Enum type you use where you've also used
create_constraint=True (the default) also has a "name".If you want
that to be automatic, you can use naming conventions.  The whole story
here is documented at http://alembic.readthedocs.org/en/latest/naming.html.

In the worst case, you in fact have production DBs, there are multiple,
you didn't give the constraints a specific name up front, and this
constraint probably has a different name on each one.Your options
there are either to manually rename constraints to something predictable
on these DBs and then use the full naming approach, *or* you'd need to
write custom rules in your migrations that do a live search of the
Oracle catalog tables, locate these constraints dynamically, and drops them.





> |
> sqlalchemy.exc.CompileError:Can't emit DROP CONSTRAINT for constraint
> CheckConstraint( 0x28a9b10>, name='_unnamed_', table=Table('t_message',
> MetaData(bind=None), Column('is_bulletin', Boolean(),
> table=), schema=None),
> _create_rule= at 0x289ac68>, _type_bound=True); it has no name
> |
> 
> 
> And on a previous version (alembic 0.6.5, sqlalchemy 0.9.7) I got the
> following error:
> 
> |
> sqlalchemy.exc.DatabaseError:(DatabaseError)ORA-02443:Cannotdrop
> constraint  -nonexistent constraint
>  'ALTER TABLE t_message DROP CONSTRAINT None'{}
> |
> 
> 
> Your help will be appreciated...
> 
> -- 
> 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
> <mailto:sqlalchemy-alembic+unsubscr...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

-- 
You received this message because you are subscribed to a topic in the Google 
Groups "sqlalchemy-alembic" group.
To unsubscribe from this topic, visit 
https://groups.google.com/d/topic/sqlalchemy-alembic/OPctuividc0/unsubscribe.
To unsubscribe from this group and all its topics, 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.

Alter boolean column on Oracle

[Ofir Herzas]

I'm trying to run an alter_column operation from a Boolean type column to 
SmallInteger on Oracle.

According to the documentation: "...Type changes which are against the 
SQLAlchemy “schema” types Boolean 

 and Enum 
 
may 
also add or drop constraints which accompany those types on backends that 
don’t support them natively. The existing_server_default argument is used 
in this case as well to remove a previous constraint"

Does this mean that the alter column should work out-of-the-box or do I 
have to remove the constraint myself because I'm getting the following 
error on alembic 0.8.3 (sqlalchemy 1.0.9):

sqlalchemy.exc.CompileError: Can't emit DROP CONSTRAINT for constraint 
CheckConstraint(, name='_unnamed_', table=Table('t_message', MetaData(bind=None), 
Column('is_bulletin', Boolean(), table=), schema=None), 
_create_rule=, _type_bound=True); it has no name


And on a previous version (alembic 0.6.5, sqlalchemy 0.9.7) I got the 
following error:

sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-02443: Cannot drop 
constraint  - nonexistent constraint
 'ALTER TABLE t_message DROP CONSTRAINT None' {}


Your help will be appreciated...

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

[sqlalchemy] load_only doesn't affect joins

[Ofir Herzas]

load_only as stated in 
http://docs.sqlalchemy.org/en/latest/orm/loading_columns.html does the 
following:
"An arbitrary set of columns can be selected as “load only” columns, which 
will be loaded *while deferring all other columns* on a given entity..."

However, joined relationships are not affected by this, meaning that the 
produced sql still joins all 'joined' relationships

Consider the following example:

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Foo(Base):
__tablename__ = 'foo'
id = sa.Column(sa.Integer, primary_key=True)
bars = sa.orm.relationship("Bar", lazy="joined")
 
class Bar(Base):
__tablename__ = 'bar'
id = sa.Column(sa.Integer, primary_key=True)
foo_id = sa.Column(sa.Integer, sa.ForeignKey('foo.id'))
baz = sa.Column(sa.Integer)
 
e = sa.create_engine("sqlite://", echo=True)
session = sa.orm.Session(e)
session.query(Foo).options(sa.orm.load_only('id')).all()

The query produced is the following:
SELECT foo.id AS foo_id, bar_1.id AS bar_1_id, bar_1.foo_id AS bar_1_foo_id, 
bar_1.baz AS bar_1_baz
FROM foo LEFT OUTER JOIN bar AS bar_1 ON foo.id = bar_1.foo_id

I understand that I can use the lazyload option to prevent the join but I 
thought it would be nice if the load_only handled it out-of-the-box...

-- 
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] Re: load_only doesn't affect joins

[Ofir Herzas]

Thanks for caring ;)
On Dec 3, 2015 6:08 PM, "Jonathan Vanasco"  wrote:

> Thanks for posting a full self-contained working example of your problem!
>
> --
> You received this message because you are subscribed to a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/DTqEnAKqipY/unsubscribe.
> To unsubscribe from this group and all its topics, 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] load_only doesn't affect joins

[Ofir Herzas]

The lazy me you mean :)
I saw that option in the documents and my comment was not regarding the 
complexity of the solution.
I just thought that a 'load_only' method is better understood if it loads 
only the specified attributes.

With an abstraction layer, the user often "sees" things differently than 
the way they really are.
For example, when I load the Foo object and I get the bars list, I don't 
want to care about 'bars' being a relationship (I know it is but I already 
handled that when I defined my model). For me, it's an attribute of type 
list...
Of course some operations are relationship-specific but those that are not 
should not (IMO) force the user to consider them differently.

Since I have my own wrapper around session.query, I added the following 
lines to it:

query = session.query(model)

if properties is not None:
props = [p for p in properties if p in model.__table__.c]
query = query.options(sa.orm.load_only(*props))

noload = set([x.key for x in model.__mapper__.relationships]) - set(
props)
for x in noload:
query = query.options(sa.orm.lazyload(x))

...

That currently does what I want...


On Thursday, December 3, 2015 at 5:23:55 PM UTC+2, Michael Bayer wrote:
>
>
>
> On 12/03/2015 10:08 AM, Mike Bayer wrote: 
> > 
> > 
> > On 12/03/2015 06:16 AM, Ofir Herzas wrote: 
> >> load_only as stated 
> >> in http://docs.sqlalchemy.org/en/latest/orm/loading_columns.html does 
> >> the following: 
> >> "An arbitrary set of columns can be selected as “load only” columns, 
> >> which will be loaded *while deferring all other columns* on a given 
> >> entity..." 
> > 
> > 
> > Hi Ofir - 
> > 
> > As it says, "on a given entity", an entity refers to a class-oriented 
> > object.  A relationship refers to a different set of entities so these 
> > must be referred to independently. 
> > 
> > The "load_only" function in that code example is hyperlinked, which is 
> > intended to encourage the reader to click and read through the more 
> > detailed documentation for this function which is at: 
> > 
> > 
> http://docs.sqlalchemy.org/en/rel_1_0/orm/loading_columns.html?highlight=load_only#sqlalchemy.orm.load_only
>  
> > 
> > 
> > 
> >> 
> >> However, joined relationships are not affected by this, meaning that 
> the 
> >> produced sql still joins all 'joined' relationships 
> >> 
> >> Consider the following example: 
> >> 
> >> | 
> >> importsqlalchemy assa 
> >> fromsqlalchemy.ext.declarative importdeclarative_base 
> >> 
> >> Base=declarative_base() 
> >> 
> >> classFoo(Base): 
> >> __tablename__ ='foo' 
> >> id =sa.Column(sa.Integer,primary_key=True) 
> >> bars =sa.orm.relationship("Bar",lazy="joined") 
> >>   
> >> classBar(Base): 
> >> __tablename__ ='bar' 
> >> id =sa.Column(sa.Integer,primary_key=True) 
> >> foo_id =sa.Column(sa.Integer,sa.ForeignKey('foo.id')) 
> >> baz =sa.Column(sa.Integer) 
> >>   
> >> e =sa.create_engine("sqlite://",echo=True) 
> >> session =sa.orm.Session(e) 
> >> session.query(Foo).options(sa.orm.load_only('id')).all() 
> >> | 
> >> 
> >> The query produced is the following: 
> >> | 
> >> SELECT foo.id AS foo_id,bar_1.id AS bar_1_id,bar_1.foo_id AS 
> >> bar_1_foo_id,bar_1.baz AS bar_1_baz 
> >> FROM foo LEFT OUTER JOIN bar AS bar_1 ON foo.id =bar_1.foo_id 
> >> | 
> >> 
> >> I understand that I can use the lazyload option to prevent the join but 
> >> I thought it would be nice if the load_only handled it 
> out-of-the-box... 
> > 
> > there's "out of the box" and there's "assuming a specific and arbitrary 
> > intent" - you have lazy="joined" in your mapping so if you don't want 
> > that to occur you need to tell the query to turn it off, load_only('a', 
> > 'b').lazyload('bars'). 
>
> another thought, you'll probably like this: 
>
> load_only('a', 'b').lazyload('*') 
>
>
> that should be easier 
>
>
>
>
> > 
> > 
> > 
> > 
> > 
> >> 
> >> -- 
> >> 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  
> >> <mailto:sqlalchemy+unsubscr...@googlegroups.com >. 
> >> To post to this group, send email to sqlal...@googlegroups.com 
>  
> >> <mailto:sqlal...@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.

alter VARCHAR column to TEXT under Oracle

[Ofir Herzas]

Changing a VARCHAR column to CLOB (TEXT) raises the following exception 
under Oracle:

sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-22858: invalid 
alteration of datatype
 [SQL: 'ALTER TABLE tab MODIFY col CLOB']


While this is an Oracle fault, it would be nice if the abstraction layer 
took care of it.

The proposed solution that I found was to add a new clob column, copy the 
data, remove the old column and rename

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

[sqlalchemy] can't read CLOB columns

[Ofir Herzas]

Sqlalchemy 1.0.6

Trying to issue a simple select on a CLOB column, throws the following 
exception:

Traceback (most recent call last):
  File "/opt/5.3/server/handlers/orm.py", line 81, in select

result = query.all()
  File 
"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/query.py", 
line 2399, in all
return list(self)
  File 
"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/loading.py"
, line 84, in instances
util.raise_from_cause(err)
  File 
"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/util/compat.py"
, line 199, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb)
  File 
"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/loading.py"
, line 65, in instances
fetch = cursor.fetchall()
  File 
"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/result.py"
, line 1243, in fetchall
row = self.fetchone()
  File 
"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/result.py"
, line 1013, in fetchone
self.cursor, self.context)
  File 
"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/base.py"
, line 1344, in _handle_dbapi_exception
util.reraise(*exc_info)
  File 
"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/result.py"
, line 1006, in fetchone
return self.process_rows([row])[0]
  File 
"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/result.py"
, line 929, in process_rows
for row in rows]
  File 
"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/result.py"
, line 1204, in __init__
row[index] = processor(row[index])
  File 
"/opt/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/dialects/oracle/cx_oracle.py"
, line 366, in process
return value.read()
AttributeError: 'str' object has no attribute 'read'


Is there any way around it? (other than not using Text columns)

-- 
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: alter VARCHAR column to TEXT under Oracle

[Ofir Herzas]

Thanks Mike,
I was hoping for a under-the-hood solution rather than a recipe since it's a 
must for using Oracle.
Nevertheless, if I get around it, I will provide the recipe (better to have 
something...)

In the meanwhile, I opted to stay with VARCHAR ...

Thanks again,
Ofir


-Original Message-
From: sqlalchemy-alembic@googlegroups.com 
[mailto:sqlalchemy-alembic@googlegroups.com] On Behalf Of Mike Bayer
Sent: Sunday, November 8, 2015 20:25
To: sqlalchemy-alembic@googlegroups.com
Subject: Re: alter VARCHAR column to TEXT under Oracle



On 11/08/2015 05:42 AM, Ofir Herzas wrote:
> Changing a VARCHAR column to CLOB (TEXT) raises the following exception
> under Oracle:
> 
> |
> sqlalchemy.exc.DatabaseError:(cx_Oracle.DatabaseError)ORA-22858:invalid
> alteration of datatype
>  [SQL:'ALTER TABLE tab MODIFY col CLOB']
> 
> |
> 
> While this is an Oracle fault, it would be nice if the abstraction layer
> took care of it.
> 
> The proposed solution that I found was to add a new clob column, copy
> the data, remove the old column and rename

Alembic offers a rough version of this feature in the form of "batch
migrations", but that involves a whole table copy.

Recipes like adding new columns and copying data can be achieved using
custom directives, see
http://alembic.readthedocs.org/en/latest/cookbook.html#replaceable-objects
for an example of how to make new directives.  I will gladly accept
documentation illustrating a recipe for this behavior.



> 
> -- 
> 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
> <mailto:sqlalchemy-alembic+unsubscr...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

-- 
You received this message because you are subscribed to a topic in the Google 
Groups "sqlalchemy-alembic" group.
To unsubscribe from this topic, visit 
https://groups.google.com/d/topic/sqlalchemy-alembic/rDqCsRIDfh4/unsubscribe.
To unsubscribe from this group and all its topics, 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.

[sqlalchemy] Re: Exception in logger while using query options

[Ofir Herzas]

BTW: sqlalchemy 1.0.8, python 3.3.4

On Wednesday, October 14, 2015 at 12:34:04 PM UTC+3, Ofir Herzas wrote:
>
> An exception pertaining to the logger is raised when using query.options
>
> Here is the relevant part of the exception:
>
>
> query = query.options(sqlalchemy.orm.load_only(*properties))
>   File 
> "/opt/enigmai/ve/python-3.3.4/lib/python3.3/site-packages/sqlalchemy/orm/query.py",
>  line 1100, in options
> return self._options(False, *args)
>   File "", line 2, in _options
>   File 
> "/opt/enigmai/ve/python-3.3.4/lib/python3.3/site-packages/sqlalchemy/orm/base.py",
>  line 201, in generate
> fn(self, *args[1:], **kw)
>   File 
> "/opt/enigmai/ve/python-3.3.4/lib/python3.3/site-packages/sqlalchemy/orm/query.py",
>  line 1117, in _options
> opt.process_query(self)
>   File 
> "/opt/enigmai/ve/python-3.3.4/lib/python3.3/site-packages/sqlalchemy/orm/strategy_options.py",
>  line 95, in process_query
> self._process(query, True)
>   File 
> "/opt/enigmai/ve/python-3.3.4/lib/python3.3/site-packages/sqlalchemy/orm/strategy_options.py",
>  line 305, in _process
> val._bind_loader(query, query._attributes, raiseerr)
>   File 
> "/opt/enigmai/ve/python-3.3.4/lib/python3.3/site-packages/sqlalchemy/orm/strategy_options.py",
>  line 422, in _bind_loader
> effective_path.set(context, "loader", loader)
>   File 
> "/opt/enigmai/ve/python-3.3.4/lib/python3.3/site-packages/sqlalchemy/orm/path_registry.py",
>  line 63, in set
> log.debug("set '%s' on path '%s' to '%s'", key, self, value)
>   File "/usr/local/lib/python3.3/logging/__init__.py", line 1225, in debug
> self._log(DEBUG, msg, args, **kwargs)
>   File "/usr/local/lib/python3.3/logging/__init__.py", line 1372, in _log
> self.handle(record)
>   File "/usr/local/lib/python3.3/logging/__init__.py", line 1382, in handle
> self.callHandlers(record)
>   File "/usr/local/lib/python3.3/logging/__init__.py", line 1444, in 
> callHandlers
> hdlr.handle(record)
>   File "/usr/local/lib/python3.3/logging/__init__.py", line 835, in handle
> self.emit(record)
>   File "/usr/local/lib/python3.3/logging/handlers.py", line 869, in emit
> msg = self.format(record)
>   File "/usr/local/lib/python3.3/logging/__init__.py", line 810, in format
> return fmt.format(record)
>   File "/opt/enigmai/6.0-test/server/handlers/LogFormatter.py", line 77, in 
> format
> return super(LogFormatter, self).format(record)
>   File "/usr/local/lib/python3.3/logging/__init__.py", line 548, in format
> record.message = record.getMessage()
>   File "/usr/local/lib/python3.3/logging/__init__.py", line 311, in getMessage
> msg = msg % self.args
>   File 
> "/opt/enigmai/ve/python-3.3.4/lib/python3.3/site-packages/sqlalchemy/orm/strategy_options.py",
>  line 183, in __str__
> return "Load(strategy=%r)" % self.strategy
> TypeError: not all arguments converted during string formatting
>
>

-- 
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] Exception in logger while using query options

[Ofir Herzas]

An exception pertaining to the logger is raised when using query.options

Here is the relevant part of the exception:


query = query.options(sqlalchemy.orm.load_only(*properties))
  File 
"/opt/enigmai/ve/python-3.3.4/lib/python3.3/site-packages/sqlalchemy/orm/query.py",
 line 1100, in options
return self._options(False, *args)
  File "", line 2, in _options
  File 
"/opt/enigmai/ve/python-3.3.4/lib/python3.3/site-packages/sqlalchemy/orm/base.py",
 line 201, in generate
fn(self, *args[1:], **kw)
  File 
"/opt/enigmai/ve/python-3.3.4/lib/python3.3/site-packages/sqlalchemy/orm/query.py",
 line 1117, in _options
opt.process_query(self)
  File 
"/opt/enigmai/ve/python-3.3.4/lib/python3.3/site-packages/sqlalchemy/orm/strategy_options.py",
 line 95, in process_query
self._process(query, True)
  File 
"/opt/enigmai/ve/python-3.3.4/lib/python3.3/site-packages/sqlalchemy/orm/strategy_options.py",
 line 305, in _process
val._bind_loader(query, query._attributes, raiseerr)
  File 
"/opt/enigmai/ve/python-3.3.4/lib/python3.3/site-packages/sqlalchemy/orm/strategy_options.py",
 line 422, in _bind_loader
effective_path.set(context, "loader", loader)
  File 
"/opt/enigmai/ve/python-3.3.4/lib/python3.3/site-packages/sqlalchemy/orm/path_registry.py",
 line 63, in set
log.debug("set '%s' on path '%s' to '%s'", key, self, value)
  File "/usr/local/lib/python3.3/logging/__init__.py", line 1225, in debug
self._log(DEBUG, msg, args, **kwargs)
  File "/usr/local/lib/python3.3/logging/__init__.py", line 1372, in _log
self.handle(record)
  File "/usr/local/lib/python3.3/logging/__init__.py", line 1382, in handle
self.callHandlers(record)
  File "/usr/local/lib/python3.3/logging/__init__.py", line 1444, in 
callHandlers
hdlr.handle(record)
  File "/usr/local/lib/python3.3/logging/__init__.py", line 835, in handle
self.emit(record)
  File "/usr/local/lib/python3.3/logging/handlers.py", line 869, in emit
msg = self.format(record)
  File "/usr/local/lib/python3.3/logging/__init__.py", line 810, in format
return fmt.format(record)
  File "/opt/enigmai/6.0-test/server/handlers/LogFormatter.py", line 77, in 
format
return super(LogFormatter, self).format(record)
  File "/usr/local/lib/python3.3/logging/__init__.py", line 548, in format
record.message = record.getMessage()
  File "/usr/local/lib/python3.3/logging/__init__.py", line 311, in getMessage
msg = msg % self.args
  File 
"/opt/enigmai/ve/python-3.3.4/lib/python3.3/site-packages/sqlalchemy/orm/strategy_options.py",
 line 183, in __str__
return "Load(strategy=%r)" % self.strategy
TypeError: not all arguments converted during string formatting

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

alter table with sequence

[Ofir Herzas]

rename_table operation does not rename sequences if they exist.
This causes a problem that the renamed table is unusable since the sequence 
is missing.

In Oracle, a simple rename works well (RENAME old_seq_name TO 
new_seq_name;) although it says "table renamed".

Is there a sequence rename operation in Alembic? (not "drop" and "create")



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

best practice for creating new tables

[Ofir Herzas]

I've been working with sqlalchemy/alembic for several years now, but just 
recently stumbled on an important issue.

Here is how I use to work:
1. Issue 
Base.metadata.create_all(engine)
to make sure that missing tables are created 

2. check the current revision by issuing
context.get_current_revision()

3. running 
command.stamp
or 
command.upgrade
if the revision is the latest or not respectively

This worked well up until the point where I needed to change a table name 
since create_all created a new table and then upgrade just failed.

Issuing create_all after the upgrade isn't an option also since changes to 
missing tables will also fail.

What is the proper way of doing this? Before I run to add a create_table in 
each revision I had, I wanted to check if there's a better way of handling 
things.

What's your opinion? If table rename is the only problem with my first 
take, is there a way to exclude a table from create_all using a 'filter' 
function? (that way, I could add a property to the model stating it's 
previous name as check for that property in the 'filter' function...

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

[sqlalchemy] MetaData.sorted_tables is inconsistant

[Ofir Herzas]

It seems that MetaData.sorted_tables returns a different result each time.
While the results seem to be always correct, it is an odd behavior.

I would expect sorted_tables to return the same results every time

import sqlalchemy as sa
engine = sa.create_engine(db_url, echo=False)

meta = sa.MetaData(bind=engine, reflect=True)
a = [t.name for t in meta.sorted_tables]

meta = sa.MetaData(bind=engine, reflect=True)
b = [t.name for t in meta.sorted_tables]

*a and b are not sorted the same*

*BTW: meta.tables is consistent ...*

-- 
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] MetaData.sorted_tables is inconsistant

[Ofir Herzas]

Nope, I still use 0.9.7 J

It's not a big deal, just seemed odd, but thanks, I will look at it again once 
I move to 1.0 …

 

From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On 
Behalf Of Mike Bayer
Sent: Sunday, June 14, 2015 4:46 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] MetaData.sorted_tables is inconsistant

 

 

On 6/14/15 9:06 AM, Ofir Herzas wrote:

It seems that MetaData.sorted_tables returns a different result each time. 

While the results seem to be always correct, it is an odd behavior.

A topological sort is non-deterministic.   If two elements A and B have no 
dependency on each other, they can be returned in either order.

However, the sorted_tables accessor was  specifically enhanced to use a 
deterministic sorting in 1.0, where the list of items passed to the topological 
algorithm is first sorted alphabetically, and an ordered set is used 
internally, so that it comes up with the same result each time.

So, did you try 1.0 ?







 

I would expect sorted_tables to return the same results every time

 

import sqlalchemy as sa
engine = sa.create_engine(db_url, echo=False)

meta = sa.MetaData(bind=engine, reflect=True)
a = [t.name for t in meta.sorted_tables]

meta = sa.MetaData(bind=engine, reflect=True)
b = [t.name for t in meta.sorted_tables]


a and b are not sorted the same

 

BTW: meta.tables is consistent ...

-- 
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 a topic in the Google 
Groups sqlalchemy group.
To unsubscribe from this topic, visit 
https://groups.google.com/d/topic/sqlalchemy/eYZvKK12qTo/unsubscribe.
To unsubscribe from this group and all its topics, 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] select rows ordered by dependency

[Ofir Herzas]

Thanks Michael,
I need a cross-dialect solution so if that's the best we have, I think I'll 
go with the inefficient load-everything-and-sort-in-python method :)

On Tuesday, June 9, 2015 at 9:32:03 PM UTC+3, Michael Bayer wrote:

  you'd need to look into recursive querying.   PG can do this with CTEs:

 http://www.postgresql.org/docs/8.4/static/queries-with.html

 here's a tutorial that IMO goes way too fast and complicated, but SQLA can 
 do the SELECT part of this at least:


 http://schinckel.net/2014/11/27/postgres-tree-shootout-part-2%3A-adjacency-list-using-ctes/



 On 6/9/15 2:13 PM, Ofir Herzas wrote:
  
  I have a self referencing table like the following:

  class Employee(Base):
 __tablename__ = t_employee


 id = sa.Column(BigInteger, sa.Sequence('%s_id_seq' % __tablename__), 
 primary_key=True, nullable=False)
 manager_id = sa.Column(BigInteger, sa.ForeignKey(t_employee.id, 
 ondelete='SET NULL'))

  

  I want to select all rows ordered by their dependency (much like 
 metadata.sorted_tables does for tables). E.g - each manager should come 
 before his employees

  What is the best way to achieve this (assuming the blunt 
 load-it-all-and-order-it is not the best method)?
  -- 
 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 javascript:.
 To post to this group, send email to sqlal...@googlegroups.com 
 javascript:.
 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.

[sqlalchemy] select rows ordered by dependency

[Ofir Herzas]

I have a self referencing table like the following:

class Employee(Base):
__tablename__ = t_employee


id = sa.Column(BigInteger, sa.Sequence('%s_id_seq' % __tablename__), 
primary_key=True, nullable=False)
manager_id = sa.Column(BigInteger, sa.ForeignKey(t_employee.id, 
ondelete='SET NULL'))



I want to select all rows ordered by their dependency (much like 
metadata.sorted_tables does for tables). E.g - each manager should come 
before his employees

What is the best way to achieve this (assuming the blunt 
load-it-all-and-order-it is not the best method)?

-- 
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] Does anyone know how to handle NoSuchColumnError

[Ofir Herzas]

That part did work, but I keep a table class also (just so the table will be 
created if it doesn't exist), but adding the argument to the table args 
generates the error:

 

class ShiftEmployeeChange(Base):

__tablename__ = t_shift_employee_change

__table_args__ = {'extend_existing': True, 'implicit_returning':False}



id = sa.Column(Identifier, sa.Sequence('%s_id_seq' % __tablename__), 
nullable=False, primary_key=True)

field1 = sa.Column(sa.BigInteger, nullable=False)

field2 = sa.Column(sa.BigInteger)

field3 = sa.Column(sa.Date, nullable=False)

field4 = sa.Column(sa.Boolean, default=False)

changed_at = sa.Column(sa.DateTime, nullable=False, 
default=datetime.datetime.now)

changed_by = sa.Column(sa.BigInteger)

 

Do I need the argument there also?

 

 

From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On 
Behalf Of Mike Bayer
Sent: Monday, May 04, 2015 6:16 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] Does anyone know how to handle NoSuchColumnError

 

 

On 5/4/15 3:23 AM, Ofir Herzas wrote:

For some reason, I get the following error while using this attribute on the 
table:

python-2.7.6/lib/python2.7/site-packages/sqlalchemy/sql/schema.py:500: 
SAWarning: Can't validate argument 'implicit_returning'; can't locate any 
SQLAlchemy dialect named 'implicit'

  self._validate_dialect_kwargs(kwargs)

 

I should note that the application I'm developing uses other DB's also (e.g 
mysql) nd that I got this error while checking the application on mysql.


implicit_returning is one of the core arguments of Table.   That error message 
would happen if you were to send implicit_returning to some other kind of 
object, like an Index.

Here is a diff against history_meta showing where the argument would go:

diff --git a/examples/versioned_history/history_meta.py 
b/examples/versioned_history/history_meta.py
index 6d7b137..b5eadcb 100644
--- a/examples/versioned_history/history_meta.py
+++ b/examples/versioned_history/history_meta.py
@@ -106,7 +106,8 @@ def _history_mapper(local_mapper):
 local_mapper.local_table.name + '_history',
 local_mapper.local_table.metadata,
 *cols,
-schema=local_mapper.local_table.schema
+schema=local_mapper.local_table.schema,
+implicit_returning=False
 )
 else:
 # single table inheritance.  take any additional columns that may have







 

 

From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On 
Behalf Of Mike Bayer
Sent: Monday, May 04, 2015 1:47 AM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] Does anyone know how to handle NoSuchColumnError

 

 

On 5/3/15 12:00 PM, Ofir Herzas wrote:

Is there any danger using the implicit_returning=False on the engine? Or is it 
the same?


not dangerous but the implicit returning feature is very nice for INSERT 
statements done by the ORM, assuming you are using sequences to generate 
primary key values; it saves a round trip of executing the sequence ahead of 
time.






 

From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On 
Behalf Of Mike Bayer
Sent: Sunday, May 03, 2015 6:07 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] Does anyone know how to handle NoSuchColumnError

 

 

On 5/3/15 2:01 AM, Ofir Herzas wrote:

Hi Michael,

Thank you very much for your fast response. I will give it a try.

 

I tried to remove unneeded stuff but I think that the problem is in the 
track_changes mixin as I never got this error before using it.

If you need more stuff or do find something, I'd be happy to know.


OK, passing implicit_returning=False to the Table objects created by the 
history mixin should fix it on your end.   I'll try to find some time to 
experiment with it.







 

BTW: for initialization, I use:

 

DB_URL=oracle://user:pass@url/db

engine = create_engine(DB_URL, echo=False, pool_recycle=3600)

Session = scoped_session(sessionmaker(bind=engine, expire_on_commit=False))

session = tracked_session(Session())

Base = declarative_base()

 

Cheers,

Ofir


On Thursday, April 30, 2015 at 7:03:10 PM UTC+3, Michael Bayer wrote: 

 

On 4/30/15 11:00 AM, Ofir Herzas wrote:

Hi,

I'm using sqlalchemy 0.9.7 and cx_oracle 5.1.3 and every once in a while 
(inconsistent), I get the following error:

 

Traceback (most recent call last):
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
 line 1919, in flush
self._flush(objects)
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
 line 2037, in _flush
transaction.rollback(_capture_exception=True)
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py,
 line 60, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
 line

Re: [sqlalchemy] Does anyone know how to handle NoSuchColumnError

[Ofir Herzas]

Hi Michael,
Thank you very much for your fast response. I will give it a try.

I tried to remove unneeded stuff but I think that the problem is in the 
track_changes mixin as I never got this error before using it.
If you need more stuff or do find something, I'd be happy to know.

BTW: for initialization, I use:

DB_URL=oracle://user:pass@url/db
engine = create_engine(DB_URL, echo=False, pool_recycle=3600)
Session = scoped_session(sessionmaker(bind=engine, expire_on_commit=False))
session = tracked_session(Session())
Base = declarative_base()

Cheers,
Ofir

On Thursday, April 30, 2015 at 7:03:10 PM UTC+3, Michael Bayer wrote:

  

 On 4/30/15 11:00 AM, Ofir Herzas wrote:
  
  Hi,
 I'm using sqlalchemy 0.9.7 and cx_oracle 5.1.3 and every once in a while 
 (inconsistent), I get the following error:

  Traceback (most recent call last):
   File 
 /opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
  
 line 1919, in flush
 self._flush(objects)
   File 
 /opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
  
 line 2037, in _flush
 transaction.rollback(_capture_exception=True)
   File 
 /opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py,
  
 line 60, in __exit__
 compat.reraise(exc_type, exc_value, exc_tb)
   File 
 /opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
  
 line 2001, in _flush
 flush_context.execute()
   File 
 /opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py,
  
 line 372, in execute
 rec.execute(self)
   File 
 /opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py,
  
 line 526, in execute
 uow
   File 
 /opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py,
  
 line 65, in save_obj
 mapper, table, insert)
   File 
 /opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py,
  
 line 602, in _emit_insert_statements
 execute(statement, params)
   File 
 /opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/base.py,
  
 line 729, in execute
 return meth(self, multiparams, params)
   File 
 /opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/sql/elements.py,
  
 line 321, in _execute_on_connection
 return connection._execute_clauseelement(self, multiparams, params)
   File 
 /opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/base.py,
  
 line 826, in _execute_clauseelement
 compiled_sql, distilled_params
   File 
 /opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/base.py,
  
 line 978, in _execute_context
 context._fetch_implicit_returning(result)
   File 
 /opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/default.py,
  
 line 815, in _fetch_implicit_returning
 ipk.append(row[c])
   File 
 /opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/result.py,
  
 line 331, in _key_fallback
 expression._string_or_unprintable(key))
 NoSuchColumnError: Could not locate column in row for column '
 t_shift_employee_change.id'
 [30/Apr/2015:15:55:17] EXCEPTION
 #  EXCEPTION DESCRIPTION BEGIN  #
 #  Type  #
 NoSuchColumnError
 #  Detail  #
 Could not locate column in row for column 't_shift_employee_change.id'
 #  Traceback  #
   File 
 /opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
  
 line 1919, in flush
 self._flush(objects)
 -
   File 
 /opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
  
 line 2037, in _flush
 transaction.rollback(_capture_exception=True)
 -
   File 
 /opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py,
  
 line 60, in __exit__
 compat.reraise(exc_type, exc_value, exc_tb)
 -
   File 
 /opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
  
 line 2001, in _flush
 flush_context.execute()
 -
   File 
 /opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py,
  
 line 372, in execute
 rec.execute(self)
 -
   File 
 /opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py,
  
 line 526, in execute
 uow
 -
   File 
 /opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py,
  
 line 65, in save_obj
 mapper, table, insert)
 -
   File 
 /opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py,
  
 line 602, in _emit_insert_statements
 execute(statement, params)
 -
   File 
 /opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/base.py,
  
 line 729, in execute
 return meth(self, multiparams, params)
 -
   File 
 /opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/sql/elements.py,
  
 line 321

RE: [sqlalchemy] Does anyone know how to handle NoSuchColumnError

[Ofir Herzas]

Is there any danger using the implicit_returning=False on the engine? Or is it 
the same?

 

From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On 
Behalf Of Mike Bayer
Sent: Sunday, May 03, 2015 6:07 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] Does anyone know how to handle NoSuchColumnError

 

 

On 5/3/15 2:01 AM, Ofir Herzas wrote:

Hi Michael,

Thank you very much for your fast response. I will give it a try.

 

I tried to remove unneeded stuff but I think that the problem is in the 
track_changes mixin as I never got this error before using it.

If you need more stuff or do find something, I'd be happy to know.


OK, passing implicit_returning=False to the Table objects created by the 
history mixin should fix it on your end.   I'll try to find some time to 
experiment with it.





 

BTW: for initialization, I use:

 

DB_URL=oracle://user:pass@url/db

engine = create_engine(DB_URL, echo=False, pool_recycle=3600)

Session = scoped_session(sessionmaker(bind=engine, expire_on_commit=False))

session = tracked_session(Session())

Base = declarative_base()

 

Cheers,

Ofir


On Thursday, April 30, 2015 at 7:03:10 PM UTC+3, Michael Bayer wrote: 

 

On 4/30/15 11:00 AM, Ofir Herzas wrote:

Hi,

I'm using sqlalchemy 0.9.7 and cx_oracle 5.1.3 and every once in a while 
(inconsistent), I get the following error:

 

Traceback (most recent call last):
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
 line 1919, in flush
self._flush(objects)
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
 line 2037, in _flush
transaction.rollback(_capture_exception=True)
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py,
 line 60, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
 line 2001, in _flush
flush_context.execute()
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py,
 line 372, in execute
rec.execute(self)
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py,
 line 526, in execute
uow
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py,
 line 65, in save_obj
mapper, table, insert)
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py,
 line 602, in _emit_insert_statements
execute(statement, params)
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/base.py,
 line 729, in execute
return meth(self, multiparams, params)
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/sql/elements.py,
 line 321, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/base.py,
 line 826, in _execute_clauseelement
compiled_sql, distilled_params
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/base.py,
 line 978, in _execute_context
context._fetch_implicit_returning(result)
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/default.py,
 line 815, in _fetch_implicit_returning
ipk.append(row[c])
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/result.py,
 line 331, in _key_fallback
expression._string_or_unprintable(key))
NoSuchColumnError: Could not locate column in row for column 
't_shift_employee_change.id'
[30/Apr/2015:15:55:17] EXCEPTION
#  EXCEPTION DESCRIPTION BEGIN  #
#  Type  #
NoSuchColumnError
#  Detail  #
Could not locate column in row for column 't_shift_employee_change.id'
#  Traceback  #
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
 line 1919, in flush
self._flush(objects)
-
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
 line 2037, in _flush
transaction.rollback(_capture_exception=True)
-
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py,
 line 60, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
-
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
 line 2001, in _flush
flush_context.execute()
-
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py,
 line 372, in execute
rec.execute(self)
-
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py,
 line 526, in execute
uow
-
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py,
 line 65, in save_obj
mapper, table, insert)
-
  File 
/opt/enigmai/ve/python

[sqlalchemy] Does anyone know how to handle NoSuchColumnError

[Ofir Herzas]

Hi,
I'm using sqlalchemy 0.9.7 and cx_oracle 5.1.3 and every once in a while 
(inconsistent), I get the following error:

Traceback (most recent call last):
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
 
line 1919, in flush
self._flush(objects)
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
 
line 2037, in _flush
transaction.rollback(_capture_exception=True)
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py,
 
line 60, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
 
line 2001, in _flush
flush_context.execute()
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py,
 
line 372, in execute
rec.execute(self)
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py,
 
line 526, in execute
uow
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py,
 
line 65, in save_obj
mapper, table, insert)
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py,
 
line 602, in _emit_insert_statements
execute(statement, params)
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/base.py,
 
line 729, in execute
return meth(self, multiparams, params)
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/sql/elements.py,
 
line 321, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/base.py,
 
line 826, in _execute_clauseelement
compiled_sql, distilled_params
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/base.py,
 
line 978, in _execute_context
context._fetch_implicit_returning(result)
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/default.py,
 
line 815, in _fetch_implicit_returning
ipk.append(row[c])
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/result.py,
 
line 331, in _key_fallback
expression._string_or_unprintable(key))
NoSuchColumnError: Could not locate column in row for column 
't_shift_employee_change.id'
[30/Apr/2015:15:55:17] EXCEPTION
#  EXCEPTION DESCRIPTION BEGIN  #
#  Type  #
NoSuchColumnError
#  Detail  #
Could not locate column in row for column 't_shift_employee_change.id'
#  Traceback  #
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
 
line 1919, in flush
self._flush(objects)
-
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
 
line 2037, in _flush
transaction.rollback(_capture_exception=True)
-
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py,
 
line 60, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
-
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/session.py,
 
line 2001, in _flush
flush_context.execute()
-
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py,
 
line 372, in execute
rec.execute(self)
-
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py,
 
line 526, in execute
uow
-
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py,
 
line 65, in save_obj
mapper, table, insert)
-
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py,
 
line 602, in _emit_insert_statements
execute(statement, params)
-
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/base.py,
 
line 729, in execute
return meth(self, multiparams, params)
-
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/sql/elements.py,
 
line 321, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
-
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/base.py,
 
line 826, in _execute_clauseelement
compiled_sql, distilled_params
-
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/base.py,
 
line 978, in _execute_context
context._fetch_implicit_returning(result)
-
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/default.py,
 
line 815, in _fetch_implicit_returning
ipk.append(row[c])
-
  File 
/opt/enigmai/ve/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/engine/result.py,
 
line 331, in _key_fallback
expression._string_or_unprintable(key))
#  EXCEPTION DESCRIPTION END  #

I saw several posts about 

[sqlalchemy] how to add an order column to an existing table

[Ofir Herzas]

I have the following table:

my_table = sa.sql.table('my_table',
sa.Column('id', sa.BigInteger),
sa.Column('employee_id', sa.BigInteger))

and I want to add a 'rank' column using alembic. The addition itself is 
pretty straight forward using add_column but I also need to populate that 
column using sqlalchemy based on existing information (each employee_id has 
its own rank ordered by the id).

For example, the following table:

idemployee_idrank
-----
1  1  1
2  1  2
5  1  3
3  2  1
4  3  1
6  3  2

The problem is similar to this one 
https://stackoverflow.com/questions/20677287/how-to-add-order-to-an-existing-table
.

The only difference is that I need it working for sqlalchemy on top of both 
mysql and oracle (a more generic solution is needed).

Any advice?

(BTW: I've posted the same question in SO here 
http://stackoverflow.com/questions/26324560/sql-how-to-add-an-order-column-to-an-existing-table
)

-- 
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] selecting from a relationship

[Ofir Herzas]

Hi,
I have a model similar to the following:

class Employee(Base):
__tablename__ = t_employee

id = sa.Column(Identifier, sa.Sequence('%s_id_seq' % __tablename__), 
primary_key=True, nullable=False)
first_name = sa.Column(sa.String(30))
last_name = sa.Column(sa.String(30))
phone_number = sa.Column(sa.String(30))

_jobs = sa.orm.relationship(EmployeeJob, lazy=joined, cascade=all, 
delete, delete-orphan)

@property
def name(self):
return self.first_name + (  + self.last_name if 
len(self.last_name or )  0 else )

@property
def jobs(self):
return [item.job_id for item in sorted(self._jobs, 
key=attrgetter('id'))]

@jobs.setter
def jobs(self, value):
self._jobs = [EmployeeJob(job_id=id_) for id_ in to_list(value)]

class EmployeeJob(Base):
id = sa.Column(sa.BigInteger, sa.Sequence('%s_id_seq' % __tablename__), 
primary_key=True, nullable=False)
employee_id = sa.Column(sa.BigInteger, sa.ForeignKey('t_employee.id', 
ondelete=CASCADE), nullable=False)
job_id = sa.Column(sa.BigInteger, sa.ForeignKey('t_job.id', 
ondelete=CASCADE), nullable=False)


Now, I'm trying to write a simple query that will fetch all employees with 
their jobs.
As I understand, I need to use joinedload so that the list of jobs will be 
eagerly loaded but I can't understand how to do it.

I tried the following:
session.query(Employee.id).options(sa.orm.joinedload(Employee.jobs))

but it doesn't work.

Just to clarify, I want to load some of the columns, not all of them, and 
I'm expecting to get the list of jobs for each employee (hopefully like the 
getter produces them)

session.query(Employee) does fetch the required information but it selects 
some unneeded columns

Also, how do I select the name property?

Thanks,
Ofir

-- 
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] selecting from a relationship

[Ofir Herzas]

Thanks Simon,
I've tried the following:

session.query(Employee).options(sa.orm.joinedload(Employee.jobs).load_only(
'id', 'first_name')).all()

which according to the documentation (
http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#deferred-loading-with-multiple-entities)
 
should work, but it throws an exception (ArgumentError: mapper option 
expects string key or list of attributes)

Can you please provide an example?

Please notice that I'm trying to load only some properties of Employee 
(including one relationship) while this behavior should not be the default 
behavior (meaning that I don't want the defer the columns at model level)


On Thursday, September 4, 2014 5:59:21 PM UTC+3, Simon King wrote:

 On Thu, Sep 4, 2014 at 3:28 PM, Ofir Herzas her...@gmail.com 
 javascript: wrote: 
  Hi, 
  I have a model similar to the following: 
  
  class Employee(Base): 
  __tablename__ = t_employee 
  
  id = sa.Column(Identifier, sa.Sequence('%s_id_seq' % __tablename__), 
  primary_key=True, nullable=False) 
  first_name = sa.Column(sa.String(30)) 
  last_name = sa.Column(sa.String(30)) 
  phone_number = sa.Column(sa.String(30)) 
  
  _jobs = sa.orm.relationship(EmployeeJob, lazy=joined, 
 cascade=all, 
  delete, delete-orphan) 
  
  @property 
  def name(self): 
  return self.first_name + (  + self.last_name if 
 len(self.last_name 
  or )  0 else ) 
  
  @property 
  def jobs(self): 
  return [item.job_id for item in sorted(self._jobs, 
  key=attrgetter('id'))] 
  
  @jobs.setter 
  def jobs(self, value): 
  self._jobs = [EmployeeJob(job_id=id_) for id_ in to_list(value)] 
  
  class EmployeeJob(Base): 
  id = sa.Column(sa.BigInteger, sa.Sequence('%s_id_seq' % 
 __tablename__), 
  primary_key=True, nullable=False) 
  employee_id = sa.Column(sa.BigInteger, sa.ForeignKey('t_employee.id', 

  ondelete=CASCADE), nullable=False) 
  job_id = sa.Column(sa.BigInteger, sa.ForeignKey('t_job.id', 
  ondelete=CASCADE), nullable=False) 
  
  
  Now, I'm trying to write a simple query that will fetch all employees 
 with 
  their jobs. 
  As I understand, I need to use joinedload so that the list of jobs will 
 be 
  eagerly loaded but I can't understand how to do it. 
  
  I tried the following: 
  session.query(Employee.id).options(sa.orm.joinedload(Employee.jobs)) 
  
  but it doesn't work. 
  
  Just to clarify, I want to load some of the columns, not all of them, 
 and 
  I'm expecting to get the list of jobs for each employee (hopefully like 
 the 
  getter produces them) 
  
  session.query(Employee) does fetch the required information but it 
 selects 
  some unneeded columns 
  
  Also, how do I select the name property? 
  
  Thanks, 
  Ofir 
  

 Eager loading means that when you have an instance of Employee, and 
 you access its 'jobs' property, no SQL is emitted because the data is 
 already available. This implies that you have to query for the 
 Employee class, not just one of its columns (otherwise you wouldn't 
 have an instance from which to access the jobs property) 

 If you don't want to load all the Employee columns, you can defer them: 

   
 http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#deferred-column-loading
  

 Hope that helps, 

 Simon 


-- 
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] selecting from a relationship

[Ofir Herzas]

Thanks Michael,

I'm using 0.9.7 and while your example did work, the following did not:

 

from sqlalchemy import *

from sqlalchemy.orm import *

from sqlalchemy.ext.declarative import declarative_base

 

Base = declarative_base()

 

class Employee(Base):

__tablename__ = 'employee'

 

id = Column(Integer, primary_key=True)

first_name = Column(String)

_jobs = relationship(EmployeeJob, lazy=joined)

 

@property

def jobs(self):

return [item.job_id for item in sorted(self._jobs,
key=attrgetter('id'))]

 

class EmployeeJob(Base):

__tablename__ = employee_job

 

id = Column(Integer, primary_key=True)

employee_id = Column(Integer, ForeignKey('employee.id'))

job_id = Column(Integer, ForeignKey('job.id'))

 

class Job(Base):

__tablename__ = 'job'

 

id = Column(Integer, primary_key=True)

name = Column(String)

 

e = create_engine(sqlite://, echo=True)

Base.metadata.create_all(e)

 

session = Session(e)

 

session.query(Employee).options(joinedload(Employee.jobs).load_only('id',
'first_name')).all()

 

 

Notice that the difference here is that I'm using a property decorator on
jobs, there is a link table (EmployeeJob), and I'm trying to load
information of Employee (first_name should be from there)

 

Thanks,

Ofir

 

From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
Behalf Of Michael Bayer
Sent: Thursday, September 04, 2014 8:00 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] selecting from a relationship

 

 

On Sep 4, 2014, at 12:28 PM, Ofir Herzas herz...@gmail.com wrote:





Thanks Simon,

I've tried the following:

 

session.query(Employee).options(sa.orm.joinedload(Employee.jobs).load_only('
id', 'first_name')).all()

 

which according to the documentation
(http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#deferred-loadi
ng-with-multiple-entities) should work, but it throws an exception
(ArgumentError: mapper option expects string key or list of attributes)

 

Can you please provide an example?

 

check your SQLAlchemy version, I cannot reproduce that issue:

 

from sqlalchemy import *

from sqlalchemy.orm import *

from sqlalchemy.ext.declarative import declarative_base

 

Base = declarative_base()

 

class Employee(Base):

__tablename__ = 'employee'

 

id = Column(Integer, primary_key=True)

jobs = relationship(Job)

 

class Job(Base):

__tablename__ = 'job'

 

id = Column(Integer, primary_key=True)

employee_id = Column(Integer, ForeignKey('employee.id'))

first_name = Column(String)

 

e = create_engine(sqlite://, echo=True)

Base.metadata.create_all(e)

 

session = Session(e)

 

session.query(Employee).options(joinedload(Employee.jobs).load_only('id',
'first_name')).all()

 

output:

 

SELECT employee.id AS employee_id, job_1.id AS job_1_id, job_1.first_name AS
job_1_first_name 

FROM employee LEFT OUTER JOIN job AS job_1 ON employee.id =
job_1.employee_id

 

 





 

Please notice that I'm trying to load only some properties of Employee
(including one relationship) while this behavior should not be the default
behavior (meaning that I don't want the defer the columns at model level)



On Thursday, September 4, 2014 5:59:21 PM UTC+3, Simon King wrote:

On Thu, Sep 4, 2014 at 3:28 PM, Ofir Herzas her...@gmail.com javascript:
 wrote: 
 Hi, 
 I have a model similar to the following: 
 
 class Employee(Base): 
 __tablename__ = t_employee 
 
 id = sa.Column(Identifier, sa.Sequence('%s_id_seq' % __tablename__), 
 primary_key=True, nullable=False) 
 first_name = sa.Column(sa.String(30)) 
 last_name = sa.Column(sa.String(30)) 
 phone_number = sa.Column(sa.String(30)) 
 
 _jobs = sa.orm.relationship(EmployeeJob, lazy=joined,
cascade=all, 
 delete, delete-orphan) 
 
 @property 
 def name(self): 
 return self.first_name + (  + self.last_name if
len(self.last_name 
 or )  0 else ) 
 
 @property 
 def jobs(self): 
 return [item.job_id for item in sorted(self._jobs, 
 key=attrgetter('id'))] 
 
 @jobs.setter 
 def jobs(self, value): 
 self._jobs = [EmployeeJob(job_id=id_) for id_ in to_list(value)] 
 
 class EmployeeJob(Base): 
 id = sa.Column(sa.BigInteger, sa.Sequence('%s_id_seq' %
__tablename__), 
 primary_key=True, nullable=False) 
 employee_id = sa.Column(sa.BigInteger, sa.ForeignKey('t_employee.id
http://t_employee.id/ ', 
 ondelete=CASCADE), nullable=False) 
 job_id = sa.Column(sa.BigInteger, sa.ForeignKey('t_job.id
http://t_job.id/ ', 
 ondelete=CASCADE), nullable=False) 
 
 
 Now, I'm trying to write a simple query that will fetch all employees with

 their jobs. 
 As I understand, I need to use joinedload so that the list of jobs will be

 eagerly loaded but I can't understand how to do it. 
 
 I tried the following: 
 session.query(Employee.id).options(sa.orm.joinedload(Employee.jobs)) 
 
 but it doesn't work. 
 
 Just to clarify, I want

RE: [sqlalchemy] selecting from a relationship

[Ofir Herzas]

Nevertheless, is there a way to achieve what I want? (which is to
selectively load several columns and this 'jobs' property from Employee)

 

Thanks,

Ofir

 

 

From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
Behalf Of Michael Bayer
Sent: Thursday, September 04, 2014 8:42 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] selecting from a relationship

 

 

On Sep 4, 2014, at 1:32 PM, Ofir Herzas herz...@gmail.com wrote:





Thanks Michael,

I'm using 0.9.7 and while your example did work, the following did not:

 

Ok that's not a relationship().  joinedload() only works with relationships.

 

 

 





 

from sqlalchemy import *

from sqlalchemy.orm import *

from sqlalchemy.ext.declarative import declarative_base

 

Base = declarative_base()

 

class Employee(Base):

__tablename__ = 'employee'

 

id = Column(Integer, primary_key=True)

first_name = Column(String)

_jobs = relationship(EmployeeJob, lazy=joined)

 

@property

def jobs(self):

return [item.job_id for item in sorted(self._jobs,
key=attrgetter('id'))]

 

class EmployeeJob(Base):

__tablename__ = employee_job

 

id = Column(Integer, primary_key=True)

employee_id = Column(Integer, ForeignKey('employee.id'))

job_id = Column(Integer, ForeignKey('job.id'))

 

class Job(Base):

__tablename__ = 'job'

 

id = Column(Integer, primary_key=True)

name = Column(String)

 

e = create_engine(sqlite://, echo=True)

Base.metadata.create_all(e)

 

session = Session(e)

 

session.query(Employee).options(joinedload(Employee.jobs).load_only('id',
'first_name')).all()

 

 

Notice that the difference here is that I'm using a property decorator on
jobs, there is a link table (EmployeeJob), and I'm trying to load
information of Employee (first_name should be from there)

 

Thanks,

Ofir

 

From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
Behalf Of Michael Bayer
Sent: Thursday, September 04, 2014 8:00 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] selecting from a relationship

 

 

On Sep 4, 2014, at 12:28 PM, Ofir Herzas  mailto:herz...@gmail.com
herz...@gmail.com wrote:






Thanks Simon,

I've tried the following:

 

session.query(Employee).options(sa.orm.joinedload(Employee.jobs).load_only('
id','first_name')).all()

 

which according to the documentation (
http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#deferred-loadi
ng-with-multiple-entities
http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#deferred-loadin
g-with-multiple-entities) should work, but it throws an exception
(ArgumentError: mapper option expects string key or list of attributes)

 

Can you please provide an example?

 

check your SQLAlchemy version, I cannot reproduce that issue:

 

from sqlalchemy import *

from sqlalchemy.orm import *

from sqlalchemy.ext.declarative import declarative_base

 

Base = declarative_base()

 

class Employee(Base):

__tablename__ = 'employee'

 

id = Column(Integer, primary_key=True)

jobs = relationship(Job)

 

class Job(Base):

__tablename__ = 'job'

 

id = Column(Integer, primary_key=True)

employee_id = Column(Integer, ForeignKey('employee.id'))

first_name = Column(String)

 

e = create_engine(sqlite://, echo=True)

Base.metadata.create_all(e)

 

session = Session(e)

 

session.query(Employee).options(joinedload(Employee.jobs).load_only('id',
'first_name')).all()

 

output:

 

SELECT employee.id AS employee_id, job_1.id AS job_1_id, job_1.first_name AS
job_1_first_name 

FROM employee LEFT OUTER JOIN job AS job_1 ON employee.id =
job_1.employee_id

 

 






 

Please notice that I'm trying to load only some properties of Employee
(including one relationship) while this behavior should not be the default
behavior (meaning that I don't want the defer the columns at model level)



On Thursday, September 4, 2014 5:59:21 PM UTC+3, Simon King wrote:

On Thu, Sep 4, 2014 at 3:28 PM, Ofir Herzas her...@gmail.com wrote: 
 Hi, 
 I have a model similar to the following: 
 
 class Employee(Base): 
 __tablename__ = t_employee 
 
 id = sa.Column(Identifier, sa.Sequence('%s_id_seq' % __tablename__), 
 primary_key=True, nullable=False) 
 first_name = sa.Column(sa.String(30)) 
 last_name = sa.Column(sa.String(30)) 
 phone_number = sa.Column(sa.String(30)) 
 
 _jobs = sa.orm.relationship(EmployeeJob, lazy=joined,
cascade=all, 
 delete, delete-orphan) 
 
 @property 
 def name(self): 
 return self.first_name + (  + self.last_name if
len(self.last_name 
 or )  0 else ) 
 
 @property 
 def jobs(self): 
 return [item.job_id for item in sorted(self._jobs, 
 key=attrgetter('id'))] 
 
 @jobs.setter 
 def jobs(self, value): 
 self._jobs = [EmployeeJob(job_id=id_) for id_ in to_list(value)] 
 
 class EmployeeJob(Base): 
 id = sa.Column(sa.BigInteger, sa.Sequence('%s_id_seq

RE: [sqlalchemy] selecting from a relationship

[Ofir Herzas]

Thanks Simon, it worked!
I did the whole thing just because I had an sql select query talking 1.5
seconds to complete (several joins and many properties, just 1000 records),
and this change reduced it to 1.1 seconds (I guess I'll have to find a
better way to improve performance)


-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
Behalf Of Simon King
Sent: Thursday, September 04, 2014 11:14 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] selecting from a relationship

You need to join along the actual relationships between your classes. You've
got this:

Employee._jobs - EmployeeJob.? - Job

(I assume EmployeeJob has a job relationship to Job.)

I think you probably want something like this:

(session.query(Employee)
 .options(load_only('id', 'first_name'),
  joinedload(_jobs).joinedload(job))
).all()

Note that load_only is a separate option. The way you had it:

joinedload(Employee._jobs).load_only('id', 'first_name')

...would be looking for 'id' and 'first_name' columns on the EmployeeJob
object.

There are lots of examples at:

  http://docs.sqlalchemy.org/en/latest/orm/loading.html

Hope that helps,

Simon

On 4 Sep 2014, at 19:17, Ofir Herzas herz...@gmail.com wrote:

 Nevertheless, is there a way to achieve what I want? (which is to 
 selectively load several columns and this 'jobs' property from 
 Employee)
  
 Thanks,
 Ofir
  
  
 From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] 
 On Behalf Of Michael Bayer
 Sent: Thursday, September 04, 2014 8:42 PM
 To: sqlalchemy@googlegroups.com
 Subject: Re: [sqlalchemy] selecting from a relationship
  
  
 On Sep 4, 2014, at 1:32 PM, Ofir Herzas herz...@gmail.com wrote:
 
 
 Thanks Michael,
 I'm using 0.9.7 and while your example did work, the following did not:
  
 Ok that's not a relationship().  joinedload() only works with
relationships.
  
  
  
 
 
  
 from sqlalchemy import *
 from sqlalchemy.orm import *
 from sqlalchemy.ext.declarative import declarative_base
  
 Base = declarative_base()
  
 class Employee(Base):
 __tablename__ = 'employee'
  
 id = Column(Integer, primary_key=True)
 first_name = Column(String)
 _jobs = relationship(EmployeeJob, lazy=joined)
  
 @property
 def jobs(self):
 return [item.job_id for item in sorted(self._jobs, 
 key=attrgetter('id'))]
  
 class EmployeeJob(Base):
 __tablename__ = employee_job
  
 id = Column(Integer, primary_key=True)
 employee_id = Column(Integer, ForeignKey('employee.id'))
 job_id = Column(Integer, ForeignKey('job.id'))
  
 class Job(Base):
 __tablename__ = 'job'
  
 id = Column(Integer, primary_key=True)
 name = Column(String)
  
 e = create_engine(sqlite://, echo=True)
 Base.metadata.create_all(e)
  
 session = Session(e)
  
 session.query(Employee).options(joinedload(Employee.jobs).load_only('i
 d', 'first_name')).all()
  
  
 Notice that the difference here is that I'm using a property decorator 
 on jobs, there is a link table (EmployeeJob), and I'm trying to load 
 information of Employee (first_name should be from there)
  
 Thanks,
 Ofir
  
 From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] 
 On Behalf Of Michael Bayer
 Sent: Thursday, September 04, 2014 8:00 PM
 To: sqlalchemy@googlegroups.com
 Subject: Re: [sqlalchemy] selecting from a relationship
  
  
 On Sep 4, 2014, at 12:28 PM, Ofir Herzas herz...@gmail.com wrote:
 
 
 
 Thanks Simon,
 I've tried the following:
  
 session.query(Employee).options(sa.orm.joinedload(Employee.jobs).load_
 only('id','first_name')).all()
  
 which according to the documentation 
 (http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#deferred
 -loading-with-multiple-entities) should work, but it throws an 
 exception (ArgumentError: mapper option expects string key or list of 
 attributes)
  
 Can you please provide an example?
  
 check your SQLAlchemy version, I cannot reproduce that issue:
  
 from sqlalchemy import *
 from sqlalchemy.orm import *
 from sqlalchemy.ext.declarative import declarative_base
  
 Base = declarative_base()
  
 class Employee(Base):
 __tablename__ = 'employee'
  
 id = Column(Integer, primary_key=True)
 jobs = relationship(Job)
  
 class Job(Base):
 __tablename__ = 'job'
  
 id = Column(Integer, primary_key=True)
 employee_id = Column(Integer, ForeignKey('employee.id'))
 first_name = Column(String)
  
 e = create_engine(sqlite://, echo=True)
 Base.metadata.create_all(e)
  
 session = Session(e)
  
 session.query(Employee).options(joinedload(Employee.jobs).load_only('i
 d', 'first_name')).all()
  
 output:
  
 SELECT employee.id AS employee_id, job_1.id AS job_1_id, 
 job_1.first_name AS job_1_first_name FROM employee LEFT OUTER JOIN job 
 AS job_1 ON employee.id = job_1.employee_id
  
  
 
 
 
  
 Please notice that I'm trying to load only some properties of Employee 
 (including one relationship

Re: [sqlalchemy] selecting from a relationship

[Ofir Herzas]

Thanks Jonathan,
I actually have more properties and relationships which were removed for
the sake of the example.
You are right though, the performance gain was not as I hoped it would be.

I'll have to think of other methods (caching maybe)...
On Sep 4, 2014 11:50 PM, Jonathan Vanasco jonat...@findmeon.com wrote:



 On Thursday, September 4, 2014 2:19:34 PM UTC-4, Ofir Herzas wrote:

 Nevertheless, is there a way to achieve what I want? (which is to
 selectively load several columns and this 'jobs' property from Employee)


 The ways I usually do that are:

 1.  Select the other object.  Query for EmployeeJob, and then load only
 the Employee columns.

 2. Explicitly load columns.  I'm not sure if this will work for
 relationships:

   session.query( TableClass.column_a, TableClass.column_b).all()

 instead of returning an ORM object, you get a tuple.

 3. Define a column as deferred in the mapper [
 http://docs.sqlalchemy.org/en/latest/orm/mapper_config.html#deferred-column-loading
 ]

 Looking at your example though, unless you have very high traffic --
 you're not going to have a significant performance gain with a query like
 that.  Your tables don't have many columns, and they're not very large.
  People are usually concerned about load_only when you have very large
 fields (BLOBS, TEXT, HSTORE) or complex joins and need to save memory.

 You might just be better off letting the ORM load stuff you don't need --
 you'll get much more code done that way.

 --
 You received this message because you are subscribed to a topic in the
 Google Groups sqlalchemy group.
 To unsubscribe from this topic, visit
 https://groups.google.com/d/topic/sqlalchemy/OprfrGJcoJU/unsubscribe.
 To unsubscribe from this group and all its topics, 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] change Oracle sequence on insert

[Ofir Herzas]

Thanks Michael,

You of course is right and I rarely have to use this method.

Having said that, once in a while, if I need to migrate a version or do some
maintenance, I do need that option.

How would I do that in sqlalchemy? Do I have to use raw SQL for that? Why
would that not scale?

 

From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
Behalf Of Michael Bayer
Sent: Saturday, July 19, 2014 6:12 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] change Oracle sequence on insert

 

 

On Jul 19, 2014, at 3:38 AM, Ofir Herzas herz...@gmail.com wrote:





I have a table with the following column:

id = sa.Column(sa.Integer, sa.Sequence('id_seq'), primary_key=True,
nullable=False)

Usually, I have no problems inserting data, but every time I insert rows
with specific id, it causes problems with Oracle since the sequence is not
modified accordingly.

For example, assuming that the table is new and the sequence starts at 1, if
I insert a row specifying id=2, the sequence doesn't change which will cause
the next insert to fail.

I do understand that Oracle does not support auto increment but what is the
proper way of handling this under sqlalchemy? Do I need to manually change
the sequence after such insert statement? can I bind to an event or use any
other magic to make it work like other dialects? (choose max(id)+1)

 

 

 

the case where you have a sequence used for a table and at the same time you
have the need to insert rows with specific identifiers as a normal matter of
course (as opposed to when you need to do a bulk insert as part of database
maintenance) is an unusual one.   Most database folks would ask why that's
the use case you have.Surrogate primary keys are not supposed to be
meaningful, you normally would just let the sequence handle creation of new
values 100% of the time.Because they increment atomically, you never
have to worry about two primary  key identifiers conflicting.   If you're
working around that then you can't be assured of integrity violations within
concurrent scenarios.

 

Short answer yes if you are inserting values directly then you need to
update the sequence manually, on oracle i think it might be ALTER SEQUENCE
or something like that.   It's not the kind of thing that would scale,
though.

-- 
You received this message because you are subscribed to a topic in the
Google Groups sqlalchemy group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/1hjzce5kg3Q/unsubscribe.
To unsubscribe from this group and all its topics, 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.

[sqlalchemy] change Oracle sequence on insert

[Ofir Herzas]

I have a table with the following column:

id = sa.Column(sa.Integer, sa.Sequence('id_seq'), primary_key=True, 
nullable=False)

Usually, I have no problems inserting data, but every time I insert rows 
with specific id, it causes problems with Oracle since the sequence is not 
modified accordingly.

For example, assuming that the table is new and the sequence starts at 1, 
if I insert a row specifying id=2, the sequence doesn't change which will 
cause the next insert to fail.

I do understand that Oracle does not support auto increment but what is the 
proper way of handling this under sqlalchemy? Do I need to manually change 
the sequence after such insert statement? can I bind to an event or use any 
other magic to make it work like other dialects? (choose max(id)+1)

-- 
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] Oracle with sequence for primary key and that sequence out of sync

[Ofir Herzas]

Didn't say it was your responsibility, just thought you should know there might 
be an issue there.

I'll modify the sequence manually and get back to it should the problem persist.

 

 

From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On 
Behalf Of Mike Bayer
Sent: Wednesday, June 25, 2014 4:27 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] Oracle with sequence for primary key and that 
sequence out of sync

 

 

On 6/25/14, 5:50 AM, Ofir Herzas wrote:

Sorry to barge in, but I'm having the exact same issue and I'm pretty sure no 
one altered the sequence manually. 

I'm using sqlalchemy 0.8, python 2.7.6, rhel 6.5, oracle 10g, cx_oracle with 
the same connection string as above

 

This issue started just recently after running ok for more than a thousand 
times.

I should also say that my application is installed at several customers, and I 
have this issue only at the one using Oracle.


I'm open to possibilities for how this could happen, short of the ALTER 
SEQUENCE command being emitted, in which case feel free to grep SQLAlchemy's 
codebase for this clause (it's not there).   From my vantage point, if there is 
actually an issue that is implicitly making this happen outside of application 
code, it would have to be on the driver or server side somehow.








On Tuesday, June 3, 2014 1:18:42 AM UTC+3, Michael Bayer wrote: 


On Jun 2, 2014, at 1:17 PM, Scott Koranda skor...@gmail.com javascript:  
wrote: 

 
 I investigated and found that the sequence seq_admin_groups_id 
 was now at the value 68 after having been used previously to 
 insert rows with IDs in the 500s. 
 
 I stopped the code and used sqlplus to change the sequence 
 back to a value in the high 500s. I then restarted the code 
 and the exception no longer occurred. 
 
 I am unable to explain how the sequence seq_admin_groups_id 
 went from in the 500s and working fine to suddenly being 68. 
 
 The only place in the Python code where the sequence is used 
 explicitly is in the definition of the AdminGroup() class. 
 
 I would be grateful for any insights on how the sequence might 
 have become out of sync or anything I can change in the code 
 to prevent it from happening again. 

there’s nothing on the Python library side that could do that, someone had to 
have run an ALTER SEQUENCE on the database side in order for that to happen.

-- 
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 a topic in the Google 
Groups sqlalchemy group.
To unsubscribe from this topic, visit 
https://groups.google.com/d/topic/sqlalchemy/4iAInBP6iyI/unsubscribe.
To unsubscribe from this group and all its topics, 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.

[sqlalchemy] Is there a reason why there is no dialect specific fix for ORA-01795?

[Ofir Herzas]

ORA-01795: maximum number of expressions in a list is 1000

As I understand, there are several options to fix this issue
(e.g. 
https://groups.google.com/forum/#!searchin/sqlalchemy/maximum$20number$20of$20expressions/sqlalchemy/Oa6YWNE0-IQ/6GVmy27B-FAJ
 
or split in_ to several or_)

Why not incorporate this fix into sqlalchemy?

-- 
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] Is there a reason why there is no dialect specific fix for ORA-01795?

[Ofir Herzas]

This can be done only for Oracle and only if the number of expressions is 
higher than 1000 (which would otherwise produce an exception)
Regarding complex queries, I guess the several or_'s fix should work: 
or_(x.in_(1...1000), x.in_(1001...2000))

How can this be done locally? (override in_ operator only for Oracle)

Thanks,
Ofir


On Tuesday, June 17, 2014 4:18:11 PM UTC+3, Michael Bayer wrote:


 On 6/17/14, 4:32 AM, Ofir Herzas wrote: 
  ORA-01795: maximum number of expressions in a list is 1000 
  
  As I understand, there are several options to fix this issue 
  (e.g. 
  
 https://groups.google.com/forum/#!searchin/sqlalchemy/maximum$20number$20of$20expressions/sqlalchemy/Oa6YWNE0-IQ/6GVmy27B-FAJ
  
  or split in_ to several or_) 
  
  Why not incorporate this fix into sqlalchemy? 
 SQLAlchemy's core SQL model is one-to-one with SQL.  Taking a single 
 statement and magically executing ten statements within the scope of an 
 execute() due to a particular operator being present would be very 
 inappropriate and surprising, and also wouldn't work for any queries 
 that are more complex than a simple WHERE x IN y. 





-- 
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] Query for date between a range

[Ofir Herzas]

You can create a custom field in your model and check against it:

class Plan(Base):
.
.
.
@property
def calculated_date(self):
return date(self.year, self.month, self.day)


Then, in your query, use that field:
session.query(Plan).filter(Plan.calculated_date.between(from_date, to_date))

Haven't checked it myself, but I guess it should work ...

-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
Behalf Of Enrico Morelli
Sent: Friday, October 04, 2013 12:07 PM
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Query for date between a range

Dear all,

I've a table where the date is separated in single fields, one for year, one
for day and one for month. So I need to query for a date range. I search in
Internet and I found the following query that seems to be works:
SELECT *
FROM plan
WHERE year * 1 + month * 100 + day BETWEEN +'20130101' AND '20130131';

Now I'm trying to translate to sqlalchemy, but I receive the following
error:

DataError: (DataError) invalid input syntax for integer:
2013-01-01T00:00:00 LINE 3: ...year * 1000 + plan.month * 100 + plan.day
BETWEEN '2013-01-0... ^ 'SELECT plan.data AS plan_data, plan.month AS
plan_month, plan.instrument_id AS plan_instrument_id,
count(plan.instrument_id) AS count_1 \nFROM plan \nWHERE plan.year *
%(year_1)s + plan.month * %(month_1)s + plan.day BETWEEN %(param_1)s AND
%(param_2)s AND plan.data ILIKE %(data_1)s GROUP BY plan.data, plan.month,
plan.instrument_id ORDER BY month, instrument_id' {'data_1': u'%#L%',
'param_1': datetime.datetime(2013, 1, 1, 0, 0), 'month_1': 100, 'year_1':
1000, 'param_2':
datetime.datetime(2013, 10, 3, 0, 0)}


The latest attempt to write the correct code is the following (the range
come from a web form using a javascript plugin):

from_date = request.POST.get('from_date', '') to_date =
request.POST.get('to_date', '') from_date = datetime.strptime(from_date,
'%Y-%m-%d') to_date = datetime.strptime(to_date, '%Y-%m-%d') if
from_date.day  10:
   day = 0%s % from_date.day
else:
   day = %s % from_date.day
if from_date.month  10:
   month = 0%s % from_date.month
else:
   month = %s % from_date.month
if to_date.day  10:
   tday = 0%s % to_date.day
else:
   tday = %s % to_date.day
if to_date.month  10:
   tmonth = 0%s % to_date.month
else:
   tmonth = %s % to_date.month
fd = '%s%s%s' % (from_date.year, month, day) td = '%s%s%s' % (to_date.year,
tmonth, tday) print fd, td results = Session.query(Plan.data,
Plan.month,Plan.instrument_id,func.count(Plan.instrument_id)).filter(
and_((Plan.year*1000+Plan.month*100+Plan.day).between(from_date,
to_date),  Plan.data.ilike('%%%s%%' % item),)).group_by(
Plan.data, Plan.month,
Plan.instrument_id ).order_by('month', 'instrument_id').all()

Where I'm wrong?

Thanks to all
--
-
  Enrico Morelli
  System Administrator | Programmer | Web Developer

  CERM - Polo Scientifico
  Via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY
  phone: +39 055 457 4269
  fax:   +39 055 457 4253
-

--
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/groups/opt_out.

-- 
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/groups/opt_out.

RE: [sqlalchemy] Query for date between a range

[Ofir Herzas]

I'm sorry, you should use hybrid_property:

from sqlalchemy.ext.hybrid import hybrid_property

class Plan(Base):
@hybrid_property
def calculated_date(self):
return date(self.year, self.month, self.day)


Also, in your query, don't use between:
session.query(Plan).\
filter(Plan.calculated_date = from_date).\
filter(Plan.calculated_date = to_date))

Cheers,
Ofir

-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
Behalf Of Enrico Morelli
Sent: Friday, October 04, 2013 4:05 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] Query for date between a range

On Fri, 4 Oct 2013 15:55:07 +0300
Ofir Herzas herz...@gmail.com wrote:

 You can create a custom field in your model and check against it:
 
 class Plan(Base):
 .
 .
 .
 @property
 def calculated_date(self):
 return date(self.year, self.month, self.day)
 
 
 Then, in your query, use that field:
 session.query(Plan).filter(Plan.calculated_date.between(from_date,
 to_date))
 
 Haven't checked it myself, but I guess it should work ...

Thanks, but now I receive the error:

AttributeError: 'property' object has no attribute 'between'

 
 -Original Message-
 From: sqlalchemy@googlegroups.com
 [mailto:sqlalchemy@googlegroups.com] On Behalf Of Enrico Morelli
 Sent: Friday, October 04, 2013 12:07 PM
 To: sqlalchemy@googlegroups.com
 Subject: [sqlalchemy] Query for date between a range
 
 Dear all,
 
 I've a table where the date is separated in single fields, one for 
 year, one for day and one for month. So I need to query for a date 
 range. I search in Internet and I found the following query that seems 
 to be works: SELECT * FROM plan WHERE year * 1 + month * 100 + day 
 BETWEEN +'20130101' AND '20130131';
 
 Now I'm trying to translate to sqlalchemy, but I receive the following
 error:
 
 DataError: (DataError) invalid input syntax for integer:
 2013-01-01T00:00:00 LINE 3: ...year * 1000 + plan.month * 100 + 
 plan.day BETWEEN '2013-01-0... ^ 'SELECT plan.data AS plan_data, 
 plan.month AS plan_month, plan.instrument_id AS plan_instrument_id,
 count(plan.instrument_id) AS count_1 \nFROM plan \nWHERE plan.year * 
 %(year_1)s + plan.month * %(month_1)s + plan.day BETWEEN %(param_1)s 
 AND %(param_2)s AND plan.data ILIKE %(data_1)s GROUP BY plan.data, 
 plan.month, plan.instrument_id ORDER BY month, instrument_id' 
 {'data_1': u'%#L%', 'param_1': datetime.datetime(2013, 1, 1, 0, 0), 
 'month_1': 100, 'year_1': 1000, 'param_2':
 datetime.datetime(2013, 10, 3, 0, 0)}
 
 
 The latest attempt to write the correct code is the following (the 
 range come from a web form using a javascript plugin):
 
 from_date = request.POST.get('from_date', '') to_date = 
 request.POST.get('to_date', '') from_date = 
 datetime.strptime(from_date, '%Y-%m-%d') to_date = 
 datetime.strptime(to_date, '%Y-%m-%d') if from_date.day  10:
day = 0%s % from_date.day
 else:
day = %s % from_date.day
 if from_date.month  10:
month = 0%s % from_date.month
 else:
month = %s % from_date.month
 if to_date.day  10:
tday = 0%s % to_date.day
 else:
tday = %s % to_date.day
 if to_date.month  10:
tmonth = 0%s % to_date.month
 else:
tmonth = %s % to_date.month
 fd = '%s%s%s' % (from_date.year, month, day) td = '%s%s%s' % 
 (to_date.year, tmonth, tday) print fd, td results = 
 Session.query(Plan.data, 
 Plan.month,Plan.instrument_id,func.count(Plan.instrument_id)).filter( 
 and_((Plan.year*1000+Plan.month*100+Plan.day).between(from_date,
 to_date),  Plan.data.ilike('%%%s%%' % item),)).group_by(
 Plan.data, Plan.month, 
 Plan.instrument_id ).order_by('month', 'instrument_id').all()
 
 Where I'm wrong?
 
 Thanks to all
 --
 -
   Enrico Morelli
   System Administrator | Programmer | Web Developer
 
   CERM - Polo Scientifico
   Via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY
   phone: +39 055 457 4269
   fax:   +39 055 457 4253
 -
 
 --
 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/groups/opt_out.
 


--
-
  Enrico Morelli
  System Administrator | Programmer | Web Developer

  CERM - Polo Scientifico
  Via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY
  phone: +39 055 457 4269
  fax:   +39 055 457 4253
-

--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe

RE: [sqlalchemy] Query for date between a range

[Ofir Herzas]

Enrico, It should be available on 0.7.10

Simon, you are right. The expression is indeed a must.

class Plan(Base):
@hybrid_property
def calculated_date(self):
return date(self.year, self.month, self.day)

@calculated_date.expression
def calculated_date(self):
return sa.cast(self.year + '-' + self.month + '-' + self.day,
sa.Date)


-Original Message-
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On
Behalf Of Simon King
Sent: Friday, October 04, 2013 7:01 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] Query for date between a range

I'm not sure that will work on it's own, will it? When used in a class
context (Plan.calculated_date), you will end up calling the date function
with 3 SQLAlchemy column objects, which won't work.

At a minimum, you'd need this:

class Plan(Base):
@hybrid_property
def calculated_date(self):
return date(self.year, self.month, self.day)

@calculated_date.expression
def calculated_date(cls):
# I suspect that what you put in here depends on the database
return sa.cast(cls.year + '-' + cls.month + '-' + cls.day, sa.Date)

On Fri, Oct 4, 2013 at 2:25 PM, Ofir Herzas herz...@gmail.com wrote:
 I'm sorry, you should use hybrid_property:

 from sqlalchemy.ext.hybrid import hybrid_property

 class Plan(Base):
 @hybrid_property
 def calculated_date(self):
 return date(self.year, self.month, self.day)


 Also, in your query, don't use between:
 session.query(Plan).\
 filter(Plan.calculated_date = from_date).\
 filter(Plan.calculated_date = to_date))

 Cheers,
 Ofir

 -Original Message-
 From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] 
 On Behalf Of Enrico Morelli
 Sent: Friday, October 04, 2013 4:05 PM
 To: sqlalchemy@googlegroups.com
 Subject: Re: [sqlalchemy] Query for date between a range

 On Fri, 4 Oct 2013 15:55:07 +0300
 Ofir Herzas herz...@gmail.com wrote:

 You can create a custom field in your model and check against it:

 class Plan(Base):
 .
 .
 .
 @property
 def calculated_date(self):
 return date(self.year, self.month, self.day)


 Then, in your query, use that field:
 session.query(Plan).filter(Plan.calculated_date.between(from_date,
 to_date))

 Haven't checked it myself, but I guess it should work ...

 Thanks, but now I receive the error:

 AttributeError: 'property' object has no attribute 'between'


 -Original Message-
 From: sqlalchemy@googlegroups.com
 [mailto:sqlalchemy@googlegroups.com] On Behalf Of Enrico Morelli
 Sent: Friday, October 04, 2013 12:07 PM
 To: sqlalchemy@googlegroups.com
 Subject: [sqlalchemy] Query for date between a range

 Dear all,

 I've a table where the date is separated in single fields, one for 
 year, one for day and one for month. So I need to query for a date 
 range. I search in Internet and I found the following query that 
 seems to be works: SELECT * FROM plan WHERE year * 1 + month * 
 100 + day BETWEEN +'20130101' AND '20130131';

 Now I'm trying to translate to sqlalchemy, but I receive the 
 following
 error:

 DataError: (DataError) invalid input syntax for integer:
 2013-01-01T00:00:00 LINE 3: ...year * 1000 + plan.month * 100 + 
 plan.day BETWEEN '2013-01-0... ^ 'SELECT plan.data AS plan_data, 
 plan.month AS plan_month, plan.instrument_id AS plan_instrument_id,
 count(plan.instrument_id) AS count_1 \nFROM plan \nWHERE plan.year * 
 %(year_1)s + plan.month * %(month_1)s + plan.day BETWEEN %(param_1)s 
 AND %(param_2)s AND plan.data ILIKE %(data_1)s GROUP BY plan.data, 
 plan.month, plan.instrument_id ORDER BY month, instrument_id'
 {'data_1': u'%#L%', 'param_1': datetime.datetime(2013, 1, 1, 0, 0),
 'month_1': 100, 'year_1': 1000, 'param_2':
 datetime.datetime(2013, 10, 3, 0, 0)}


 The latest attempt to write the correct code is the following (the 
 range come from a web form using a javascript plugin):

 from_date = request.POST.get('from_date', '') to_date = 
 request.POST.get('to_date', '') from_date = 
 datetime.strptime(from_date, '%Y-%m-%d') to_date = 
 datetime.strptime(to_date, '%Y-%m-%d') if from_date.day  10:
day = 0%s % from_date.day
 else:
day = %s % from_date.day
 if from_date.month  10:
month = 0%s % from_date.month
 else:
month = %s % from_date.month
 if to_date.day  10:
tday = 0%s % to_date.day
 else:
tday = %s % to_date.day
 if to_date.month  10:
tmonth = 0%s % to_date.month
 else:
tmonth = %s % to_date.month
 fd = '%s%s%s' % (from_date.year, month, day) td = '%s%s%s' % 
 (to_date.year, tmonth, tday) print fd, td results = 
 Session.query(Plan.data, 
 Plan.month,Plan.instrument_id,func.count(Plan.instrument_id)).filter(
 and_((Plan.year*1000+Plan.month*100+Plan.day).between(from_date,
 to_date),  Plan.data.ilike('%%%s%%' % item),)).group_by(
 Plan.data, Plan.month, 
 Plan.instrument_id ).order_by('month

Re: [sqlalchemy] Re: Sqlalchemy sintax for a query with not like operator

[Ofir Herzas]

Hi Luca,
Keep in mind that contains will not map to 'like'. Hence if you want your
query to have a 'like' clause, you'll have to use the 'like' method (the
end result of 'contains' will be the same though as 'like' beginning and
ending with a percent sign).
 On Sep 27, 2013 5:06 PM, pyArchInit ArcheoImagineers 
pyarchi...@gmail.com wrote:

 Thanks a lot...sorry for my prevous post that I delete beacause I didn't
 undestand I must to use this  ~

 def test_not_like(self):
 Session = sessionmaker(bind=self.engine, autoflush=True, autocommit=True)
 session = Session()
 res = session.query(MAPPER).filter(and_(~MAPPER.field.contains('Value1'),
 ~US.field.contains('Value2'),
 ~US.field.contains('Value3'),~US.field.contains('Value4')))

 return res

 This appear to run properly now I test with my little dataset and I'll try
 to understand is what I must to find.

 Thank you very much and sorry for the unnecessary my old replies.
 Bye
 Luca

 --
 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/groups/opt_out.


-- 
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/groups/opt_out.

Re: [sqlalchemy] Sqlalchemy sintax for a query with not like operator

[Ofir Herzas]

Each column has a  'like' method, so you should be able to filter by it:
filter(~Table.field.like(%value1%))
Hi to all,
I need to realize a query like this

select * from my_table where field not like %value1%  and field not like
%value2%

with a sqlalchemy sintax. I looked for around the documentation but it
seems be impossible.
Am I wrong? It's possible that the only way could be to use the
engine.execute method?

Thanks for any suggestion

Best regards
Luca

-- 
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/groups/opt_out.

-- 
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/groups/opt_out.

Re: [sqlalchemy] query question

[Ofir Herzas]

Haven't tried it myself, but I think you can use the add_columns method of
Query.

Check out http://docs.sqlalchemy.org/ru/latest/orm/query.html
 On 4 Sep, 2013 2:05 PM, lars van gemerden l...@rational-it.com wrote:

 I  think i must be reading over something, but:

 is there a way to delay the selection of attributes in a query; something
 like

   session.query(Person).filter(Person.age 
 100).select(Person.name).first()

(uAncient Bob,)

 Cheers, Lars

 --
 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/groups/opt_out.


-- 
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/groups/opt_out.

Re: [sqlalchemy] Calculate birthdays

[Ofir Herzas]

What's wrong with Member.dateofbirth==datetime.today() ?
On 28 Aug, 2013 7:47 PM, sjo...@congressus.nl wrote:

 Hi,

 I want to retrieve all the people who are born at today's date. I'm using 
 Flask with sqlalchemy;

 class Member(db.Model):

 ...

 dateofbirth = Column(Date)
 ...


 In my view.py;

 from datetime import date, timedelta

 today = date.today()

 members_today = 
 Member.query.filter(Member.dateofbirth.replace(year=today.year) == 
 today).all()


 But I receive this error;

 AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator'
 object associated with Member.dateofbirth has an attribute 'replace'


 Anyone has an idea?

 Thanks



 --
 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/groups/opt_out.


-- 
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/groups/opt_out.