New project item: Gdx Export - [merged] #1201

Closed spine-o-bot closed 3 years ago

In GitLab by @soininen on Sep 27, 2019, 13:28

_Merges issue#274_convert_db_togdx -> dev

This MR adds a new project item, Gdx Export to Toolbox. The item allows exporting Data Stores to GAMS .gdx files. The current implementation should give minimum support for running the TIMES model within Toolbox.

Implements #274.

What has been done here:

Added spinetoolbox.spine_io.exporters.gdx module which does the heavy lifting. Using export functionality needs GAMS and its Python bindings installed. Note that GAMS has not become a requirement and that the export functionality is merely disabled if GAMS is not found.
Added spinetoolbox.project_items.gdx_export subpackage for the project item
Some unit tests, documentation

About item naming: we discussed in the issue that perhaps this item should be called Data Export while Data Interface should correspondingly be renamed to Data Import. No renaming have been done as part of this MR as perhaps it is better to execute the renaming under a separate issue. The changes introduced here are already quite large.

To test:

Don't have GAMS installed? Fear not! You can still check if everything else works except executing the DAG and that the execution should give meaningful failure messages and not crash Toolbox.

Need to install GAMS Python bindings? Please follow the official tutorial. Note that you may need to pay attention to supported Python versions (3.6) and that both Python and GAMS have to be compiled for the same bitness (32 vs. 64).

A test database is available for download: times_mock.sqlite

Create a new project
Add a Data Store, link the above database to it
Add a Gdx Export
Connect the Data Store to the Gdx Export
Give a name to the exported file in Gdx Export's properties tab
Open the Gdx Export settings window, check that the database is represented there.
Execute the project.
If you have GAMS, open the generated .gdx there and check that sets etc. got exported in the order they were defined in the Gdx Export settings window.

Other thinks to look at:

Build the documentation, check that the updated Project items and Importing and exporting data sections are readable and the links work

In GitLab by @soininen on Sep 27, 2019, 13:37

added 22 commits

43301de3...efc3f86b - 21 commits from branch dev
2f69e7de - Merge branch 'dev' into issue#274_convert_db_to_gdx

Compare with previous version

In GitLab by @PekkaSavolainen on Sep 27, 2019, 17:55

EDIT Let's call this Issue A for clarity.

This happens sometimes when clicking on 'Ok' button in GDX Export Settings Window. Nothing has been changed and that file path does exist. I'll try to figure out the steps to reproduce this if needed.

Traceback (most recent call last):
  File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\project_items\gdx_export\gdx_export.py", line 191, in <lambda>
    settings_window.button_box.accepted.connect(lambda: self._settings_approved(database_path))
  File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\project_items\gdx_export\gdx_export.py", line 209, in _settings_approved
    settings_window = self._settings_windows[database_path]
KeyError: 'C:/data/GIT/SPINETOOLBOX/projects/gdx_export_test/database/times_mock.sqlite'

In GitLab by @PekkaSavolainen on Sep 27, 2019, 17:59

EDIT Let's call this Issue B.

It seems that I actually had the gams package installed on my Python but it was a couple of years ago so this happens when executing a Data Store -> GDX Export DAG.

Traceback (most recent call last):
  File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\executioner.py", line 376, in item_execution_finished
    self.execute_project_item(item_name)
  File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\executioner.py", line 351, in execute_project_item
    self.running_item.execute()
  File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\project_items\gdx_export\gdx_export.py", line 135, in execute
    _, gams_database = gdx.to_gams_workspace(database_map, settings)
  File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\spine_io\exporters\gdx.py", line 390, in to_gams_workspace
    gams_workspace = make_gams_workspace()
  File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\spine_io\exporters\gdx.py", line 299, in make_gams_workspace
    return gams.GamsWorkspace()
AttributeError: module 'gams' has no attribute 'GamsWorkspace'

In GitLab by @PekkaSavolainen on Sep 27, 2019, 18:07

added 1 commit

d36fcbc6 - Updated docstrings and added the url as the Tooltip for the url_field QLineEdit.

Compare with previous version

In GitLab by @PekkaSavolainen on Sep 27, 2019, 18:10

Issue C

The Settings window (File->Settings) does not open.

In GitLab by @PekkaSavolainen on Sep 27, 2019, 18:12

About Issue B. How do you install the gams package and the other requirements for using the GDX Export item? pip install gams does not work.

