Caching the result of automap?

[TomaszParadysz-SlashDB]

Is there a way to cache/save the result of the reflect and automap?
I know that the MetaData object can be pickled, but recreating the classes still takes quite some time (i.e. doing the Base.prepare())
(From my testing, .prepare takes about twice as long as creating the metadata object itself (excluding I/O))
And because the result should always be the same (if the schema doesn't change), it would be nice to not have to go through that every time.

I tried pickling the Base class, but it results in an exception:

_pickle.PicklingError: Can't pickle <class 'sqlalchemy.ext.automap.Base'>: attribute lookup Base on sqlalchemy.ext.automap failed

I assume it's because Base is created dynamically at runtime.

Are there any solutions? Or this has to always happen at runtime and there's no way around that?

[zzzeek]

Python classes are not pickleable (unless something has changed in py 3.12, 3.13 maybe) and in this case yes those classes are dynamically generated so they are really not directly pickleable - it's still possible in theory for the structure of the attributes on these classes to be serialized, and to create deserialization functions that re-apply these attributes to newly generated classes while skipping the heuristics used in creating and configuring mappers, however SQLAlchemy has nothing like that in place and it would be extremely labor intensive to create and maintain such a system.

[TomaszParadysz-SlashDB]

That's what I thought, thanks for confirming!

[CaselIT]

Hi,

You can look into sqlacodegen that's an external library that can generate the python code for the classes given the reflected structure. https://github.com/agronholm/sqlacodegen

[TomaszParadysz-SlashDB]

I tried that, but importing the generated file takes about the same amount of time as loading the pickled metadata and creating classes from that. The way I understand it is, that in both cases the mapper object (or something else) needs to be created, relations need to be discovered and set as attributes, etc.