Many To Many Error Example

test_oracle_and_pg.py

import yaml
import datetime
import sqlalchemy as sa
import sqlalchemy.orm as saOrm

class Run(object):
    def __init__(self, **kwds):
        for key in kwds:
            setattr(self, key, kwds[key])
        return
    def __repr__(self):
        # Some pretty formatting
        inspected = sa.inspect(self)
        ret = {state.key:"{}".format(state.value) if not (isinstance(state.value, Run) or isinstance(state.value, list)) else "<Run>" for state in inspected.attrs}
        return "{}<{}>".format(self.__class__.__name__, yaml.safe_dump(ret, default_flow_style=False))

connection = "oracle://user:password@localhost:1521/xe"
engine = sa.create_engine(connection)
Session = saOrm.sessionmaker(bind=engine)
session = Session()
metadata = sa.MetaData(bind=engine)
tableNames = ["Runs", "Runs_Runs"]
tables = {}
for name in tableNames:
    print("Loading table={}".format(name))
    tables[name] = sa.Table(name, metadata, autoload=True, schema='example')
    pass


runJoinTable = tables['Runs_Runs']
runTable = tables['Runs']
saOrm.mapper(Run, runTable, version_id_col=runTable.columns.revision,
    properties={
        "children": saOrm.relationship(Run,
            secondary=runJoinTable,
            primaryjoin = runTable.c.runId == runJoinTable.c.parentRunId,
            secondaryjoin = runTable.c.runId == runJoinTable.c.childRunId,
            backref='parents')
    })

parentRun = session.query(Run).filter_by(runId=4).first()
print("ParentRun={}".format(parentRun))

childRun = Run(status='queued', \
    timeCreated=datetime.datetime.utcnow(), \
    timeUpdated=datetime.datetime.utcnow(), \
    parents=[parentRun])
print("ChildRun - {}".format(childRun))
session.add(childRun)
session.commit()

test_sqlite.py

import sqlalchemy as sa
import sqlalchemy.orm as saOrm
import yaml
import datetime

class Run(object):
    def __init__(self, **kwds):
        for key in kwds:
            setattr(self, key, kwds[key])
        return
    def __repr__(self):
        # Some pretty formatting
        inspected = sa.inspect(self)
        ret = {state.key:"{}".format(state.value) if not isinstance(state.value, Run) else "<Run>" for state in inspected.attrs}
        return "{}<{}>".format(self.__class__.__name__, yaml.safe_dump(ret, default_flow_style=False))

connection = "sqlite://"
engine = sa.create_engine(connection)
metadata = sa.MetaData(bind=engine)
tableDefs = """
  - name: Runs
    columns:
      - name: runId
        type: Integer
        kwds:
          primary_key: true
      - name: status
        type: Text
      - name: timeCreated
        type: DateTime
      - name: revision
        type: Integer
  - name: Runs_Runs
    columns:
      - name: parentRunId
        type: Integer
        foreignKey: Runs.runId
      - name: childRunId
        type: Integer
        foreignKey: Runs.runId
"""
#=============================== Setup ===================================#
tableDefs = yaml.load(tableDefs)
tables = {}
def buildColumn(columnConf):
    typeClass = getattr(sa, columnConf['type'])
    columnType = typeClass()
    columnKwds= columnConf.get('kwds', {})
    if 'foreignKey' in columnConf:
        foreignKeyInfo = columnConf['foreignKey']
        foreignKeyInfo = {"name": foreignKeyInfo}
        fkey = sa.ForeignKey(foreignKeyInfo['name'], ondelete=foreignKeyInfo.get('onDelete'), onupdate=foreignKeyInfo.get('onUpdate'))
    else:
        fkey = None
    print("Building column: name={}, type={}, foreignKey={}, kwds={}".format(columnConf['name'], columnType, fkey, columnKwds))
    column = sa.Column(columnConf['name'], columnType, fkey, **columnKwds)
    return column

def buildTable(tableDef):
    print("Building table={}".format(tableDef['name']))
    table = sa.Table(tableDef['name'], metadata, autoload=False)
    for columnConf in tableDef['columns']:
        column = buildColumn(columnConf)
        table.append_column(column)
    return table

for tableDef in tableDefs:
    name = tableDef['name']
    tables[name] = buildTable(tableDef)
    pass
metadata.create_all(engine)
#=============================== End Setup ===================================#
runJoinTable = tables['Runs_Runs']
runTable = tables['Runs']
saOrm.mapper(Run, runTable, version_id_col=runTable.columns.revision,
    properties={
        "children": saOrm.relationship(Run,
            secondary=runJoinTable,
            primaryjoin = runTable.c.runId == runJoinTable.c.parentRunId,
            secondaryjoin = runTable.c.runId == runJoinTable.c.childRunId,
            backref='parents')
    })

Session = saOrm.sessionmaker(bind=engine)
session = Session()
parentRun = Run(status='done')
session.add(parentRun)
session.commit()

# Get a new session
Session = saOrm.sessionmaker(bind=engine)
session = Session()
parentRun = session.query(Run).first()
childRun = Run(status='queued', \
    timeCreated=datetime.datetime.utcnow(), \
    timeUpdated=datetime.datetime.utcnow(), \
    parents=[parentRun])
session.add(childRun)
session.commit()