capdsqlw.gms : Test Connect agents PandasSqlWriter

Description


Small Model of Type : GAMS


Category : GAMS Test library


Main file : capdsqlw.gms

$onechoV > t.gms
Set ij(*,*) /
'new-york'.'seattle',
'chicago'.'san-diego'/;


Set i(*) canning plants /
'seattle',
'san-diego' /;

Set j(*) markets /
'new-york',
'chicago',
'topeka' /;

Parameter a(i) capacity of plant i in cases /
'seattle' 350,
'san-diego' 600 /;

Parameter b(j) demand at market j in cases /
'new-york' 325,
'chicago' 300,
'topeka' 275 /;

Parameter d(i,j) distance in thousands of miles /
'seattle'.'new-york' 2.5,
'seattle'.'chicago' 1.7,
'seattle'.'topeka' 1.8,
'san-diego'.'new-york' 2.5,
'san-diego'.'chicago' 1.8,
'san-diego'.'topeka' 1.4 /;

Scalar f freight in dollars per case per thousand miles / 90 /;

Parameter c(i,j) transport cost in thousands of dollars per case /
'seattle'.'new-york' 0.225,
'seattle'.'chicago' 0.153,
'seattle'.'topeka' 0.162,
'san-diego'.'new-york' 0.225,
'san-diego'.'chicago' 0.162,
'san-diego'.'topeka' 0.126 /;

positive Variable x(i,j) shipment quantities in cases /
'seattle'.'new-york'.L 50,
'seattle'.'chicago'.L 300,
'seattle'.'topeka'.M 0.036,
'san-diego'.'new-york'.L 275,
'san-diego'.'chicago'.M 0.009,
'san-diego'.'topeka'.L 275 /;

free     Variable z total transportation costs in thousands of dollars /L 153.675 /;

Equation cost define objective function /M 1, LO 0, UP 0 /;

Equation supply(i) observe supply limit at plant i /
'seattle'.L 350,
'seattle'.M Eps,
'seattle'.LO -Inf,
'seattle'.UP 350,
'san-diego'.L 550,
'san-diego'.LO -Inf,
'san-diego'.UP 600 /;

Equation demand(j) satisfy demand at market j /
'new-york'.L 325,
'new-york'.M 0.225,
'new-york'.LO 325,
'new-york'.UP +Inf,
'chicago'.L 300,
'chicago'.M 0.153,
'chicago'.LO 300,
'chicago'.UP +Inf,
'topeka'.L 275,
'topeka'.M 0.126,
'topeka'.LO 275,
'topeka'.UP +Inf /;

$offEmpty
$offEcho
$call.checkErrorLevel gams t.gms lo=%gams.lo% gdx t.gdx


$log write sql
$onEchoV > t.gms
set sym / j, d, f /;
loop(sym,
   put_utility 'ecArguments' / '--SYM=' sym.tl:0;
   EmbeddedCode Connect:
   - GDXReader:
       file: t.gdx
       symbols:
            - name: %SYM%
   - PandasSQLWriter:
       connection: "sqlite:///writetest.db"
       symbols:
         - name: %SYM%
           tableName: %SYM%_table
           ifExists: "replace"
   endEmbeddedCode
);
$offEcho
$call.checkErrorLevel gams t.gms lo=%gams.lo%


$OnEmbeddedCode Connect:
    - GDXReader:
        file: t.gdx
        symbols:
            - name: demand
            - name: x
            - name: z
    - Projection:
        name: demand(j)
        newName: pdemand(j)
        asParameter: True
    - Projection:
        name: x(i,j)
        newName: px(i,j)
        asParameter: True
    - Projection:
        name: x.l(i,j)
        newName: xl(i,j)
    - Projection:
        name: z
        newName: pz
        asParameter: True
    - PandasSQLWriter:
        connection: "sqlite:///writetest.db"
        symbols:
           - name: pdemand
             tableName: demand_table
             ifExists: "replace"
             unstack: True
           - name: px
             tableName: x_table
             ifExists: "replace"
             unstack: True
           - name: xl
             tableName: xl_table
             ifExists: "replace"
           - name: pz
             tableName: z_table
             ifExists: "replace"
$OffEmbeddedCode

$OnEmbeddedCode Connect:
    - GDXReader:
        file: t.gdx
        symbols:
            - name: demand
            - name: x
            - name: z
    - Projection:
        name: demand(j)
        newName: pdemand(j)
        asParameter: True
    - PandasSQLWriter:
        connection: "sqlite:///writetest.db"
        symbols:
           - name: PDemand
             tableName: demand_table
             ifExists: "replace"
             unstack: True
$OffEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$OnEmbeddedCode Connect:
    - Options:
        caseSensitiveLookup: False
    - GDXReader:
        file: t.gdx
        symbols:
            - name: demand
            - name: x
            - name: z
    - Projection:
        name: demand(j)
        newName: pdemand(j)
        asParameter: True
    - PandasSQLWriter:
        connection: "sqlite:///writetest.db"
        symbols:
           - name: PDemand
             tableName: demand_table
             ifExists: "replace"
             unstack: True
