Description
This test ensures the correctness of the Connect agent SQLReader against the following DBMS 1. SQLite 2. Postgres 3. MySQL 4. SQL-Server 5. MS-Access 6. SQLAlchemy Contributor: Vaibhavnath Jha, March 2023 On the major platforms (Windows, Linux, Mac), GMSPYTHONLIB gets automatically set to use the internal Python installation in sysdir/GMSPython.
Small Model of Type : GAMS
Category : GAMS Test library
Main file : casqlr.gms
$title 'Test Connect agent SQLReader' (CASQLR,SEQ=909)
$onText
This test ensures the correctness of the Connect agent SQLReader against the following DBMS
1. SQLite
2. Postgres
3. MySQL
4. SQL-Server
5. MS-Access
6. SQLAlchemy
Contributor: Vaibhavnath Jha, March 2023
$offText
* On the major platforms (Windows, Linux, Mac), GMSPYTHONLIB gets automatically set
* to use the internal Python installation in sysdir/GMSPython.
$if not setEnv GMSPYTHONLIB $abort.noError Embedded code Python not ready to be used
$log --- Using Python library %sysEnv.GMSPYTHONLIB%
$onEmbeddedCode Python:
import string, os
from random import choices
schemaName = ''.join(choices(string.ascii_lowercase, k=10))
os.environ['CONNECTSQLSCHEMASUFFIX'] = schemaName
os.environ['CONNECTSQLTABLEPREFIX'] = schemaName + '.'
$offEmbeddedCode
$onEcho> sqlitescript.sql
CREATE TABLE scalarTable
(
value_col INTEGER PRIMARY KEY
);
INSERT INTO scalarTable(value_col) VALUES(653);
CREATE TABLE parameterTable
(
label TEXT,
value_col1 DOUBLE PRECISION,
value_col2 DOUBLE PRECISION
);
INSERT INTO parameterTable (label, value_col1, value_col2) VALUES('i3', 0.453, 0.532);
INSERT INTO parameterTable (label, value_col1, value_col2) VALUES('i2', 0.638, 0.478);
INSERT INTO parameterTable (label, value_col1, value_col2) VALUES('i1', 0.987, 0.804);
CREATE TABLE deciTable
(
label TEXT,
value_col1 DOUBLE PRECISION
);
INSERT INTO deciTable (label, value_col1) VALUES('i3', 121.12);
INSERT INTO deciTable (label, value_col1) VALUES('i2', 122.22);
INSERT INTO deciTable (label, value_col1) VALUES('i1', 123.32);
CREATE TABLE setTable
(
id INTEGER PRIMARY KEY,
label1 TEXT,
label2 TEXT,
expText1 TEXT,
expText2 TEXT
);
INSERT INTO setTable (id, label1, label2, expText1, expText2) VALUES(1, 'i1', 'j1', 'Text1', 'RandomText1');
INSERT INTO setTable (id, label1, label2, expText1, expText2) VALUES(2, 'i1', 'j2', 'Text2', 'RandomText2');
INSERT INTO setTable (id, label1, label2, expText1, expText2) VALUES(3, 'i1', 'j3', 'Text3', 'RandomText3');
INSERT INTO setTable (id, label1, label2, expText1, expText2) VALUES(4, 'i2', 'j1', NULL, 'RandomText4');
INSERT INTO setTable (id, label1, label2, expText1, expText2) VALUES(5, 'i2', 'j2', 'Text5', 'RandomText5');
INSERT INTO setTable (id, label1, label2, expText1, expText2) VALUES(6, 'i2', 'j3', NULL, 'RandomText6');
CREATE TABLE distanceTableOne
(
id INTEGER PRIMARY KEY,
source TEXT,
newyork DOUBLE PRECISION,
chicago DOUBLE PRECISION,
topeka DOUBLE PRECISION
);
INSERT INTO distanceTableOne (id, source, newyork, chicago, topeka) VALUES(1, 'seattle', 2.5, 1.7, 1.8);
INSERT INTO distanceTableOne (id, source, newyork, chicago, topeka) VALUES(2, 'san-diego', 2.5, 1.8, 1.4);
CREATE TABLE distanceTableTwo
(
id INTEGER PRIMARY KEY,
i_place TEXT,
j_place TEXT,
miles DOUBLE PRECISION
);
INSERT INTO distanceTableTwo (id, i_place, j_place, miles) VALUES(1, 'seattle','new-york', 2.5);
INSERT INTO distanceTableTwo (id, i_place, j_place, miles) VALUES(2, 'seattle','chicago', 1.7);
INSERT INTO distanceTableTwo (id, i_place, j_place, miles) VALUES(3, 'seattle','topeka', 1.8);
INSERT INTO distanceTableTwo (id, i_place, j_place, miles) VALUES(4, 'san-diego','new-york', 2.5);
INSERT INTO distanceTableTwo (id, i_place, j_place, miles) VALUES(5, 'san-diego','chicago', 1.8);
INSERT INTO distanceTableTwo (id, i_place, j_place, miles) VALUES(6, 'san-diego', 'topeka', 1.4);
CREATE TABLE notSortedTableOne
(
id INTEGER PRIMARY KEY,
i TEXT,
j TEXT,
value DOUBLE PRECISION
);
INSERT INTO notSortedTableOne (id, i, j, value) VALUES(1, 'i1','j2', 1.0);
INSERT INTO notSortedTableOne (id, i, j, value) VALUES(2, 'i1','j1', 2.0);
INSERT INTO notSortedTableOne (id, i, j, value) VALUES(3, 'i2','j1', 4.0);
INSERT INTO notSortedTableOne (id, i, j, value) VALUES(4, 'i2','j2', 3.0);
CREATE TABLE notSortedTableTwo
(
i TEXT,
j TEXT,
k1 DOUBLE PRECISION,
k2 DOUBLE PRECISION
);
INSERT INTO notSortedTableTwo (i, j, k1, k2) VALUES('i1', 'j2', NULL, 1.0);
INSERT INTO notSortedTableTwo (i, j, k1, k2) VALUES('i1', 'j1', 2.0, 3.0);
INSERT INTO notSortedTableTwo (i, j, k1, k2) VALUES('i2', 'j1', 6.0, NULL);
INSERT INTO notSortedTableTwo (i, j, k1, k2) VALUES('i2', 'j2', 4.0, 5.0);
$offEcho
$onEcho > testScript.sql
CREATE TABLE %sysEnv.CONNECTSQLTABLEPREFIX%scalarTable
(
value_col INTEGER PRIMARY KEY
);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%scalarTable(value_col) VALUES(653);
CREATE TABLE %sysEnv.CONNECTSQLTABLEPREFIX%parameterTable
(
label TEXT,
value_col1 DOUBLE PRECISION,
value_col2 DOUBLE PRECISION
);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%parameterTable (label, value_col1, value_col2) VALUES('i3', 0.453, 0.532);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%parameterTable (label, value_col1, value_col2) VALUES('i2', 0.638, 0.478);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%parameterTable (label, value_col1, value_col2) VALUES('i1', 0.987, 0.804);
CREATE TABLE %sysEnv.CONNECTSQLTABLEPREFIX%deciTable
(
label TEXT,
value_col1 DOUBLE PRECISION
);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%deciTable (label, value_col1) VALUES('i3', 121.12);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%deciTable (label, value_col1) VALUES('i2', 122.22);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%deciTable (label, value_col1) VALUES('i1', 123.32);
CREATE TABLE %sysEnv.CONNECTSQLTABLEPREFIX%setTable
(
id INTEGER PRIMARY KEY,
label1 TEXT,
label2 TEXT,
expText1 TEXT,
expText2 TEXT
);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%setTable (id, label1, label2, expText1, expText2) VALUES(1, 'i1', 'j1', 'Text1', 'RandomText1');
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%setTable (id, label1, label2, expText1, expText2) VALUES(2, 'i1', 'j2', 'Text2', 'RandomText2');
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%setTable (id, label1, label2, expText1, expText2) VALUES(3, 'i1', 'j3', 'Text3', 'RandomText3');
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%setTable (id, label1, label2, expText1, expText2) VALUES(4, 'i2', 'j1', NULL, 'RandomText4');
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%setTable (id, label1, label2, expText1, expText2) VALUES(5, 'i2', 'j2', 'Text5', 'RandomText5');
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%setTable (id, label1, label2, expText1, expText2) VALUES(6, 'i2', 'j3', NULL, 'RandomText6');
CREATE TABLE %sysEnv.CONNECTSQLTABLEPREFIX%distanceTableOne
(
id INTEGER PRIMARY KEY,
source TEXT,
newyork DOUBLE PRECISION,
chicago DOUBLE PRECISION,
topeka DOUBLE PRECISION
);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%distanceTableOne (id, source, newyork, chicago, topeka) VALUES(1, 'seattle', 2.5, 1.7, 1.8);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%distanceTableOne (id, source, newyork, chicago, topeka) VALUES(2, 'san-diego', 2.5, 1.8, 1.4);
CREATE TABLE %sysEnv.CONNECTSQLTABLEPREFIX%distanceTableTwo
(
id INTEGER PRIMARY KEY,
i_place TEXT,
j_place TEXT,
miles DOUBLE PRECISION
);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%distanceTableTwo (id, i_place, j_place, miles) VALUES(1, 'seattle','new-york', 2.5);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%distanceTableTwo (id, i_place, j_place, miles) VALUES(2, 'seattle','chicago', 1.7);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%distanceTableTwo (id, i_place, j_place, miles) VALUES(3, 'seattle','topeka', 1.8);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%distanceTableTwo (id, i_place, j_place, miles) VALUES(4, 'san-diego','new-york', 2.5);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%distanceTableTwo (id, i_place, j_place, miles) VALUES(5, 'san-diego','chicago', 1.8);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%distanceTableTwo (id, i_place, j_place, miles) VALUES(6, 'san-diego', 'topeka', 1.4);
CREATE TABLE %sysEnv.CONNECTSQLTABLEPREFIX%notSortedTableOne
(
id INTEGER PRIMARY KEY,
i TEXT,
j TEXT,
value DOUBLE PRECISION
);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%notSortedTableOne (id, i, j, value) VALUES(1, 'i1','j2', 1.0);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%notSortedTableOne (id, i, j, value) VALUES(2, 'i1','j1', 2.0);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%notSortedTableOne (id, i, j, value) VALUES(3, 'i2','j1', 4.0);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%notSortedTableOne (id, i, j, value) VALUES(4, 'i2','j2', 3.0);
CREATE TABLE %sysEnv.CONNECTSQLTABLEPREFIX%notSortedTableTwo
(
i TEXT,
j TEXT,
k1 DOUBLE PRECISION,
k2 DOUBLE PRECISION
);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%notSortedTableTwo (i, j, k1, k2) VALUES('i1', 'j2', NULL, 1.0);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%notSortedTableTwo (i, j, k1, k2) VALUES('i1', 'j1', 2.0, 3.0);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%notSortedTableTwo (i, j, k1, k2) VALUES('i2', 'j1', 6.0, NULL);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%notSortedTableTwo (i, j, k1, k2) VALUES('i2', 'j2', 4.0, 5.0);
$offEcho
* The following SQL Script creates 5 tables in MS-SQL(SQL Server).
* Two similar(almost) scripts are created because SQL-Server uses T-SQL queries.
$onEcho > SQLServerScript.sql
CREATE TABLE dbo.scalarTable
(
value_col INTEGER PRIMARY KEY
);
INSERT INTO dbo.scalarTable(value_col) VALUES(653);
CREATE TABLE dbo.parameterTable
(
label TEXT,
value_col1 DOUBLE PRECISION,
value_col2 DOUBLE PRECISION
);
INSERT INTO dbo.parameterTable VALUES('i3', 0.453, 0.532);
INSERT INTO dbo.parameterTable VALUES('i2', 0.638, 0.478);
INSERT INTO dbo.parameterTable VALUES('i1', 0.987, 0.804);
CREATE TABLE dbo.deciTable
(
label TEXT,
value_col1 DOUBLE PRECISION
);
INSERT INTO dbo.deciTable (label, value_col1) VALUES('i3', 121.12);
INSERT INTO dbo.deciTable (label, value_col1) VALUES('i2', 122.22);
INSERT INTO dbo.deciTable (label, value_col1) VALUES('i1', 123.32);
CREATE TABLE dbo.setTable
(
id INTEGER PRIMARY KEY,
label1 TEXT,
label2 TEXT,
expText1 TEXT,
expText2 TEXT
);
INSERT INTO dbo.setTable VALUES(1, 'i1', 'j1', 'Text1', 'RandomText1');
INSERT INTO dbo.setTable VALUES(2, 'i1', 'j2', 'Text2', 'RandomText2');
INSERT INTO dbo.setTable VALUES(3, 'i1', 'j3', 'Text3', 'RandomText3');
INSERT INTO dbo.setTable VALUES(4, 'i2', 'j1', NULL, 'RandomText4');
INSERT INTO dbo.setTable VALUES(5, 'i2', 'j2', 'Text5', 'RandomText5');
INSERT INTO dbo.setTable VALUES(6, 'i2', 'j3', NULL, 'RandomText6');
CREATE TABLE dbo.distanceTableOne
(
id INTEGER PRIMARY KEY,
source TEXT,
newyork DOUBLE PRECISION,
chicago DOUBLE PRECISION,
topeka DOUBLE PRECISION
);
INSERT INTO dbo.distanceTableOne VALUES(1, 'seattle', 2.5, 1.7, 1.8);
INSERT INTO dbo.distanceTableOne VALUES(2, 'san-diego', 2.5, 1.8, 1.4);
CREATE TABLE dbo.distanceTableTwo
(
id INTEGER PRIMARY KEY,
i_place TEXT,
j_place TEXT,
miles DOUBLE PRECISION
);
INSERT INTO dbo.distanceTableTwo VALUES(1, 'seattle','new-york', 2.5);
INSERT INTO dbo.distanceTableTwo VALUES(2, 'seattle','chicago', 1.7);
INSERT INTO dbo.distanceTableTwo VALUES(3, 'seattle','topeka', 1.8);
INSERT INTO dbo.distanceTableTwo VALUES(4, 'san-diego','new-york', 2.5);
INSERT INTO dbo.distanceTableTwo VALUES(5, 'san-diego','chicago', 1.8);
INSERT INTO dbo.distanceTableTwo VALUES(6, 'san-diego', 'topeka', 1.4);
CREATE TABLE dbo.notSortedTableOne
(
id INTEGER PRIMARY KEY,
i TEXT,
j TEXT,
value DOUBLE PRECISION
);
INSERT INTO dbo.notSortedTableOne VALUES(1, 'i1','j2', 1.0);
INSERT INTO dbo.notSortedTableOne VALUES(2, 'i1','j1', 2.0);
INSERT INTO dbo.notSortedTableOne VALUES(3, 'i2','j1', 4.0);
INSERT INTO dbo.notSortedTableOne VALUES(4, 'i2','j2', 3.0);
CREATE TABLE dbo.notSortedTableTwo
(
i TEXT,
j TEXT,
k1 DOUBLE PRECISION,
k2 DOUBLE PRECISION
);
INSERT INTO dbo.notSortedTableTwo (i, j, k1, k2) VALUES('i1', 'j2', NULL, 1.0);
INSERT INTO dbo.notSortedTableTwo (i, j, k1, k2) VALUES('i1', 'j1', 2.0, 3.0);
INSERT INTO dbo.notSortedTableTwo (i, j, k1, k2) VALUES('i2', 'j1', 6.0, NULL);
INSERT INTO dbo.notSortedTableTwo (i, j, k1, k2) VALUES('i2', 'j2', 4.0, 5.0);
$offEcho
* Executing SQL Scripts
$onEmbeddedCode Python:
import sqlite3, psycopg2, pymysql, pymssql
import os
# Create test schemas for server based databases
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()
# Executing prepared scripts
with sqlite3.connect(**{'database': f'{schemaName}.db'}) as conn:
with open('sqlitescript.sql', 'r') as sql_file:
sql_script = sql_file.read()
cur = conn.cursor()
cur.executescript(sql_script)
conn.commit()
cur.close()
with open('testScript.sql', 'r') as sql_file:
sql_script = sql_file.read()
sql_script_stripped = sql_script.split(";")
sql_script_stripped.pop()
with psycopg2.connect(**{'user':'postgres', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 22000, 'host': 'internal2.gams.com', 'database': 'testdb'}) as conn:
cur = conn.cursor()
cur.execute(sql_script)
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()
for ele in sql_script_stripped:
cur.execute(ele+ ";")
conn.commit()
cur.close()
with open('SQLServerScript.sql', 'r') as sql_file:
sql_script = sql_file.read()
with pymssql.connect(**{'user':'sa', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 23000, 'host': 'internal2.gams.com', 'database': schemaName}) as conn:
cur = conn.cursor()
cur.execute(sql_script)
conn.commit()
cur.close()
$offEmbeddedCode
$onEchoV > readTest.gms
$log %CONNECTIONTYPE% test connection dictionary missing
$onEmbeddedCode Connect:
- SQLReader:
symbols:
- name: scalarTable
query: "SELECT * FROM %TABLEPREFIX%scalarTable;"
valueColumns: "lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors
$log %CONNECTIONTYPE% test name missing
$onEmbeddedCode Connect:
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- query: "SELECT * FROM %TABLEPREFIX%scalarTable;"
valueColumns: "lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors
$log %CONNECTIONTYPE% test SQL query missing
$onEmbeddedCode Connect:
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: scalarTable
valueColumns: "lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors
$log %CONNECTIONTYPE% test type option "set" missing
$onEmbeddedCode Connect:
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: setTable
query: "SELECT * FROM %TABLEPREFIX%setTable;"
valueColumns: "lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors
$log %CONNECTIONTYPE% reading scalar
$onEmbeddedCode Connect:
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: scalarTable
query: "SELECT * FROM %TABLEPREFIX%scalarTable;"
valueColumns: "lastCol"
- PythonCode:
code: |
data = connect.container.data["scalarTable"].records.values.tolist()
expected = [[653.0]]
if data != expected:
raise Exception("Unexpected data scalarTable.")
$offEmbeddedCode
$log %CONNECTIONTYPE% reading parameter
$onEmbeddedCode Connect:
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: parameterTable
query: "SELECT label, value_col1 FROM %TABLEPREFIX%parameterTable;"
valueColumns: "lastCol"
- PythonCode:
code: |
data = connect.container.data["parameterTable"].records.values.tolist()
expected = [['i3', 0.453],
['i2', 0.638],
['i1', 0.987]]
if data != expected:
raise Exception("Unexpected data parameterTable.")
$offEmbeddedCode
$log %CONNECTIONTYPE% reading parameter with dTypeMap
$onEmbeddedCode Connect:
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: deciTable
query: "SELECT label, value_col1 FROM %TABLEPREFIX%deciTable;"
dTypeMap: {'value_col1': 'int64'}
valueColumns: "lastCol"
- PythonCode:
code: |
data = connect.container.data["deciTable"].records.values.tolist()
expected = [['i3', 121],
['i2', 122],
['i1', 123]]
if data != expected:
raise Exception("Unexpected data deciTable.")
$offEmbeddedCode
$log %CONNECTIONTYPE% reading set with SQLAlchemy
$onEmbeddedCode Connect:
- SQLReader:
connection: %CONNECTIONDICTSQLALCHEMY%
connectionType: sqlalchemy
symbols:
- name: setTable
query: "SELECT label1, label2, expText1 FROM %TABLEPREFIX%setTable;"
type: set
valueColumns: "lastCol"
valueSubstitutions: {.NaN: ""}
- PythonCode:
code: |
data = connect.container.data["setTable"].records.values.tolist()
expected = [['i1', 'j1', 'Text1'],
['i1', 'j2', 'Text2'],
['i1', 'j3', 'Text3'],
['i2', 'j1', ''],
['i2', 'j2', 'Text5'],
['i2', 'j3', '']]
if data != expected:
raise Exception("Unexpected data setTable.")
$offEmbeddedCode
$log %CONNECTIONTYPE% reading set without explanatoryText
$onEmbeddedCode Connect:
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: setTable
query: "SELECT * FROM %TABLEPREFIX%setTable;"
type: set
valueColumns: none
- PythonCode:
code: |
data = connect.container.data["setTable"].records.values.tolist()
expected = [['1', 'i1', 'j1', 'Text1', 'RandomText1', ''],
['2', 'i1', 'j2', 'Text2', 'RandomText2', ''],
['3', 'i1', 'j3', 'Text3', 'RandomText3', ''],
['5', 'i2', 'j2', 'Text5', 'RandomText5', '']]
if data != expected:
raise Exception("Unexpected data setTable.")
$offEmbeddedCode
$log %CONNECTIONTYPE% reading set without explanatoryText and substituting NULL values with Empty string
$onEmbeddedCode Connect:
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: setTable
query: "SELECT * FROM %TABLEPREFIX%setTable;"
type: set
valueColumns: none
indexSubstitutions: {.NaN: ""}
- PythonCode:
code: |
data = connect.container.data["setTable"].records.values.tolist()
expected = [['1', 'i1', 'j1', 'Text1', 'RandomText1', ''],
['2', 'i1', 'j2', 'Text2', 'RandomText2', ''],
['3', 'i1', 'j3', 'Text3', 'RandomText3', ''],
['4', 'i2', 'j1', '', 'RandomText4', ''],
['5', 'i2', 'j2', 'Text5', 'RandomText5', ''],
['6', 'i2', 'j3', '', 'RandomText6', ''],]
if data != expected:
raise Exception("Unexpected data setTable.")
$offEmbeddedCode
$log %CONNECTIONTYPE% reading 1-dim parameter from 2-dim data with SELECT query
$onEmbeddedCode Connect:
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: distanceTableOne
query: "SELECT source, newyork FROM %TABLEPREFIX%distanceTableOne;"
valueColumns: "lastCol"
- PythonCode:
code: |
data = connect.container.data["distanceTableOne"].records.values.tolist()
expected = [['seattle', 2.5],
['san-diego', 2.5]]
if data != expected:
raise Exception("Unexpected data distanceTableOne.")
$offEmbeddedCode
$log %CONNECTIONTYPE% Substituting multi-level indices through local indexSubstitutions
$onEmbeddedCode Connect:
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: distanceTableOne_indexSub
query: "SELECT * FROM distanceTableOne;"
valueColumns: ["newyork", "chicago", "topeka"]
indexSubstitutions: {'san-diego': 'detroit', 'newyork': 'miami'}
- PythonCode:
code: |
data = connect.container.data["distanceTableOne_indexSub"].records.values.tolist()
expected = [['1', 'seattle', 'miami', 2.5],
['1', 'seattle', 'chicago', 1.7],
['1', 'seattle', 'topeka', 1.8],
['2', 'detroit', 'miami', 2.5],
['2', 'detroit', 'chicago', 1.8],
['2', 'detroit', 'topeka', 1.4]]
if data != expected:
raise Exception("Unexpected data distanceTableOne_indexSub.")
$offEmbeddedCode
$log %CONNECTIONTYPE% Substituting indices through global indexSubstitutions
$onEmbeddedCode Connect:
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
indexSubstitutions: {'san-diego': 'detroit', 'newyork': 'miami', 'i2': 'i3', .NaN: ''}
symbols:
- name: distanceTableOne_indexSub
query: "SELECT * FROM distanceTableOne;"
valueColumns: ["newyork", "chicago", "topeka"]
- name: setTable_indexSub
query: "SELECT * FROM setTable;"
type: set
valueColumns: none
- PythonCode:
code: |
data1 = connect.container.data["distanceTableOne_indexSub"].records.values.tolist()
expected1 = [['1', 'seattle', 'miami', 2.5],
['1', 'seattle', 'chicago', 1.7],
['1', 'seattle', 'topeka', 1.8],
['2', 'detroit', 'miami', 2.5],
['2', 'detroit', 'chicago', 1.8],
['2', 'detroit', 'topeka', 1.4]]
if data1 != expected1:
raise Exception("Unexpected data distanceTableOne_indexSub.")
data2 = connect.container.data["setTable_indexSub"].records.values.tolist()
expected2 = [['1', 'i1', 'j1', 'Text1', 'RandomText1', ''],
['2', 'i1', 'j2', 'Text2', 'RandomText2', ''],
['3', 'i1', 'j3', 'Text3', 'RandomText3', ''],
['4', 'i3', 'j1', '', 'RandomText4', ''],
['5', 'i3', 'j2', 'Text5', 'RandomText5', ''],
['6', 'i3', 'j3', '', 'RandomText6', '']]
if data2 != expected2:
raise Exception("Unexpected data setTable_indexSub.")
$offEmbeddedCode
$log %CONNECTIONTYPE% Substituting values through global valueSubstitutions
$onEmbeddedCode Connect:
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
valueSubstitutions: {653: 700, 122.22: 400}
symbols:
- name: scalarTable_valueSub
query: "SELECT * FROM scalarTable;"
- name: deciTable_valueSub
query: "SELECT label, value_col1 FROM deciTable;"
- PythonCode:
code: |
data1 = connect.container.data["scalarTable_valueSub"].records.values.tolist()
expected1 = [[700.0]]
if data1 != expected1:
raise Exception("Unexpected data scalarTable_valueSub.")
data2 = connect.container.data["deciTable_valueSub"].records.values.tolist()
expected2 = [['i3', 121.12], ['i2', 400.0], ['i1', 123.32]]
if data2 != expected2:
raise Exception("Unexpected data deciTable_valueSub.")
$offEmbeddedCode
$log %CONNECTIONTYPE% reading scalar from 2-dim data with SELECT query
$onEmbeddedCode Connect:
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: distanceTableOne
query: "SELECT newyork FROM %TABLEPREFIX%distanceTableOne WHERE distanceTableOne.source LIKE 'seattle';"
valueColumns: "lastCol"
- PythonCode:
code: |
data = connect.container.data["distanceTableOne"].records.values.tolist()
expected = [[2.5]]
if data != expected:
raise Exception("Unexpected data distanceTableOne.")
$offEmbeddedCode
$log %CONNECTIONTYPE% reading 2-dim parameter from 2-dim data
$onEmbeddedCode Connect:
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: distanceTableTwo
query: "SELECT i_place, j_place, miles FROM %TABLEPREFIX%distanceTableTwo;"
valueColumns: "lastCol"
- PythonCode:
code: |
data = connect.container.data["distanceTableTwo"].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 distanceTableTwo.")
$offEmbeddedCode
$log test SQLReader create categorical but never sort and SQLWriter always sort
$log %CONNECTIONTYPE% reading 2-dim parameter from 2-dim data
$onEmbeddedCode Connect:
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: notSortedTableOne
query: "SELECT i, j, value FROM %TABLEPREFIX%notSortedTableOne;"
valueColumns: "lastCol"
- PythonCode:
code: |
# checks that categoricals are correct
sym = connect.container.data["notSortedTableOne"]
data_sorted = sym.records.sort_values(sym.records.columns[:-1].tolist()).values.tolist()
expected = [['i1', 'j2', 1.0],
['i1', 'j1', 2.0],
['i2', 'j2', 3.0],
['i2', 'j1', 4.0]]
if data_sorted != expected:
raise Exception("Unexpected sorted data notSortedTableOne.")
# reorder UELs to check that SQLWriter sorts
sym.reorderUELs(uels=['j1', 'j2'], dimensions=1)
- SQLWriter:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
schemaName: %SCHEMASUFFIX%
symbols:
- name: notSortedTableOne
tableName: SortedTableOne
ifExists: "replace"
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: SortedTableOne
query: "SELECT * FROM %TABLEPREFIX%SortedTableOne;"
valueColumns: "lastCol"
- PythonCode:
code: |
# checks that SQLWriter sorts
sym = connect.container.data["SortedTableOne"]
data_sorted = sym.records.sort_values(sym.records.columns[:-1].tolist()).values.tolist()
expected = [['i1', 'j1', 2.0],
['i1', 'j2', 1.0],
['i2', 'j1', 4.0],
['i2', 'j2', 3.0]]
if data_sorted != expected:
raise Exception("Unexpected sorted data SortedTableOne.")
$offEmbeddedCode
$log %CONNECTIONTYPE% reading 3-dim parameter from 3-dim data
$onEmbeddedCode Connect:
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: notSortedTableTwo
query: "SELECT i, j, k1, k2 FROM %TABLEPREFIX%notSortedTableTwo;"
valueColumns: ["k1", "k2"]
- PythonCode:
code: |
# checks that categoricals are correct
sym = connect.container.data["notSortedTableTwo"]
data_sorted = sym.records.sort_values(sym.records.columns[:-1].tolist()).values.tolist()
expected = [['i1', 'j2', 'k2', 1.0],
['i1', 'j1', 'k1', 2.0],
['i1', 'j1', 'k2', 3.0],
['i2', 'j2', 'k1', 4.0],
['i2', 'j2', 'k2', 5.0],
['i2', 'j1', 'k1', 6.0]]
if data_sorted != expected:
raise Exception("Unexpected sorted data notSortedTableTwo.")
# reorder UELs to check that SQLWriter sorts
sym.reorderUELs(uels=['i2', 'i1'], dimensions=0)
sym.reorderUELs(uels=['j1', 'j2'], dimensions=1)
sym.reorderUELs(uels=['k2', 'k1'], dimensions=2)
- SQLWriter:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
schemaName: %SCHEMASUFFIX%
symbols:
- name: notSortedTableTwo
tableName: SortedTableTwo
ifExists: "replace"
unstack: True
- SQLReader:
connection: %CONNECTIONDICT%
connectionType: %CONNECTIONTYPE%
symbols:
- name: SortedTableTwo
query: "SELECT * FROM %TABLEPREFIX%SortedTableTwo;"
valueColumns: ["k1", "k2"]
- PythonCode:
code: |
# checks that SQLWriter sorts
sym = connect.container.data["SortedTableTwo"]
data_sorted = sym.records.sort_values(sym.records.columns[:-1].tolist()).values.tolist()
expected = [['i2', 'j1', 'k1', 6.0],
['i2', 'j2', 'k2', 5.0],
['i2', 'j2', 'k1', 4.0],
['i1', 'j1', 'k2', 3.0],
['i1', 'j1', 'k1', 2.0],
['i1', 'j2', 'k2', 1.0]]
if data_sorted != expected:
raise Exception("Unexpected sorted data SortedTableTwo.")
$offEmbeddedCode
$offEcho
$onEchoV.access > readAccessTest.gms
$call rm -f testdb.accdb
$call rm -f distanceTableOne.csv
$call rm -f distanceTableTwo.csv
$call rm -f parameterTable.csv
$call rm -f deciTable.csv
$call rm -f scalarTable.csv
$call rm -f setTable.csv
$onEcho > distanceTableOne.csv
id,source,newyork,chicago,topeka
1,seattle,2.5,1.7,1.8
2,san-diego,2.5,1.8,1.4
$offEcho
$onEcho > distanceTableTwo.csv
id,i_place,j_place,miles
1,seattle,new-york,2.5
2,seattle,chicago,1.7
3,seattle,topeka,1.8
4,san-diego,new-york,2.5
5,san-diego,chicago,1.8
6,san-diego,topeka,1.4
$offEcho
$onEcho > parameterTable.csv
label,value_col1,value_col2
i3,0.453,0.532
i2,0.638,0.478
i1,0.987,0.804
$offEcho
$onEcho > deciTable.csv
label,value_col1
i3,121.12
i2,122.22
i1,123.32
$offEcho
$onEcho > scalarTable.csv
value_col
653
$offEcho
$onEcho > setTable.csv
id,label1,label2,expText1,expText2
1,i1,j1,Text1,RandomText1
2,i1,j2,Text2,RandomText2
3,i1,j3,Text3,RandomText3
4,i2,j1,,RandomText4
5,i2,j2,Text5,RandomText5
6,i2,j3,,RandomText6
$offEcho
$onEmbeddedCode Python:
import win32com.client as win32
import pyodbc
Access = win32.Dispatch("Access.Application")
#Create a new .accdb file, this takes care of dropping old existing tables
Access.NewCurrentDataBase(r"%system.fp%testdb.accdb")
Access.CloseCurrentDataBase()
Access.Quit()
del Access
with pyodbc.connect(**{'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': r'%system.fp%testdb.accdb'}) as conn:
cur = conn.cursor()
csvs = ['distanceTableOne', 'distanceTableTwo', 'parameterTable', 'deciTable', 'scalarTable', 'setTable']
for file in csvs:
cur.execute(r"SELECT * INTO [{0}] FROM [text;HDR=Yes;FMT=Delimited(,);Database=%system.fp%].{0}.csv;".format(file))
conn.commit()
cur.close()
$offEmbeddedCode
$log test connection dictionary missing
$onEmbeddedCode Connect:
- SQLReader:
connectionType: access
symbols:
- name: scalarTable
query: "SELECT * FROM scalarTable;"
valueColumns: "lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors
$log test wrong connection type (default: sqlite)
$onEmbeddedCode Connect:
- SQLReader:
connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
connectionType: wrongpyodbc
symbols:
- name: scalarTable
query: "SELECT * FROM scalarTable;"
valueColumns: "lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors
$log test name missing
$onEmbeddedCode Connect:
- SQLReader:
connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
connectionType: access
symbols:
- query: "SELECT * FROM scalarTable;"
valueColumns: "lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors
$log test SQL query missing
$onEmbeddedCode Connect:
- SQLReader:
connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
connectionType: access
symbols:
- name: scalarTable
valueColumns: "lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors
$log test type option "set" missing
$onEmbeddedCode Connect:
- SQLReader:
connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
connectionType: access
symbols:
- name: setTable
query: "SELECT * FROM setTable;"
valueColumns: "lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors
$log reading scalar
$onEmbeddedCode Connect:
- SQLReader:
connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
connectionType: access
symbols:
- name: scalarTable
query: "SELECT * FROM scalarTable;"
valueColumns: "lastCol"
- PythonCode:
code: |
data = connect.container.data["scalarTable"].records.values.tolist()
expected = [[653.0]]
if data != expected:
raise Exception("Unexpected data scalarTable.")
$offEmbeddedCode
$log reading parameter
$onEmbeddedCode Connect:
- SQLReader:
connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
connectionType: access
symbols:
- name: parameterTable
query: "SELECT label, value_col1 FROM parameterTable;"
valueColumns: "lastCol"
- PythonCode:
code: |
data = connect.container.data["parameterTable"].records.values.tolist()
expected = [['i3', 0.453],
['i2', 0.638],
['i1', 0.987]]
if data != expected:
raise Exception("Unexpected data parameterTable.")
$offEmbeddedCode
$log reading parameter with dTypeMap
$onEmbeddedCode Connect:
- SQLReader:
connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
connectionType: access
symbols:
- name: deciTable
query: "SELECT label, value_col1 FROM deciTable;"
dTypeMap: {'value_col1': 'int64'}
valueColumns: "lastCol"
- PythonCode:
code: |
data = connect.container.data["deciTable"].records.values.tolist()
expected = [['i3', 121],
['i2', 122],
['i1', 123]]
if data != expected:
raise Exception("Unexpected data deciTable.")
$offEmbeddedCode
$log reading set with explanatoryText
$onEmbeddedCode Connect:
- SQLReader:
connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
connectionType: access
symbols:
- name: setTable
query: "SELECT label1, label2, expText1 FROM setTable;"
type: set
valueColumns: "lastCol"
valueSubstitutions: {.NaN: ""}
- PythonCode:
code: |
data = connect.container.data["setTable"].records.values.tolist()
expected = [['i1', 'j1', 'Text1'],
['i1', 'j2', 'Text2'],
['i1', 'j3', 'Text3'],
['i2', 'j1', ''],
['i2', 'j2', 'Text5'],
['i2', 'j3', '']]
if data != expected:
raise Exception("Unexpected data setTable.")
$offEmbeddedCode
$log reading set without explanatoryText
$onEmbeddedCode Connect:
- SQLReader:
connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
connectionType: access
symbols:
- name: setTable
query: "SELECT * FROM setTable;"
type: set
valueColumns: none
- PythonCode:
code: |
data = connect.container.data["setTable"].records.values.tolist()
expected = [['1', 'i1', 'j1', 'Text1', 'RandomText1', ''],
['2', 'i1', 'j2', 'Text2', 'RandomText2', ''],
['3', 'i1', 'j3', 'Text3', 'RandomText3', ''],
['5', 'i2', 'j2', 'Text5', 'RandomText5', '']]
if data != expected:
raise Exception("Unexpected data setTable.")
$offEmbeddedCode
$log reading set without explanatoryText and substituting NULL values with Empty string
$onEmbeddedCode Connect:
- SQLReader:
connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
connectionType: access
symbols:
- name: setTable
query: "SELECT * FROM setTable;"
type: set
valueColumns: none
indexSubstitutions: {.NaN: ""}
- PythonCode:
code: |
data = connect.container.data["setTable"].records.values.tolist()
expected = [['1', 'i1', 'j1', 'Text1', 'RandomText1', ''],
['2', 'i1', 'j2', 'Text2', 'RandomText2', ''],
['3', 'i1', 'j3', 'Text3', 'RandomText3', ''],
['4', 'i2', 'j1', '', 'RandomText4', ''],
['5', 'i2', 'j2', 'Text5', 'RandomText5', ''],
['6', 'i2', 'j3', '', 'RandomText6', ''],]
if data != expected:
raise Exception("Unexpected data setTable.")
$offEmbeddedCode
$log reading 1-dim parameter from 2-dim data with SELECT query
$onEmbeddedCode Connect:
- SQLReader:
connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
connectionType: access
symbols:
- name: distanceTableOne
query: "SELECT source, newyork FROM distanceTableOne;"
valueColumns: "lastCol"
- PythonCode:
code: |
data = connect.container.data["distanceTableOne"].records.values.tolist()
expected = [['seattle', 2.5],
['san-diego', 2.5]]
if data != expected:
raise Exception("Unexpected data distanceTableOne.")
$offEmbeddedCode
$log reading scalar from 2-dim data with SELECT query
$onEmbeddedCode Connect:
- SQLReader:
connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
connectionType: access
symbols:
- name: distanceTableOne
query: "SELECT newyork FROM distanceTableOne WHERE distanceTableOne.source LIKE 'seattle';"
valueColumns: "lastCol"
- PythonCode:
code: |
data = connect.container.data["distanceTableOne"].records.values.tolist()
expected = [[2.5]]
if data != expected:
raise Exception("Unexpected data distanceTableOne.")
$offEmbeddedCode
$log reading 2-dim parameter from 2-dim data
$onEmbeddedCode Connect:
- SQLReader:
connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
connectionType: access
symbols:
- name: distanceTableTwo
query: "SELECT i_place, j_place, miles FROM distanceTableTwo;"
valueColumns: "lastCol"
- PythonCode:
code: |
data = connect.container.data["distanceTableTwo"].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 distanceTableTwo.")
$offEmbeddedCode
$offEcho.access
$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 readTest.gms lo=%gams.lo% pf=connect.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 readTest.gms lo=%gams.lo% pf=connect.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 readTest.gms lo=%gams.lo% pf=connect.txt
$ifE errorLevel<>0 $goTo cleanup
$onEcho > connect.txt
eolonly 1
--SCHEMASUFFIX Null
--TABLEPREFIX dbo.
--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'}
$offEcho
$log testing sqlserver
$call gams readTest.gms lo=%gams.lo% pf=connect.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 readAccessTest.gms lo=%gams.lo%
*$ifE sameas('%system.FileSys%','MSNT')and(not(sameas('%system.computername%','KERMIT'))) $call.checkErrorLevel gams readAccessTest.gms lo=%gams.lo%
$log Test that empty table reads as an empty symbol (Not None)
$onEmbeddedCode Connect:
- PythonCode:
code: |
import sqlite3
conn = sqlite3.connect('%sysEnv.CONNECTSQLSCHEMASUFFIX%.db')
cursor = conn.cursor()
create_table = """
CREATE TABLE %sysEnv.CONNECTSQLSCHEMASUFFIX%emptyTable (
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER
);
"""
cursor.execute(create_table)
conn.commit()
conn.close()
- SQLReader:
connection: {"database": "%sysEnv.CONNECTSQLSCHEMASUFFIX%.db"}
symbols:
- name: d
query: "SELECT * FROM %sysEnv.CONNECTSQLSCHEMASUFFIX%emptyTable;"
type: par
- PythonCode:
code: |
data = connect.container['d'].records
expected_cols = [0, 1, 'value']
if data is None or not data.empty:
raise Exception("Expected >d< to have an empty DataFrame.")
if (data.columns != expected_cols).any():
raise Exception("Unexpected columns for >d<.")
$offEmbeddedCode
$if not errorFree $abort 'Errors'
$log Test raising an exception for existing symbol name
$onEmbeddedCode Connect:
- PythonCode:
code: |
connect.container.addSet("i")
connect.container.addSet("j")
connect.container.addParameter("d", domain=["i", "j"])
- PythonCode:
code: |
import sqlite3
conn = sqlite3.connect('%sysEnv.CONNECTSQLSCHEMASUFFIX%.db')
cursor = conn.cursor()
create_table = """
CREATE TABLE %sysEnv.CONNECTSQLSCHEMASUFFIX%existingSymbol (
i INTEGER PRIMARY KEY,
j TEXT,
age INTEGER
);
"""
cursor.execute(create_table)
conn.commit()
conn.close()
- SQLReader:
connection: {"database": "%sysEnv.CONNECTSQLSCHEMASUFFIX%.db"}
symbols:
- name: d
query: "SELECT * FROM %sysEnv.CONNECTSQLSCHEMASUFFIX%existingSymbol;"
type: par
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors