Description

This test ensures the correctness of the Connect agent PandasSQLReader.

Contributor: Vaibhavnath Jha, July 2022

Small Model of Type : GAMS

Category : GAMS Test library

Main file : capdsqlr.gms

$title 'Test Connect agent PandasSQLReader' (CAPDSQLR,SEQ=909)

$ontext
This test ensures the correctness of the Connect agent PandasSQLReader.

Contributor: Vaibhavnath Jha, July 2022
$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%


$onEchoV > SQLScript.sql
DROP TABLE IF EXISTS [scalarTable];

DROP TABLE IF EXISTS [parameterTable];

DROP TABLE IF EXISTS [setTable];

DROP TABLE IF EXISTS [distanceTableOne];

DROP TABLE IF EXISTS [distanceTableTwo];



CREATE TABLE [scalarTable] 
(
    [value_col] INTEGER PRIMARY KEY
);

INSERT INTO scalarTable([value_col]) VALUES(653);



CREATE TABLE [parameterTable] 
(
    [label] NVARCHAR(5)  PRIMARY KEY,
    [value_col1] NUMERIC(5, 3),
    [value_col2] NUMERIC(5, 3)
);

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 [setTable]
(
    [id] INTEGER PRIMARY KEY,
    [label1] NVARCHAR(5),
    [label2] NVARCHAR(5),
    [expText1] NVARCHAR(20),
    [expText2] NVARCHAR(20)
);

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] NVARCHAR(10),
    [newyork] NUMERIC(10, 3),
    [chicago] NUMERIC(10, 3),
    [topeka] NUMERIC(10, 3)
);

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] NVARCHAR(10),
    [j_place] NVARCHAR(10),
    [miles] NUMERIC(10, 3)
);

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);
$offEcho


$onEmbeddedCode Python:
import sqlite3

with open('SQLScript.sql', 'r') as sql_file:
    sql_script = sql_file.read()

sqliteConnection = sqlite3.connect('test.db')
cursor = sqliteConnection.cursor()

cursor.executescript(sql_script)

cursor.close()
$offEmbeddedCode


$log test connection string missing
$onEmbeddedCode Connect:
- PandasSQLReader:
    symbols:
      - name: scalarTable
        query: "SELECT * FROM scalarTable;"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test name missing
$onEmbeddedCode Connect:
- PandasSQLReader:
    connection: "sqlite:///test.db"
    symbols:
      - query: "SELECT * FROM scalarTable;"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test SQL query missing
$onEmbeddedCode Connect:
- PandasSQLReader:
    connection: "sqlite:///test.db"
    symbols:
      - name: scalarTable
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test no value column ("") for parameter
$onEmbeddedCode Connect:
- PandasSQLReader:
    connection: "sqlite:///test.db"
    symbols:
      - name: scalarTable
        query: "SELECT * FROM scalarTable;"
        valueColumns: ""
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test type option "set" missing
$onEmbeddedCode Connect:
- PandasSQLReader:
    connection: "sqlite:///test.db"
    symbols:
      - name: setTable
        query: "SELECT * FROM setTable;"
        valueColumns: "lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors


$log reading scalar
$onEmbeddedCode Connect:
- PandasSQLReader:
    connection: "sqlite:///test.db"
    symbols:
      - name: scalarTable
        query: "SELECT * FROM scalarTable;"
- 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:
- PandasSQLReader:
    connection: "sqlite:///test.db"
    symbols:
      - name: parameterTable
        query: "SELECT label, value_col1 FROM parameterTable;"
- 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 set with explanatoryText
$onEmbeddedCode Connect:
- PandasSQLReader:
    connection: "sqlite:///test.db"
    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:
- PandasSQLReader:
    connection: "sqlite:///test.db"
    symbols:
      - name: setTable
        query: "SELECT * FROM setTable;"
        type: set
- 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 NaN values with Empty string
$onEmbeddedCode Connect:
- PandasSQLReader:
    connection: "sqlite:///test.db"
    symbols:
      - name: setTable
        query: "SELECT * FROM setTable;"
        type: set
        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:
- PandasSQLReader:
    connection: "sqlite:///test.db"
    symbols:
      - name: distanceTableOne
        query: "SELECT source, newyork FROM distanceTableOne;"
- 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:
- PandasSQLReader:
    connection: "sqlite:///test.db"
    symbols:
      - name: distanceTableOne
        query: "SELECT newyork FROM distanceTableOne WHERE distanceTableOne.source LIKE 'seattle';"
- 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:
- PandasSQLReader:
    connection: "sqlite:///test.db"
    symbols:
      - name: distanceTableTwo
        query: "SELECT i_place, j_place, miles FROM distanceTableTwo;"
- 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 reading 2-dim parameter from 2-dim data with read_sql arguments
$onEmbeddedCode Connect:
- PandasSQLReader:
    connection: "sqlite:///test.db"
    symbols:
      - name: distanceTableTwo
        query: "distanceTableTwo"
        readSQLArguments: {"columns": ["id", "miles"]}
- PythonCode:
    code: |
        data = connect.container.data["distanceTableTwo"].records.values.tolist()
        expected = [['1', 2.5],
                    ['2', 1.7],
                    ['3', 1.8],
                    ['4', 2.5],
                    ['5', 1.8],
                    ['6', 1.4]]
        if data != expected:
            raise Exception("Unexpected data distanceTableTwo.") 
$offEmbeddedCode

$log reading 2-dim parameter using dTypeMap
$onEmbeddedCode Connect:
- PandasSQLReader:
    connection: "sqlite:///test.db"
    dTypeMap: {id: float, missing_column: int}
    symbols:
      - name: distanceTableTwo1
        query: "SELECT id, miles FROM distanceTableTwo"
      - name: distanceTableTwo2
        query: "SELECT id, miles FROM distanceTableTwo"
        dTypeMap: {}
- PythonCode:
    code: |
        data = connect.container.data["distanceTableTwo1"].records.values.tolist()
        expected = [['1.0', 2.5],
                    ['2.0', 1.7],
                    ['3.0', 1.8],
                    ['4.0', 2.5],
                    ['5.0', 1.8],
                    ['6.0', 1.4]]
        if data != expected:
            raise Exception("Unexpected data distanceTableTwo1.")
        data = connect.container.data["distanceTableTwo2"].records.values.tolist()
        expected = [['1', 2.5],
                    ['2', 1.7],
                    ['3', 1.8],
                    ['4', 2.5],
                    ['5', 1.8],
                    ['6', 1.4]]
        if data != expected:
            raise Exception("Unexpected data distanceTableTwo2.")
$offEmbeddedCode

$log reading 1-dim set without text
$onEmbeddedCode Connect:
- PandasSQLReader:
    connection: "sqlite:///test.db"
    symbols:
      - name: parameterTable
        query: "SELECT label FROM parameterTable;"
        type: set
- PythonCode:
    code: |
        data = connect.container.data["parameterTable"].records.values.tolist()
        expected = [['i1', ''],
                    ['i2', ''],
                    ['i3', '']]
        if data != expected:
            raise Exception("Unexpected data parameterTable.")
$offEmbeddedCode

$log reading parameter with multiple value columns
$onEmbeddedCode Connect:
- PandasSQLReader:
    connection: "sqlite:///test.db"
    symbols:
      - name: parameterTable
        query: "SELECT label, value_col1, value_col2 FROM parameterTable;"
        valueColumns: ["value_col1","value_col2"]
- PythonCode:
    code: |
        data = connect.container.data["parameterTable"].records.values.tolist()
        expected = [['i3', 'value_col1', 0.453],
                    ['i3', 'value_col2', 0.532],
                    ['i2', 'value_col1', 0.638],
                    ['i2', 'value_col2', 0.478],
                    ['i1', 'value_col1', 0.987],
                    ['i1', 'value_col2', 0.804]]
        if data != expected:
            raise Exception("Unexpected data parameterTable.")
$offEmbeddedCode