Re: [sqlalchemy] ORM: Implementing abstract base class that generates auxiliary tables for each child class
Thanks Simon !
I got it working using the hack.
I'm not using the "mapper_configured" event because I need to add a
"parent" attribute to the subclass of TreeNode,
which should be done before the mapping.
On Friday, February 17, 2017 at 6:21:57 PM UTC+8, Simon King wrote:
>
> On Fri, Feb 17, 2017 at 10:03 AM, >
> wrote:
> > I want to achieve something like this:
> >
> > Base = declarative_base()
> >
> > # the abstract base, no table generated for it
> > class TreeNode(Base):
> > __abstract__ = True
> > parent = Column(ForeignKey('child_class_table.id'), nullable=False)
> >
> > def is_root(self):
> > pass
> > def get_parent(self):
> > pass
> >
> > # more methods
> >
> >
> > # the child, mapped to a table(in this case named 'discipline')
> > class Discipline(TreeNode):
> > __tablename__ = 'discipline'
> >
> > id = Column(Integer, primary_key=True)
> > name = Column(Unicode(500), nullable=False)
> >
> >
> > # a generated auxiliary table for the child(in this case named
> > 'discipline_closure')
> > class DisciplineClosure(Base):
> > __tablename__ = 'discipline_closure'
> >
> > ancestor_id = Column(ForeignKey('discipline.id'), nullable=False)
> > descendant_id = Column(ForeignKey('discipline.id'), nullable=False)
> > depth = Column(Integer, nullable=False)
> >
> > Is this possible in SQLAlchemy? How to implement it ?
> > Or is there an another way to implement my closure table ?
> >
> > My Django implementation Attached, but I wanna do it using SQLAlchemy.
> >
>
> You could look at the versioned_history example, which creates history
> tables for classes inheriting from a "Versioned" mixin:
>
>
> http://docs.sqlalchemy.org/en/latest/orm/examples.html#module-examples.versioned_history
>
>
> Here's the Versioned class:
>
> class Versioned(object):
> @declared_attr
> def __mapper_cls__(cls):
> def map(cls, *arg, **kw):
> mp = mapper(cls, *arg, **kw)
> _history_mapper(mp)
> return mp
> return map
>
> Declarative accesses the __mapper_cls__ attribute when creating the
> SQLAlchemy mapper for the class. The Versioned class uses that as a
> hook to generate the second class.
>
> This is a bit of a hack (particularly since __mapper_cls__ seems to be
> barely documented). It might be possible to do something similar using
> ORM events, but I haven't tried it. You could investigate the
> mapper_configured event:
>
>
> http://docs.sqlalchemy.org/en/latest/orm/events.html#sqlalchemy.orm.events.MapperEvents.mapper_configured
>
>
> The code would look something like:
>
> @event.listens_for(TreeNode, 'mapper_configured', propagate=True)
> def receive_mapper_configured(mapper, class_):
> # Create your Closure class here
>
> Hope that helps,
>
> Simon
>
--
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] Status of cascading polymorphism, in docs as well
I understand from the docs and several questions both here and on bitbucket
that cascading polymorphism is not supported. This
is what it says on the docs:
Warning
Currently, *only one discriminator column may be set*, typically on the
base-most class in the hierarchy. “Cascading” polymorphic columns are not
yet supported.
However, the following experiment works for me
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
import json
Base = declarative_base()
class Top(Base):
__tablename__ = 'top'
id = Column(Integer, primary_key=True)
type = Column(String(8), nullable=False)
__mapper_args__ = {
'polymorphic_on': type,
'with_polymorphic': '*'
}
def dict(self):
return {
'id': self.id
}
class Primary(Top):
__tablename__ = 'primary'
id = Column(None, ForeignKey(Top.id), primary_key=True)
type = Column(String(8), nullable=False)
__mapper_args__ = {
'polymorphic_identity': 'primary',
'polymorphic_on': type,
'with_polymorphic': '*'
}
def dict(self):
return {
'type': self.type,
**super().dict()
}
class One(Primary):
__tablename__ = 'one'
id = Column(None, ForeignKey(Primary.id), primary_key=True)
one = Column(String(32), nullable=False)
__mapper_args__ = {
'polymorphic_identity': 'one'
}
def dict(self):
return {
'one': self.one,
**super().dict()
}
class Two(Primary):
__tablename__ = 'two'
id = Column(None, ForeignKey(Primary.id), primary_key=True)
two = Column(String(32), nullable=False)
__mapper_args__ = {
'polymorphic_identity': 'two'
}
def dict(self):
return {
'two': self.two,
**super().dict()
}
class Secondary(Top):
__tablename__ = 'secondary'
id = Column(None, ForeignKey(Top.id), primary_key=True)
type = Column(String(8), nullable=False)
__mapper_args__ = {
'polymorphic_identity': 'secondary',
'polymorphic_on': type,
'with_polymorphic': '*'
}
def dict(self):
return {
'type': self.type,
**super().dict()
}
class Three(Secondary):
__tablename__ = 'three'
id = Column(None, ForeignKey(Secondary.id), primary_key=True)
three = Column(String(32), nullable=False)
__mapper_args__ = {
'polymorphic_identity': 'three'
}
def dict(self):
return {
'three': self.three,
**super().dict()
}
class Four(Secondary):
__tablename__ = 'four'
id = Column(None, ForeignKey(Secondary.id), primary_key=True)
four = Column(String(32), nullable=False)
__mapper_args__ = {
'polymorphic_identity': 'four'
}
def dict(self):
return {
'four': self.four,
**super().dict()
}
if __name__ == '__main__':
e = create_engine('sqlite:///poly_casc.db', echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
s = create_session(e)
s.begin()
s.add_all([One(one='one'), Two(two='two'), Three(three='three'),
Four(four='four')])
s.commit()
for m in s.query(Top).all():
print(json.dumps(m.dict(), indent=4))
print(type(m))
s.expunge_all()
for m in s.query(Primary).all():
print(json.dumps(m.dict(), indent=4))
print(type(m))
s.expunge_all()
for m in s.query(One).all():
print(json.dumps(m.dict(), indent=4))
print(type(m))
so I am wondering if the docs needed to be updated, or I am missing
something regarding the functionality.
Thanks
--
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] Status of cascading polymorphism, in docs as well
On 02/21/2017 03:56 PM, Shane Carey wrote:
I understand from the docs and several questions both here and on
bitbucket that cascading polymorphism is not supported. This
is what it says on the docs:
Warning
Currently, *only one discriminator column may be set*, typically on the
base-most class in the hierarchy. “Cascading” polymorphic columns are
not yet supported.
However, the following experiment works for me
it's possible that because you've named the column "type" on every
subclass, the value is shared as these are mapped to the same attribute
name by default (though I thought it emits a warning when this happens
implicitly).
The feature in general is not supported for the normal case when the
columns are differently named. A recipe to make this work is at
https://bitbucket.org/zzzeek/sqlalchemy/issues/2555/cascading-polymorphic-ons.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
import json
Base = declarative_base()
class Top(Base):
__tablename__ = 'top'
id = Column(Integer, primary_key=True)
type = Column(String(8), nullable=False)
__mapper_args__ = {
'polymorphic_on': type,
'with_polymorphic': '*'
}
def dict(self):
return {
'id': self.id
}
class Primary(Top):
__tablename__ = 'primary'
id = Column(None, ForeignKey(Top.id), primary_key=True)
type = Column(String(8), nullable=False)
__mapper_args__ = {
'polymorphic_identity': 'primary',
'polymorphic_on': type,
'with_polymorphic': '*'
}
def dict(self):
return {
'type': self.type,
**super().dict()
}
class One(Primary):
__tablename__ = 'one'
id = Column(None, ForeignKey(Primary.id), primary_key=True)
one = Column(String(32), nullable=False)
__mapper_args__ = {
'polymorphic_identity': 'one'
}
def dict(self):
return {
'one': self.one,
**super().dict()
}
class Two(Primary):
__tablename__ = 'two'
id = Column(None, ForeignKey(Primary.id), primary_key=True)
two = Column(String(32), nullable=False)
__mapper_args__ = {
'polymorphic_identity': 'two'
}
def dict(self):
return {
'two': self.two,
**super().dict()
}
class Secondary(Top):
__tablename__ = 'secondary'
id = Column(None, ForeignKey(Top.id), primary_key=True)
type = Column(String(8), nullable=False)
__mapper_args__ = {
'polymorphic_identity': 'secondary',
'polymorphic_on': type,
'with_polymorphic': '*'
}
def dict(self):
return {
'type': self.type,
**super().dict()
}
class Three(Secondary):
__tablename__ = 'three'
id = Column(None, ForeignKey(Secondary.id), primary_key=True)
three = Column(String(32), nullable=False)
__mapper_args__ = {
'polymorphic_identity': 'three'
}
def dict(self):
return {
'three': self.three,
**super().dict()
}
class Four(Secondary):
__tablename__ = 'four'
id = Column(None, ForeignKey(Secondary.id), primary_key=True)
four = Column(String(32), nullable=False)
__mapper_args__ = {
'polymorphic_identity': 'four'
}
def dict(self):
return {
'four': self.four,
**super().dict()
}
if __name__ == '__main__':
e = create_engine('sqlite:///poly_casc.db', echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
s = create_session(e)
s.begin()
s.add_all([One(one='one'), Two(two='two'), Three(three='three'),
Four(four='four')])
s.commit()
for m in s.query(Top).all():
print(json.dumps(m.dict(), indent=4))
print(type(m))
s.expunge_all()
for m in s.query(Primary).all():
print(json.dumps(m.dict(), indent=4))
print(type(m))
s.expunge_all()
for m in s.query(One).all():
print(json.dumps(m.dict(), indent=4))
print(type(m))
so I am wondering if the docs needed to be updated, or I am missing
something regarding the functionality.
Thanks
--
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, s
[sqlalchemy] self-referential many-to-many with single attribute
I apologize if this is well trodden ground, but having googled, and
stack-overflowed, read the docs, and searched this list, where lots of
people have asked the same (or similar) questions, I couldn't find a
concrete answer...
I'm trying to set up a Model such that an entry can be connected to one or
more other entries, and that the reverse relationship can be found from the
same attribute.
Effectively I'm trying to do this:
```
from sqlalchemy import Integer, ForeignKey, String, Column, Tablefrom
sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.orm import
relationship
Base = declarative_base()
node_to_node = Table("node_to_node", Base.metadata,
Column("left_node_id", Integer, ForeignKey("node.id"), primary_key=True),
Column("right_node_id", Integer, ForeignKey("node.id"), primary_key=True))
class Node(Base):
__tablename__ = 'node'
id = Column(Integer, primary_key=True)
label = Column(String)
connected = relationship("Node",
secondary=node_to_node,
primaryjoin=id==node_to_node.c.left_node_id,
secondaryjoin=id==node_to_node.c.right_node_id,
backref="connected"
)
```
However, that naturally fails (`Error creating backref 'translations' on
relationship 'Sentence.translations': property of that name exists`) as
there's no magic to figure out that `translations` should be
bi-directional.
Is there another way to achieve this?
Thanks!
Matt.
--
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] self-referential many-to-many with single attribute
you want "Node.connected" to be the set of all nodes connected in either
direction.The problem is that relationally, all graphs are
"directed", so we have the "left", "right" aspect of things.
The easiest way to get ".connected" as the union of both sets (hint!) is
to just union them in Python. Give Node a "left_nodes" and
"right_nodes" relationship (example: see
http://docs.sqlalchemy.org/en/latest/_modules/examples/graphs/directed_graph.html)
then make a regular Python @property that returns
"self.left_nodes.union(self.right_nodes)", easy enough.
If you want to get into querying this relationally, then you need to do
the UNION on the SQL side and you'd need to get into creating a mapping
against a UNION and then building a relationship to it. This is
significantly more involved and would be using some of the techniques at
http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#relationship-to-non-primary-mapper.
But, a @property that does a Python union of two relationships, quick
and easy.
On 02/21/2017 04:43 PM, Matthew Brookes wrote:
I apologize if this is well trodden ground, but having googled, and
stack-overflowed, read the docs, and searched this list, where lots of
people have asked the same (or similar) questions, I couldn't find a
concrete answer...
I'm trying to set up a Model such that an entry can be connected to one
or more other entries, and that the reverse relationship can be found
from the same attribute.
Effectively I'm trying to do this:
```
from sqlalchemy import Integer, ForeignKey, String, Column, Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
Base = declarative_base()
node_to_node = Table("node_to_node", Base.metadata,
Column("left_node_id", Integer, ForeignKey("node.id"), primary_key=True),
Column("right_node_id", Integer, ForeignKey("node.id"), primary_key=True)
)
class Node(Base):
__tablename__ = 'node'
id = Column(Integer, primary_key=True)
label = Column(String)
connected = relationship("Node",
secondary=node_to_node,
primaryjoin=id==node_to_node.c.left_node_id,
secondaryjoin=id==node_to_node.c.right_node_id,
backref="connected"
)
```
However, that naturally fails (`Error creating backref 'translations' on
relationship 'Sentence.translations': property of that name exists`) as
there's no magic to figure out that `translations` should be
bi-directional.
Is there another way to achieve this?
Thanks!
Matt.
--
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] Geo query support in SqlAlchemy
I am using Python 3.3 with sqlalchemy using a MySql 5.6.33 Database with SQLAlchemy 1.1.5. I know that mysql supports Geo data types, but I was unable to find the equivalent in sqlalchemy in the docs. I did see GeoAlchemy and GeoAlchemy2 but GeoAlchemy is not for Python 3 and I am not using a PostgresQL for GeoAlchemy2. Does sqlAlchemy support this out of the box? If not, any recommendations? Thanks very much in advance for any help! -- 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.