In GitLab by @PekkaSavolainen on Sep 27, 2019, 18:45

added 1 commit

0d5cdb5c - Changed GDX Export Settings window modality to WindowModal. Changed...

Compare with previous version

In GitLab by @PekkaSavolainen on Sep 27, 2019, 18:50

Issue D

GDX Export settings window modality. I changed it to WindowModal. Do users really need to have more than one of those windows open at a time? What do you think?

In GitLab by @PekkaSavolainen on Sep 27, 2019, 19:01

Issue E

I changed the GdxExportSettings class to inherit QWidget instead of QMainWindow. Does it need to be a QMainWindow? To me the inheritance structure seems a bit strange in case we have the ToolboxUI (QMainWindow) which has a child GdxExportProperties (QWidget) which has a child GdxExportSettings (QMainWindow). Also, I don't know what setCentralWidget actually does.

I know that there probably are other sub QMainWindows with the same inheritance structure in our app. We should probably change them to inherit from QWidget as well.

In GitLab by @soininen on Sep 30, 2019, 08:11

The Export item can export more than a single database on execution and each database can have its own settings window so yes, I expect there can be more of them open at the same time. Which reminds me that the window title should incorporate the database path to differentiate the windows.

In GitLab by @soininen on Sep 30, 2019, 08:19

My long-term plan was to add a menu and a status bar to the Settings window, thus the choice of QMainWindow as base class. QMainWindow supports menus and status bars out of the box, hence the setCentralWidget() method. One should not fret over the class names here: QWidget, QMainWindow and QDialog can all serve as any window in an application. They are differentiated just by their default window flags, default widgets etc. which can be tweaked at will.

In GitLab by @soininen on Sep 30, 2019, 08:24

changed the description

In GitLab by @soininen on Sep 30, 2019, 08:25

Sorry, I forgot to add the instructions to the Description. Please follow the tutorial here.

In GitLab by @soininen on Sep 30, 2019, 08:42

That is a curious bug. I cannot reproduce it on my system but it looks like you've managed to click the OK button twice before the window closed or something to that effect. I could add a check to prevent the Traceback but I would like to figure out the ultimate reason. Would you be so kind to figure out the steps to reproduce this?

In GitLab by @soininen on Sep 30, 2019, 08:45

On a second thought maybe the Settings window should be a modal window for starters. One less window getting hidden behind everything else, lost in the desktop's jungle. We can change the modality afterwards if a use case arises.

In GitLab by @soininen on Sep 30, 2019, 08:48

Seems that we would need to check the GAMS version in addition to its existence.

In GitLab by @soininen on Sep 30, 2019, 08:50

I think we can leave the window as a QWidget for now. I can re-evaluate the situation if I ever get to implement the menu/status bar.

In GitLab by @soininen on Sep 30, 2019, 09:20

added 1 commit

c621681c - Check GAMS version in available(), rename

Compare with previous version

In GitLab by @soininen on Sep 30, 2019, 10:21

added 1 commit

00f12879 - Fix settings and tool template windows

Compare with previous version

In GitLab by @soininen on Sep 30, 2019, 10:23

Nice catch! There were some changes that should never have landed here but somehow managed to filter through all the merges and rebases I had to do because of the project item refactorings. Should be fixed now.

In GitLab by @soininen on Sep 30, 2019, 10:24

Added the check. If you did not update GAMS yet, perhaps you could give it another try? Just to see if you get a reasonable error message instead of a traceback.

In GitLab by @PekkaSavolainen on Sep 30, 2019, 11:59

Settings window works again.

In GitLab by @PekkaSavolainen on Sep 30, 2019, 12:02

I have GAMS 24.0 (32-bit) and 24.1 (64-bit). Looks like my gams python package is very old though since it does not have 'GamsWorkspace' attribute. It still gives me basically the same error

Traceback (most recent call last):
  File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\executioner.py", line 376, in item_execution_finished
    self.execute_project_item(item_name)
  File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\executioner.py", line 351, in execute_project_item
    self.running_item.execute()
  File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\project_items\gdx_export\gdx_export.py", line 121, in execute
    availability_error = gdx.gams_import_error()
  File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\spine_io\exporters\gdx.py", line 559, in gams_import_error
    if gams.GamsWorkspace.api_major_rel_number < 24 and gams.GamsWorkspace.api_gold_rel_number < 1:
