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