sqlalchemy graph database data manipulation

Question

I am designing a graph database structure. A Node could be a person, a department etc. That is why I have added nodeType. Relations between nodes can be of multiple types as well. From the code below r1 and r2 are inserted into the database. When I read the db I see null null null. Why is that ? I expected to see the values that relate to the.id fields (eg n1.id, rt1.id, n2.id)

from sqlalchemy import MetaData, Table, Column, Integer, ForeignKey, \
    create_engine, String
from sqlalchemy.orm import mapper, relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:///dir_graph.sqlite', echo=True)

Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()

class NodeType(Base):
    __tablename__ = 'nodetype'
    id      = Column(Integer, primary_key=True)
    name    = Column(String(20), unique=True)
    nodes   = relationship('Node', backref='nodetype')

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return "Nodetype: %s" % (self.name)


class Node(Base):
    __tablename__ = 'node'

    id = Column(Integer, primary_key=True)
    name = Column(String(20), unique=True)
    type_id = Column(Integer,
                        ForeignKey('nodetype.id')) 

    def __init__(self, _name, _type_id):
        self.name = _name
        self.type_id = _type_id

class Relation(Base):
    __tablename__ = 'relation'

    id      = Column(Integer, primary_key=True)
    name    = Column(String(20), unique=True)
    type_id = Column(Integer,
                        ForeignKey('relationtype.id')) 

    from_id = Column(Integer,
                        ForeignKey('node.id'))

    to_id = Column(Integer,
                        ForeignKey('node.id'))


    def __init__(self, _fromNode, _type_id, _toNode):
            self.from_id = _fromNode
            self.type_id =  _type_id
            self.to_id   = _toNode

class RelationType(Base):
    __tablename__ = 'relationtype'

    id             = Column(Integer, primary_key=True)
    name           = Column(String(20), unique=True)
    description    = Column(String(30), unique=True)

    relations      = relationship('Relation', backref='relationtype')

    def __init__(self, _name):  
        self.name = _name

    def description(self, _description):
        self.description = _description  
Base.metadata.create_all(engine)

nt1 = NodeType('nt1')
nt2 = NodeType('nt2')

n1 = Node('n1type1', 1)
n2 = Node('n2type1', 1)
n3 = Node('n3type1', 1)
n4 = Node('n4type2', 2)
n5 = Node('n5type2', 2)

rt1 = RelationType('rt1')
rt2 = RelationType('rt2')
rt3 = RelationType('rt3')

r1 = Relation(n1.id,rt1.id,n2.id)
r2 = Relation(n3.id,rt2.id,n5.id)

session.add_all([nt1,nt2,n1,n2,n3,n4,n5,rt1,rt2,rt3,r1,r2])
session.commit()

Other comment on code improvement are also appreciated.

Answer 1

The problem with your code is that you try to use the Node.id property before the nodes have been inserted in the DB. Until you call session.flush() or session.commit(), newly created objects don't have an id, and cannot since it is the DB engine which generates the IDs.

So the SQLAlchemy way of doing this would be to define relationships between the Relation object and the Node object, and assign the objects in Relation.__init__ rather than assigning ids. Just like this:

from sqlalchemy import MetaData, Table, Column, Integer, ForeignKey, \
    create_engine, String
from sqlalchemy.orm import mapper, relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:///dir_graph.sqlite', echo=True)

Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()


class NodeType(Base):
    __tablename__ = 'nodetype'
    id = Column(Integer, primary_key=True)
    name = Column(String(20), unique=True)
    nodes = relationship('Node', backref='nodetype')

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return "Nodetype: %s" % self.name


class Node(Base):
    __tablename__ = 'node'

    id = Column(Integer, primary_key=True)
    name = Column(String(20), unique=True)
    type_id = Column(Integer,
                     ForeignKey('nodetype.id'))


    def __init__(self, _name, _type_id):
        self.name = _name
        self.type_id = _type_id


class Relation(Base):
    __tablename__ = 'relation'

    id = Column(Integer, primary_key=True)
    name = Column(String(20), unique=True)
    type_id = Column(Integer,
                     ForeignKey('relationtype.id'))

    from_id = Column(Integer,
                     ForeignKey('node.id'))

    to_id = Column(Integer,
                   ForeignKey('node.id'))

    from_node = relationship(Node, primaryjoin=Node.id == from_id)
    to_node = relationship(Node, primaryjoin=Node.id == to_id)

    def __init__(self, _fromNode, _type_id, _toNode):
        self.from_node = _fromNode
        self.type_id = _type_id
        self.to_node = _toNode


class RelationType(Base):
    __tablename__ = 'relationtype'

    id = Column(Integer, primary_key=True)
    name = Column(String(20), unique=True)
    description = Column(String(30), unique=True)

    relations = relationship('Relation', backref='relationtype')

    def __init__(self, _name):
        self.name = _name

    def description(self, _description):
        self.description = _description


Base.metadata.create_all(engine)

nt1 = NodeType('nt1')
nt2 = NodeType('nt2')

n1 = Node('n1type1', 1)
n2 = Node('n2type1', 1)
n3 = Node('n3type1', 1)
n4 = Node('n4type2', 2)
n5 = Node('n5type2', 2)

rt1 = RelationType('rt1')
rt2 = RelationType('rt2')
rt3 = RelationType('rt3')

r1 = Relation(n1, rt1.id, n2)
r2 = Relation(n3, rt2.id, n5)

session.add_all([nt1, nt2, n1, n2, n3, n4, n5, rt1, rt2, rt3, r1, r2])
session.commit()