On Wed, Jul 20, 2016 at 9:44 PM, bkcsfi sfi <bkc...@gmail.com> wrote:
> I have a legacy MySQL database that I am working with sqla version 1.0.11
> and MySQL-Python engine (just upgraded to 1.0.14, problem persists)
>
> I use automap_base and prepare with reflect=True
>
> some of the tables in this database are association tables. Those tables do
> show up in metadata, e.g.
>
>> In [74]: Base.metadata.tables['TripManifests']
>> Out[74]: Table('TripManifests', MetaData(bind=None), Column('trip_id',
>> INTEGER(display_width=11), ForeignKey(u'Trips.id'), table=<TripManifests>,
>> nullable=False), Column('manifest_id', INTEGER(display_width=11),
>> table=<TripManifests>, nullable=False), schema=None)
>
>
>
> But the table isn't in Base.classes
>
>> In [75]: Base.classes.TripManifests
>> AttributeError: TripManifests
>
>
>
> The TripManifests table joins the Manifests table to the Trips table,
> neither of which appear to show a fk to each or nor the TripManifests Table
>
>> In [80]: Base.metadata.tables['Trips'].foreign_keys
>> Out[80]: {ForeignKey(u'Users.id'), ForeignKey(u'TripStatuses.id')}
>> In [81]: Base.metadata.tables['Manifests'].foreign_keys
>> Out[81]:
>> {ForeignKey(u'Users.id'),
>> ForeignKey(u'People.id'),
>> ForeignKey(u'Lists.id'),
>> ForeignKey(u'Equipment.id'),
>> ForeignKey(u'Equipment.id')}
The foreign keys are pointing in the opposite direction.
TripManifests.trip_id is a foreign key pointing at Trips.id. I imagine
that TripManifests.manifest_id is supposed to be a foreign key
pointing at Manifests.id, but either SA isn't detecting it, or the
database doesn't actually have that as a proper constraint.
>
>
>
> Since TripManifests is not Base.classes I'm not sure how to create an ORM
> query using joins. I'd be ok with manually specifying the .join() conditions
> if that would work, but I haven't seen an example of doing that w/o using
> Base.classes
>
Since TripManifests is a plain association table, there's not normally
any reason to map it directly. I think automap is supposed to detect
these and configure appropriate relationships:
http://docs.sqlalchemy.org/en/latest/orm/extensions/automap.html#many-to-many-relationships
But since the manifest_id column is not an actual foreign key, that
relationship hasn't been configured. Before investigating other
options, I think it would be worth finding out why this foreign key is
missing.
> Alternatively I could try manually adding this class to Base but I haven't
> been able to get that to work, does that need to be done before or after
> prepare(reflect=True)?
>
> Ultimately I would like to get away from using reflection. Does anyone know
> of a tool that can reflect and then generate the declarative classes and
> relationships as Python source.. which I could then hand-edit.
>
> Moving forward I could then use alembic to manage the DB schema.. Though it
> looks like adding a column would require that I use alembic to update the
> database itself, then I'd still have to edit the Python declaration as well
> (assuming I didn't want to use reflection), but that's a different
> discussion.
FWIW, I find alembic's autogeneration feature very handy here. I edit
the Python class definitions then alembic inspects the database and
generates an appropriate migration script:
http://alembic.zzzcomputing.com/en/latest/autogenerate.html
Hope that helps,
Simon
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
