automap_base from MySQL doesn't setup all tables in Base.classes but they are in metadata.tables

[bkcsfi sfi]

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')}

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

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.

[Mike Bayer]

On 07/20/2016 04:44 PM, bkcsfi sfi 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

likely because these columns are not primary key columns. SQLAlchemy
ORM can't map a class to a table that has no primary key, and doesn't
alternatively establish this via the "primary_key" parameter of mapper().

>
>
> 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
>
> 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)?

you'd do it before. The
http://docs.sqlalchemy.org/en/latest/orm/extensions/automap.html?highlight=automap#using-automap-with-explicit-declarations
shows an example of this. In this case, you'd want to put
__mapper_args__ = {"primary_key": [ ... cols .. ]} here, and i think
those have to be the Column objects so you'd pretty much name the
TripManifests class and include the two Columns fully. or just stick
primary_key=True on each of those Column objects.

>
> 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.

yes you can use sqlacodegen: https://pypi.python.org/pypi/sqlacodegen

>
> 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.
>
>

> --
> 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+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Simon King]

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