Issues

Issue #15 new

ordering of tables makes some relationships invalid

jonorthwash
created an issue

Using MySQL with InnoDB support, sqlite3, and probably numerous other engines allows for the specification of foreign keys directly in the database.

sqlacodegen responds to these specifications correctly in that it creates code for these relationships. However, in the code, it places certain classes after other classes that link to them.

For example, if a Participant table/class has a foreign key in an Individual table/class, the Individual class may follow the Participant class, resulting in sqlalchemy throwing an error like the following:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'participants.id' could not find table 'individuals' with which to generate a foreign key to target column 'id'

The classes can be rearranged manually for expected results (i.e., no errors being thrown).

Comments (3)

  1. Alex Grönholm repo owner

    But all the references are quoted. That is, they are only resolved after all the classes have been defined, when the mapped classes are actually used for the first time. Can you post an example that reproduces this?

  2. jonorthwash reporter

    I've pared down the database a bit while still keeping the error producible, but I didn't make a fully minimal test case. My apologies if this is too much...

    Schema of source sqlite3:

    CREATE TABLE `dups` (
      `id` INTEGER NULL  DEFAULT NULL,
      `person1` INTEGER NULL DEFAULT NULL,
      `person2` INTEGER NULL DEFAULT NULL,
      `confidence` VARCHAR(10) NULL DEFAULT NULL,
      PRIMARY KEY (`id`)
    ,
        FOREIGN KEY (person1) REFERENCES `individuals` (`id`),
        FOREIGN KEY (person2) REFERENCES `individuals` (`id`)
    );
    CREATE TABLE `individuals` (
      `id` INTEGER NULL  DEFAULT NULL,
      `fullname` VARCHAR(255) NULL DEFAULT NULL,
      `given_name` VARCHAR(255) NULL DEFAULT NULL,
      `surname` VARCHAR(255) NULL DEFAULT NULL,
      `father` INTEGER NULL DEFAULT NULL,
      `mother` INTEGER NULL DEFAULT NULL,
      `gender` CHAR NULL DEFAULT NULL,
      PRIMARY KEY (`id`)
    ,
        FOREIGN KEY (father) REFERENCES `individuals` (`id`),
        FOREIGN KEY (mother) REFERENCES `individuals` (`id`)
    );
    CREATE TABLE `log` (
      `id` INTEGER NULL  DEFAULT NULL,
      `user` VARCHAR(255) NULL DEFAULT NULL,
      `table` VARCHAR(63) NULL DEFAULT NULL,
      `entry_id` INTEGER NULL DEFAULT NULL,
      `field` VARCHAR(63) NULL DEFAULT NULL,
      `old_value` BLOB NULL DEFAULT NULL,
      `new_value` BLOB NULL DEFAULT NULL,
      PRIMARY KEY (`id`)
    );
    CREATE TABLE `participants` (
      `id` INTEGER NULL  DEFAULT NULL,
      `event` INTEGER NULL DEFAULT NULL,
      `role` VARCHAR(255) NULL DEFAULT NULL ,
      PRIMARY KEY (`id`)
    ,
        FOREIGN KEY (id) REFERENCES `tamgas` (`id`),
        FOREIGN KEY (id) REFERENCES `individuals` (`id`)
    );
    CREATE TABLE `tamgas` (
      `id` INTEGER NULL  DEFAULT NULL,
      `uri` VARCHAR(255) NULL DEFAULT NULL,
      `name` VARCHAR(20) NULL DEFAULT NULL,
      PRIMARY KEY (`id`)
    );
    

    Generated python:

    # coding: utf-8
    from sqlalchemy import Column, ForeignKey, Integer, LargeBinary, String
    from sqlalchemy.orm import relationship
    from sqlalchemy.ext.declarative import declarative_base
    
    
    Base = declarative_base()
    metadata = Base.metadata
    
    
    class Dup(Base):
        __tablename__ = 'dups'
    
        id = Column(Integer, primary_key=True)
        person1 = Column(ForeignKey('individuals.id'), server_default='NULL')
        person2 = Column(ForeignKey('individuals.id'), server_default='NULL')
        confidence = Column(String(10), server_default='NULL')
    
        individual = relationship('Individual', primaryjoin='Dup.person1 == Individual.id')
        individual1 = relationship('Individual', primaryjoin='Dup.person2 == Individual.id')
    
    
    class Individual(Base):
        __tablename__ = 'individuals'
    
        id = Column(Integer, primary_key=True)
        fullname = Column(String(255), server_default='NULL')
        given_name = Column(String(255), server_default='NULL')
        surname = Column(String(255), server_default='NULL')
        father = Column(ForeignKey('individuals.id'), server_default='NULL')
        mother = Column(ForeignKey('individuals.id'), server_default='NULL')
        gender = Column(String, server_default='NULL')
    
        parent = relationship('Individual', remote_side=[id], primaryjoin='Individual.father == Individual.id')
        parent1 = relationship('Individual', remote_side=[id], primaryjoin='Individual.mother == Individual.id')
    
    
    class Participant(Individual):
        __tablename__ = 'participants'
    
        id = Column(ForeignKey('individuals.id'), ForeignKey('tamgas.id'), primary_key=True)
        event = Column(Integer, server_default='NULL')
        role = Column(String(255), server_default='NULL')
    
        tamga = relationship('Tamga', uselist=False)
    
    
    class Log(Base):
        __tablename__ = 'log'
    
        id = Column(Integer, primary_key=True)
        user = Column(String(255), server_default='NULL')
        table = Column(String(63), server_default='NULL')
        entry_id = Column(Integer, server_default='NULL')
        field = Column(String(63), server_default='NULL')
        old_value = Column(LargeBinary, server_default='NULL')
        new_value = Column(LargeBinary, server_default='NULL')
    
    
    class Tamga(Base):
        __tablename__ = 'tamgas'
    
        id = Column(Integer, primary_key=True)
        uri = Column(String(255), server_default='NULL')
        name = Column(String(20), server_default='NULL')
    

    Error message when python3 runs the generated python file:

    Traceback (most recent call last):
      File "../2014-05-21.py", line 38, in <module>
        class Participant(Individual):
      File "/usr/local/lib/python3.4/site-packages/sqlalchemy/ext/declarative/api.py", line 53, in __init__
        _as_declarative(cls, classname, cls.__dict__)
      File "/usr/local/lib/python3.4/site-packages/sqlalchemy/ext/declarative/base.py", line 326, in _as_declarative
        mt.map()
      File "/usr/local/lib/python3.4/site-packages/sqlalchemy/ext/declarative/base.py", line 409, in map
        **mapper_args
      File "<string>", line 2, in mapper
      File "/usr/local/lib/python3.4/site-packages/sqlalchemy/orm/mapper.py", line 613, in __init__
        self._configure_inheritance()
      File "/usr/local/lib/python3.4/site-packages/sqlalchemy/orm/mapper.py", line 929, in _configure_inheritance
        consider_as_foreign_keys=fks)
      File "/usr/local/lib/python3.4/site-packages/sqlalchemy/sql/util.py", line 425, in criterion_as_pairs
        visitors.traverse(expression, {}, {'binary': visit_binary})
      File "/usr/local/lib/python3.4/site-packages/sqlalchemy/sql/visitors.py", line 255, in traverse
        return traverse_using(iterate(obj, opts), obj, visitors)
      File "/usr/local/lib/python3.4/site-packages/sqlalchemy/sql/visitors.py", line 246, in traverse_using
        meth(target)
      File "/usr/local/lib/python3.4/site-packages/sqlalchemy/sql/util.py", line 422, in visit_binary
        elif binary.right.references(binary.left):
      File "/usr/local/lib/python3.4/site-packages/sqlalchemy/sql/schema.py", line 1100, in references
        if fk.column.proxy_set.intersection(column.proxy_set):
      File "/usr/local/lib/python3.4/site-packages/sqlalchemy/util/langhelpers.py", line 712, in __get__
        obj.__dict__[self.__name__] = result = self.fget(obj)
      File "/usr/local/lib/python3.4/site-packages/sqlalchemy/sql/schema.py", line 1646, in column
        tablekey)
    sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'participants.id' could not find table 'tamgas' with which to generate a foreign key to target column 'id'
    

    The behaviour seems a little inconsistent. When I repeatedly regenerate the sqlite database (from a source schema) and then rerun sqlacodegen, sometimes this behaviour appears, and sometimes it doesn't.

  3. Alex Grönholm repo owner

    Curiously, the order doesn't seem to be a problem if I remove the other foreign key from the PK column of Participant. Also, a very real bug here seems to be the generation of server_default='NULL'. I have never seen this before.

  4. Log in to comment