Re: [sqlalchemy] Representing Tree Structures with sqlAlchemy

[Nathan Mooth]

Thanks for the example, it was exactly what I was looking for.

On Friday, July 21, 2017 at 10:47:16 AM UTC-6, Mike Bayer wrote:
>
> On Fri, Jul 21, 2017 at 11:40 AM, Nathan Mooth  > wrote: 
> > So I have a tree-like data structure that I need to store in a way so 
> that I 
> > can see all children or all parents of a query as well as their relative 
> > depth from the query item. In the past I have been using the closure 
> table 
> > method demonstrated in this blog post, however now that I am switching 
> to 
> > sqlAlchemy I am wondering if there is a better way to do this. So 
> basically 
> > I am trying to figure out how to run a series of inserts on my closure 
> table 
> > whenever an item is added to the main table, as well as what would be 
> the 
> > best way to load the child or parent items of a query. 
>
> you'd likely want to use the same approaches illustrated in the nested 
> sets example at 
>
> http://docs.sqlalchemy.org/en/latest/_modules/examples/nested_sets/nested_sets.html
>  
> - basically intercept before_insert(), or after_insert(), the run the 
> additional SQL you need on the given connection within the flush 
> process.The query approach is also illustrated there. 
>
> If you're just going for in-memory, "load a node, then access 
> node.children", and you don't need elaborate cross-depth searching and 
> set operations, then all of these approaches are far too complicated, 
> just use adjacency list. 
>
>
>
>
>
> > 
> > -- 
> > 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 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] Association proxy in classical mapping keeping the business logic and the db schema separated

[Mike Bayer]

On Sun, Jul 23, 2017 at 11:26 AM, Ruben Di Battista
 wrote:
> Hello,
>
> I'm trying to introduce database persistence into an already existent class
> hierarchy. That mean I have in a separate module all the class representing
> the models of my application (without SQL interaction methods), then I have
> in another module the SQL schema and the mapping (so I'm using the classical
> mapping):
>
> sql.py
>
> import sqlalchemy.orm as orm
> from sqlalchemy import Metadata, Integer, String, DateTime
> from myproject.models import Item, Order
>
> metadata = MetaData()
>
> item = Table('item', metadata,
>  Column('id', Integer, primary_key=True),
>  Column('name', String),
>  Column('quantity', Integer)
>  )
>
> order = Order('order', metadata,
>   Column('id', Integer, primary_key=True),
>   Column('date', DateTime),
>  )
>
> orm.mapper(Item, item)
> orm.mapper(Order, order)
>
>
>
>
> models.py
> class Item(object):
> def __init__(self, name, quantity):
> self.name = name
> self.quantity = quantity
>
> class Order(object):
> def __init__(self, date):
> self.date = date
>
> Now I would like to introduce a M2M relationship with additional column in
> the association table:
>
> order_item = Table('order_item', metadata,
>Column('order_id', Integer, ForeignKey('order.id')),
>Column('item_id', Integer, ForeignKey('item.id')),
>Column('price', Float)
>   )
>
>
> In the documentation, when they present the Association proxy, they do that
> in a declarative way. So they add an attribute to the class:
>
> # ... Class definition ...
> items = association_proxy('order_items', 'items')
>
>
> Is there a way to achieve this without touching the classes in models.py?
> Maybe using the orm.mapper? Or the only way is to modify the class adding
> the association proxy like in the documentation? Do you have any reference
> to point me out on the right direction to use association proxies with
> classical mapping?

Basically, just do this:

orm.mapper(Order, order)
Order.items = association_proxy('order_items', 'items')

The association proxy is independent of the declarative system and if
you look in early documentation you'll see it described in terms of
classical mapping.

As far as the term "without touching the classes", there's not much
point trying to be purist about this as the call to mapper(Order,
order) highly alters the Order class in any case.


>
> Thanks,
>
> PS: You find this same question on SO, if you want you can answer also
> there.
>
>
>
> --
> 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] Association proxy in classical mapping keeping the business logic and the db schema separated

[Ruben Di Battista]

Hello, 

I'm trying to introduce database persistence into an already existent class 
hierarchy. That mean I have in a separate module all the class representing 
the models of my application (without SQL interaction methods), then I have 
in another module the SQL schema and the mapping (so I'm using the 
classical mapping): 

*sql.py*





















*import sqlalchemy.orm as ormfrom sqlalchemy import Metadata, Integer, 
String, DateTimefrom myproject.models import Item, Ordermetadata = 
MetaData()item = Table('item', metadata,  Column('id', Integer, 
primary_key=True),  Column('name', String), 
 Column('quantity', Integer) )order = Order('order', metadata,  
Column('id', Integer, primary_key=True),  
Column('date', DateTime), )orm.mapper(Item, 
item)orm.mapper(Order, order)*

*models.py*
class Item(object):
def __init__(self, name, quantity):
self.name = name
self.quantity = quantity

class Order(object): 
def __init__(self, date):
self.date = date

Now I would like to introduce a M2M relationship with additional column in 
the association table:

order_item = Table('order_item', metadata,
   Column('order_id', Integer, ForeignKey('order.id')), 
   Column('item_id', Integer, ForeignKey('item.id')), 
   Column('price', Float)
  )


In the documentation, when they present the Association proxy, they do that 
in a declarative way. So they add an attribute to the class:

# ... Class definition ...
items = association_proxy('order_items', 'items')


Is there a way to achieve this without touching the classes in *models.py? 
*Maybe 
using the *orm.mapper? *Or the only way is to modify the class adding the 
association proxy like in the documentation? Do you have any reference to 
point me out on the right direction to use association proxies with 
classical mapping?

Thanks, 

PS: You find this same question on SO, if you want you can answer also there 
.



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