Description
This test ensures the correctness of the Connect agent SQLWriter against the following DBMS 1. SQLite 2. Postgres 3. MySQL 4. SQL-Server 5. MS-Access 6. SQLAlchemy Contributor: Vaibhavnath Jha, March 2023
Small Model of Type : GAMS
Category : GAMS Test library
Main file : casqlw.gms
$title 'Test Connect agent SQLWriter' (CASQLW,SEQ=910)
$onText
This test ensures the correctness of the Connect agent SQLWriter against the following DBMS
1. SQLite
2. Postgres
3. MySQL
4. SQL-Server
5. MS-Access
6. SQLAlchemy
Contributor: Vaibhavnath Jha, March 2023
$offText
$if not setEnv GMSPYTHONLIB $abort.noError Embedded code Python not ready to be used
$log --- Using Python library %sysEnv.GMSPYTHONLIB%
$onEcho > 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 /;
Set bulk /index1*index2/;
alias(bulk,i1,i2,i3,i4);
Parameter pbulk(i1,i2,i3,i4);
pbulk(i1,i2,i3,i4) = uniform(0,1);
Set jnew(*) new markets/
'toronto',
'vancouver'
/;
$offEmpty
$offEcho
$call.checkErrorLevel gams t.gms lo=%gams.lo% gdx t.gdx
$onEmbeddedCode Python:
import os
import string
from random import choices
import psycopg2, pymysql, pymssql
schemaName = ''.join(choices(string.ascii_lowercase, k=10))
os.environ['CONNECTSQLSCHEMASUFFIX'] = schemaName
os.environ['CONNECTSQLTABLEPREFIX'] = schemaName + '.'
with psycopg2.connect(**{'user':'postgres', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 22000, 'host': 'internal2.gams.com', 'database': 'testdb'}) as conn:
cur = conn.cursor()
cur.execute(f"CREATE SCHEMA {schemaName};")
conn.commit()
cur.close()
with pymysql.connect(**{'user':'root', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 21000, 'host': 'internal2.gams.com', 'database': 'testdb'}) as conn:
cur = conn.cursor()
cur.execute(f"CREATE SCHEMA {schemaName};")
conn.commit()
cur.close()
with pymssql.connect(**{'user':'sa', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 23000, 'host': 'internal2.gams.com'}, autocommit=True) as conn:
cur = conn.cursor()
cur.execute(f"CREATE DATABASE {schemaName};")
conn.commit()
cur.close()
$offEmbeddedCode
$onEchoV > writeTest.gms
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);
$log test ifExists set to `fail`
$onEmbeddedCode Connect:
- GDXReader:
file: t.gdx
symbols:
- name: j
newName: j_write_all_tbl
- SQLWriter:
connectionType: %CONNECTIONTYPE%
connection: %CONNECTIONDICT%
writeAll: True
ifExists: "replace"
schemaName: %SCHEMASUFFIX%
- SQLWriter:
connectionType: %CONNECTIONTYPE%
connection: %CONNECTIONDICT%
writeAll: True
ifExists: "fail"
schemaName: %SCHEMASUFFIX%
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors
$log test global writeAll & ifExists
$onEmbeddedCode Connect:
- GDXReader:
file: t.gdx
symbols:
- name: j
newName: j_write_all_tbl
- name: d
newName: d_write_all_tbl
- name: f
newName: f_write_all_tbl
- SQLWriter:
connectionType: %CONNECTIONTYPE%
connection: %CONNECTIONDICT%
writeAll: True
ifExists: "replace"
schemaName: %SCHEMASUFFIX%
$offEmbeddedCode
$log test set without explanatory text
$onEmbeddedCode Connect:
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: j_write_all_tbl
query: "SELECT * FROM %TABLEPREFIX%j_write_all_tbl;"
type: set
valueColumns: "lastCol"
- PythonCode:
code: |
data = connect.container.data["j_write_all_tbl"].records.values.tolist()
expected = [['new-york',''],
['chicago',''],
['topeka','']]
if data != expected:
raise Exception("Unexpected data j_write_all_tbl.")
$offEmbeddedCode
$log test 2-dim parameter
$onEmbeddedCode Connect:
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: d_write_all_tbl
query: "SELECT * FROM %TABLEPREFIX%d_write_all_tbl;"
valueColumns: "lastCol"
- PythonCode:
code: |
data = connect.container.data["d_write_all_tbl"].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_write_all_tbl.")
$offEmbeddedCode
$log test scalar
$onEmbeddedCode Connect:
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: f_write_all_tbl
query: "SELECT * FROM %TABLEPREFIX%f_write_all_tbl;"
valueColumns: "lastCol"
- PythonCode:
code: |
data = connect.container.data["f_write_all_tbl"].records.values.tolist()
expected = [[90.0,]]
if data != expected:
raise Exception("Unexpected data f_write_all_tbl.")
$offEmbeddedCode
$log adding list of symbols
$onEmbeddedCode Connect:
- GDXReader:
file: t.gdx
symbols:
- name: x
- name: z
- Projection:
name: x.all(i,j)
newName: px(i,j)
- Projection:
name: x.l(i,j)
newName: xl(i,j)
- Projection:
name: z.all
newName: pz
- SQLWriter:
connectionType: %CONNECTIONTYPE%
connection: %CONNECTIONDICT%
schemaName: %SCHEMASUFFIX%
symbols:
- name: px
tableName: x_tbl
ifExists: "replace"
valueSubstitutions: %VALUESUBST%
unstack: True
- name: xl
tableName: xl_tbl
ifExists: "replace"
valueSubstitutions: %VALUESUBST%
- name: pz
tableName: z_tbl
ifExists: "replace"
valueSubstitutions: %VALUESUBST%
$offEmbeddedCode
$log test positive variable x
$onEmbeddedCode Connect:
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: x_tbl
query: "SELECT i, j, level FROM %TABLEPREFIX%x_tbl;"
valueColumns: "lastCol"
- PythonCode:
code: |
data = connect.container.data["x_tbl"].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_tbl.")
$offEmbeddedCode
$log test xl
$onEmbeddedCode Connect:
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: xl_tbl
query: "SELECT * FROM %TABLEPREFIX%xl_tbl;"
valueColumns: "lastCol"
- PythonCode:
code: |
data = connect.container.data["xl_tbl"].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_tbl.")
$offEmbeddedCode
$log test free variable z
$onEmbeddedCode Connect:
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: z_tbl
query: "SELECT * FROM %TABLEPREFIX%z_tbl;"
valueColumns: "lastCol"
valueSubstitutions: %VALUESUBSTREADER%
- PythonCode:
code: |
data = connect.container.data["z_tbl"].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_tbl.")
$offEmbeddedCode
$log test 1-dim parameter
$onEmbeddedCode Connect:
- GDXReader:
file: t.gdx
symbols:
- name: b
- SQLWriter:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: b
tableName: b_tbl
ifExists: "replace"
unstack: True
schemaName: %SCHEMASUFFIX%
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: b_tbl
query: "SELECT * FROM %TABLEPREFIX%b_tbl;"
valueColumns: ["new-york", "chicago", "topeka"]
- PythonCode:
code: |
data = connect.container.data["b_tbl"].records.values.tolist()
expected = [['new-york', 325.0],
['chicago', 300.0],
['topeka', 275.0]]
if data != expected:
raise Exception("Unexpected data d_tbl.")
$offEmbeddedCode
$log test 1-dim set
$onEmbeddedCode Connect:
- GDXReader:
file: t.gdx
symbols:
- name: j
- SQLWriter:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: j
tableName: j_tbl_unstack
ifExists: "replace"
unstack: True
schemaName: %SCHEMASUFFIX%
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: j_tbl_unstack
query: "SELECT * FROM %TABLEPREFIX%j_tbl_unstack;"
type: set
valueColumns: ["new-york", "chicago", "topeka"]
- PythonCode:
code: |
data = connect.container.data["j_tbl_unstack"].records.values.tolist()
expected = [['new-york', 'Y'],
['chicago', 'Y'],
['topeka', 'Y']]
if data != expected:
raise Exception("Unexpected data j_tbl_unstack.")
$offEmbeddedCode
$log test 2-dim set with global unstack
$onEmbeddedCode Connect:
- GDXReader:
file: t.gdx
symbols:
- name: ij
- SQLWriter:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
unstack: True
symbols:
- name: ij
tableName: ij_tbl
ifExists: "replace"
schemaName: %SCHEMASUFFIX%
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: ij_tbl
query: "SELECT * FROM %TABLEPREFIX%ij_tbl;"
type: set
valueColumns: ["seattle", "san-diego"]
- PythonCode:
code: |
data = connect.container.data["ij_tbl"].records.values.tolist()
expected = [['new-york', 'seattle', 'Y'],
['chicago', 'san-diego', 'Y']]
if data != expected:
raise Exception("Unexpected data ij_tbl.")
$offEmbeddedCode
$log test 1-dim set written with SQLAlchemy with global unstack
$onEmbeddedCode Connect:
- GDXReader:
file: t.gdx
symbols:
- name: bulk
- SQLWriter:
connection: %CONNECTIONDICTSQLALCHEMY%
connectionType: sqlalchemy
unstack: True
symbols:
- name: bulk
tableName: sqlalchemy_tbl
ifExists: "replace"
schemaName: %SCHEMASUFFIX%
- SQLReader:
connection: %CONNECTIONDICTSQLALCHEMY%
connectionType: sqlalchemy
symbols:
- name: sqlalchemy_tbl
query: "SELECT * FROM %TABLEPREFIX%sqlalchemy_tbl;"
type: set
valueColumns: ["index1", "index2"]
- PythonCode:
code: |
data = connect.container.data["sqlalchemy_tbl"].records.values.tolist()
expected = [['index1', 'Y'],
['index2', 'Y']]
if data != expected:
raise Exception("Unexpected data sqlalchemy_tbl.")
$offEmbeddedCode
$log test SQLAlchemy with writeAll
$onEmbeddedCode Connect:
- GDXReader:
file: t.gdx
symbols:
- name: demand
- Projection:
name: demand.l(j)
newName: pdemand(j)
- SQLWriter:
connection: %CONNECTIONDICTSQLALCHEMY%
connectionType: sqlalchemy
writeAll: True
ifExists: "replace"
schemaName: %SCHEMASUFFIX%
- SQLReader:
connection: %CONNECTIONDICTSQLALCHEMY%
connectionType: sqlalchemy
symbols:
- name: pdemand_tbl
query: "SELECT * FROM %TABLEPREFIX%pdemand;"
- PythonCode:
code: |
data = connect.container.data["pdemand_tbl"].records.values.tolist()
expected = [['new-york', 325.0],
['chicago', 300.0],
['topeka', 275.0]]
if data != expected:
raise Exception("Unexpected data pdemand_tbl.")
$offEmbeddedCode
$log test appending to existing table
$onEmbeddedCode Connect:
- GDXReader:
file: t.gdx
symbols:
- name: jnew
newName: j_write_all_tbl
- SQLWriter:
connectionType: %CONNECTIONTYPE%
connection: %CONNECTIONDICT%
writeAll: True
ifExists: "append"
schemaName: %SCHEMASUFFIX%
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: j_append_data
query: "SELECT * FROM %TABLEPREFIX%j_write_all_tbl;"
type: set
valueColumns: "lastCol"
- PythonCode:
code: |
data = connect.container.data["j_append_data"].records.values.tolist()
expected = [['new-york',''],
['chicago',''],
['topeka',''],
['toronto',''],
['vancouver','']]
if data != expected:
raise Exception("Unexpected data j_append_data.")
$offEmbeddedCode
$log test writing empty symbols to databases
$onEmbeddedCode Connect:
- PythonCode:
code: |
connect.container.addSet("k", domain=["*"])
connect.container.addParameter("p", domain=["*"])
- SQLWriter:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
schemaName: %SCHEMASUFFIX%
writeAll: True
ifExists: "replace"
$offEmbeddedCode
$log test retrieving empty tables created by empty symbols
$onEmbeddedCode Connect:
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: k
query: "SELECT * FROM %TABLEPREFIX%k"
type: set
valueColumns: lastCol
- name: p
query: "SELECT * FROM %TABLEPREFIX%p"
type: par
- PythonCode:
code: |
data_k = connect.container["k"].records
cols_k = [0, 'element_text']
data_p = connect.container["p"].records
cols_p = [0, 'value']
if data_k is None or not data_k.empty:
raise Exception("Expected set >k< to have empty records.")
if data_p is None or not data_p.empty:
raise Exception("Expected parameter >p< to have empty records.")
if (data_k.columns != cols_k).any():
raise Exception("Unexpected columns for set >k<.")
if (data_p.columns != cols_p).any():
raise Exception("Unexpected columns for parameter >p<.")
$offEmbeddedCode
* test for correct categoricals
EmbeddedCode Connect:
- GAMSReader:
symbols:
- name: p4
- SQLWriter:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
schemaName: %SCHEMASUFFIX%
symbols:
- name: p4
tableName: p4Table
ifExists: "replace"
unstack: True
endEmbeddedCode
EmbeddedCode Connect:
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: p4
query: "SELECT * FROM %TABLEPREFIX%p4Table;"
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]]
sym = connect.container.data["p4"]
data_sorted = sym.records.sort_values(sym.records.columns[:-1].tolist()).values.tolist()
if data_sorted != expected:
raise Exception("Unexpected sorted data p4")
- SQLWriter:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
schemaName: %SCHEMASUFFIX%
symbols:
- name: p4
tableName: p4Table_ref
ifExists: "replace"
unstack: True
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: p4_ref
query: "SELECT * FROM %TABLEPREFIX%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]]
sym = connect.container.data["p4_ref"]
data_sorted = sym.records.sort_values(sym.records.columns[:-1].tolist()).values.tolist()
if data_sorted != expected:
raise Exception("Unexpected sorted data p4_ref")
endEmbeddedCode
$ifThenE sameas('%CONNECTIONTYPE%','mysql')or(sameas('%CONNECTIONTYPE%','postgres'))
* Add bulkInsert tests for select DBMS
$log test 4-dim parameter written with bulkInsert & unstack: True
$onEmbeddedCode Connect:
- GDXReader:
file: t.gdx
symbols:
- name: pbulk
newName: pbulk_tbl_unstk
- SQLWriter:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
insertMethod: bulkInsert
unstack: True
writeAll: True
ifExists: replace
schemaName: %SCHEMASUFFIX%
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: pbulk_unstack_tbl
query: "SELECT * FROM %TABLEPREFIX%pbulk_tbl_unstk;"
valueColumns: ["index1", "index2"]
- PythonCode:
code: |
data = connect.container.data["pbulk_unstack_tbl"].records.values.tolist()
expected = [['index1', 'index1', 'index1', 'index1', 0.17174713200000002],
['index1', 'index1', 'index1', 'index2', 0.843266708],
['index1', 'index1', 'index2', 'index1', 0.550375356],
['index1', 'index1', 'index2', 'index2', 0.301137904],
['index1', 'index2', 'index1', 'index1', 0.292212117],
['index1', 'index2', 'index1', 'index2', 0.22405286700000002],
['index1', 'index2', 'index2', 'index1', 0.34983050400000004],
['index1', 'index2', 'index2', 'index2', 0.856270347],
['index2', 'index1', 'index1', 'index1', 0.067113723],
['index2', 'index1', 'index1', 'index2', 0.500210669],
['index2', 'index1', 'index2', 'index1', 0.9981176270000001],
['index2', 'index1', 'index2', 'index2', 0.578733378],
['index2', 'index2', 'index1', 'index1', 0.991133039],
['index2', 'index2', 'index1', 'index2', 0.7622504670000001],
['index2', 'index2', 'index2', 'index1', 0.130692483],
['index2', 'index2', 'index2', 'index2', 0.6397187590000001]]
if data != expected:
raise Exception("Unexpected data pbulk_unstack_tbl.")
$offEmbeddedCode
$log test 4-dim parameter written with bulkInsert
$onEmbeddedCode Connect:
- GDXReader:
file: t.gdx
symbols:
- name: pbulk
- SQLWriter:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
insertMethod: bulkInsert
schemaName: %SCHEMASUFFIX%
symbols:
- name: pbulk
tableName: pbulk_tbl
ifExists: "replace"
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: pbulk_tbl
query: "SELECT * FROM %TABLEPREFIX%pbulk_tbl;"
- PythonCode:
code: |
data = connect.container.data["pbulk_tbl"].records.values.tolist()
expected = [['index1', 'index1', 'index1', 'index1', 0.17174713200000002],
['index1', 'index1', 'index1', 'index2', 0.843266708],
['index1', 'index1', 'index2', 'index1', 0.550375356],
['index1', 'index1', 'index2', 'index2', 0.301137904],
['index1', 'index2', 'index1', 'index1', 0.292212117],
['index1', 'index2', 'index1', 'index2', 0.22405286700000002],
['index1', 'index2', 'index2', 'index1', 0.34983050400000004],
['index1', 'index2', 'index2', 'index2', 0.856270347],
['index2', 'index1', 'index1', 'index1', 0.067113723],
['index2', 'index1', 'index1', 'index2', 0.500210669],
['index2', 'index1', 'index2', 'index1', 0.9981176270000001],
['index2', 'index1', 'index2', 'index2', 0.578733378],
['index2', 'index2', 'index1', 'index1', 0.991133039],
['index2', 'index2', 'index1', 'index2', 0.7622504670000001],
['index2', 'index2', 'index2', 'index1', 0.130692483],
['index2', 'index2', 'index2', 'index2', 0.6397187590000001]]
if data != expected:
raise Exception("Unexpected data pbulk_tbl.")
$offEmbeddedCode
$log test 2-dim set with unstack and NULL values using bulkInsert
$onEmbeddedCode Connect:
- GDXReader:
file: t.gdx
symbols:
- name: ij
- SQLWriter:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
schemaName: %SCHEMASUFFIX%
symbols:
- name: ij
tableName: ij_tbl
ifExists: "replace"
unstack: True
insertMethod: bulkInsert
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: ij_tbl
query: "SELECT * FROM %TABLEPREFIX%ij_tbl;"
type: set
valueColumns: ["seattle", "san-diego"]
- PythonCode:
code: |
data = connect.container.data["ij_tbl"].records.values.tolist()
expected = [['new-york', 'seattle', 'Y'],
['chicago', 'san-diego', 'Y']]
if data != expected:
raise Exception("Unexpected data ij_tbl.")
$offEmbeddedCode
$endIf
$ifThenE.bcp sameas('%CONNECTIONTYPE%','sqlserver')and(sameas('%HAVEBCP%',1))
$log adding symbol thru insertMethod: bcp
$onEmbeddedCode Connect:
- GDXReader:
file: t.gdx
symbols:
- name: pbulk
newName: pbulk_tbl_bcp
- SQLWriter:
connection: %CONNECTIONDICT%
connectionType: sqlserver
insertMethod: bcp
writeAll: True
ifExists: replace
schemaName: %SCHEMASUFFIX%
$offEmbeddedCode
$log test 4-dim parameter written with insertMethod: bcp
$onEmbeddedCode Connect:
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: sqlserver
symbols:
- name: pbulk_tbl_bcp
query: "SELECT * FROM %TABLEPREFIX%pbulk_tbl_bcp;"
- PythonCode:
code: |
data = connect.container.data["pbulk_tbl_bcp"].records.values.tolist()
expected = [['index1', 'index1', 'index1', 'index1', 0.17174713200000002],
['index1', 'index1', 'index1', 'index2', 0.843266708],
['index1', 'index1', 'index2', 'index1', 0.550375356],
['index1', 'index1', 'index2', 'index2', 0.301137904],
['index1', 'index2', 'index1', 'index1', 0.292212117],
['index1', 'index2', 'index1', 'index2', 0.22405286700000002],
['index1', 'index2', 'index2', 'index1', 0.34983050400000004],
['index1', 'index2', 'index2', 'index2', 0.856270347],
['index2', 'index1', 'index1', 'index1', 0.067113723],
['index2', 'index1', 'index1', 'index2', 0.500210669],
['index2', 'index1', 'index2', 'index1', 0.9981176270000001],
['index2', 'index1', 'index2', 'index2', 0.578733378],
['index2', 'index2', 'index1', 'index1', 0.991133039],
['index2', 'index2', 'index1', 'index2', 0.7622504670000001],
['index2', 'index2', 'index2', 'index1', 0.130692483],
['index2', 'index2', 'index2', 'index2', 0.6397187590000001]]
if data != expected:
raise Exception("Unexpected data pbulk_tbl_bcp.")
$offEmbeddedCode
$elseIfE.bcp sameas('%CONNECTIONTYPE%','sqlserver')and(sameas('%HAVEBCP%',0))
$log bcp utility not found. Skipping bcp tests.
$endIf.bcp
$offEcho
$onEchoV > writeAccess.gms
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);
$set connection {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
$log test ifExists set to `fail`
$onEmbeddedCode Connect:
- GDXReader:
file: t.gdx
symbols:
- name: j
newName: j_write_all_table
- SQLWriter:
connectionType: access
connection: %connection%
writeAll: True
ifExists: "replace"
- SQLWriter:
connectionType: access
connection: %connection%
writeAll: True
ifExists: "fail"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors
$log test global writeAll & ifExists
$onEmbeddedCode Connect:
- GDXReader:
file: t.gdx
symbols:
- name: j
newName: j_write_all_table
- name: d
newName: d_write_all_table
- name: f
newName: f_write_all_table
- SQLWriter:
connectionType: access
connection: %connection%
writeAll: True
ifExists: "replace"
$offEmbeddedCode
$log test set without explanatory text
$onEmbeddedCode Connect:
- SQLReader:
connection: %connection%
connectionType: access
symbols:
- name: j_write_all_table
query: "SELECT * FROM j_write_all_table;"
type: set
valueColumns: "lastCol"
- PythonCode:
code: |
data = connect.container.data["j_write_all_table"].records.values.tolist()
expected = [['new-york',''],
['chicago',''],
['topeka','']]
if data != expected:
raise Exception("Unexpected data j_write_all_table.")
$offEmbeddedCode
$log test 2-dim parameter
$onEmbeddedCode Connect:
- SQLReader:
connection: %connection%
connectionType: access
symbols:
- name: d_write_all_table
query: "SELECT * FROM d_write_all_table;"
valueColumns: "lastCol"
- PythonCode:
code: |
data = connect.container.data["d_write_all_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_write_all_table.")
$offEmbeddedCode
$log test scalar
$onEmbeddedCode Connect:
- SQLReader:
connection: %connection%
connectionType: access
symbols:
- name: f_write_all_table
query: "SELECT * FROM f_write_all_table;"
valueColumns: "lastCol"
- PythonCode:
code: |
data = connect.container.data["f_write_all_table"].records.values.tolist()
expected = [[90.0,]]
if data != expected:
raise Exception("Unexpected data f_write_all_table.")
$offEmbeddedCode
$log test 1-dim parameter
$onEmbeddedCode Connect:
- GDXReader:
file: t.gdx
symbols:
- name: b
- SQLWriter:
connection: %connection%
connectionType: access
symbols:
- name: b
tableName: b_table
ifExists: "replace"
unstack: True
$offEmbeddedCode
$onEmbeddedCode Connect:
- SQLReader:
connection: %connection%
connectionType: access
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:
- GDXReader:
file: t.gdx
symbols:
- name: j
- SQLWriter:
connection: %connection%
connectionType: access
symbols:
- name: j
tableName: j_table_unstack
ifExists: "replace"
unstack: True
$offEmbeddedCode
$onEmbeddedCode Connect:
- SQLReader:
connection: %connection%
connectionType: access
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 with global unstack
$onEmbeddedCode Connect:
- GDXReader:
file: t.gdx
symbols:
- name: ij
- SQLWriter:
connection: %connection%
connectionType: access
unstack: True
symbols:
- name: ij
tableName: ij_table
ifExists: "replace"
$offEmbeddedCode
$onEmbeddedCode Connect:
- SQLReader:
connection: %connection%
connectionType: access
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
$log add to existing table
$onEmbeddedCode Connect:
- GDXReader:
file: t.gdx
symbols:
- name: jnew
newName: j_write_all_table
- SQLWriter:
connection: %connection%
connectionType: access
writeAll: True
ifExists: "append"
$offEmbeddedCode
$log test appended table
$onEmbeddedCode Connect:
- SQLReader:
connection: %connection%
connectionType: access
symbols:
- name: j_write_all_table
query: "SELECT * FROM j_write_all_table;"
type: set
valueColumns: "lastCol"
- PythonCode:
code: |
data = connect.container.data["j_write_all_table"].records.values.tolist()
expected = [['new-york',''],
['chicago',''],
['topeka',''],
['toronto',''],
['vancouver','']]
if data != expected:
raise Exception("Unexpected data j_write_all_table.")
$offEmbeddedCode
$log adding list of symbols
$onEmbeddedCode Connect:
- GDXReader:
file: t.gdx
symbols:
- name: x
- name: z
- Projection:
name: x.all(i,j)
newName: px(i,j)
- Projection:
name: x.l(i,j)
newName: xl(i,j)
- Projection:
name: z.all
newName: pz
- SQLWriter:
connectionType: access
connection: %connection%
symbols:
- name: px
tableName: x_table
ifExists: "replace"
unstack: True
- name: xl
tableName: xl_table
ifExists: "replace"
- name: pz
tableName: z_table
ifExists: "replace"
$offEmbeddedCode
$log test positive variable x
$onEmbeddedCode Connect:
- SQLReader:
connection: %connection%
connectionType: access
symbols:
- name: x_table
# level is a reserved keyword in MS-Access, needs to be enclosed within []
query: "SELECT i, j, [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 xl
$onEmbeddedCode Connect:
- SQLReader:
connection: %connection%
connectionType: access
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 free variable z
$onEmbeddedCode Connect:
- SQLReader:
connection: %connection%
connectionType: access
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 writing empty symbols to access
$onEmbeddedCode Connect:
- PythonCode:
code: |
connect.container.addSet("k", domain=["*"])
connect.container.addParameter("p", domain=["*"])
- SQLWriter:
connectionType: access
connection: %connection%
writeAll: True
ifExists: "replace"
$offEmbeddedCode
$log test retrieving empty tables created by empty symbols
$onEmbeddedCode Connect:
- SQLReader:
connectionType: access
connection: %connection%
symbols:
- name: k
query: "SELECT * FROM k"
type: set
valueColumns: lastCol
- name: p
query: "SELECT * FROM p"
type: par
- PythonCode:
code: |
data_k = connect.container["k"].records
cols_k = [0, 'element_text']
data_p = connect.container["p"].records
cols_p = [0, 'value']
if data_k is None or not data_k.empty:
raise Exception("Expected set >k< to have empty records.")
if data_p is None or not data_p.empty:
raise Exception("Expected parameter >p< to have empty records.")
if (data_k.columns != cols_k).any():
raise Exception("Unexpected columns for set >k<.")
if (data_p.columns != cols_p).any():
raise Exception("Unexpected columns for parameter >p<.")
$offEmbeddedCode
* test for correct categoricals
EmbeddedCode Connect:
- GAMSReader:
symbols:
- name: p4
- SQLWriter:
connection: %connection%
connectionType: access
symbols:
- name: p4
tableName: p4Table
ifExists: "replace"
unstack: True
endEmbeddedCode
EmbeddedCode Connect:
- SQLReader:
connection: %connection%
connectionType: access
symbols:
- name: p4
query: "SELECT * FROM p4Table;"
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]]
sym = connect.container.data["p4"]
data_sorted = sym.records.sort_values(sym.records.columns[:-1].tolist()).values.tolist()
if data_sorted != expected:
raise Exception("Unexpected sorted data p4")
- SQLWriter:
connection: %connection%
connectionType: access
symbols:
- name: p4
tableName: p4Table_ref
ifExists: "replace"
unstack: True
- SQLReader:
connection: %connection%
connectionType: access
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]]
sym = connect.container.data["p4_ref"]
data_sorted = sym.records.sort_values(sym.records.columns[:-1].tolist()).values.tolist()
if data_sorted != expected:
raise Exception("Unexpected sorted data p4_ref")
endEmbeddedCode
$log adding 4-dim parameter with insertMethod: bulkInsert
$onEmbeddedCode Connect:
- GDXReader:
file: t.gdx
symbols:
- name: pbulk
- SQLWriter:
connection: %connection%
connectionType: access
insertMethod: bulkInsert
symbols:
- name: pbulk
tableName: pbulk_table
ifExists: "replace"
$offEmbeddedCode
$log test 4-dim parameter written with bulkInsert
$onEmbeddedCode Connect:
- SQLReader:
connection: %connection%
connectionType: access
symbols:
- name: pbulk_table
query: "SELECT * FROM pbulk_table;"
- PythonCode:
code: |
data = connect.container.data["pbulk_table"].records.values.tolist()
expected = [['index1', 'index1', 'index1', 'index1', 0.17174713200000002],
['index1', 'index1', 'index1', 'index2', 0.843266708],
['index1', 'index1', 'index2', 'index1', 0.550375356],
['index1', 'index1', 'index2', 'index2', 0.301137904],
['index1', 'index2', 'index1', 'index1', 0.292212117],
['index1', 'index2', 'index1', 'index2', 0.22405286700000002],
['index1', 'index2', 'index2', 'index1', 0.34983050400000004],
['index1', 'index2', 'index2', 'index2', 0.856270347],
['index2', 'index1', 'index1', 'index1', 0.067113723],
['index2', 'index1', 'index1', 'index2', 0.500210669],
['index2', 'index1', 'index2', 'index1', 0.9981176270000001],
['index2', 'index1', 'index2', 'index2', 0.578733378],
['index2', 'index2', 'index1', 'index1', 0.991133039],
['index2', 'index2', 'index1', 'index2', 0.7622504670000001],
['index2', 'index2', 'index2', 'index1', 0.130692483],
['index2', 'index2', 'index2', 'index2', 0.6397187590000001]]
if data != expected:
raise Exception("Unexpected data pbulk_table.")
$offEmbeddedCode
$log adding 4-dim parameter with unstack: True
$onEmbeddedCode Connect:
- GDXReader:
file: t.gdx
symbols:
- name: pbulk
newName: pbulk_table_unstack
- SQLWriter:
connection: %connection%
connectionType: access
insertMethod: bulkInsert
unstack: True
writeAll: True
ifExists: replace
$offEmbeddedCode
$log test 4-dim parameter written with bulkInsert & unstack: True
$onEmbeddedCode Connect:
- SQLReader:
connection: %connection%
connectionType: access
symbols:
- name: pbulk_table_unstack
query: "SELECT * FROM pbulk_table_unstack;"
valueColumns: ["index1", "index2"]
- PythonCode:
code: |
data = connect.container.data["pbulk_table_unstack"].records.values.tolist()
expected = [['index1', 'index1', 'index1', 'index1', 0.17174713200000002],
['index1', 'index1', 'index1', 'index2', 0.843266708],
['index1', 'index1', 'index2', 'index1', 0.550375356],
['index1', 'index1', 'index2', 'index2', 0.301137904],
['index1', 'index2', 'index1', 'index1', 0.292212117],
['index1', 'index2', 'index1', 'index2', 0.22405286700000002],
['index1', 'index2', 'index2', 'index1', 0.34983050400000004],
['index1', 'index2', 'index2', 'index2', 0.856270347],
['index2', 'index1', 'index1', 'index1', 0.067113723],
['index2', 'index1', 'index1', 'index2', 0.500210669],
['index2', 'index1', 'index2', 'index1', 0.9981176270000001],
['index2', 'index1', 'index2', 'index2', 0.578733378],
['index2', 'index2', 'index1', 'index1', 0.991133039],
['index2', 'index2', 'index1', 'index2', 0.7622504670000001],
['index2', 'index2', 'index2', 'index1', 0.130692483],
['index2', 'index2', 'index2', 'index2', 0.6397187590000001]]
if data != expected:
raise Exception("Unexpected data pbulk_table_unstack.")
$offEmbeddedCode
$offEcho
$onEcho > sub.txt
eolonly 1
--VALUESUBST {.inf: 99999, -.inf: -99999}
--VALUESUBSTREADER {99999: .inf, -99999: -.inf}
$offEcho
$onEcho > nosub.txt
eolonly 1
--VALUESUBST {}
--VALUESUBSTREADER {}
$offEcho
$onEcho > connect.txt
eolonly 1
--SCHEMASUFFIX Null
--TABLEPREFIX
--CONNECTIONTYPE sqlite
--CONNECTIONDICT {'database': '%sysEnv.CONNECTSQLSCHEMASUFFIX%.db'}
--CONNECTIONDICTSQLALCHEMY {'drivername': 'sqlite', 'database': '%sysEnv.CONNECTSQLSCHEMASUFFIX%.db'}
$offEcho
$log testing sqlite
$call gams writeTest.gms lo=%gams.lo% pf=connect.txt pf=nosub.txt
$ifE errorLevel<>0 $goTo cleanup
$onEcho > connect.txt
eolonly 1
--SCHEMASUFFIX %sysEnv.CONNECTSQLSCHEMASUFFIX%
--TABLEPREFIX %sysEnv.CONNECTSQLTABLEPREFIX%
--CONNECTIONTYPE mysql
--CONNECTIONDICT {'user':'root', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 21000, 'host': 'internal2.gams.com', 'database': 'testdb'}
--CONNECTIONDICTSQLALCHEMY {'username':'root', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 21000, 'host': 'internal2.gams.com', 'database': 'testdb', 'drivername': 'mysql+pymysql'}
$offEcho
$log testing mysql
$call gams writeTest.gms lo=%gams.lo% pf=connect.txt pf=sub.txt
$ifE errorLevel<>0 $goTo cleanup
$onEcho > connect.txt
eolonly 1
--SCHEMASUFFIX %sysEnv.CONNECTSQLSCHEMASUFFIX%
--TABLEPREFIX %sysEnv.CONNECTSQLTABLEPREFIX%
--CONNECTIONTYPE postgres
--CONNECTIONDICT {'user':'postgres', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 22000, 'host': 'internal2.gams.com', 'database': 'testdb'}
--CONNECTIONDICTSQLALCHEMY {'username':'postgres', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 22000, 'host': 'internal2.gams.com', 'database': 'testdb', 'drivername': 'postgresql+psycopg2'}
$offEcho
$log testing postgres
$call gams writeTest.gms lo=%gams.lo% pf=connect.txt pf=nosub.txt
$ifE errorLevel<>0 $goTo cleanup
$log testing sqlserver
$set HAVEBCP 0
$ifI %system.FileSys% == unix $call which bcp > /dev/null
$ifI not %system.FileSys% == unix $call where -q bcp > nul
$ifE errorLevel==0 $set HAVEBCP 1
$onEcho > connect.txt
eolonly 1
--SCHEMASUFFIX Null
--TABLEPREFIX
--CONNECTIONTYPE sqlserver
--CONNECTIONDICT {'user':'sa', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%', 'port': 23000, 'host': 'internal2.gams.com', 'database': '%sysEnv.CONNECTSQLSCHEMASUFFIX%'}
--CONNECTIONDICTSQLALCHEMY {'username':'sa', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 23000, 'host': 'internal2.gams.com', 'database': '%sysEnv.CONNECTSQLSCHEMASUFFIX%', 'drivername': 'mssql+pymssql'}
--HAVEBCP %HAVEBCP%
$offEcho
$call gams writeTest.gms lo=%gams.lo% pf=connect.txt pf=sub.txt
$ifE errorLevel<>0 $goto cleanup
$label cleanup
$log dropping test schemas
$onEmbeddedCode Python:
import psycopg2, pymysql, pymssql
with psycopg2.connect(**{'user':'postgres', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 22000, 'host': 'internal2.gams.com', 'database': 'testdb'}) as conn:
cur = conn.cursor()
cur.execute(f"DROP SCHEMA IF EXISTS {schemaName} CASCADE;")
conn.commit()
cur.close()
with pymysql.connect(**{'user':'root', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 21000, 'host': 'internal2.gams.com', 'database': 'testdb'}) as conn:
cur = conn.cursor()
cur.execute(f"DROP SCHEMA IF EXISTS {schemaName};")
conn.commit()
cur.close()
with pymssql.connect(**{'user':'sa', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 23000, 'host': 'internal2.gams.com'}, autocommit=True) as conn:
cur = conn.cursor()
cur.execute("USE MASTER;")
cur.execute(f"""IF DB_ID('{schemaName}') IS NOT NULL DROP DATABASE {schemaName};""")
conn.commit()
cur.close()
$offEmbeddedCode
$ifE errorLevel<>0 $abort "Error occurred! Check log"
$log testing access
$if set RUNACCESS $call.checkErrorLevel gams writeAccess.gms lo=%gams.lo%
$onEcho>writePyodbc.gms
$log test global dTypeMap and columnEncloser
$onEmbeddedCode Connect:
- GDXReader:
file: t.gdx
symbols:
- name: j
newName: j_pyodbc_dtype_table
- SQLWriter:
connectionType: pyodbc
connection: %connection%
writeAll: True
ifExists: "replace"
dTypeMap: {"text": "TINYTEXT"}
columnEncloser: "`"
- SQLReader:
connection: %connection%
connectionType: pyodbc
symbols:
- name: j_pyodbc_dtype
query: "SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'testdb'
AND TABLE_NAME = 'j_pyodbc_dtype_table'
AND COLUMN_NAME = 'uni';"
type: set
valueColumns: ""
- PythonCode:
code: |
data = connect.container.data["j_pyodbc_dtype"].records.values.tolist()
expected = [['uni','tinytext', '']]
if data != expected:
raise Exception("Unexpected data j_pyodbc_dtype.")
$offEmbeddedCode
$log test writing empty symbols using pyodbc
$onEmbeddedCode Connect:
- PythonCode:
code: |
connect.container.addSet("k", domain=["*"])
connect.container.addParameter("p", domain=["*"])
- SQLWriter:
connectionType: pyodbc
connection: %connection%
columnEncloser: "`"
writeAll: True
ifExists: "replace"
$offEmbeddedCode
$log test retrieving empty tables created by empty symbols
$onEmbeddedCode Connect:
- SQLReader:
connectionType: pyodbc
connection: %connection%
symbols:
- name: k
query: "SELECT * FROM k"
type: set
valueColumns: lastCol
- name: p
query: "SELECT * FROM p"
type: par
- PythonCode:
code: |
data_k = connect.container["k"].records
cols_k = [0, 'element_text']
data_p = connect.container["p"].records
cols_p = [0, 'value']
if data_k is None or not data_k.empty:
raise Exception("Expected set >k< to have empty records.")
if data_p is None or not data_p.empty:
raise Exception("Expected parameter >p< to have empty records.")
if (data_k.columns != cols_k).any():
raise Exception("Unexpected columns for set >k<.")
if (data_p.columns != cols_p).any():
raise Exception("Unexpected columns for parameter >p<.")
$offEmbeddedCode
$offEcho
$log testing pyodbc options on KUMANEW as the DSN is setup only on KUMANEW
$onecho > connect.txt
eolonly 1
--CONNECTION {'DSN':'kumaNewMySQL'}
$offEcho
$ifE sameas('%system.computername%','KUMANEW') $call.checkErrorLevel gams writePyodbc.gms lo=%gams.lo% pf=connect.txt