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