>
> Thank you, Mike, for having a look at my clumsy code (there were some
> mistakes in the code, I only saw later) and making this effort.
>
Firstly, I have done a bit more tinkering yesterday, and also got aware,
that the Mapper or the internal cranking for sqlalchemy in this
hierarchical scenario have trouble to resolve the query via a inherited
relationship to the correct object. I think this is in essence what you
want to get at in your first paragraph.
I think the problem stems from that the relationship object takes the to be
querried/mapped-to first argument as a string, and inspects the namespace
for this very class, not its derivatives. I was able to resolve this in a
kind-off working example by overriding the relationship in every derived
class. Not nice, but I understand that it is kind of necessary, becausing
finding and resolving derived classes in the namespace is not trivial.
Arriving at your second example: I am all willing to learn about new
techniques and I can see the idea that you tried to established there.
However, I also noticed that you in the main routine only operate with the
highest derived class 'Source_mapped' and 'Location_mapped'. All other
classes are not mapped and in principle are just structure Mixin's. This is
kind of against the goal I to achieve a strict hierarchy, so that I could
break up the code into different packages, which build on top of each
other, but are functional at each level: myproj_lvl0 < myproj_lvlA <
myproj_lvlB. Anyhow, I took your second example and wrangled it into the
structure that I think is desiderable to make this happen. Please also look
at the __main__-routine at the bottom, which should make it more clear what
at what kind of functionality I would like to arrive at.
Many Thanks
Marcel
# example 2 - the safer architecture
import datetime as dt
from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import DateTime
from sqlalchemy import ForeignKey
from sqlalchemy import inspect
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import Sequence
from sqlalchemy import String
from sqlalchemy import Table
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session
from sqlalchemy.orm.session import object_session
metadata = MetaData()
source_table = Table("source", metadata,
Column("id", Integer, Sequence("src_id_seq", metadata=metadata),
primary_key=True),
Column("name", String(length=32), unique=True, nullable=False, index=True),
Column("created", DateTime(timezone=False), default=dt.datetime.utcnow,
nullable=False)
)
location_table = Table("location_detail", metadata,
Column("id", Integer, Sequence("loc_id_seq", metadata=metadata),
primary_key=True),
Column("name", String(length=32), unique=True, nullable=False, index=True),
Column("created", DateTime(timezone=False), default=dt.datetime.utcnow,
nullable=False),
Column("source_id", ForeignKey("source.id"))
)
Base = declarative_base(metadata=metadata)
# Low Level functions - lvl0 : possibly packed into a base package 'myproj_lvl0'
# base definition of the Source-class for Mapper
class Source_orm_lvl0(Base):
__abstract__ = True
@declared_attr
def __table__(cls):
return source_table
@declared_attr
def _loc(cls):
return relationship("Location_mapped_lvl0", uselist=False) # <<< need
to know the name of the mapped classes
loc_name = association_proxy("_loc", "name")
def __init__(self, name):
self.name = name
# base definition of the Location-class for Mapping
class Location_orm_lvl0(Base):
__abstract__ = True
@declared_attr
def __table__(cls):
return location_table
def __init__(self, name):
self.name = name
class Source_mapped_lvl0(Source_orm_lvl0):
pass
class Location_mapped_lvl0(Location_orm_lvl0):
pass
# Higher functions - lvlA : isolated into a different module/package
'myproj_lvlA'
class Source_orm_lvlA(Source_orm_lvl0):
__abstract__ = True
@declared_attr
def _loc(cls):
return relationship("Location_mapped_lvlA", uselist=False) # <<< need
to override with next hierarchy level class?!
@classmethod
def get_by_name(cls, session, name):
return session.query(cls).filter(cls.name == name).one()
def move_to_loc_by_name(self, loc_name):
Location_mapped = inspect(self).mapper.attrs._loc.mapper.class_
session = object_session(self)
loc = (
session.query(Location_mapped)
.filter(Location_mapped.name == loc_name)
.one()
)
self._loc = loc
session.commit()
class Location_orm_lvlA(Location_orm_lvl0):
__abstract__ = True
@classmethod
def get_by_name(cls, session, name):
return session.query(cls).filter(cls.name == name).one()
def move_src_here(self, src):
session = object_session(self)
src._loc = self
session.merge(src)
session.commit()
class Source_mapped_lvlA(Source_orm_lvlA):
pass
class Location_mapped_lvlA(Source_orm_lvlA):
pass
# Even Higher functions - lvlB : isolated into a different module/package
'myproj_lvlB'
class Source_orm_lvlB(Source_orm_lvlA):
__abstract__ = True
@declared_attr
def _loc(cls):
return relationship("Location_mapped_lvlB", uselist=False)
def assemble_info(self):
return f"<Source> {self.name} at <Location> {self.loc_name}"
class Location_orm_lvlB(Location_orm_lvlA):
__abstract__ = True
def assemble_info(self):
return f"<Location> {self.name}"
class Source_mapped_lvlB(Source_orm_lvlB):
pass
class Location_mapped_lvlB(Location_orm_lvlB):
pass
#----------------------------------------------------------------------
if __name__ == "__main__":
engine = create_engine("sqlite://", echo=False)
Base.metadata.create_all(engine)
session = Session(engine)
# from myproj_lvl0 import Source_mapped_lvl0, Location_mapped_lvl0
# create low level objects
s = Source_mapped_lvl0("MySource")
session.add(s)
session.flush()
l = Location_mapped_lvl0("MyLocation")
session.add(l)
session.flush()
# operate on the db use a higher level function
# from myproj_lvlA import Source_mapped_lvlA, Location_mapped_lvlA
s = Source_mapped_lvlA.get_by_name(session, "MySource")
s.move_to_loc_by_name(
Location_mapped_lvlA.get_by_name(session, "MyLocation").name
)
assert isinstance(s._loc, Location_mapped_lvlA)
# use highest level functionality
# from myproj_lvlB import Source_mapped_lvlA, Location_mapped_lvlB
s = Source_mapped_lvlB.get_by_name(session, "MySource")
print( s.assemble_info() )
assert isinstance(s._loc, Location_mapped_lvlB)
--
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/9f20896a-b740-4107-a6b2-f6994b175d89%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
