mardi 9 mai 2017

sqlalchemy reflect missing a ForeignKey

I'm using the following code to reflect an already present database.

from sqlalchemy.engine.url import URL
from sqlalchemy import create_engine, Table, Column, MetaData
from sqlalchemy.orm import sessionmaker, relation, mapper

engine_URL = URL(drivername='mssql+pymssql',
                 username='username removed',
                 password="""password removed""",
                 host='host removed',
                 database='database removed')

engine = create_engine(engine_URL)
Session = sessionmaker(bind=engine)
metadata = MetaData()
metadata.reflect(bind=engine)

MAPPERS = {
}

repr_name = lambda t: '{}{}'.format(t[0].upper(), t[1:])

for table in metadata.tables:

    cls = None
    # 1. create class object
    print()
    print("Table({})".format(table))
    print(metadata.tables[table].primary_key)
    cls_name = repr_name(str(table))
    exec("""class %s(object): pass""" % cls_name)
    exec("""cls = %s""" % cls_name)

    # 2. collect relations by FK
    properties = {}
    for c in metadata.tables[table].columns:
        for fk in c.foreign_keys:
            print(fk)
            name = str(fk.column).split('.')[0]
            properties.update({
                name: relation(lambda: MAPPERS[repr_name(name)]),
            })

    # 3. map table to class object
    mapper(cls, metadata.tables[table], properties=properties)


    MAPPERS.update({cls_name: cls})

if __name__ == "__main__":

    #Create a session to use the tables
    session = Session()

    api = session.query(ApiSpecifications)
    print(api)

It is intended to gather the relationships between tables, report on those relationships and then attempt to perform a SELECT

The relevant reporting for the tables two tables in the sqlalchemy.exc.NoForeignKeysError is:

Table(ApiResponse)
PrimaryKeyConstraint(Column('Id', INTEGER(), table=<ApiResponse>, primary_key=True, nullable=False, default=Sequence('Id_identity', start=1, increment=1, metadata=MetaData(bind=None))))
ForeignKey('ApiSpecifications.Id')
ForeignKey('ApiDataType.Id')

Table(ApiSpecifications)
PrimaryKeyConstraint(Column('Id', INTEGER(), table=<ApiSpecifications>, primary_key=True, nullable=False, default=Sequence('Id_identity', start=1, increment=1, metadata=MetaData(bind=None))))
ForeignKey('ApiRequestType.Id')
ForeignKey('ApiScheme.Id')

Specifically the table ApiResponse does appear to have a Foreign Key constraint to the ApiSpecifications table

ForeignKey('ApiSpecifications.Id')

However I get the following exception when session.query(ApiSpecifications) is called

sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship ApiResponse.ApiSpecifications - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

Any thoughts on why this might be and how I may resolve this would be appreciated.





Aucun commentaire:

Enregistrer un commentaire