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 utilizes 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).

fast boolean N Specify if the tool should accelerate data inserts using some non-standard pragmas. Enabling this compromises data consistency in the event of a program crash.

gdxIn string None Specify 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.

small boolean N Specify if the UELs are stored in a separate table resulting in a smaller database. A user-friendly SQL VIEW is created to hide the complexity of the joins.

trace integer 0 Specify the trace level for debugging output. (0,1,2,3) See Connect agent SQLWriter option trace for more details.

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

Note: Enabling option small is not allowed in append mode.

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