CODEWITHSUNDEEP
pythonsqlalchemy
JimB
JimB

Reputation: 1057

SQLAlchemy MetaData.reflect() vs. automap_base.prepare()

It seems to me that MetaData.reflect() and sqlalchemy.ext.automap.prepare() tables should be able to be used interchangeably for many use cases, but they can't be.

The metadata.tables['mytable'] into conn.execute(select(...)) returns a sqlalchemy.engine.cursor.CursorResult and your iterator gets the columns directly (eg x.columnA).

But automap_base().classes.mytable into the same conn.execute(select(...)) returns a sqlalchemy.engine.result.ChunkedIteratorResult and you need x.mytable.columnA to get at the column.

The sqlalchemy.engine.Result() documention says as much:

New in version 1.4: The Result object provides a completely updated usage model and calling facade for SQLAlchemy Core and SQLAlchemy ORM. In Core, it forms the basis of the CursorResult object which replaces the previous ResultProxy interface. When using the ORM, a higher level object called ChunkedIteratorResult is normally used.

Can I generically convert one to the other? That is, some wrapper that works for every table without needing the table name?

What's the best futureproof way to do this? I want my code to be forward-looking to sqlalchemy 2.0. Does that mean I should move away from either automap or MetaData?

sqlalchemy 1.4.35

Upvotes: 0

Views: 1934

Answers (1)

JimB
JimB

Reputation: 1057

This is the difference between the Core and the ORM.

select() from a Table vs. ORM class

While the SQL generated in these examples looks the same whether we invoke select(user_table) or select(User), in the more general case they do not necessarily render the same thing, as an ORM-mapped class may be mapped to other kinds of “selectables” besides tables. The select() that’s against an ORM entity also indicates that ORM-mapped instances should be returned in a result, which is not the case when SELECTing from a Table object.

Don't hesitate to use the ORM. It's higher level, pythonic, cool, and automap is ORM.

Upvotes: 1

Related Questions