SQLA don't set FK in relationship if inheritance present

[Вася Гайкин]

I want build pseudo file system on DB tables and make several classes. But assign to item.parent attribute has no effect (NOT NULL constraint failed). It work if write item.parent_id = parent.id.

(in attanchment simple example project)

class Item( Base ):
    name = Column( String, nullable=False )

    parent_id = Column( Integer,
                        ForeignKey( 'Item.id',
                                    onupdate='cascade',
                                    ondelete='cascade',
                                    deferrable=True ),
                        nullable=False )

    parent = relationship( 'Folder',
                           foreign_keys=parent_id,
                           uselist=False,
                           post_update=True )

    _type = Column( String, nullable=False )
    __mapper_args__ = { 'polymorphic_on': _type }


class File( Item ):
    id = Column( Integer,
                 ForeignKey( 'Item.id',
                             onupdate='cascade',
                             ondelete='cascade' ),
                 primary_key=True )

    data = Column( LargeBinary, nullable=False )
    mime = Column( String, nullable=False )

    preview_id = Column( Integer,
                         ForeignKey( 'File.id',
                                     onupdate='cascade',
                                     ondelete='cascade' ) )

    preview = relationship( 'File', foreign_keys=preview_id, uselist=False )

    __mapper_args__ = { 'polymorphic_identity': 'file' }

part of utils.py

def load_file( path, parent ):
    data = open( path, 'rb' ).read()
    name = os.path.basename( path )

    file = File()
    file.name = name
    file.data = data
    file.mime = 'image/jpeg'

    file.parent_id = parent.id  # <--- OK
    # file.parent  = parent # <--- FAIL

    session.add( file )
    session.commit()

    return file

part of run.py

    main_file = load_file( '/tmp/example_2.jpg', usr )
    preview = load_file( '/tmp/example_3.jpg', bin_ )

    main_file.preview = preview  # <--- OK

    link = Link()
    link.target = root
    link.name = main_file.name
    # link.parent_id = root.id  # <--- OK
    link.parent = root  # <--- FAIL
    session.add( link )

    session.commit()

[Mike Bayer]

hello -

your "parent" relationship is misconfigured and should be corrected as follows:

    parent = relationship('Item',
                          foreign_keys=parent_id,

                          # BUG: many-to-one self referential requires
                          # remote side, see
                          # http://docs.sqlalchemy.org/en/latest/orm/self_referential.html
                          remote_side=id,

                          uselist=False,

                          # BUG: post_update means it will emit another
                          # UPDATE to fullfill this relationship; not an
                          # option due to parent_id NOT NULL
                          # post_update=True
                          )

complete example, derived from your zipfile (please do not send test cases as zipfiles, please use a single script):

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Item(Base):
    __tablename__ = 'item'

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)

    parent_id = Column(
        Integer,
        ForeignKey('item.id',
                   onupdate='cascade',
                   ondelete='cascade',
                   deferrable=True),
        nullable=False)

    parent = relationship('Item',
                          foreign_keys=parent_id,

                          # BUG: many-to-one self referential requires
                          # remote side, see
                          # http://docs.sqlalchemy.org/en/latest/orm/self_referential.html
                          remote_side=id,

                          uselist=False,

                          # BUG: post_update means it will emit another
                          # UPDATE to fullfill this relationship; not an
                          # option due to parent_id NOT NULL
                          # post_update=True
                          )

    _type = Column(String, nullable=False)
    __mapper_args__ = {'polymorphic_on': _type}


class Folder(Item):
    __tablename__ = 'folder'

    id = Column(Integer,
                ForeignKey('item.id',
                           onupdate='cascade',
                           ondelete='cascade'),
                primary_key=True)

    __mapper_args__ = {'polymorphic_identity': 'folder'}


class File(Item):
    __tablename__ = 'file'

    id = Column(Integer,
                ForeignKey('item.id',
                           onupdate='cascade',
                           ondelete='cascade'),
                primary_key=True)

    data = Column(String, nullable=False)
    mime = Column(String, nullable=False)

    __mapper_args__ = {'polymorphic_identity': 'file'}


e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

s = Session(e)

# POTENTIAL ISSUE: avoid using "0" for a primary key value, as it
# evaluates to false which can cause problems, also some databases
# might not like "0" in this context
root = Folder(id=0, parent_id=0, name="some folder")
s.add(root)
s.commit()


file = File(name='some name', data='some data', mime='image/jpeg')

file.parent = root

s.add(file)
s.commit()


assert file.parent is root

[Вася Гайкин]

thank you so match. Is it possible to restrict the parent to a link only to a Folder?

[Mike Bayer]

oh, sure - just make it Folder like you had:

parent = relationship('Folder',

try setting a non-Folder as parent and you'll see an error message during the flush.