Need persistent PYTHON pandas/variables and/or system state. Each new runpython execution is "virgin"

[LisbonCoder]

Common difficulty:

Each time I run a .py mod, the moment it exits to come back to excel, whatever happened in the .py "dies".

What I need is persistence of data, variables or state in memory.

Example:
I have a big sheet full of data. Want to load it into pandas, then push it, then have it STICK there.

The most use will be if I can use it as an indexed data source, to pull stuff much faster than what excel lookups and indexes can (this really slows me down!), do searches and so on.

Besides, I want to run different routines and .py mods on the same dataset, change stuff, click on a transform of it, then another, see what works; run some iterative stuff.

When needed, then I'll pull it back into the sheet.

Any way to currently do this? Is it something that can be added?

[fzumstein]

Why not just work from Python/Jupyter notebook and pull/push pandas df instead of trying to run it from Excel?

Also have a look at: OPTIMIZED_CONNECTION (http://docs.xlwings.org/en/stable/vba.html#settings) and the UDF section including the macro decorator: http://docs.xlwings.org/en/stable/udfs.html#macros

[vermosen]

Hi,
I managed to get persistent objects with OPTIMIZED_CONNECTION set to True and using the macro decorator as indicated above but I had to tag my variable as global:

def setup():
    global foo
    foo = ...

Nethertheless, I fail to get the same behavior with variables defined at the module level...

[wkschwartz]

We have successfully preserved state between macro calls by copying data from Excel to a SQLite database (using an ORM such as Peewee or Django, but the former is lighter weight). A SQL database is designed for persistent state whereas global variables have all kinds of problems. I recommend using a database.

[fzumstein]

Closing this as the two recommendations should solve this.

[adriangb]

@fzumstein as far as I know, neither the OPTIMIZED_CONNECTION nor using the macro decorator work for Mac. Do you have any suggestions for Mac? In my case, I specifically want to use Excel as a frontend, so I need macros to call Python code (which needs to be statefull). Thanks!

[wkschwartz]

@adriangb I have a package for using Excel as a front end via xlwings. It's proprietary, and I don't want to spam the thread here, so if you're interested, please e-mail me directly: wkschwartz at gmail dot com

[fzumstein]

On mac, there's nothing that xlwings supports out of the box. You could simply pickle your objects for the next run or look at something like this: https://github.com/luozhijian/jupyterexcel