$OffEmbeddedCode

$onEmbeddedCode Connect:
   - GDXReader:
       file: t.gdx
       symbols:
         - name: b
   - PandasSQLWriter:
       connection: "sqlite:///writetest.db"
       symbols:
         - name: b
           tableName: b_table
           ifExists: "replace"
           unstack: True
$offEmbeddedCode

$onEmbeddedCode Connect:
   - GDXReader:
       file: t.gdx
       symbols:
         - name: j
   - PandasSQLWriter:
       connection: "sqlite:///writetest.db"
       symbols:
         - name: j
           tableName: j_table_unstack
           ifExists: "replace"
           unstack: True
$offEmbeddedCode

$onEmbeddedCode Connect:
   - GDXReader:
       file: t.gdx
       symbols:
         - name: ij
   - PandasSQLWriter:
       connection: "sqlite:///writetest.db"
       symbols:
         - name: ij
           tableName: ij_table
           ifExists: "replace"
           unstack: True
$offEmbeddedCode


$log test scalar 
$onEmbeddedCode Connect:
- PandasSQLReader:
    connection: "sqlite:///writetest.db"
    symbols:
      - name: f_table
        query: "SELECT * FROM f_table;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["f_table"].records.values.tolist()
        expected = [[90.0,]]
        if data != expected:
            raise Exception("Unexpected data f_table.")
$offEmbeddedCode


$log test 2-dim parameter
$onEmbeddedCode Connect:
- PandasSQLReader:
    connection: "sqlite:///writetest.db"
    symbols:
      - name: d_table
        query: "SELECT * FROM d_table;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["d_table"].records.values.tolist()
        expected = [['seattle', 'new-york', 2.5],
                    ['seattle', 'chicago', 1.7],
                    ['seattle', 'topeka', 1.8],
                    ['san-diego', 'new-york', 2.5],
                    ['san-diego', 'chicago', 1.8],
                    ['san-diego', 'topeka', 1.4]]
        if data != expected:
            raise Exception("Unexpected data d_table.")
$offEmbeddedCode


$log test set without explanatory text
$onEmbeddedCode Connect:
- PandasSQLReader:
    connection: "sqlite:///writetest.db"
    symbols:
      - name: j_table
        query: "SELECT * FROM j_table;"
        type: set
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["j_table"].records.values.tolist()
        expected = [['new-york',''],
                    ['chicago',''],
                    ['topeka','']]
        if data != expected:
            raise Exception("Unexpected data j_table.")
$offEmbeddedCode


$log test xl
$onEmbeddedCode Connect:
- PandasSQLReader:
    connection: "sqlite:///writetest.db"
    symbols:
      - name: xl_table
        query: "SELECT * FROM 'xl_table';"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["xl_table"].records.values.tolist()
        expected = [['seattle', 'new-york', 50.0],
                    ['seattle', 'chicago', 300],
                    ['seattle', 'topeka', 0.0],
                    ['san-diego', 'new-york', 275.0],
                    ['san-diego', 'chicago', 0.0],
                    ['san-diego', 'topeka', 275.0]]
        if data != expected:
            raise Exception("Unexpected data xl_table.")
$offEmbeddedCode


$log test positive variable x
$onEmbeddedCode Connect:
- PandasSQLReader:
    connection: "sqlite:///writetest.db"
    symbols:
      - name: x_table
        query: "SELECT i_0, j_1, level FROM x_table;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["x_table"].records.values.tolist()
        expected = [['seattle', 'new-york', 50.0],
                    ['seattle', 'chicago', 300.0],
                    ['seattle', 'topeka', 0.0],
                    ['san-diego', 'new-york', 275.0],
                    ['san-diego', 'chicago', 0.0],
                    ['san-diego', 'topeka', 275.0]]
        if data != expected:
            raise Exception("Unexpected data x_table.")
$offEmbeddedCode


$log test free variable z
$onEmbeddedCode Connect:
- PandasSQLReader:
    connection: "sqlite:///writetest.db"
    symbols:
      - name: z_table
        query: "SELECT * FROM z_table;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["z_table"].records.values.tolist()
        expected = [['level', 153.675],
                    ['marginal', 0.0],
                    ['lower', float('-inf')],
                    ['upper', float('inf')],
                    ['scale', 1.0]]
        if data != expected:
            raise Exception("Unexpected data z_table.")
$offEmbeddedCode


$log test 1-dim parameter
$onEmbeddedCode Connect:
- PandasSQLReader:
    connection: "sqlite:///writetest.db"
    symbols:
      - name: b_table
        query: "SELECT * FROM b_table;"
        valueColumns: ["new-york", "chicago", "topeka"]
- PythonCode:
    code: |
        data = connect.container.data["b_table"].records.values.tolist()
        expected = [['new-york', 325.0],
                    ['chicago', 300.0],
                    ['topeka', 275.0]]
        if data != expected:
            raise Exception("Unexpected data d_table.")
$offEmbeddedCode


