Re: [Sqlalchemy-users] Extra Insert Mapper Problems

Fri, 29 Sep 2006 15:32:59 -0700

when you set up a relationship, in most cases the "foreignkey" parameter refers to the column in join condition that actually contains a FOREIGN KEY constraint on it.   by matching which table the foreignkey is on to the association of classes with those tables, the mapper figures out what kind of "dependency" exists between the two classes/tables.  So as long as your tables have ForeignKey's on them, you dont need any foreignkey parameters.  If you comment them all out, all the test cases pass...since you had some of them in the opposite direction needed, which was creating circular dependencies that didnt actually exist (and in many runs it excepts out with a bad depdency sort, before it even gets around to SQL).

its only in the case of a table that explicitly references itself that you ever need the "foreignkey" parameter to explicitly disagree with the actual foreign key column in the join condition, which is used as a clue to the mapper as to which direction to set up a relationship.   this example doesnt have any self-referential tables.

Its also generally a good idea to specify all of your bi-directional relationships explicitly as backreferences.  in the case of a many-to-many bidirectional relationship, its necessary that one side of the bi-directional relationship be "turned off" to eliminate multiple row inserts, which a "backref" can handle.  this example, not using any many-to-many, works the way it is, but its also a lot less effort to use backreferences.

Some other things to make this mapping easier, if you just say "backref='keyname'" it will usually do what you want, including using the explicit primaryjoin condition that you specified for the backref as well.  You also might want to set up each individual mapper completely instead of using add_property(); sqlalchemy for many versions now has the behavior of not "compiling" all the relationships until as late as possible so that you dont have to construct mapper relationships in any particular order.

So you take all of that, and you have a smaller and easier to read mapping with much less room for mistakes:

Project.mapper = mapper(Project, tables['projects'], properties={
    'nodes': relation(Node,
            primaryjoin=tables['nodes'].c.project_id == tables['projects'].c.id,
            backref='project'
            ),
    'edges': relation(Edge,
            primaryjoin=tables['edges'].c.project_id == tables['projects'].c.id,
            backref='project'
            ),
})

node_types = {'Node':0, 'Algorithm':1,'Parameter':2}

Node.mapper = mapper(Node, tables['nodes'],
    polymorphic_on=tables['nodes'].c.type,
    polymorphic_identity=node_types['Node'],
    properties = {
    'inputs': relation(Edge,
            primaryjoin=tables['nodes'].c.id == tables['edges'].c.to_node_id,
            backref='to_node'
        ),
    'outputs': relation(Edge,
            primaryjoin=tables['nodes'].c.id == tables['edges'].c.from_node_id,
            backref='from_node'
        )
    }
)

Edge.mapper = mapper(Edge,tables['edges'])      

Algorithm.mapper = mapper(Algorithm, inherits=Node.mapper,
    polymorphic_identity=node_types['Algorithm'],
    properties={
    'sub_project':relation(Project,
            primaryjoin=Node.c.sub_project_id == Project.c.id,
            backref="alg_nodes_using"
        )
    })

Parameter.mapper = mapper(Parameter, inherits=Node.mapper,
    polymorphic_identity=node_types['Parameter'])



On Sep 29, 2006, at 4:02 PM, Hogarty, David A. wrote:

I’m trying to represent a graph with nodes and edges… For the nodes I’m using polymorphic single-table inheritance. The relevant mapping & code is:

 

# Mapper classes:

#

# Project

#

# Node

#   -> Algorithm

#   -> Parameter

#

# Edge

 

# Project.nodes -> Node             (->N)

# Project       <- Node.project     (0,1<-)

 

# Project.edges -> Edge             (->N)

# Project       <- Node.project     (0,1<-)

 

# Node.inputs   -> Edge             (->N)

# Node          <- Edge.to_node     (0,1<-)

 

# Node.outputs  -> Edge             (->N)

# Node          <- Edge.from_node   (0,1<-)

 

# Algorithm.sub_project -> Project                  (->0,1)

# Algorithm             <- Project.alg_nodes_using  (N<-)

 

    def make_graph_small(self):

        p = Project(name='small')

        self.db.session.save(p)

        algs = [Algorithm(name='a'+str(num)) for num in range(0,2)]

        params = [Parameter(name='p'+str(num)) for num in range(0,8)]

        for a in algs+params:

            self.db.session.save(a)

            p.nodes.append(a)

        for i in [0,1,2,3]:

            Edge(from_node=params[i],to_node=algs[0])

        for i in [4,5]:

            Edge(from_node=algs[0],to_node=params[i])

        for i in [6,7]:

            Edge(from_node=algs[1],to_node=params[i])

        Edge(from_node=params[5],to_node=algs[1])

        self.db.session.flush()

 

on the flush, the project gets inserted twice, causing a primary key error.

 

Attached file:

test.tar.gz:

  -> db.py                      : Schema/Mapper definitions

  -> test_db.py               : Test that’s breaking

  -> project_pkey_error   : Output with logging enabled from test run

 

Any help in figuring out why this is occurring would be great,

 

-Dave H

 

<test.tar.gz>
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
Sqlalchemy-users mailing list

-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users

Reply via email to