Re: [sqlalchemy] creating indexes from inherited fields

2015-06-02 Thread Mike Bayer



On 6/2/15 12:59 PM, Richard Gerd Kuesters wrote:

thanks again Mike!

almost there. the problem now are inherited tables ...

taken from my example code, let's say I have a table that inherits 
SomeOtherClass; then, the error is something like:


sqlalchemy.exc.ArgumentError: Index 'ix_adapter_created_on' is against 
table 'adapter', and cannot be associated with table 'http_adapter'.


in the example above, http_error inherits from adapter.


take a look at using has_inherited_table(), the section in 
http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/declarative/mixins.html#controlling-table-inheritance-with-mixins 
has clues






best regards,
richard.

On 06/02/2015 01:45 PM, Mike Bayer wrote:



On 6/2/15 12:05 PM, Richard Gerd Kuesters wrote:
oh, sorry Mike, I forgot to mention that I use TimestampMixin in 
other classes aswell, so I got an existent index error (something 
like that). Is there a way I don't need to declare the index name? 
(IMHO this appears to be the problem).


indexes have to be named something so you'd have to figure out a 
naming convention based on tables/column names.   There should be an 
existing convention for indexes so if you set None as the name it 
should make one up for you like ix_tablename_columnname




thanks a lot!
richard.

On 06/02/2015 12:28 PM, Mike Bayer wrote:



On 6/2/15 10:16 AM, Richard Gerd Kuesters wrote:

hi all, again :)

how can I create an index in fields inherited by other classes?

example:


class TimestampMixin(object):
updated_on = Column(DateTime)  # i wanted to create three 
indexes in this field, updated_on, updated_on.asc() and 
updated_on.desc()



class SomeOtherClass(Base, TimestampMixin):
__tablename__ = 'some_table'
id = Column(Integer)



Index('ix_some_other_class_01', SomeOtherClass.updated_on)
Index('ix_some_other_class_02', SomeOtherClass.updated_on.asc())
Index('ix_some_other_class_03', SomeOtherClass.updated_on.desc())


I just want to avoid repetition, if possible :)


you could use a __table_args__() callable with @declared_attr.  Or 
use a mapping event like __declare_first__() or __declare_last__() 
(these are hooked into the before_configured / after_configured 
events which you could also use directly, e.g. 
http://docs.sqlalchemy.org/en/rel_1_0/orm/events.html?highlight=before_configure#sqlalchemy.orm.events.MapperEvents.before_configured)








best regards,
richard.
--
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 
mailto: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 
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com 
mailto: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 
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com 
mailto: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 
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com 
mailto: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 
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com 
mailto: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 

Re: [sqlalchemy] creating indexes from inherited fields

2015-06-02 Thread Richard Gerd Kuesters

thanks a lot, Mike!

good catch of yours. the answer was in front of me all the time and i 
couldn't see it ... my mistake, thanks for pointing the right direction ;)



best regards,
richard.


On 06/02/2015 03:17 PM, Mike Bayer wrote:



On 6/2/15 12:59 PM, Richard Gerd Kuesters wrote:

thanks again Mike!

almost there. the problem now are inherited tables ...

taken from my example code, let's say I have a table that inherits 
SomeOtherClass; then, the error is something like:


sqlalchemy.exc.ArgumentError: Index 'ix_adapter_created_on' is 
against table 'adapter', and cannot be associated with table 
'http_adapter'.


in the example above, http_error inherits from adapter.


take a look at using has_inherited_table(), the section in 
http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/declarative/mixins.html#controlling-table-inheritance-with-mixins 
has clues






best regards,
richard.

On 06/02/2015 01:45 PM, Mike Bayer wrote:



On 6/2/15 12:05 PM, Richard Gerd Kuesters wrote:
oh, sorry Mike, I forgot to mention that I use TimestampMixin in 
other classes aswell, so I got an existent index error (something 
like that). Is there a way I don't need to declare the index name? 
(IMHO this appears to be the problem).


indexes have to be named something so you'd have to figure out a 
naming convention based on tables/column names.   There should be an 
existing convention for indexes so if you set None as the name it 
should make one up for you like ix_tablename_columnname




thanks a lot!
richard.

On 06/02/2015 12:28 PM, Mike Bayer wrote:



On 6/2/15 10:16 AM, Richard Gerd Kuesters wrote:

hi all, again :)

