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

[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] Grouped data in a Flask/SQLAlchemy class

[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

[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

[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] Grouped data in a Flask/SQLAlchemy class

[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  

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

[Greg Silverman]

Mike,
I'll try it and let you know the outcome (it may be a bit before I get to
it, but I like your suggestions much better than having to use SQL views,
which I am only doing due to a time crunch).

Much appreciated!

On Wed, Mar 1, 2017 at 9:52 PM, mike bayer  wrote:

>
>
> On 03/01/2017 10:22 PM, Greg Silverman wrote:
>
>>
>>
>> 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?
>>
>>
>>
>> In as much as having the ORM do the work versus the backend, I guess.
>>
>>
>>
>> (or a list of them?)  (the answer is..sure?  just build Python code
>> to generate objects from a list of DiagnosisDetail objects).
>>
>>
>>
>>
>> Hmmm... but I don't get all the benefits of related data/data
>> associations via key constraints that way with a non SQLA object. For
>> example, I have a form that binds the Grouped records to their Detailed
>> records in another form utilizing the one-to-many relationship between
>> the two classes.
>>
>
>
> my example illustrates joining the two types of objects together in the
> same way as a relationship-bound collection would.
>
>
>
>>
>>
>>
>> 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
>> > 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/library/itertools.html#itertools.groupby
>> 
>>
>>
>>
>>
>> Indeed. I have used this for other things, but never thought of it for
>> this case.
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> 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
>> )
>>

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

[mike bayer]

On 03/01/2017 10:22 PM, Greg Silverman wrote:



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?



In as much as having the ORM do the work versus the backend, I guess.



(or a list of them?)  (the answer is..sure?  just build Python code
to generate objects from a list of DiagnosisDetail objects).




Hmmm... but I don't get all the benefits of related data/data
associations via key constraints that way with a non SQLA object. For
example, I have a form that binds the Grouped records to their Detailed
records in another form utilizing the one-to-many relationship between
the two classes.



my example illustrates joining the two types of objects together in the 
same way as a relationship-bound collection would.







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





Indeed. I have used this for other things, but never thought of it for
this case.









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




Is there a way to use these as methods within a class model using the
mapper, like in the stackoverflow link I gave?


this functionality can be placed on a @property on your class, can be 
done bidirectionally too.If you want a DiagnosisGroup to have a 
collection of all the DiagnosisDetails on it you'd need to find a place 
to stash the collection of all the DD objects you're dealing with in memory.






Thanks for the out-of-the-box approach to thinking about this.

Greg--

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

[Greg Silverman]

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?



In as much as having the ORM do the work versus the backend, I guess.



> (or a list of them?)  (the answer is..sure?  just build Python code to
> generate objects from a list of DiagnosisDetail objects).
>



Hmmm... but I don't get all the benefits of related data/data associations
via key constraints that way with a non SQLA object. For example, I have a
form that binds the Grouped records to their Detailed records in another
form utilizing the one-to-many relationship between the two classes.



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




Indeed. I have used this for other things, but never thought of it for this
case.




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



Is there a way to use these as methods within a class model using the
mapper, like in the stackoverflow link I gave?

Thanks for the out-of-the-box approach to thinking about this.

Greg--



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

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

[mike bayer]

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:
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  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] Grouped data in a Flask/SQLAlchemy class

[GMS]

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

Note: the primary key for DiagnosisGroup, is a concatenation of dx_code and 
another field patient_id. Groupings thus are unique. 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] Grouped data in a Flask/SQLAlchemy class

[GMS]

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, 
diagnosis_group, are SQL views. DiagnosisGroup is so that I can have a more 
succinct view of the data, since a patient can have the same dx_code many 
times. I am wondering if there is a way to do this within the class 
structure for , instead of at the db server? 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.

Note: the primary key for DiagnosisGroup, is a concatenation of dx_code and 
another field patient_id. Groupings thus are unique. 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.