date:20170424

Re: [sqlalchemy] eager loading relationships after an initial query?

2017-04-24 Thread Jonathan Vanasco


On Monday, April 24, 2017 at 4:28:22 PM UTC-4, Mike Bayer wrote:
>
> yeah just load the object again w/ the eagerloads option you want. 
>


Thanks.  I was hoping there was a way to just say `Obj.load('foo')` ?

I'll just untangle the code and load the relationship in the first place. 
 At this point in the code, I don't have a session handy; grabbing it from 
the object isn't preferred for maintenance/readability.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Grouped data in a Flask/SQLAlchemy class

2017-04-24 Thread Greg Silverman

Yes, I can certainly do that. Sounds pretty simple, actually. I may have
more questions as I dive into this.

Thanks!




On Mon, Apr 24, 2017 at 3:06 PM, mike bayer 
wrote:

>
>
> On 04/24/2017 03:42 PM, Greg Silverman wrote:
>
>> My naive first response would be that I want the class to look like
>>
>> class DiagnosisTest(Model):
>> __tablename__= 'vw_svc_diagnosis'
>> #id = Column(Integer, primary_key=True, autoincrement=True)
>> dx_id= Column(String(32),primary_key=True)
>>  first_name= Column(String(255))
>>  last_name= Column(String(255))
>>  mrn= Column(String(255))
>>  dx_code= Column(String(255))
>>  #dx_code_orig = Column(String(255))
>> dx_code_type= Column(String(255))
>>  dx_name= Column(String(255))
>>  #chronic_diagnosis_yn = Column(String(255))
>> diagnosis_datetime= Column(DateTime)
>>
>>
>>
>>  @property
>> def get_diagnosis_groups(self):
>>
>> import itertoolsas itertools
>>
>>  for (dx_code, patient_id), detailsin itertools.groupby(self,
>> keyfunc):
>> diagnosis_group  = DiagnosisGroup(
>>  dx_code, patient_id
>>  )
>>  diagnosis_group.details= details
>>  for detailin details:
>> detail.group= diagnosis_group
>>  yield diagnosis_group
>>
>>
>> So that I could then instantiate this and access grouped elements like
>> DiagnosisTest.get_diagnosis_groups.patient_id and
>> DiagnosisTest.get_diagnosis_groups.dx_code for example.
>>
>
>
> OK can you add a relationship() to DiagnosisTest that returns a list of
> DiagnosisDetail objects?  that would be all you need here.
>
>
>
>
>> I am sure this does not answer your question, but what I really want to
>> do is have the data grouped within the class and available whenever I
>> instantiate the class.
>>
>> I know what I need, but I am having problems in articulating the details.
>>
>> Thanks for being patient with me! ^_^
>>
>>
>> On Mon, Apr 24, 2017 at 12:21 PM, mike bayer > > wrote:
>>
>>
>>
>> On 04/24/2017 12:51 PM, Greg Silverman wrote:
>>
>> Hi Mike,
>> I'm finally getting to this.  Instead of having both a detail
>> and grouped methods, I would like to have only the grouped
>> method. I'm not sure I follow your suggestion for the two
>> methods above and how that would be modified to fit into the
>> @property in my class.
>>
>> Could you please elaborate a bit how these would work within
>> context of the class?
>>
>>
>> cutting and pasting:
>>
>>
>>def get_diagnosis_groups(sorted_list_of_diagnosis_detail):
>>
>>  for (dx_code, patient_id), details in
>> itertools.groupby(sorted_list_of_diagnosisdetail, keyfunc):
>>  diagnosis_group = DiagnosisGroup(
>> dx_code, patient_id
>>  )
>>  diagnosis_group.details = details
>>  for detail in details:
>>  detail.group = diagnosis_group
>>  yield diagnosis_group
>>
>>
>> you want to take out the "sorted_list_of_diagnosis_detail" argument.
>> OKbut I don't know where you'd like to get this from.  Where is
>> this "method" going and are you calling that as a classmethod or an
>> instance method?  what class?   Just show a usage example.
>>
>>
>>
>>
>>
>>
>>
>> Thanks!
>>
>> Greg--
>>
>>
>>
>>
>> On Wed, Mar 1, 2017 at 8:53 PM, mike bayer
>> 
>> >
>> >> wrote:
>>
>>
>>
>>  On 03/01/2017 08:27 PM, GMS wrote:
>>
>>  I have the following class models:
>>
>>
>>  |  class DiagnosisDetail(Model):
>>   __tablename__ = 'vw_svc_diagnosis'
>>   diagnosis_id = Column(String(32),
>> primary_key=True)
>>   first_name = Column(String(255))
>>   last_name = Column(String(255))
>>   mrn = Column(String(255))
>>   dx_code = Column(String(255))
>>   dx_id = Column(String(255),
>> ForeignKey('dx_group.dx_id'))
>>   diagnosisgroup = relationship("DiagnosisGroup")
>>   dx_code_type = Column(String(255))
>>   dx_name = Column(String(255))
>>
>>   __mapper_args__ = {
>>"order_by":[mrn, dx_name]
>>  }
>>
>>   class DiagnosisGroup(Model):
>>   __tablename__ = 'diagnosis_group'
>>   dx_id = Column(String(32), primary_key=True)
>>   mrn = Column(String(255))
>>

