Hello SQLAlchemy friends, I'm struggling to get sqlalchemy to do what I
want.
I'd like to use "classical mappings" so that my business objects are free
from any direct dependency on sqlalchemy.
I'm representing two different objects that are very similar: customer
orders and warehouse stock. Both are essentially a collection of lines
mapping product codes ("sku") to quantities.
here's some not-quite-python pseudocode
Objective 1: Order and Warehouse class have a dict attribute representing
skus and quantities
class Order:
lines: dict # {sku: qty}
class Warehouse:
lines: dict # {sku: qty}
my basic problem here is I can't figure out how to get sqlalchemy to
populate a dict of raw values (not mapped objects) from a join/related
table.
Objective 2 (backup plan) Order and Stock class have an attribute .lines
which is a list of "Line" objects
class Line:
sku: str
qty: int
class Order:
lines: List[Line]
class Warehouse:
lines: List[Line]
my problem here is sqlalchemy really doesn't like a single class (Line)
being effectively mapped to two tables.
tables look something like this (actual code)
order = Table(
'order', metadata,
Column('id', Integer, primary_key=True, autoincrement=True),
)
order_lines = Table(
'order_lines', metadata,
Column('order_id', ForeignKey('order.id'), primary_key=True),
Column('sku', String(255), primary_key=True),
Column('qty', Integer),
)
warehouse = Table(
'warehouse', metadata,
Column('id', Integer, primary_key=True, autoincrement=True),
)
warehouse_lines = Table(
'warehouse_lines', metadata,
Column('warehouse_id', ForeignKey('warehouse.id'), primary_key=True),
Column('sku', String(255), primary_key=True),
Column('qty', Integer),
)
but configuring the mapper is beyond me. I never got anything remotely
close to working for option 1.
and for option 2, having the ".lines" attribute mapped as a relationship
but using the same Line class, doesn't seem to work at all:
mapper(domain_model.Line, order_lines)
mapper(domain_model.Order, order, properties={
'lines': relationship(domain_model.Line, cascade="all, delete-orphan")
})
warehouse_line_mapper = mapper(domain_model.Line, warehouse_lines,
non_primary=True)
mapper(domain_model.Warehouse, warehouse, properties={
'lines': relationship(warehouse_line_mapper, cascade="all,
delete-orphan")
})
If anyone can help, I'd love to see if I can get option 1 working, where
sqlalchemy can build dicts for the Order and Warehouse classes using the
order_lines and warehouse_lines tables, without needing a class to
represent those lines. alternatively, the backup plan using a single Line
class for both tables would be a consolation prize. but i'd really like to
avoid being force to c creating two different classes to represent the
lines for order and warehouse just to make my orm happy.
gist with stripped-down example code here:
https://gist.github.com/hjwp/09fd282062e934eeb2a46a40945e48c8
thanks in advance!
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