how can I create an index in fields inherited by other classes?

example:


class TimestampMixin(object):
updated_on = Column(DateTime)  # i wanted to create three 
indexes in this field, updated_on, updated_on.asc() and 
updated_on.desc()



class SomeOtherClass(Base, TimestampMixin):
__tablename__ = 'some_table'
id = Column(Integer)



Index('ix_some_other_class_01', SomeOtherClass.updated_on)
Index('ix_some_other_class_02', SomeOtherClass.updated_on.asc())
Index('ix_some_other_class_03', SomeOtherClass.updated_on.desc())


I just want to avoid repetition, if possible :)


you could use a __table_args__() callable with @declared_attr.  Or 
use a mapping event like __declare_first__() or __declare_last__() 
(these are hooked into the before_configured / after_configured 
events which you could also use directly, e.g. 
http://docs.sqlalchemy.org/en/rel_1_0/orm/events.html?highlight=before_configure#sqlalchemy.orm.events.MapperEvents.before_configured)








best regards,
richard.
--
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 
mailto: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 
mailto: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 
mailto: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 
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com 
mailto: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 
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com 
mailto:sqlalchemy@googlegroups.com.

Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, 

[sqlalchemy] Re: getting 'No database selected' randomly from session

2015-06-02 Thread eli rashlin
BTW is there a way in sqlalchemy to append the name of the database to the 
table name 
like SELECT * from dbname.table_name

-- 
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] creating indexes from inherited fields

2015-06-02 Thread Richard Gerd Kuesters

hi all, again :)

how can I create an index in fields inherited by other classes?

example:


class TimestampMixin(object):
updated_on = Column(DateTime)  # i wanted to create three indexes 
in this field, updated_on, updated_on.asc() and updated_on.desc()



class SomeOtherClass(Base, TimestampMixin):
__tablename__ = 'some_table'
id = Column(Integer)



Index('ix_some_other_class_01', SomeOtherClass.updated_on)
Index('ix_some_other_class_02', SomeOtherClass.updated_on.asc())
Index('ix_some_other_class_03', SomeOtherClass.updated_on.desc())


I just want to avoid repetition, if possible :)


best regards,
richard.

--
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.
attachment: richard.vcf

[sqlalchemy] getting 'No database selected' randomly from session

2015-06-02 Thread eli rashlin
Hi,

I have a very strange behavior, I have a program that uses bulk insertions 
to the DB.
for some reason the process which iterate on one table and create bulk and 
insert into another table fails with the error of  'No database selected'.


this is how I bind the session:

#---
# use_db: 
#this method will return a session reference
# using this DB
#---


def use_db(self, new_db_name):
self.connection.execute(USE %s % (new_db_name))
session_ref = sessionmaker(bind = self.connection, 
expire_on_commit=False)
self.session_cover_tst = session_ref()
return self.session_cover_tst



engine = connection_engine.use_db(db_name)
count_sig = 
engine.query(func.count(distinct(signals_table.Signals.sig_value)).label('count_sig')).\

 filter(signals_table.Signals.message_id == msg_row.id).\

 filter(signals_table.Signals.signal_id == sig_id).\

 group_by(signals_table.Signals.signal_id).\

 one()[0]


now the table Signals is huge (500M records), but the table is indexed and 
everything is working great for a few rounds and then I'm getting the 
error  'No database selected'  and it fails...

-- 
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] getting 'No database selected' randomly from session

2015-06-02 Thread Mike Bayer



On 6/2/15 5:52 AM, eli rashlin wrote:

Hi,

I have a very strange behavior, I have a program that uses bulk 
insertions to the DB.
for some reason the process which iterate on one table and create bulk 
and insert into another table fails with the error of  'No database 
selected'.



this is how I bind the session:

#---
# use_db:
#this method will return a session reference
# using this DB
#--- 


def use_db(self, new_db_name):
self.connection.execute(USE %s % (new_db_name))
session_ref = sessionmaker(bind = self.connection, 
expire_on_commit=False)

self.session_cover_tst = session_ref()
return self.session_cover_tst



engine = connection_engine.use_db(db_name)
count_sig = 
engine.query(func.count(distinct(signals_table.Signals.sig_value)).label('count_sig')).\

 filter(signals_table.Signals.message_id == msg_row.id).\
 filter(signals_table.Signals.signal_id == sig_id).\
 group_by(signals_table.Signals.signal_id).\
 one()[0]


now the table Signals is huge (500M records), but the table is indexed 
and everything is working great for a few rounds and then I'm getting 
the error  'No database selected'  and it fails...


it sounds like at some point your program is using self.connection 
without emitting that USE statement, or you have some kind of routine 
that is undoing the USE and perhaps multiple callers and/or threads 
are accessing self.connection at the same time.


--
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: getting 'No database selected' randomly from session

2015-06-02 Thread Mike Bayer



On 6/2/15 5:54 AM, eli rashlin wrote:
BTW is there a way in sqlalchemy to append the name of the database to 
the table name

like SELECT * from dbname.table_name
this is the schema argument documented at 
http://docs.sqlalchemy.org/en/rel_1_0/core/metadata.html?highlight=schema#sqlalchemy.schema.Table.params.schema.








--
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 
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com 
mailto: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] creating indexes from inherited fields

2015-06-02 Thread Richard Gerd Kuesters
oh, sorry Mike, I forgot to mention that I use TimestampMixin in other 
classes aswell, so I got an existent index error (something like 
that). Is there a way I don't need to declare the index name? (IMHO this 
appears to be the problem).


thanks a lot!
richard.

On 06/02/2015 12:28 PM, Mike Bayer wrote:



On 6/2/15 10:16 AM, Richard Gerd Kuesters wrote:

hi all, again :)

how can I create an index in fields inherited by other classes?

example:


class TimestampMixin(object):
updated_on = Column(DateTime)  # i wanted to create three indexes 
in this field, updated_on, updated_on.asc() and updated_on.desc()



class SomeOtherClass(Base, TimestampMixin):
__tablename__ = 'some_table'
id = Column(Integer)



Index('ix_some_other_class_01', SomeOtherClass.updated_on)
Index('ix_some_other_class_02', SomeOtherClass.updated_on.asc())
Index('ix_some_other_class_03', SomeOtherClass.updated_on.desc())


I just want to avoid repetition, if possible :)


you could use a __table_args__() callable with @declared_attr.  Or use 
a mapping event like __declare_first__() or __declare_last__() (these 
are hooked into the before_configured / after_configured events which 
you could also use directly, e.g. 
http://docs.sqlalchemy.org/en/rel_1_0/orm/events.html?highlight=before_configure#sqlalchemy.orm.events.MapperEvents.before_configured)








best regards,
richard.
--
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 
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com 
mailto: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 
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com 
mailto: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.
attachment: richard.vcf

[sqlalchemy] querying Class Inheritance Hierarchies

2015-06-02 Thread g
Hi all 

Is there a way to get the classname  of the polymorphic class result  in a 
single query on the base class ?

soemthing like this

session.query(cast(Employee.__mapper__.polymorphic_map[Employee.type].class_.__name__,
 String)).filter(Employee.name=='Employee_Name').one()



*Details *

*===*
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import aliased
from sqlalchemy.ext.hybrid import hybrid_property

Base = declarative_base()

class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String(50))
type = Column(String(50))
__mapper_args__ = {
'polymorphic_identity':'employee',
'polymorphic_on':type
}