Re: [sqlalchemy] eager loading relationships after an initial query?

2017-04-24 Thread mike bayer


yeah just load the object again w/ the eagerloads option you want.

On 04/24/2017 04:12 PM, Jonathan Vanasco wrote:
[I couldn't find any docs on this, and my luck with the list archives 
only showed some potential work for this in 2008.]


Is it currently possible to eager-load a ORM relationship after a query?

For example, I have loaded an instance of `Foo`, but I did not do an 
eager load on `Foo.bars` and 'Foo.bars.widget`.  I'd like to instruct 
Foo to eagerload the `Foo.bars.widget`, instead of lazyloading them as 
iterated.



--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and 
Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
description.

---
You received this message because you are subscribed to the Google 
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to sqlalchemy+unsubscr...@googlegroups.com 
.
To post to this group, send email to sqlalchemy@googlegroups.com 
.

Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] eager loading relationships after an initial query?

2017-04-24 Thread Jonathan Vanasco

[I couldn't find any docs on this, and my luck with the list archives only 
showed some potential work for this in 2008.]

Is it currently possible to eager-load a ORM relationship after a query?

For example, I have loaded an instance of `Foo`, but I did not do an eager 
load on `Foo.bars` and 'Foo.bars.widget`.  I'd like to instruct Foo to 
eagerload the `Foo.bars.widget`, instead of lazyloading them as iterated.


-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Grouped data in a Flask/SQLAlchemy class

2017-04-24 Thread mike bayer




On 04/24/2017 03:42 PM, Greg Silverman wrote:

My naive first response would be that I want the class to look like

class DiagnosisTest(Model):
__tablename__= 'vw_svc_diagnosis'
#id = Column(Integer, primary_key=True, autoincrement=True)
dx_id= Column(String(32),primary_key=True)
 first_name= Column(String(255))
 last_name= Column(String(255))
 mrn= Column(String(255))
 dx_code= Column(String(255))
 #dx_code_orig = Column(String(255))
dx_code_type= Column(String(255))
 dx_name= Column(String(255))
 #chronic_diagnosis_yn = Column(String(255))
diagnosis_datetime= Column(DateTime)



 @property
def get_diagnosis_groups(self):

import itertoolsas itertools

 for (dx_code, patient_id), detailsin itertools.groupby(self, keyfunc):
diagnosis_group  = DiagnosisGroup(
 dx_code, patient_id
 )
 diagnosis_group.details= details
 for detailin details:
detail.group= diagnosis_group
 yield diagnosis_group


So that I could then instantiate this and access grouped elements like 
DiagnosisTest.get_diagnosis_groups.patient_id and 
DiagnosisTest.get_diagnosis_groups.dx_code for example.



OK can you add a relationship() to DiagnosisTest that returns a list of 
DiagnosisDetail objects?  that would be all you need here.






I am sure this does not answer your question, but what I really want to 
do is have the data grouped within the class and available whenever I 
instantiate the class.


I know what I need, but I am having problems in articulating the details.

Thanks for being patient with me! ^_^


On Mon, Apr 24, 2017 at 12:21 PM, mike bayer > wrote:




On 04/24/2017 12:51 PM, Greg Silverman wrote:

Hi Mike,
I'm finally getting to this.  Instead of having both a detail
and grouped methods, I would like to have only the grouped
method. I'm not sure I follow your suggestion for the two
methods above and how that would be modified to fit into the
@property in my class.

Could you please elaborate a bit how these would work within
context of the class?


cutting and pasting:


   def get_diagnosis_groups(sorted_list_of_diagnosis_detail):

 for (dx_code, patient_id), details in
itertools.groupby(sorted_list_of_diagnosisdetail, keyfunc):
 diagnosis_group = DiagnosisGroup(
dx_code, patient_id
 )
 diagnosis_group.details = details
 for detail in details:
 detail.group = diagnosis_group
 yield diagnosis_group


you want to take out the "sorted_list_of_diagnosis_detail" argument.
OKbut I don't know where you'd like to get this from.  Where is
this "method" going and are you calling that as a classmethod or an
instance method?  what class?   Just show a usage example.







Thanks!

Greg--




On Wed, Mar 1, 2017 at 8:53 PM, mike bayer