AttributeError: module 'gams' has no attribute 'GamsWorkspace'

In GitLab by @PekkaSavolainen on Sep 30, 2019, 12:21

Would it be possible to have just one window for all databases connected to the GDX export item. Maybe each database setting would be one tab in the gdx export settings window.

Example. GDX Export 1 item has 3 databases connected to it and GDX Export 2 item has 5 databases connected to it. In my suggestion, GDX Export 1 would have a settings window with 3 tabs and GDX Export 2 would have a settings window with 5 tabs. Instead of 8 separate window's.

But if you think it's ok as a modal window, then let's keep it for now. If my suggestion makes sense, we can create a new issue for it.

In GitLab by @PekkaSavolainen on Sep 30, 2019, 12:34

I'll try to remember what I did. No luck yet.

In GitLab by @PekkaSavolainen on Sep 30, 2019, 12:41

Maybe this is just a pet peeve of mine. At some point I thought that it is not allowed to have more than one QMainWindow in an application. This turned out to be a wrong assumption and I'm still trying to wrap head around this.

But yes, it's true that the QMainWindow has a menu and a status bar built-in so if there's a need for those then QMainWindow is the way to go.

In GitLab by @soininen on Sep 30, 2019, 12:44

I think it is a good idea to go with a single settings window for now unless somebody wants to compare things between multiple settings windows or such. How to organize changing the databases in the window is another question.

Currently user chooses the database from the Gdx Export's properties tab in the main window. There is a separate Settings... button for each database.

What you are proposing is a single Settings... button in the properties tab and a tabulated interface in the settings window.

The tabbed settings window would mainly allow quick switching between the different databases. With the current implementation user has to first close the settings window then open it for another database from the properties tab. I think tabbed window would be a nice little usability improvement but I don't know how ofter users would need to do that. Perhaps we should keep the option in mind for the time being and gather a bit more experience on how this item gets used eventually.

In GitLab by @soininen on Sep 30, 2019, 12:49

If I understood GAMS release notes correctly, 24.0.1 should have been the first version to ship with the 'object-oriented Python API'. Can you check if

from gams.workspace import GamsWorkspace

works in your Python interpreter?

In GitLab by @soininen on Sep 30, 2019, 12:52

By the way, GAMS has to have the same bitness as your Python interpreter (32 vs. 64).

In GitLab by @PekkaSavolainen on Sep 30, 2019, 13:13

Ok, sorry, I did not even check what happens now when you have multiple databases connected to one GDX Export. I think the way it now works makes sense. Let's see what user's think.

In GitLab by @PekkaSavolainen on Sep 30, 2019, 13:35

No. There's no gams.workspace attribute.

In both my GAMS installations, the \..\apifiles\Python\ directory only has \api and \api_26 directories. I haven't got a clue, how the gams package ended up in my Python 3.6 site-packages.

Maybe I should just upgrade to a newer version.

In GitLab by @soininen on Sep 30, 2019, 13:44

Maybe... I think api_26 refers to Python version 2.6 :) There is a way, though, to force install the bindings regardless of Python's version. I tried it to get GAMS to work with Python 3.7 but no luck.

In GitLab by @PekkaSavolainen on Sep 30, 2019, 18:42

I did get this to reappear. It happens when doing this:

Open GDX settings
Close it from top-left corner menu (spine logo) OR from top-right X button
Open GDX settings
Click Ok or Cancel

Traceback ensues.

In GitLab by @soininen on Oct 1, 2019, 08:32

added 1 commit

1e0bfa19 - Fix Gdx Export settings window closing

Compare with previous version

In GitLab by @soininen on Oct 1, 2019, 08:38

This is now hopefully fixed. I had forgotten that there are other ways to close a window than the 'OK' and 'Cancel' buttons and then there was an issue regarding the 'OK' button being connected to both close and _approve_settings and no-one knowing which slot would be invoked first. Now closing the window from anywhere else than from 'OK' just discard everything while 'OK' first calls _approve_settings and only then close.

In GitLab by @ererkka on Oct 1, 2019, 09:08

Yes, api_26 refers to Python 2.6. The default api is for Python 2.7.

In GitLab by @PekkaSavolainen on Oct 1, 2019, 10:43

My test journal

I installed gams package on Python 3.6 (64-bit) from C:\GAMS\win32\24.0\apifiles\Python\api (32-bit) by doing python gamssetup.py install. Executing the example project prints this to Event Log