class Engineer(Employee):
__tablename__ = 'engineer'
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
engineer_name = Column(String(30))

__mapper_args__ = {
'polymorphic_identity':'engineer',
}

class Manager(Employee):
__tablename__ = 'manager'
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
manager_name = Column(String(30))

__mapper_args__ = {
'polymorphic_identity':'manager',
}
e = create_engine('postgresql+psycopg2://:@host/test', echo=False)
Base.metadata.create_all(e)

session = Session(e)

*WITH THIS DATA*
*===*

eng = Engineer(name= 'Employee_Name', engineer_name= 'Engineer_name')
session.add(eng)
session.commit()

*SIMPLE QUERY *
*==*
empl = session.query(Employee).filter(Employee.name=='Employee_Name').one()
print empl  __main__.Engineer at 0x6573c50

BUT WHAT I WANT IS ONLY THE CLASS NAME OF POLYMORPHIC CLASS SO I TRIED THIS


employee = 
session.query(cast(Employee.__mapper__.polymorphic_map['employee'].class_.__name__,
 
String)).filter(Employee.name=='Employee_Name').one() print(employee) = 
(u'Employee',)

and with 

e = 
session.query(cast(Employee.__mapper__.polymorphic_map['engineer'].class_.__name__,
 
String)).filter(Employee.name=='Employee_Name').one()
print(e)

AND finally to generalize i tried:
e = 
session.query(cast(Employee.__mapper__.polymorphic_map[Employee.type].class_.__name__,
 
String)).filter(Employee.name=='Employee_Name').one() print(e)

---KeyError
  Traceback (most recent call 
last)ipython-input-31-45f6c96837bf in module()  1 #generalize 2 e 
= 
session.query(cast(Employee.__mapper__.polymorphic_map[Employee.type].class_.__name__,
 String)).filter(Employee.name=='EN').one()  3 print(e)
KeyError: sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x0654FD80


I think i am doing  something logically wrong .

QUESTION



Is there a way to get the classname  of the polymorphic class result in a 
single query ?

I tried with hybrid_property but i had the same error


Some hints  ? 


Regards

G










-- 
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] creating indexes from inherited fields

2015-06-02 Thread Richard Gerd Kuesters

thanks again Mike!

almost there. the problem now are inherited tables ...

taken from my example code, let's say I have a table that inherits 
SomeOtherClass; then, the error is something like:


sqlalchemy.exc.ArgumentError: Index 'ix_adapter_created_on' is against 
table 'adapter', and cannot be associated with table 'http_adapter'.


in the example above, http_error inherits from adapter.


best regards,
richard.

On 06/02/2015 01:45 PM, Mike Bayer wrote:



On 6/2/15 12:05 PM, Richard Gerd Kuesters wrote:
oh, sorry Mike, I forgot to mention that I use TimestampMixin in 
other classes aswell, so I got an existent index error (something 
like that). Is there a way I don't need to declare the index name? 
(IMHO this appears to be the problem).


indexes have to be named something so you'd have to figure out a 
naming convention based on tables/column names.   There should be an 
existing convention for indexes so if you set None as the name it 
should make one up for you like ix_tablename_columnname




thanks a lot!
richard.

On 06/02/2015 12:28 PM, Mike Bayer wrote:



On 6/2/15 10:16 AM, Richard Gerd Kuesters wrote:

hi all, again :)

how can I create an index in fields inherited by other classes?

example:


class TimestampMixin(object):
updated_on = Column(DateTime)  # i wanted to create three 
indexes in this field, updated_on, updated_on.asc() and 
updated_on.desc()



class SomeOtherClass(Base, TimestampMixin):
__tablename__ = 'some_table'
id = Column(Integer)



Index('ix_some_other_class_01', SomeOtherClass.updated_on)
Index('ix_some_other_class_02', SomeOtherClass.updated_on.asc())
Index('ix_some_other_class_03', SomeOtherClass.updated_on.desc())