>> wrote:



 On 03/01/2017 08:27 PM, GMS wrote:

 I have the following class models:


 |  class DiagnosisDetail(Model):
  __tablename__ = 'vw_svc_diagnosis'
  diagnosis_id = Column(String(32),
primary_key=True)
  first_name = Column(String(255))
  last_name = Column(String(255))
  mrn = Column(String(255))
  dx_code = Column(String(255))
  dx_id = Column(String(255),
ForeignKey('dx_group.dx_id'))
  diagnosisgroup = relationship("DiagnosisGroup")
  dx_code_type = Column(String(255))
  dx_name = Column(String(255))

  __mapper_args__ = {
   "order_by":[mrn, dx_name]
 }

  class DiagnosisGroup(Model):
  __tablename__ = 'diagnosis_group'
  dx_id = Column(String(32), primary_key=True)
  mrn = Column(String(255))
  dx_code = Column(String(255))
  dx_code_type = Column(String(255))
  dx_name = Column(String(255))
  diagnosis_datetime = Column(DateTime)

  __mapper_args__ = {
   "order_by":[mrn, dx_name]
 }|



 where the underlying tables for DiagnosisGroup and
 DiagnosisDetail are
 SQL views. DiagnosisGroup is so that I can have a more
succinct

Re: [sqlalchemy] Grouped data in a Flask/SQLAlchemy class

2017-04-24 Thread Greg Silverman

My naive first response would be that I want the class to look like

class DiagnosisTest(Model):
__tablename__ = 'vw_svc_diagnosis'
#id = Column(Integer, primary_key=True, autoincrement=True)
dx_id = Column(String(32), primary_key=True)
first_name = Column(String(255))
last_name = Column(String(255))
mrn = Column(String(255))
dx_code = Column(String(255))
#dx_code_orig = Column(String(255))
dx_code_type = Column(String(255))
dx_name = Column(String(255))
#chronic_diagnosis_yn = Column(String(255))
diagnosis_datetime = Column(DateTime)



@property
def get_diagnosis_groups(self):

import itertools as itertools

for (dx_code, patient_id), details in itertools.groupby(self, keyfunc):
diagnosis_group = DiagnosisGroup(
dx_code, patient_id
)
diagnosis_group.details = details
for detail in details:
detail.group = diagnosis_group
yield diagnosis_group


So that I could then instantiate this and access grouped elements like
DiagnosisTest.get_diagnosis_groups.patient_id and
DiagnosisTest.get_diagnosis_groups.dx_code for example.

I am sure this does not answer your question, but what I really want to do
is have the data grouped within the class and available whenever I
instantiate the class.

I know what I need, but I am having problems in articulating the details.

Thanks for being patient with me! ^_^


On Mon, Apr 24, 2017 at 12:21 PM, mike bayer 
wrote:

>
>
> On 04/24/2017 12:51 PM, Greg Silverman wrote:
>
>> Hi Mike,
>> I'm finally getting to this.  Instead of having both a detail and grouped
>> methods, I would like to have only the grouped method. I'm not sure I
>> follow your suggestion for the two methods above and how that would be
>> modified to fit into the @property in my class.
>>
>> Could you please elaborate a bit how these would work within context of
>> the class?
>>
>
> cutting and pasting:
>
>
>   def get_diagnosis_groups(sorted_list_of_diagnosis_detail):
>
> for (dx_code, patient_id), details in
> itertools.groupby(sorted_list_of_diagnosisdetail, keyfunc):
> diagnosis_group = DiagnosisGroup(
>dx_code, patient_id
> )
> diagnosis_group.details = details
> for detail in details:
> detail.group = diagnosis_group
> yield diagnosis_group
>
>
> you want to take out the "sorted_list_of_diagnosis_detail" argument.
> OKbut I don't know where you'd like to get this from.  Where is this
> "method" going and are you calling that as a classmethod or an instance
> method?  what class?   Just show a usage example.
>
>
>
>
>
>
>
>> Thanks!
>>
>> Greg--
>>
>>
>>
>>
>> On Wed, Mar 1, 2017 at 8:53 PM, mike bayer > > wrote:
>>
>>
>>
>> On 03/01/2017 08:27 PM, GMS wrote:
>>
>> I have the following class models:
>>
>>
>> |  class DiagnosisDetail(Model):
>>  __tablename__ = 'vw_svc_diagnosis'
>>  diagnosis_id = Column(String(32), primary_key=True)
>>  first_name = Column(String(255))
>>  last_name = Column(String(255))
>>  mrn = Column(String(255))
>>  dx_code = Column(String(255))
>>  dx_id = Column(String(255), ForeignKey('dx_group.dx_id'))
>>  diagnosisgroup = relationship("DiagnosisGroup")
>>  dx_code_type = Column(String(255))
>>  dx_name = Column(String(255))
>>
>>  __mapper_args__ = {
>>   "order_by":[mrn, dx_name]
>> }
>>
>>  class DiagnosisGroup(Model):
>>  __tablename__ = 'diagnosis_group'
>>  dx_id = Column(String(32), primary_key=True)
>>  mrn = Column(String(255))
>>  dx_code = Column(String(255))
>>  dx_code_type = Column(String(255))
>>  dx_name = Column(String(255))
>>  diagnosis_datetime = Column(DateTime)
>>
>>  __mapper_args__ = {
>>   "order_by":[mrn, dx_name]
>> }|
>>
>>
>>
>> where the underlying tables for DiagnosisGroup and
>> DiagnosisDetail are
>> SQL views. DiagnosisGroup is so that I can have a more succinct
>> view of
>> the data, since a patient can have the same diagnosis many
>> times. I am
>> wondering if there is a way to do this within the class structure
>>   instead of at the db server?
>>
>>
>> do you mean, derive a DiagnosisGroup object from a DiagnosisDetail
>> without running SQL? (or a list of them?)  (the answer is..sure?
>>  just build Python code to generate objects from a list of
>> DiagnosisDetail objects).
>>
>> I do not wish to do this through

