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

engine = create_engine("sqlite:///:memory:", echo="debug")
Session = sessionmaker(bind=engine)
BASE = declarative_base()

session = Session()

class Node(BASE):
    __tablename__ = "nodes"
    id = Column(Integer, primary_key=True)

class ZoneNodeAssociation(BASE):
    __tablename__ = "zone_node_association"
    node_id = Column(Integer, ForeignKey('nodes.id'),
                     primary_key=True, nullable=False)
    zone_id = Column(Integer, ForeignKey('zones.id'),
                     primary_key=True, nullable=False)

class Zone(BASE):
    __tablename__ = "zones"
    id = Column(Integer, primary_key=True, nullable=False, autoincrement=True)
    name = Column(String(255), unique=True)
    nodes = relationship("Node",
                         secondary="zone_node_association",
                         backref="zones",)

BASE.metadata.create_all(engine)


node = Node()
zone = Zone()
session.add(node)
session.add(zone)
zone.nodes.append(node)
session.flush()

zna = ZoneNodeAssociation(zone_id=zone.id, node_id=node.id)
session.add(zna)
session.commit()