Can anyone explain to me why this raises an error ```import

Question

Can anyone explain to me why this raises an error ```import prefect import sqlalchemy as sql engine = sql create engine <postgresql tmp tmp localhost 5432 tmp> tmp schema = sql MetaData table = sql Table tmp tmp schema sql Column tmp sql Integer prefect task def create table tmp schema create all engine tables= table with prefect Flow tmp as flow create table flow storage = prefect environments storage Docker registry url= localhost 5000 python dependencies= sqlalchemy flow register ``` Raises ```Traceback most recent call last File issue py line 23 in lt module gt flow register File Users howardcornwell files dev misc prefect pickle issue venv lib python3 8 site packages prefect core flow py line 1437 in register registered flow = client register File Users howardcornwell files dev misc prefect pickle issue venv lib python3 8 site packages prefect client client py line 649 in register serialized flow = flow serialize build=build type Any File Users howardcornwell files dev misc prefect pickle issue venv lib python3 8 site packages prefect core flow py line 1299 in serialize storage = self storage build type Optional Storage File Users howardcornwell files dev misc prefect pickle issue venv lib python3 8 site packages prefect environments storage docker py line 293 in build self build image push=push File Users howardcornwell files dev misc prefect pickle issue venv lib python3 8 site packages prefect environments storage docker py line 329 in build image dockerfile path = self create dockerfile object directory=tempdir File Users howardcornwell files dev misc prefect pickle issue venv lib python3 8 site packages prefect environments storage docker py line 436 in create dockerfile object cloudpickle dump self flows flow name f File Users howardcornwell files dev misc prefect pickle issue venv lib python3 8 site packages cloudpickle cloudpickle fast py line 48 in dump CloudPickler file protocol=protocol buffer callback=buffer callback dump obj File Users howardcornwell files dev misc prefect pickle issue venv lib python3 8 site packages cloudpickle cloudpickle fast py line 548 in dump return Pickler dump self obj TypeError cannot pickle thread local object```

Zachary Hughes · Answer

Hi < Howard Cornwell> taking a look now Will be back to you with an answer ASAP

Zachary Hughes · Answer

This looks like an issue serializing the sqlalchemy engine I m not super familiar with what s going on under the hood of the sqlalchemy engine but moving the engine inside the `create table` task seems to have resolved the issue Is that a reasonable workaround for you We can also open a GitHub issue if you d like to keep digging

Howard Cornwell · Answer

Hey thanks for getting back I ve used a similar workaround in a few places this occurs e g ```def create company footprint table create table engine ingest schema arin company footprints task def create company footprint table Call separate function to avoid TypeError cannot pickle thread local object create company footprint table ``` I can use the workaround it s not a huge issue I was curious if there was a clear explanation I wan t aware of

Jeremiah · Answer

< Howard Cornwell> not all Python objects can be serialized to bytes and recovered Prefect uses a generous serialization protocol `cloudpickle` but it can t serialize the SQLAlchemy engine due to its reliance on threadlocals ```cloudpickle dumps sqlalchemy create engine TypeError Traceback most recent call last lt ipython input 8 a20a9641c605 gt in lt module gt gt 1 cloudpickle dumps sqlalchemy create engine usr local Caskroom miniconda base envs jlowin lib python3 8 site packages cloudpickle cloudpickle fast py in dumps obj protocol 60 with io BytesIO as file 61 cp = CloudPickler file protocol=protocol gt 62 cp dump obj 63 return file getvalue 64 usr local Caskroom miniconda base envs jlowin lib python3 8 site packages cloudpickle cloudpickle fast py in dump self obj 536 def dump self obj 537 try gt 538 return Pickler dump self obj 539 except RuntimeError as e 540 if recursion in e args 0 TypeError cannot pickle thread local object``` In your original code it was instantiated and serialized as a global variable for the task function in your updated code it is generated inside the task function and so it is never serialized

Howard Cornwell · Answer

That makes sense Thanks for the explanation I really appreciate it