Re: [sqlalchemy] Grouped data in a Flask/SQLAlchemy class

2017-04-24 Thread mike bayer




On 04/24/2017 12:51 PM, Greg Silverman wrote:

Hi Mike,
I'm finally getting to this.  Instead of having both a detail and 
grouped methods, I would like to have only the grouped method. I'm not 
sure I follow your suggestion for the two methods above and how that 
would be modified to fit into the @property in my class.


Could you please elaborate a bit how these would work within context of 
the class?


cutting and pasting:


  def get_diagnosis_groups(sorted_list_of_diagnosis_detail):

for (dx_code, patient_id), details in 
itertools.groupby(sorted_list_of_diagnosisdetail, keyfunc):

diagnosis_group = DiagnosisGroup(
   dx_code, patient_id
)
diagnosis_group.details = details
for detail in details:
detail.group = diagnosis_group
yield diagnosis_group


you want to take out the "sorted_list_of_diagnosis_detail" argument. 
OKbut I don't know where you'd like to get this from.  Where is this 
"method" going and are you calling that as a classmethod or an instance 
method?  what class?   Just show a usage example.









Thanks!

Greg--



On Wed, Mar 1, 2017 at 8:53 PM, mike bayer > wrote:




On 03/01/2017 08:27 PM, GMS wrote:

I have the following class models:


|  class DiagnosisDetail(Model):
 __tablename__ = 'vw_svc_diagnosis'
 diagnosis_id = Column(String(32), primary_key=True)
 first_name = Column(String(255))
 last_name = Column(String(255))
 mrn = Column(String(255))
 dx_code = Column(String(255))
 dx_id = Column(String(255), ForeignKey('dx_group.dx_id'))
 diagnosisgroup = relationship("DiagnosisGroup")
 dx_code_type = Column(String(255))
 dx_name = Column(String(255))

 __mapper_args__ = {
  "order_by":[mrn, dx_name]
}

 class DiagnosisGroup(Model):
 __tablename__ = 'diagnosis_group'
 dx_id = Column(String(32), primary_key=True)
 mrn = Column(String(255))
 dx_code = Column(String(255))
 dx_code_type = Column(String(255))
 dx_name = Column(String(255))
 diagnosis_datetime = Column(DateTime)

 __mapper_args__ = {
  "order_by":[mrn, dx_name]
}|



where the underlying tables for DiagnosisGroup and
DiagnosisDetail are
SQL views. DiagnosisGroup is so that I can have a more succinct
view of
the data, since a patient can have the same diagnosis many
times. I am
wondering if there is a way to do this within the class structure
  instead of at the db server?


do you mean, derive a DiagnosisGroup object from a DiagnosisDetail
without running SQL? (or a list of them?)  (the answer is..sure? 
just build Python code to generate objects from a list of

DiagnosisDetail objects).

I do not wish to do this through any ORM

session queries, since these two classes have distinct use cases
where
they bind to wtform views. Thus, I would like to inherit the
properties
of these two classes from another distinct class.

I have not been able to find anything like this, short
of [create-column-properties-that-use-a-groupby][1]

>,
but this uses session queries to achieve the result. I would like to
keep everything within the class itself through inheritance of the
DiagnosisDetail class.


You don't need a relational database to do grouping, if you have a
list of data in memory it can be grouped using sets, or most
succinctly Python's own groupby function:
https://docs.python.org/2/library/itertools.html#itertools.groupby





Note: the primary key for DiagnosisGroup, is a concatenation of
dx_code
and another field patient_id. Groupings thus are unique.


OK, so

 def keyfunc(detail):
 return (detail.dx_code, detail.patient_id)

 def get_diagnosis_groups(sorted_list_of_diagnosis_detail):

 for (dx_code, patient_id), details in
itertools.groupby(sorted_list_of_diagnosisdetail, keyfunc):
 diagnosis_group = DiagnosisGroup(
dx_code, patient_id
 )
 diagnosis_group.details = details
 for detail in details:

Re: [sqlalchemy] .one is not returning a KeyedTuple, but a

2017-04-24 Thread mike bayer




