sqla nodes

sqla_nodes.py

"""
simple example showing sqlalchemy Adjacency List Relationships
https://docs.sqlalchemy.org/en/13/orm/self_referential.html
"""
from sqlalchemy import create_engine, Column, ForeignKey, Integer, String
from sqlalchemy.orm import sessionmaker, relationship, backref
from sqlalchemy.ext.declarative import declarative_base

DB_PATH = 'sqlite:///nodes.sql'

Base = declarative_base()


class Node(Base):
    __tablename__ = 'node'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('node.id'))
    data = Column(String(50))
    children = relationship("Node",
                            backref=backref('parent', remote_side=[id]),
                            lazy='joined',
                            join_depth=3
                            )

    def __repr__(self):
        return f"node: {self.data}, par: {self.parent.data}"


def create():
    engine = create_engine(DB_PATH, echo=True)
    if engine.dialect.has_table(engine, Node.__tablename__):
        Node.__table__.drop(engine)

    Node.__table__.create(engine)

    Session = sessionmaker(engine)
    ss = Session()

    root = Node(
        data='root',
        children=[
            Node(data='1.1', children=[
                Node(data='1.1.1'),
            ]),
            Node(data='1.2', children=[
                Node(data='1.2.1'),
                Node(data='1.2.2'),
                Node(data='1.2.3', children=[
                    Node(data='1.2.3.1'),
                    Node(data='1.2.3.2'),
                ])
            ])
        ]
    )
    ss.add(root)
    ss.commit()


def bfs(r, level=0):
    print(" " * level + r.data)
    for child in r.children:
        bfs(child, level + 1)


def main():
    engine = create_engine(DB_PATH, echo=True)
    Session = sessionmaker(engine)
    ss = Session()

    r = ss.query(Node).filter(Node.parent_id.is_(None)).first()
    bfs(r)


if __name__ == '__main__':
    create()
    main()

sqla_output.txt

2020-11-20 22:13:32,731 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2020-11-20 22:13:32,731 INFO sqlalchemy.engine.base.Engine ()
2020-11-20 22:13:32,731 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2020-11-20 22:13:32,731 INFO sqlalchemy.engine.base.Engine ()
2020-11-20 22:13:32,732 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info("node")
2020-11-20 22:13:32,732 INFO sqlalchemy.engine.base.Engine ()
2020-11-20 22:13:32,733 INFO sqlalchemy.engine.base.Engine 
DROP TABLE node
2020-11-20 22:13:32,733 INFO sqlalchemy.engine.base.Engine ()
2020-11-20 22:13:32,735 INFO sqlalchemy.engine.base.Engine COMMIT
2020-11-20 22:13:32,736 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE node (
    id INTEGER NOT NULL, 
    parent_id INTEGER, 
    data VARCHAR(50), 
    PRIMARY KEY (id), 
    FOREIGN KEY(parent_id) REFERENCES node (id)
)