$log test 1-dim set
$onEmbeddedCode Connect:
- PandasSQLReader:
    connection: "sqlite:///writetest.db"
    symbols:
      - name: j_table_unstack
        query: "SELECT * FROM j_table_unstack;"
        type: set
        valueColumns: ["new-york", "chicago", "topeka"]
- PythonCode:
    code: |
        data = connect.container.data["j_table_unstack"].records.values.tolist()
        expected = [['new-york', 'Y'],
                    ['chicago', 'Y'],
                    ['topeka', 'Y']]
        if data != expected:
            raise Exception("Unexpected data j_table_unstack.")
$offEmbeddedCode


$log test 2-dim set
$onEmbeddedCode Connect:
- PandasSQLReader:
    connection: "sqlite:///writetest.db"
    symbols:
      - name: ij_table
        query: "SELECT * FROM ij_table;"
        type: set
        valueColumns: ["seattle", "san-diego"]
- PythonCode:
    code: |
        data = connect.container.data["ij_table"].records.values.tolist()
        expected = [['new-york', 'seattle', 'Y'],
                    ['chicago', 'san-diego', 'Y']]
        if data != expected:
            raise Exception("Unexpected data ij_table.")
$offEmbeddedCode


set i / i4, i3, i2, i1 /;
set j / j1*j4 /;
set k / k4, k3, k2, k1 /;
set l / l1*l4 /;
parameter p4(i,j,k,l);
p4(i,j,k,l)$(uniform(0,1)<0.1) = uniform(0,1);
parameter p1(*);

* test for correct order
EmbeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p1
      - name: p4
- PandasSQLWriter:
    connection: "sqlite:///test.db"
    symbols:
      # do not through an exception if there is no data
      - name: p1 
        tableName: "p1_empty_table"
        ifExists: replace
      - name: p4
        tableName: p4Table
        ifExists: "replace"
        unstack: True
endEmbeddedCode
EmbeddedCode Connect:
- PandasSQLReader:
    connection: "sqlite:///test.db"
    symbols:
      - name: p4
        query: "SELECT * FROM p4Table;"
        valueColumns: ["l1","l2","l3","l4"]
- PandasSQLWriter:
    connection: "sqlite:///test.db"
    symbols:
      - name: p4
        tableName: p4Table_ref
        ifExists: "replace"
        unstack: True
- PandasSQLReader:
    connection: "sqlite:///test.db"
    symbols:
      - name: p4_ref
        query: "SELECT * FROM p4Table_ref;"
        valueColumns: ["l1","l2","l3","l4"] 
- PythonCode:
    code: |
      # we expect the original UEL order: l1, l2, l3, l4
      expected = [['i4', 'j1', 'k2', 'l1', 0.500210669],
                  ['i4', 'j3', 'k2', 'l3', 0.33855027200000004],
                  ['i4', 'j4', 'k2', 'l1', 0.175661049],
                  ['i4', 'j4', 'k3', 'l1', 0.102514669],
                  ['i4', 'j4', 'k3', 'l4', 0.792360642],
                  ['i4', 'j4', 'k1', 'l1', 0.5851311730000001],
                  ['i3', 'j1', 'k2', 'l2', 0.202015557],
                  ['i3', 'j1', 'k2', 'l3', 0.269613052],
                  ['i3', 'j3', 'k1', 'l1', 0.40134625700000004],
                  ['i3', 'j4', 'k3', 'l1', 0.7831020040000001],
                  ['i3', 'j2', 'k2', 'l3', 0.576299805],
                  ['i3', 'j2', 'k2', 'l4', 0.006008368],
                  ['i2', 'j1', 'k2', 'l4', 0.15034771600000002],
                  ['i2', 'j3', 'k2', 'l1', 0.196093864],
                  ['i2', 'j3', 'k3', 'l1', 0.177822574],
                  ['i2', 'j3', 'k3', 'l2', 0.016643898],
                  ['i2', 'j4', 'k3', 'l4', 0.948836169],
                  ['i2', 'j2', 'k3', 'l3', 0.187448731],
                  ['i2', 'j2', 'k3', 'l4', 0.540400638],
                  ['i2', 'j2', 'k1', 'l4', 0.543870155],
                  ['i1', 'j1', 'k3', 'l1', 0.36863057200000005],
                  ['i1', 'j1', 'k3', 'l4', 0.84181978],
                  ['i1', 'j1', 'k1', 'l1', 0.7737034340000001],
                  ['i1', 'j3', 'k2', 'l2', 0.32300194000000004],
                  ['i1', 'j3', 'k4', 'l4', 0.263857554],
                  ['i1', 'j4', 'k1', 'l3', 0.37419850000000004],
                  ['i1', 'j4', 'k4', 'l4', 0.41459935800000003],
                  ['i1', 'j2', 'k2', 'l4', 0.485176103],
                  ['i1', 'j2', 'k1', 'l1', 0.698580858]]
      data = connect.container.data["p4_ref"].records.values.tolist()
      if data != expected:
        raise Exception("Unexpected Data p4_ref")
endEmbeddedCode