On 04/24/2017 12:44 PM, Mark Jones wrote:
We are upgrading from 0.9.7 to 1.1.9 and I've run into a bit of a 
problem with the upgrade.


I have a query which  is based on a .select_from() and .join() so the 
mapping doesn't really exist from a single model.  All of the fields 
have labels though and the result set produces the right results.  The 
problem comes from the return type of the results:


def one_or_none(self):
 ret = list(self)

 l = len(ret)
 if l == 1:
 return ret[0]
 elif l == 0:
 return None
 else:
 raise orm_exc.MultipleResultsFound("Multiple rows were 
found for one_or_none()")
When it returns ret[0] is not returning an exported sqlalchemy type. 
  Before it would return a KeyedTuple or based on some of our code maybe 
even a RowProxywhich we would use to control serialization to json for 
the response to the client.  However, this new type is NOT a KeyTupleand 
therefore isinstance(obj, KeyedTuple) returns False and then our code 
fails. isinstance(obj, AbstractKeyedTuple)does return True, but that 
isn't one of the exported classes from sqlalchemy.util. isinstance(obj, 
_LW) returns True as well, but the same objections affect that type as well


The actual class looks like:

 >>> obj.__class__
 

I could fix this by simply importing it from _collections, but that 
doesn't seem like the right solution.  So, I'm wondering if this is a 
bug or if I'm just looking at the problem wrong.  I could pick up on the 
presence of the keys() method and use that, but what if that conflicts 
with a field in the db.


why don't you use isinstance(result, tuple) or even isinstance(result, 
collections.Sequence) ?   That would be the usual Python technique  to use.