I just want to avoid repetition, if possible :)


you could use a __table_args__() callable with @declared_attr.  Or 
use a mapping event like __declare_first__() or __declare_last__() 
(these are hooked into the before_configured / after_configured 
events which you could also use directly, e.g. 
http://docs.sqlalchemy.org/en/rel_1_0/orm/events.html?highlight=before_configure#sqlalchemy.orm.events.MapperEvents.before_configured)








best regards,
richard.
--
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 
mailto: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 
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com 
mailto: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 
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com 
mailto: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 
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com 
mailto: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.
attachment: richard.vcf

Re: [sqlalchemy] querying Class Inheritance Hierarchies

2015-06-02 Thread Mike Bayer



On 6/2/15 12:30 PM, g wrote:

Hi all

Is there a way to get the classname  of thepolymorphic class result in a 
single query on the base class ?
soemthing like this


well that's lookup / conditional logic, I'd just do it as an in-Python 
filter on the results you get:


for result in query:
classname = get_the_classname(result.type)

if that is unappealing, then you'd need to build a conditional 
expression in SQL using CASE: 
http://docs.sqlalchemy.org/en/rel_1_0/core/sqlelement.html?highlight=case#sqlalchemy.sql.expression.case


You can build out this case construct dynamically using the 
keys/values in the polymorphic_map.It would just be a chunky SQL 
expression.




session.query(cast(Employee.__mapper__.polymorphic_map[Employee.type].class_.__name__, 
String)).filter(Employee.name=='Employee_Name').one()

*Details *
*===*
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import aliased
from sqlalchemy.ext.hybrid import hybrid_property

Base = declarative_base()

class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String(50))
type = Column(String(50))
__mapper_args__ = {
'polymorphic_identity':'employee',
'polymorphic_on':type
}
class Engineer(Employee):
__tablename__ = 'engineer'
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
engineer_name = Column(String(30))

__mapper_args__ = {
'polymorphic_identity':'engineer',
}

class Manager(Employee):
__tablename__ = 'manager'
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
manager_name = Column(String(30))

__mapper_args__ = {
'polymorphic_identity':'manager',
}
e = create_engine('postgresql+psycopg2://:@host/test', echo=False)
Base.metadata.create_all(e)

session = Session(e)

*WITH THIS DATA*
*===*

eng = Engineer(name= 'Employee_Name', engineer_name= 'Engineer_name')
session.add(eng)
session.commit()

*SIMPLE QUERY *
*==*
empl = 
session.query(Employee).filter(Employee.name=='Employee_Name').one()

print empl __main__.Engineer at 0x6573c50
BUT WHAT I WANT IS ONLY THE CLASS NAME OF POLYMORPHIC CLASS SO I TRIED 
THIS
employee = 
session.query(cast(Employee.__mapper__.polymorphic_map['employee'].class_.__name__, 
String)).filter(Employee.name=='Employee_Name').one() print(employee) 
= (u'Employee',)

and with
e = 
session.query(cast(Employee.__mapper__.polymorphic_map['engineer'].class_.__name__, 
String)).filter(Employee.name=='Employee_Name').one()

print(e)
AND finally to generalize i tried:
e = 
session.query(cast(Employee.__mapper__.polymorphic_map[Employee.type].class_.__name__, 
String)).filter(Employee.name=='Employee_Name').one() print(e)


---
KeyError   Traceback (most recent call last)
ipython-input-31-45f6c96837bf  inmodule() 1 #generalize 2e = 
session.query(cast(Employee.__mapper__.polymorphic_map[Employee.type].class_.__name__, 
String)).filter(Employee.name=='EN').one()3 print(e)KeyError: 
sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x0654FD80

I think i am doing  something logically wrong .
QUESTION

Is there a way to get the classname  of thepolymorphic class result in a single 
query ?
I tried with hybrid_property but i had the same error
Some hints ?
Regards
G



--
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 
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com 
mailto: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.