[1-10-2019 10:33:48] ---------------------------------------
[1-10-2019 10:33:48] Executing All Directed Acyclic Graphs
[1-10-2019 10:33:48] Starting DAG 1/1
[1-10-2019 10:33:48] Order: Database -> GDX
[1-10-2019 10:33:48] ---------------------------------------
[1-10-2019 10:33:48] 
[1-10-2019 10:33:49] Executing Data Store Database
[1-10-2019 10:33:49] ***
[1-10-2019 10:33:49] ***
[1-10-2019 10:33:49] Could not load the `gams` package. No GAMS Python binding found.
[1-10-2019 10:33:49] DAG 1/1 finished
[1-10-2019 10:33:49] Execution complete

Maybe it should say Executing GDX before printing the Could not load... error since executing the Data Store was not the problem.

In GitLab by @ererkka on Oct 1, 2019, 11:01

Works fine on my system (Win 10, Python 3.6, GAMS 24.9.2, 64-bit)

[1-10-2019 10:54:02] Project gdx_test is now open
[1-10-2019 10:54:07] 
[1-10-2019 10:54:07] ---------------------------------------
[1-10-2019 10:54:07] Executing All Directed Acyclic Graphs
[1-10-2019 10:54:07] Starting DAG 1/1
[1-10-2019 10:54:07] Order: ds1 -> export
[1-10-2019 10:54:07] ---------------------------------------
[1-10-2019 10:54:07] 
[1-10-2019 10:54:07] Executing Data Store ds1
[1-10-2019 10:54:07] ***
[1-10-2019 10:54:07] ***
[1-10-2019 10:54:07] 
[1-10-2019 10:54:07] Executing Gdx Export export
[1-10-2019 10:54:07] ***
[1-10-2019 10:54:09] DAG 1/1 finished
[1-10-2019 10:54:09] Execution complete

Result is a gdx file with 6 sets and 4 parameters:

> gdxdump test.gdx Symbols
   Symbol      Dim Type  Explanatory text
 1 ALL_REG       1  Set
 2 ALL_TS        1  Set
 3 COM           1  Set
 4 COM_GRP       1  Set
 5 globals       1  Set  Global TIMES parameters
 6 G_DYEAR       1  Par
 7 G_OVERLAP     1  Par
 8 healthiness   1  Par
 9 price         1  Par
10 REG           1  Set

In GitLab by @PekkaSavolainen on Oct 1, 2019, 11:44

Next I manually deleted the site-packages/gams directory along with the gams-...egg-info file. Then I installed gams package again from C:\GAMS\win64\24.1\apifiles\Python\api (64-bit) by doing python setup.py install. I tried executing the project again but it gives me the same print out to Event Log.

[1-10-2019 11:35:25] Executing All Directed Acyclic Graphs
[1-10-2019 11:35:25] Starting DAG 1/1
[1-10-2019 11:35:25] Order: Database -> GDX
[1-10-2019 11:35:25] ---------------------------------------
[1-10-2019 11:35:25] 
[1-10-2019 11:35:25] Executing Data Store Database
[1-10-2019 11:35:25] ***
[1-10-2019 11:35:25] ***
[1-10-2019 11:35:25] Could not load the `gams` package. No GAMS Python binding found.
[1-10-2019 11:35:25] DAG 1/1 finished
[1-10-2019 11:35:25] Execution complete

This should have worked, right?

In GitLab by @ererkka on Oct 1, 2019, 11:47

Files in C:\\GAMS\\win64\\24.1\\apifiles\\Python\\api should be for Python 2.7. What Python version are u using?

In GitLab by @ererkka on Oct 1, 2019, 11:48

Do you have folder api_36?

In GitLab by @PekkaSavolainen on Oct 1, 2019, 12:01

There's only

C:\\GAMS\\win64\\24.1\\apifiles\\Python\\api

and

C:\\GAMS\\win64\\24.1\\apifiles\\Python\\api26

directories. I used Python 3.6 to install and there were no error messages.

In GitLab by @ererkka on Oct 1, 2019, 12:08