A shortened version of the query looks like this:
 return db.query(Account.name.label('account_name'),
 Account.id.label('id'),
 Account.id.label('account_id'),
 
select([func.coalesce(func.sum(...).as_scalar().label('count'),

 not_(Account.disabled).label('enabled'),
 AccountStatus.name.label('account_status'),
 User.login,
 User.first_name,
 User.last_name,
  .select_from(Account)\
  .join(User, User.id == Account.primary_user_id)\
  .join(AccountStatus, Account.account_status_id == 
AccountStatus.id)\
  .outerjoin(account_manager, 
Account.account_manager_user_id == account_manager.id)


--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and 
Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
description.

---
You received this message because you are subscribed to the Google 
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to sqlalchemy+unsubscr...@googlegroups.com 
.
To post to this group, send email to sqlalchemy@googlegroups.com 
.

Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Grouped data in a Flask/SQLAlchemy class

2017-04-24 Thread Greg Silverman

Hi Mike,
I'm finally getting to this.  Instead of having both a detail and grouped
methods, I would like to have only the grouped method. I'm not sure I
follow your suggestion for the two methods above and how that would be
modified to fit into the @property in my class.

Could you please elaborate a bit how these would work within context of the
class?

Thanks!

Greg--



On Wed, Mar 1, 2017 at 8:53 PM, mike bayer  wrote:

>
>
> On 03/01/2017 08:27 PM, GMS wrote:
>
>> I have the following class models:
>>
>>
>> |  class DiagnosisDetail(Model):
>> __tablename__ = 'vw_svc_diagnosis'
>> diagnosis_id = Column(String(32), primary_key=True)
>> first_name = Column(String(255))
>> last_name = Column(String(255))
>> mrn = Column(String(255))
>> dx_code = Column(String(255))
>> dx_id = Column(String(255), ForeignKey('dx_group.dx_id'))
>> diagnosisgroup = relationship("DiagnosisGroup")
>> dx_code_type = Column(String(255))
>> dx_name = Column(String(255))
>>
>> __mapper_args__ = {
>>  "order_by":[mrn, dx_name]
>>}
>>
>> class DiagnosisGroup(Model):
>> __tablename__ = 'diagnosis_group'
>> dx_id = Column(String(32), primary_key=True)
>> mrn = Column(String(255))
>> dx_code = Column(String(255))
>> dx_code_type = Column(String(255))
>> dx_name = Column(String(255))
>> diagnosis_datetime = Column(DateTime)
>>
>> __mapper_args__ = {
>>  "order_by":[mrn, dx_name]
>>}|
>>
>>
>>
>> where the underlying tables for DiagnosisGroup and DiagnosisDetail are
>> SQL views. DiagnosisGroup is so that I can have a more succinct view of
>> the data, since a patient can have the same diagnosis many times. I am
>> wondering if there is a way to do this within the class structure
>>  instead of at the db server?
>>
>
> do you mean, derive a DiagnosisGroup object from a DiagnosisDetail without
> running SQL? (or a list of them?)  (the answer is..sure?  just build Python
> code to generate objects from a list of DiagnosisDetail objects).
>
> I do not wish to do this through any ORM
>
>> session queries, since these two classes have distinct use cases where
>> they bind to wtform views. Thus, I would like to inherit the properties
>> of these two classes from another distinct class.
>>
>> I have not been able to find anything like this, short
>> of [create-column-properties-that-use-a-groupby][1]
>> > e-column-properties-that-use-a-groupby/25879453>,
>> but this uses session queries to achieve the result. I would like to
>> keep everything within the class itself through inheritance of the
>> DiagnosisDetail class.
>>
>
> You don't need a relational database to do grouping, if you have a list of
> data in memory it can be grouped using sets, or most succinctly Python's
> own groupby function: https://docs.python.org/2/libr
> ary/itertools.html#itertools.groupby
>
>
>
>
>> Note: the primary key for DiagnosisGroup, is a concatenation of dx_code
>> and another field patient_id. Groupings thus are unique.
>>
>
> OK, so
>
> def keyfunc(detail):
> return (detail.dx_code, detail.patient_id)
>
> def get_diagnosis_groups(sorted_list_of_diagnosis_detail):
>
> for (dx_code, patient_id), details in
> itertools.groupby(sorted_list_of_diagnosisdetail, keyfunc):
> diagnosis_group = DiagnosisGroup(
>dx_code, patient_id
> )
> diagnosis_group.details = details
> for detail in details:
> detail.group = diagnosis_group
> yield diagnosis_group
>
>
>
>
> I do also need
>
>> the FK relation back to the DiagnosisDetail class, which leads me to
>> believe there should be three classes, where the two above classes
>> inherit their properties from a parent class.
>>
>
>
>
>
>
>> --
>> SQLAlchemy -
>> The Python SQL Toolkit and Object Relational Mapper
>>
>> http://www.sqlalchemy.org/
>>
>> To post example code, please provide an MCVE: Minimal, Complete, and
>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>> description.
>> ---
>> You received this message because you are subscribed to the Google
>> Groups "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it, send
>> an email to sqlalchemy+unsubscr...@googlegroups.com
>> .
>> To post to this group, send email to sqlalchemy@googlegroups.com
>> .
>> Visit this group at https://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
> --
> SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example.  See

[sqlalchemy] .one is not returning a KeyedTuple, but a

2017-04-24 Thread Mark Jones

We are upgrading from 0.9.7 to 1.1.9 and I've run into a bit of a problem 
with the upgrade.

I have a query which  is based on a .select_from() and .join() so the 
mapping doesn't really exist from a single model.  All of the fields have 
labels though and the result set produces the right results.  The problem 
comes from the return type of the results:

def one_or_none(self):
ret = list(self)

l = len(ret)
if l == 1:
return ret[0]
elif l == 0:
return None
else:
raise orm_exc.MultipleResultsFound("Multiple rows were found 
for one_or_none()")
 
When it returns ret[0] is not returning an exported sqlalchemy type. 
 Before it would return a KeyedTuple or based on some of our code maybe 
even a RowProxy which we would use to control serialization to json for the 
response to the client.  However, this new type is NOT a KeyTuple and 
therefore isinstance(obj, KeyedTuple) returns False and then our code 
fails.  isinstance(obj, AbstractKeyedTuple) does return True, but that 
isn't one of the exported classes from sqlalchemy.util.  isinstance(obj, 
_LW) returns True as well, but the same objections affect that type as well

The actual class looks like:

>>> obj.__class__


I could fix this by simply importing it from _collections, but that doesn't 
seem like the right solution.  So, I'm wondering if this is a bug or if I'm 
just looking at the problem wrong.  I could pick up on the presence of the 
keys() method and use that, but what if that conflicts with a field in the 
db.

A shortened version of the query looks like this:
return db.query(Account.name.label('account_name'),
Account.id.label('id'),
Account.id.label('account_id'),

select([func.coalesce(func.sum(...).as_scalar().label('count'),
not_(Account.disabled).label('enabled'),
AccountStatus.name.label('account_status'),
User.login,
User.first_name,
User.last_name,
 .select_from(Account)\
 .join(User, User.id == Account.primary_user_id)\
 .join(AccountStatus, Account.account_status_id == 
AccountStatus.id)\
 .outerjoin(account_manager, 
Account.account_manager_user_id == account_manager.id)

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] autogenerate migrations with alembic for inherited tables

2017-04-24 Thread mike bayer

On 04/24/2017 07:21 AM, Антонио Антуан wrote:

Here is definition.
PostgreSQL ensures that all columns that are in "table_master" will be 
in the inherited tables, but the indexes won't: I should create them 
manually by describing it in procedure code. So, if I add new index, I 
should add them into trigger code.

That is my migration:
|
fromalembic importop
importsqlalchemy assa

revision='77e958e7e1bd'
down_revision='e47e752436d1'

defupgrade():
op.add_column('foos',sa.Column('new_column',sa.Integer))
op.create_index('ix_foos_name','foos',['name'])
|

After execution, table "foos" and its children will have new column 
("new_column"), but new index will be only into "foos" table.
So, currently I should get all partitions and execute "CREATE INDEX" 
manually, something like that:

|
conn =op.get_bind()

forchild_name inconn.execute(text("select child.relname from 
pg_catelog.pg_ingerits
INNER JOIN pg_catalog.pg_class as child ON (pg_inherits.inhrelid = 
child.oid WHERE inhparent = 'foos'::regclass")):

  op.create_index('ix_{table}_name'.format(table=child_name),child_name,['name'])

|

First of all, it would be cool, If I did not have to insert "CREATE 
INDEX" statement manually in trigger code. But it seems to be impossible 
now :)
Also, as you can see, I should manually find each partition of master 
table and manually create index for it. Can alembic do it himself?

OK so this is really complicated, there's these elements:

1. the trigger needs to CREATE INDEX

2. when you add Index to your application, you need to:

a. add the index to all the existing partitions
b. rewrite the trigger with the new index

If this were me I'm not sure I'd be taking the plunge into having 
partitions be on the fly, and instead pre-partition in some way, with a 
hashing scheme or something like that.

However, for this you'd need to customize Alembic's generation using the 
hooks described at 
http://alembic.zzzcomputing.com/en/latest/api/autogenerate.html#customizing-revision-generation.

Basically you would traverse the autogenerate for a new index, then add 
new op.execute() directives that also add the index to the other tables 
as well as rewrite the whole trigger.   For the trigger you'd probably 
use ops.ExecuteSQLOp("CREATE TRIGGER "), that op is at 
http://alembic.zzzcomputing.com/en/latest/api/operations.html#alembic.operations.ops.ExecuteSQLOp.

There's a lot to grok here so try working from the examples and I can 
try to advise where something needs to happen if you show some sample code.

 >
 > If autodetection not working...
 > I can get list of inherited tables with such query:
 > |SELECT child.relname, parent.relname
 > FROM pg_catalog.pg_inherits
 >   INNER JOIN pg_catalog.pg_class as child ON (pg_inherits.inhrelid =
 > child.oid)
 >   INNER JOIN pg_catalog.pg_class as parent ON
(pg_inherits.inhparent =
 > parent.oid)
 > WHERE inhparent = 'base_table'::regclass;|
 >
 > Returned names can be specified as parameter "only" in "reflect()"
 > method of MetaData instance. Can I specify target table for each
table
 > in metadata for migration?
 > I found just such solution:
 > |
 > for table_name in inherit_table_names:
 >  meta_data.tables[table_name].name = "base_table"

I'm not really following what you're doing here.   Changing the name of
a Table like that is probably not safe in general, depends on the
context.

 > |
 >
 > Is my solution safe?

 >
 > --
 > SQLAlchemy -
 > The Python SQL Toolkit and Object Relational Mapper
 >
 > http://www.sqlalchemy.org/
 >
 > To post example code, please provide an MCVE: Minimal, Complete, and
 > Verifiable Example. See http://stackoverflow.com/help/mcve
 for a full
 > description.
 > ---
 > You received this message because you are subscribed to the Google
 > Groups "sqlalchemy" group.
 > To unsubscribe from this group and stop receiving emails from it,
send
 > an email to sqlalchemy+...@googlegroups.com 
 > .
 > To post to this group, send email to sqlal...@googlegroups.com

 > .
 > Visit this group at https://groups.google.com/group/sqlalchemy
.
 > For more options, visit https://groups.google.com/d/optout
.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and 
Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
description.

---
You received this message because you are subscribed to the Google 
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving

Re: [sqlalchemy] autogenerate migrations with alembic for inherited tables

2017-04-24 Thread Антонио Антуан



пятница, 21 апреля 2017 г., 17:07:34 UTC+3 пользователь Mike Bayer написал:
>
>
>
> On 04/21/2017 09:16 AM, Антонио Антуан wrote: 
> > Helllo. 
> > I have a model, with specified __tablename__ = 'base_table'. 
> > In postgresql, the table has trigger, which executes before each insert: 
> > it creates partition for current month (if it not exist yet), specifies 
> > "INHERITS (base_table)" for new partition and insert data into it. 
> > 
> > Is there any way to autodetect table inheritance and generate migration 
> > script for it? 
>
>
> What's the actual migration you want to generate?   E.g. start with A, 
> change to Bare you starting with Table(), and then adding 
> "postgresql_inherits" to it after the fact?  it's not clear. 
>
> if you can illustrate what this migration would be I'd have some idea 
> what you are actually trying to do.The "trigger" you refer to seems 
> to be something that emits CREATE TABLE upon INSERT, which would be 
> outside the realm of Alembic. 
>

For example, here is my model:
import time
from sqlalchemy import Column, Integer, Unicode, event, DDL
from myproject import Base

class Foo(Base):
__tablename__ = 'foos'
id = Column(Integer, primary_key=True, autoincrement=True)
ts_spawn = Column(Integer, nullable=False)
name = Column(Unicode, nullable=False)
data_id = Column(Integer, index=True)

def __init__(self):
self.ts_spawn = int(time.time())

event.listen(Foo.__table__, 'after_create', 
 DDL("""
CREATE OR REPLACE FUNCTION foos_insert_trigger() RETURNS TRIGGER AS 
$BODY$
DECLARE
table_master VARCHAR(255) := 'foos';
table_part VARCHAR(255) := '';
ts_spawn_date DATE := to_timestamp(NEW.ts_spawn);
ts_start INT := date_part('epoch', date_trunc('day', 
ts_spawn_date))::INT;
ts_end INT := date_part('epoch', date_trunc('day', ts_spawn_date + 
INTERVAL '1day))::INT;
BEGIN

-- Giving name for partition --
table_part := table_master
  || '_y' || DATE_PART('year', ts_spawn_date)::TEXT
  || '_m' || DATE_PART('month', ts_spawn_date)::TEXT
  || '_d' || DATE_PART('day', ts_spawn_date)::TEXT;

-- Trying to insert into partition 
EXECUTE 'INSERT INTO ' || quote_ident(table_part) || ' SELECT ($1).*' USING NEW;
RETURN NULL;
-- If not then creating it 
EXCEPTION WHEN UNDEFINED_TABLE
THEN
BEGIN
-- Creating partition ---

EXECUTE 'CREATE TABLE ' || table_part || ' (
CHECK ( ts_spawn >= ' || ts_start || ' AND ts_spawn < ' || 
ts_end || '),

CONSTRAINT ' || table_part || '_pk PRIMARY KEY
(id)
) INHERITS ( ' || table_master || ' ) WITH ( OIDS=FALSE )';
EXECUTE 'CREATE INDEX ids_' || table_part || '_data_id
ON ' || table_part || ' USING btree (data_id);'

EXCEPTION WHEN DUPLICATE_TABLE THEN
-- Do nothing

EXECUTE 'INSERT INTO ' || quote_ident(table_part) || ' SELECT ($1).*' USING NEW;
RETURN NULL;

END;
$BODY$
LANGUAGE plpgsql VOLATILE COST 100;

CREATE TRIGGER insert_foo_trigger BEFORE INSERT
ON foos
FOR EACH ROW
EXECUTE PROCEDURE foos_insert_trigger();

"""))


Here is definition.
PostgreSQL ensures that all columns that are in "table_master" will be in 
the inherited tables, but the indexes won't: I should create them manually 
by describing it in procedure code. So, if I add new index, I should add 
them into trigger code.

That is my migration:
from alembic importop
import sqlalchemy as sa

revision = '77e958e7e1bd'
down_revision = 'e47e752436d1'

def upgrade():
   op.add_column('foos', sa.Column('new_column', sa.Integer))
   op.create_index('ix_foos_name', 'foos', ['name'])


After execution, table "foos" and its children will have new column 
("new_column"), but new index will be only into "foos" table.
So, currently I should get all partitions and execute "CREATE INDEX" 
manually, something like that:

conn = op.get_bind()

for child_name in conn.execute(text("select child.relname from 
pg_catelog.pg_ingerits 
INNER JOIN pg_catalog.pg_class as child ON (pg_inherits.inhrelid = 
child.oid WHERE inhparent = 'foos'::regclass")):
   op.create_index('ix_{table}_name'.format(table=child_name), child_name, [
'name'])


First of all, it would be cool, If I did not have to insert "CREATE INDEX" 
statement manually in trigger code. But it seems to be impossible now :)
Also, as you can see, I should manually find each partition of master table 
and manually create index for it. Can alembic do it himself?




>
> > 
> > If autodetection not working... 
> > I can get list of inherited tables with such query: 
> > |SELECT child.relname, parent.relname 
> > FROM pg_catalog.pg_inherits 
> >   INNER JOIN pg_catalog.pg_class as child ON (pg_inherits.inhrelid = 
> > child.oid) 
> >   INNER JOIN

Re: [sqlalchemy] eager loading relationships after an initial query?

Re: [sqlalchemy] Grouped data in a Flask/SQLAlchemy class

Re: [sqlalchemy] eager loading relationships after an initial query?

[sqlalchemy] eager loading relationships after an initial query?

Re: [sqlalchemy] Grouped data in a Flask/SQLAlchemy class

Re: [sqlalchemy] Grouped data in a Flask/SQLAlchemy class

Re: [sqlalchemy] Grouped data in a Flask/SQLAlchemy class

Re: [sqlalchemy] .one is not returning a KeyedTuple, but a

Re: [sqlalchemy] Grouped data in a Flask/SQLAlchemy class

[sqlalchemy] .one is not returning a KeyedTuple, but a

Re: [sqlalchemy] autogenerate migrations with alembic for inherited tables

Re: [sqlalchemy] autogenerate migrations with alembic for inherited tables

12 matches

Site Navigation

Mail list logo

Footer information