2020-11-20 22:13:32,737 INFO sqlalchemy.engine.base.Engine ()
2020-11-20 22:13:32,737 INFO sqlalchemy.engine.base.Engine COMMIT
2020-11-20 22:13:32,742 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2020-11-20 22:13:32,743 INFO sqlalchemy.engine.base.Engine INSERT INTO node (parent_id, data) VALUES (?, ?)
2020-11-20 22:13:32,743 INFO sqlalchemy.engine.base.Engine (None, 'root')
2020-11-20 22:13:32,746 INFO sqlalchemy.engine.base.Engine INSERT INTO node (parent_id, data) VALUES (?, ?)
2020-11-20 22:13:32,746 INFO sqlalchemy.engine.base.Engine (1, '1.1')
2020-11-20 22:13:32,746 INFO sqlalchemy.engine.base.Engine INSERT INTO node (parent_id, data) VALUES (?, ?)
2020-11-20 22:13:32,746 INFO sqlalchemy.engine.base.Engine (1, '1.2')
2020-11-20 22:13:32,747 INFO sqlalchemy.engine.base.Engine INSERT INTO node (parent_id, data) VALUES (?, ?)
2020-11-20 22:13:32,747 INFO sqlalchemy.engine.base.Engine (2, '1.1.1')
2020-11-20 22:13:32,747 INFO sqlalchemy.engine.base.Engine INSERT INTO node (parent_id, data) VALUES (?, ?)
2020-11-20 22:13:32,747 INFO sqlalchemy.engine.base.Engine (3, '1.2.1')
2020-11-20 22:13:32,747 INFO sqlalchemy.engine.base.Engine INSERT INTO node (parent_id, data) VALUES (?, ?)
2020-11-20 22:13:32,747 INFO sqlalchemy.engine.base.Engine (3, '1.2.2')
2020-11-20 22:13:32,748 INFO sqlalchemy.engine.base.Engine INSERT INTO node (parent_id, data) VALUES (?, ?)
2020-11-20 22:13:32,748 INFO sqlalchemy.engine.base.Engine (3, '1.2.3')
2020-11-20 22:13:32,748 INFO sqlalchemy.engine.base.Engine INSERT INTO node (parent_id, data) VALUES (?, ?)
2020-11-20 22:13:32,748 INFO sqlalchemy.engine.base.Engine (7, '1.2.3.1')
2020-11-20 22:13:32,748 INFO sqlalchemy.engine.base.Engine INSERT INTO node (parent_id, data) VALUES (?, ?)
2020-11-20 22:13:32,748 INFO sqlalchemy.engine.base.Engine (7, '1.2.3.2')
2020-11-20 22:13:32,749 INFO sqlalchemy.engine.base.Engine COMMIT
2020-11-20 22:13:32,753 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2020-11-20 22:13:32,753 INFO sqlalchemy.engine.base.Engine ()
2020-11-20 22:13:32,754 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2020-11-20 22:13:32,754 INFO sqlalchemy.engine.base.Engine ()
2020-11-20 22:13:32,754 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2020-11-20 22:13:32,755 INFO sqlalchemy.engine.base.Engine SELECT anon_1.node_id AS anon_1_node_id, anon_1.node_parent_id AS anon_1_node_parent_id, anon_1.node_data AS anon_1_node_data, node_1.id AS node_1_id, node_1.parent_id AS node_1_parent_id, node_1.data AS node_1_data, node_2.id AS node_2_id, node_2.parent_id AS node_2_parent_id, node_2.data AS node_2_data, node_3.id AS node_3_id, node_3.parent_id AS node_3_parent_id, node_3.data AS node_3_data 
FROM (SELECT node.id AS node_id, node.parent_id AS node_parent_id, node.data AS node_data 
FROM node 
WHERE node.parent_id IS NULL
 LIMIT ? OFFSET ?) AS anon_1 LEFT OUTER JOIN node AS node_3 ON anon_1.node_id = node_3.parent_id LEFT OUTER JOIN node AS node_2 ON node_3.id = node_2.parent_id LEFT OUTER JOIN node AS node_1 ON node_2.id = node_1.parent_id
2020-11-20 22:13:32,755 INFO sqlalchemy.engine.base.Engine (1, 0)
root
 1.1
  1.1.1
 1.2
  1.2.1
  1.2.2
  1.2.3
   1.2.3.1
2020-11-20 22:13:32,758 INFO sqlalchemy.engine.base.Engine SELECT node.id AS node_id, node.parent_id AS node_parent_id, node.data AS node_data, node_1.id AS node_1_id, node_1.parent_id AS node_1_parent_id, node_1.data AS node_1_data, node_2.id AS node_2_id, node_2.parent_id AS node_2_parent_id, node_2.data AS node_2_data, node_3.id AS node_3_id, node_3.parent_id AS node_3_parent_id, node_3.data AS node_3_data 
FROM node LEFT OUTER JOIN node AS node_3 ON node.id = node_3.parent_id LEFT OUTER JOIN node AS node_2 ON node_3.id = node_2.parent_id LEFT OUTER JOIN node AS node_1 ON node_2.id = node_1.parent_id 
WHERE ? = node.parent_id
2020-11-20 22:13:32,758 INFO sqlalchemy.engine.base.Engine (8,)
   1.2.3.2
2020-11-20 22:13:32,759 INFO sqlalchemy.engine.base.Engine SELECT node.id AS node_id, node.parent_id AS node_parent_id, node.data AS node_data, node_1.id AS node_1_id, node_1.parent_id AS node_1_parent_id, node_1.data AS node_1_data, node_2.id AS node_2_id, node_2.parent_id AS node_2_parent_id, node_2.data AS node_2_data, node_3.id AS node_3_id, node_3.parent_id AS node_3_parent_id, node_3.data AS node_3_data 
FROM node LEFT OUTER JOIN node AS node_3 ON node.id = node_3.parent_id LEFT OUTER JOIN node AS node_2 ON node_3.id = node_2.parent_id LEFT OUTER JOIN node AS node_1 ON node_2.id = node_1.parent_id 
WHERE ? = node.parent_id
2020-11-20 22:13:32,759 INFO sqlalchemy.engine.base.Engine (9,)
[Finished in 0.3s]

depth = 3, so the level 4 nodes are fetched later.