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