Strange. Seems that support for Python 3.6 was only introduced in GAMS v24.8.4 (https://www.gams.com/latest/docs/API_PY_RN.html).

In GitLab by @PekkaSavolainen on Oct 1, 2019, 12:13

It does say in the tutorial https://www.gams.com/latest/docs/API_PY_TUTORIAL.html quite clearly that

while the API itself is found in apifiles/Python/api for Python 2.7, in apifiles/Python/api_34 for Python 3.4 and in apifiles/Python/api_36 for Python 3.6.

In GitLab by @PekkaSavolainen on Oct 1, 2019, 12:40

Now I manually uninstalled gams package and the other related packages from Python 3.6. Then I installed the api C:\GAMS\win64\24.1\apifiles\Python\api to Python 2.7. Then I started C:\Python27\python.exe and tried import gams and this happened.

C:\Python27>python
Python 2.7.14 (v2.7.14:84471935ed, Sep 16 2017, 20:19:30) [MSC v.1500 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import gams
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Python27\lib\site-packages\gams\__init__.py", line 116, in <module>
    from workspace import *
ImportError: DLL load failed: %1 is not a valid Win32 application.

Oops, my Python 2.7 was a 32-bit and I installed the 64-bit bindings on it.

In GitLab by @ererkka on Oct 1, 2019, 12:46

Does Spine Toolbox still run on Python 2.7? I don’t think we need to support the legacy Python anymore.

In GitLab by @soininen on Oct 1, 2019, 12:47

Maybe it should say Executing GDX before printing the Could not load... error

True, I will improve logging. In general, at the moment the items themselves are responsible for all logging. I think we could offload some of that to the ProjectItem base class, for example the 'Executing item ...' part.

In GitLab by @soininen on Oct 1, 2019, 12:51

For me the installation was a breeze once I got these right:

Python version has to be 3.6 for api_36
The bitness must match between GAMS and Python

In GitLab by @PekkaSavolainen on Oct 1, 2019, 12:59

Next, I uninstalled the 64-bit gams python bindings from the 32-bit Python 2.7. I downloaded 32-bit GAMS 24.1. I installed gams python bindings from C:\GAMS\win32\24.1\apifiles\Python\api on 32-bit Python 2.7. I did this

C:\Python27>python
Python 2.7.14 (v2.7.14:84471935ed, Sep 16 2017, 20:19:30) [MSC v.1500 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import gams
>>>

and so I finally installed gams successfully. Next I went to Spine Toolbox and selected the Python 2.7 as my Python environment in File -> Settings. Guess what happens?

[1-10-2019 12:57:38] Executing All Directed Acyclic Graphs
[1-10-2019 12:57:38] Starting DAG 1/1
[1-10-2019 12:57:38] Order: Database -> GDX
[1-10-2019 12:57:38] ---------------------------------------
[1-10-2019 12:57:38] 
[1-10-2019 12:57:38] Executing Data Store Database
[1-10-2019 12:57:38] ***
[1-10-2019 12:57:38] ***
[1-10-2019 12:57:38] Could not load the `gams` package. No GAMS Python binding found.
[1-10-2019 12:57:38] DAG 1/1 finished
[1-10-2019 12:57:38] Execution complete

So the Python environment that GDX Export uses should be the one that is set in Settings.

In GitLab by @PekkaSavolainen on Oct 1, 2019, 13:03

@ererkka Python 2.7 works very well.

EDIT This is my Spine Toolbox Python Console output after running a Tool which runs some python_script.py. Setting up IPython and ipykernel for the selected Python may be broken at the moment, though.

Jupyter QtConsole 4.5.4
Python 2.7.14 (v2.7.14:84471935ed, Sep 16 2017, 20:19:30) [MSC v.1500 32 bit (Intel)]
Type "copyright", "credits" or "license" for more information.

IPython 5.8.0 -- An enhanced Interactive Python.
?         -> Introduction and overview of IPython's features.
%quickref -> Quick reference.
help      -> Python's own help system.
object?   -> Details about 'object', use 'object??' for extra details.
%cd -q C:\data\GIT\SPINETOOLBOX\work\python_script__ow72kinu__toolbox 

%run "python_script.py" 
Hello World. work dir:C:\data\GIT\SPINETOOLBOX\work\python_script__ow72kinu__toolbox
Args:['python_script.py']
Running in 2.7.14 (v2.7.14:84471935ed, Sep 16 2017, 20:19:30) [MSC v.1500 32 bit (Intel)]

spine-tools / Spine-Toolbox

New project item: Gdx Export - [merged] #1201