This tool exports symbols from a GDX file to a SQLite database. If gdxIn is not specified, all symbols are exported directly from the GAMS database. In the background, it utlizes the GAMS Connect Projection agent to convert the variables and equations to parameters and the SQLWriter agent to export the symbols to a SQLite database.

Usage

Command line:

gamstool [data.]sqlitewrite gdxIn=input_gdx_filename.gdx o=output_sqlite_filename.db

Compile time:

$callTool [data.]sqlitewrite [gdxIn=input_gdx_filename.gdx] o=output_sqlite_filename.db

Execution time:

executeTool '[data.]sqlitewrite [gdxIn=input_gdx_filename.gdx] o=output_sqlite_filename.db';

The following named parameters are available:

Parameter Type Default Description

append boolean N Specify whether to write new symbols to new tables in an existing database. Adding to existing tables is not allowed. (Y for Yes, N for No)

expltext boolean N Export explanatory text for set elements in the database table. (Y for Yes, N for No).

gdxIn string None Specify the name for the input gdx file.

ids string None Comma-separated list of symbols to export. If None, all symbols are exported.

o string None Specify the name for the output SQLite database file.

trace integer 0 Specify the trace level for debugging output.

unstack boolean N Specify if the last index column will be used as a header row. (Y for Yes, N for No)

Example

set i /i1, i2/;
set j /j1, j2/;
set k /k1, k2/;

parameter p(i,j,k);

p(i,j,k) = uniform(0,10);

execute_unload 'input.gdx' i,j,k,p;

executeTool 'data.sqlitewrite o=output.db';

*** The below usage prduces same results
*executeTool 'data.sqlitewrite gdxIn=input.gdx o=output.db';

embeddedCode Connect:
- SQLReader:
    connection: {'database': 'output.db'}
    symbols:
        - name: i_out
          query: "SELECT * FROM i;"
          type: set
        - name: j_out
          query: "SELECT * FROM j;"
          type: set
        - name: k_out
          query: "SELECT * FROM k;"
          type: set
        - name: p_out
          query: "SELECT * FROM p;"
- PythonCode:
    code: |
        connect.print_log(f"Symbol: i\n{connect.container['i_out'].records}\n")
        connect.print_log(f"Symbol: j\n{connect.container['j_out'].records}\n")
        connect.print_log(f"Symbol: k\n{connect.container['k_out'].records}\n")
        connect.print_log(f"Symbol: p\n{connect.container['p_out'].records}\n")
endEmbeddedCode

The above example generates the following log output.

Symbol: i
    0 element_text
0  i1
1  i2             

Symbol: j
    0 element_text
0  j1             
1  j2             

Symbol: k
    0 element_text
0  k1             
1  k2             

Symbol: p
    0   1   2     value
0  i1  j1  k1  1.717471
1  i1  j1  k2  8.432667
2  i1  j2  k1  5.503754
3  i1  j2  k2  3.011379
4  i2  j1  k1  2.922121
5  i2  j1  k2  2.240529
6  i2  j2  k1  3.498305
7  i2  j2  k2  8.562703

How data is stored

Sets

n-dimensional sets are stored as tables with n text columns. In case the option -expltext is used, another column may be added with explanatory text.

Parameters

n-dimensional parameters will have n index columns plus a value column. Scalars are collected in a separate table named scalars.

Variables and Equations

n-dimensional variables and equations have besides n index columns, columns for the level, the lower and upper-bound, the marginal and the scale. Scalar variables and Equations are collected in the tables scalarvariables and scalarequations. Note that EPS is stored as zero and the special values UNDEF and NA are exported as NULL.

Set i / i1*i4 /;
Positive Variable x(i);
x.l(i) = uniform(0,1);

Variable z;
z.m = 1;

executeTool "sqlitewrite ids=x,z o=data.db";

embeddedCode Connect:
- SQLReader:
    connection: {'database': 'data.db'}
    symbols:
        - name: x
          query: "SELECT * FROM x;"
        - name: scalarvariables
          query: "SELECT * FROM scalarvariables;"
- PythonCode:
    code: |
        connect.container["x"].records.columns = ["i","level","marginal","lower","upper","scale"]
        connect.container["scalarvariables"].records.columns = ["name","level","marginal","lower","upper","scale"]
        
        connect.print_log(f"Symbol: x\n{connect.container['x'].records}\n")
        connect.print_log(f"Symbol: scalarvariables\n{connect.container['scalarvariables'].records}\n")
endEmbeddedCode