# Data Command Files¶

Note

The discussion and presentation below are adapted from Chapter 6 of
the “Pyomo Book” [PyomoBookII]. The discussion of the
`DataPortal`

class uses these same examples to illustrate how data can be loaded
into Pyomo models within Python scripts (see the
Data Portals section).

## Model Data¶

Pyomo’s *data command files* employ a domain-specific language whose
syntax closely resembles the syntax of AMPL’s data commands [AMPL]. A
data command file consists of a sequence of commands that either (a)
specify set and parameter data for a model, or (b) specify where such
data is to be obtained from external sources (e.g. table files, CSV
files, spreadsheets and databases).

The following commands are used to declare data:

- The
`set`

command declares set data. - The
`param`

command declares a table of parameter data, which can also include the declaration of the set data used to index the parameter data. - The
`table`

command declares a two-dimensional table of parameter data. - The
`load`

command defines how set and parameter data is loaded from external data sources, including ASCII table files, CSV files, XML files, YAML files, JSON files, ranges in spreadsheets, and database tables.

The following commands are also used in data command files:

- The
`include`

command specifies a data command file that is processed immediately. - The
`data`

and`end`

commands do not perform any actions, but they provide compatibility with AMPL scripts that define data commands. - The
`namespace`

keyword allows data commands to be organized into named groups that can be enabled or disabled during model construction.

The following data types can be represented in a data command file:

**Numeric value**: Any Python numeric value (e.g. integer, float, scientific notation, or boolean).**Simple string**: A sequence of alpha-numeric characters.**Quoted string**: A simple string that is included in a pair of single or double quotes. A quoted string can include quotes within the quoted string.

Numeric values are automatically converted to Python integer or floating point values when a data command file is parsed. Additionally, if a quoted string can be intepreted as a numeric value, then it will be converted to Python numeric types when the data is parsed. For example, the string “100” is converted to a numeric value automatically.

Warning

Pyomo data commands do *not* exactly correspond to AMPL data
commands. The `set`

and `param`

commands are designed to
closely match AMPL’s syntax and semantics, though these commands
only support a subset of the corresponding declarations in AMPL.
However, other Pyomo data commands are not generally designed to
match the semantics of AMPL.

Note

Pyomo data commands are terminated with a semicolon, and the syntax of data commands does not depend on whitespace. Thus, data commands can be broken across multiple lines – newlines and tab characters are ignored – and data commands can be formatted with whitespace with few restrictions.

## The `set`

Command¶

### Simple Sets¶

The `set`

data command explicitly specifies the members of either a
single set or an array of sets, i.e., an indexed set. A single set is
specified with a list of data values that are included in this set. The
formal syntax for the set data command is:

```
set <setname> := [<value>] ... ;
```

A set may be empty, and it may contain any combination of numeric and
non-numeric string values. For example, the following are valid `set`

commands:

```
# An empty set
set A := ;
# A set of numbers
set A := 1 2 3;
# A set of strings
set B := north south east west;
# A set of mixed types
set C :=
0
-1.0e+10
'foo bar'
infinity
"100"
;
```

### Sets of Tuple Data¶

The `set`

data command can also specify tuple data with the standard
notation for tuples. For example, suppose that set `A`

contains
3-tuples:

```
model.A = Set(dimen=3)
```

The following `set`

data command then specifies that `A`

is the set
containing the tuples `(1,2,3)`

and `(4,5,6)`

:

```
set A := (1,2,3) (4,5,6) ;
```

Alternatively, set data can simply be listed in the order that the tuple is represented:

```
set A := 1 2 3 4 5 6 ;
```

Obviously, the number of data elements specified using this syntax should be a multiple of the set dimension.

Sets with 2-tuple data can also be specified in a matrix denoting set
membership. For example, the following `set`

data command declares
2-tuples in `A`

using plus (`+`

) to denote valid tuples and minus
(`-`

) to denote invalid tuples:

```
set A : A1 A2 A3 A4 :=
1 + - - +
2 + - + -
3 - + - - ;
```

This data command declares the following five 2-tuples: `('A1',1)`

,
`('A1',2)`

, `('A2',3)`

, `('A3',2)`

, and `('A4',1)`

.

Finally, a set of tuple data can be concisely represented with tuple
*templates* that represent a *slice* of tuple data. For example,
suppose that the set `A`

contains 4-tuples:

```
model.A = Set(dimen=4)
```

The following `set`

data command declares groups of tuples that are
defined by a template and data to complete this template:

```
set A :=
(1,2,*,4) A B
(*,2,*,4) A B C D ;
```

A tuple template consists of a tuple that contains one or more asterisk
(`*`

) symbols instead of a value. These represent indices where the
tuple value is replaced by the values from the list of values that
follows the tuple template. In this example, the following tuples are
in set `A`

:

```
(1, 2, 'A', 4)
(1, 2, 'B', 4)
('A', 2, 'B', 4)
('C', 2, 'D', 4)
```

### Set Arrays¶

The `set`

data command can also be used to declare data for a set
array. Each set in a set array must be declared with a separate `set`

data command with the following syntax:

```
set <set-name>[<index>] := [<value>] ... ;
```

Because set arrays can be indexed by an arbitrary set, the index value may be a numeric value, a non-numeric string value, or a comma-separated list of string values.

Suppose that a set `A`

is used to index a set `B`

as follows:

```
model.A = Set()
model.B = Set(model.A)
```

Then set `B`

is indexed using the values declared for set `A`

:

```
set A := 1 aaa 'a b';
set B[1] := 0 1 2;
set B[aaa] := aa bb cc;
set B['a b'] := 'aa bb cc';
```

## The `param`

Command¶

Simple or non-indexed parameters are declared in an obvious way, as shown by these examples:

```
param A := 1.4;
param B := 1;
param C := abc;
param D := true;
param E := 1.0e+04;
```

Parameters can be defined with numeric data, simple strings and quoted strings. Note that parameters cannot be defined without data, so there is no analog to the specification of an empty set.

### One-dimensional Parameter Data¶

Most parameter data is indexed over one or more sets, and there are a
number of ways the `param`

data command can be used to specify indexed
parameter data. One-dimensional parameter data is indexed over a single
set. Suppose that the parameter `B`

is a parameter indexed by the set
`A`

:

```
model.A = Set()
model.B = Param(model.A)
```

A `param`

data command can specify values for `B`

with a list of
index-value pairs:

```
set A := a c e;
param B := a 10 c 30 e 50;
```

Because whitespace is ignored, this example data command file can be reorganized to specify the same data in a tabular format:

```
set A := a c e;
param B :=
a 10
c 30
e 50
;
```

Multiple parameters can be defined using a single `param`

data
command. For example, suppose that parameters `B`

, `C`

, and `D`

are one-dimensional parameters all indexed by the set `A`

:

```
model.A = Set()
model.B = Param(model.A)
model.C = Param(model.A)
model.D = Param(model.A)
```

Values for these parameters can be specified using a single `param`

data command that declares these parameter names followed by a list of
index and parameter values:

```
set A := a c e;
param : B C D :=
a 10 -1 1.1
c 30 -3 3.3
e 50 -5 5.5
;
```

The values in the `param`

data command are interpreted as a list of
sublists, where each sublist consists of an index followed by the
corresponding numeric value.

Note that parameter values do not need to be defined for all indices. For example, the following data command file is valid:

```
set A := a c e g;
param : B C D :=
a 10 -1 1.1
c 30 -3 3.3
e 50 -5 5.5
;
```

The index `g`

is omitted from the `param`

command, and consequently
this index is not valid for the model instance that uses this data.
More complex patterns of missing data can be specified using the period
(`.`

) symbol to indicate a missing value. This syntax is useful when
specifying multiple parameters that do not necessarily have the same
index values:

```
set A := a c e;
param : B C D :=
a . -1 1.1
c 30 . 3.3
e 50 -5 .
;
```

This example provides a concise representation of parameters that share a common index set while using different index values.

Note that this data file specifies the data for set `A`

twice:
(1) when `A`

is defined and (2) implicitly when the parameters are
defined. An alternate syntax for `param`

allows the user to concisely
specify the definition of an index set along with associated parameters:

```
param : A : B C D :=
a 10 -1 1.1
c 30 -3 3.3
e 50 -5 5.5
;
```

Finally, we note that default values for missing data can also be
specified using the `default`

keyword:

```
set A := a c e;
param B default 0.0 :=
c 30
e 50
;
```

Note that default values can only be specified in `param`

commands
that define values for a single parameter.

### Multi-Dimensional Parameter Data¶

Multi-dimensional parameter data is indexed over either multiple sets or
a single multi-dimensional set. Suppose that parameter `B`

is a
parameter indexed by set `A`

that has dimension 2:

```
model.A = Set(dimen=2)
model.B = Param(model.A)
```

The syntax of the `param`

data command remains essentially the same
when specifying values for `B`

with a list of index and parameter
values:

```
set A := a 1 c 2 e 3;
param B :=
a 1 10
c 2 30
e 3 50;
```

Missing and default values are also handled in the same way with multi-dimensional index sets:

```
set A := a 1 c 2 e 3;
param B default 0 :=
a 1 10
c 2 .
e 3 50;
```

Similarly, multiple parameters can defined with a single `param`

data
command. Suppose that parameters `B`

, `C`

, and `D`

are parameters
indexed over set `A`

that has dimension 2:

```
model.A = Set(dimen=2)
model.B = Param(model.A)
model.C = Param(model.A)
model.D = Param(model.A)
```

These parameters can be defined with a single `param`

command that
declares the parameter names followed by a list of index and parameter
values:

```
set A := a 1 c 2 e 3;
param : B C D :=
a 1 10 -1 1.1
c 2 30 -3 3.3
e 3 50 -5 5.5
;
```

Similarly, the following `param`

data command defines the index set
along with the parameters:

```
param : A : B C D :=
a 1 10 -1 1.1
c 2 30 -3 3.3
e 3 50 -5 5.5
;
```

The `param`

command also supports a matrix syntax for specifying the
values in a parameter that has a 2-dimensional index. Suppose parameter
`B`

is indexed over set `A`

that has dimension 2:

```
model.A = Set(dimen=2)
model.B = Param(model.A)
```

The following `param`

command defines a matrix of parameter values:

```
set A := 1 a 1 c 1 e 2 a 2 c 2 e 3 a 3 c 3 e;
param B : a c e :=
1 1 2 3
2 4 5 6
3 7 8 9
;
```

Additionally, the following syntax can be used to specify a transposed matrix of parameter values:

```
set A := 1 a 1 c 1 e 2 a 2 c 2 e 3 a 3 c 3 e;
param B (tr) : 1 2 3 :=
a 1 4 7
c 2 5 8
e 3 6 9
;
```

This functionality facilitates the presentation of parameter data in a natural format. In particular, the transpose syntax may allow the specification of tables for which the rows comfortably fit within a single line. However, a matrix may be divided column-wise into shorter rows since the line breaks are not significant in Pyomo data commands.

For parameters with three or more indices, the parameter data values may
be specified as a series of slices. Each slice is defined by a template
followed by a list of index and parameter values. Suppose that
parameter `B`

is indexed over set `A`

that has dimension 4:

```
model.A = Set(dimen=4)
model.B = Param(model.A)
```

The following `param`

command defines a matrix of parameter values
with multiple templates:

```
set A := (a,1,a,1) (a,2,a,2) (b,1,b,1) (b,2,b,2);
param B :=
[*,1,*,1] a a 10 b b 20
[*,2,*,2] a a 30 b b 40
;
```

The `B`

parameter consists of four values: `B[a,1,a,1]=10`

,
`B[b,1,b,1]=20`

, `B[a,2,a,2]=30`

, and `B[b,2,b,2]=40`

.

## The `table`

Command¶

The `table`

data command explicitly specifies a two-dimensional array
of parameter data. This command provides a more flexible and complete
data declaration than is possible with a `param`

declaration. The
following example illustrates a simple `table`

command that declares
data for a single parameter:

```
table M(A) :
A B M N :=
A1 B1 4.3 5.3
A2 B2 4.4 5.4
A3 B3 4.5 5.5
;
```

The parameter `M`

is indexed by column `A`

, which must be
pre-defined unless declared separately (see below). The column labels
are provided after the colon and before the colon-equal (`:=`

).
Subsequently, the table data is provided. The syntax is not sensitive
to whitespace, so the following is an equivalent `table`

command:

```
table M(A) :
A B M N :=
A1 B1 4.3 5.3 A2 B2 4.4 5.4 A3 B3 4.5 5.5 ;
```

Multiple parameters can be declared by simply including additional parameter names. For example:

```
table M(A) N(A,B) :
A B M N :=
A1 B1 4.3 5.3
A2 B2 4.4 5.4
A3 B3 4.5 5.5
;
```

This example declares data for the `M`

and `N`

parameters, which
have different indexing columns. The indexing columns represent set
data, which is specified separately. For example:

```
table A={A} Z={A,B} M(A) N(A,B) :
A B M N :=
A1 B1 4.3 5.3
A2 B2 4.4 5.4
A3 B3 4.5 5.5
;
```

This example declares data for the `M`

and `N`

parameters, along
with the `A`

and `Z`

indexing sets. The correspondence between the
index set `Z`

and the indices of parameter `N`

can be made more
explicit by indexing `N`

by `Z`

:

```
table A={A} Z={A,B} M(A) N(Z) :
A B M N :=
A1 B1 4.3 5.3
A2 B2 4.4 5.4
A3 B3 4.5 5.5
;
```

Set data can also be specified independent of parameter data:

```
table Z={A,B} Y={M,N} :
A B M N :=
A1 B1 4.3 5.3
A2 B2 4.4 5.4
A3 B3 4.5 5.5
;
```

Warning

If a `table`

command does not explicitly indicate the indexing
sets, then these are assumed to be initialized separately. A
`table`

command can separately initialize sets and parameters in a
Pyomo model, and there is no presumed association between the data
that is initialized. For example, the `table`

command initializes
a set `Z`

and a parameter `M`

that are not related:

```
table Z={A,B} M(A):
A B M N :=
A1 B1 4.3 5.3
A2 B2 4.4 5.4
A3 B3 4.5 5.5
;
```

Finally, simple parameter values can also be specified with a `table`

command:

```
table pi := 3.1416 ;
```

The previous examples considered examples of the `table`

command where
column labels are provided. The `table`

command can also be used
without column labels. For example, the first example can be revised to
omit column labels as follows:

```
table columns=4 M(1)={3} :=
A1 B1 4.3 5.3
A2 B2 4.4 5.4
A3 B3 4.5 5.5
;
```

The `columns=4`

is a keyword-value pair that defines the number of
columns in this table; this must be explicitly specified in tables
without column labels. The default column labels are integers starting
from `1`

; the labels are columns `1`

, `2`

, `3`

, and `4`

in
this example. The `M`

parameter is indexed by column `1`

. The
braces syntax declares the column where the `M`

data is provided.

Similarly, set data can be declared referencing the integer column labels:

```
table columns=4 A={1} Z={1,2} M(1)={3} N(1,2)={4} :=
A1 B1 4.3 5.3
A2 B2 4.4 5.4
A3 B3 4.5 5.5
;
```

Declared set names can also be used to index parameters:

```
table columns=4 A={1} Z={1,2} M(A)={3} N(Z)={4} :=
A1 B1 4.3 5.3
A2 B2 4.4 5.4
A3 B3 4.5 5.5
;
```

Finally, we compare and contrast the `table`

and `param`

commands.
Both commands can be used to declare parameter and set data, and both
commands can be used to declare a simple parameter. However, there are
some important differences between these data commands:

- The
`param`

command can declare a single set that is used to index one or more parameters. The`table`

command can declare data for any number of sets, independent of whether they are used to index parameter data. - The
`param`

command can declare data for multiple parameters only if they share the same index set. The`table`

command can declare data for any number of parameters that are may be indexed separately. - The
`table`

syntax unambiguously describes the dimensionality of indexing sets. The`param`

command must be interpreted with a model that provides the dimension of the indexing set.

This last point provides a key motivation for the `table`

command.
Specifically, the `table`

command can be used to reliably initialize
concrete models using Pyomo’s `DataPortal`

class. By contrast, the
`param`

command can only be used to initialize concrete models with
parameters that are indexed by a single column (i.e., a simple set).

## The `load`

Command¶

The `load`

command provides a mechanism for loading data from a
variety of external tabular data sources. This command loads a table of
data that represents set and parameter data in a Pyomo model. The table
consists of rows and columns for which all rows have the same length,
all columns have the same length, and the first row represents labels
for the column data.

The `load`

command can load data from a variety of different external
data sources:

**TAB File**: A text file format that uses whitespace to separate columns of values in each row of a table.**CSV File**: A text file format that uses comma or other delimiters to separate columns of values in each row of a table.**XML File**: An extensible markup language for documents and data structures. XML files can represent tabular data.**Excel File**: A spreadsheet data format that is primarily used by the Microsoft Excel application.**Database**: A relational database.

This command uses a *data manager* that coordinates how data is
extracted from a specified *data source*. In this way, the `load`

command provides a generic mechanism that enables Pyomo models to
interact with standard data repositories that are maintained in an
application-specific manner.

### Simple Load Examples¶

The simplest illustration of the `load`

command is specifying data for
an indexed parameter. Consider the file `Y.tab`

:

```
A Y
A1 3.3
A2 3.4
A3 3.5
```

This file specifies the values of parameter `Y`

which is indexed by
set `A`

. The following `load`

command loads the parameter data:

```
load Y.tab : [A] Y;
```

The first argument is the filename. The options after the colon
indicate how the table data is mapped to model data. Option `[A]`

indicates that set `A`

is used as the index, and option `Y`

indicates the parameter that is initialized.

Similarly, the following load command loads both the parameter data as
well as the index set `A`

:

```
load Y.tab : A=[A] Y;
```

The difference is the specification of the index set, `A=[A]`

, which
indicates that set `A`

is initialized with the index loaded from the
ASCII table file.

Set data can also be loaded from a ASCII table file that contains a single column of data:

```
A
A1
A2
A3
```

The `format`

option must be specified to denote the fact that the
relational data is being interpreted as a set:

```
load A.tab format=set : A;
```

Note that this allows for specifying set data that contains tuples.
Consider file `C.tab`

:

```
A B
A1 1
A1 2
A1 3
A2 1
A2 2
A2 3
A3 1
A3 2
A3 3
```

A similar `load`

syntax will load this data into set `C`

:

```
load C.tab format=set : C;
```

Note that this example requires that `C`

be declared with dimension
two.

### Load Syntax Options¶

The syntax of the `load`

command is broken into two parts. The first
part ends with the colon, and it begins with a filename, database URL,
or DSN (data source name). Additionally, this first part can contain
option value pairs. The following options are recognized:

`format` |
A string that denotes how the relational table is interpreted |

`password` |
The password that is used to access a database |

`query` |
The query that is used to request data from a database |

`range` |
The subset of a spreadsheet that is requestedindex{spreadsheet} |

`user` |
The user name that is used to access the data source |

`using` |
The data manager that is used to process the data source |

`table` |
The database table that is requested |

The `format`

option is the only option that is required for all data
managers. This option specifies how a relational table is interpreted
to represent set and parameter data. If the `using`

option is
omitted, then the filename suffix is used to select the data manager.
The remaining options are specific to spreadsheets and relational
databases (see below).

The second part of the `load`

command consists of the specification of
column names for indices and data. The remainder of this section
describes different specifications and how they define how data is
loaded into a model. Suppose file `ABCD.tab`

defines the following
relational table:

```
A B C D
A1 B1 1 10
A2 B2 2 20
A3 B3 3 30
```

There are many ways to interpret this relational table. It could
specify a set of 4-tuples, a parameter indexed by 3-tuples, two
parameters indexed by 2-tuples, and so on. Additionally, we may wish to
select a subset of this table to initialize data in a model.
Consequently, the `load`

command provides a variety of syntax options
for specifying how a table is interpreted.

A simple specification is to interpret the relational table as a set:

```
load ABCD.tab format=set : Z ;
```

Note that `Z`

is a set in the model that the data is being loaded
into. If this set does not exist, an error will occur while loading
data from this table.

Another simple specification is to interpret the relational table as a parameter with indexed by 3-tuples:

```
load ABCD.tab : [A,B,C] D ;
```

Again, this requires that `D`

be a parameter in the model that the
data is being loaded into. Additionally, the index set for `D`

must
contain the indices that are specified in the table. The `load`

command also allows for the specification of the index set:

```
load ABCD.tab : Z=[A,B,C] D ;
```

This specifies that the index set is loaded into the `Z`

set in the
model. Similarly, data can be loaded into another parameter than what
is specified in the relational table:

```
load ABCD.tab : Z=[A,B,C] Y=D ;
```

This specifies that the index set is loaded into the `Z`

set and that
the data in the `D`

column in the table is loaded into the `Y`

parameter.

This syntax allows the `load`

command to provide an arbitrary
specification of data mappings from columns in a relational table into
index sets and parameters. For example, suppose that a model is defined
with set `Z`

and parameters `Y`

and `W`

:

```
model.Z = Set()
model.Y = Param(model.Z)
model.W = Param(model.Z)
```

Then the following command defines how these data items are loaded using
columns `B`

, `C`

and `D`

:

```
load ABCD.tab : Z=[B] Y=D W=C;
```

When the `using`

option is omitted the data manager is inferred from
the filename suffix. However, the filename suffix does not always
reflect the format of the data it contains. For example, consider the
relational table in the file `ABCD.txt`

:

```
A,B,C,D
A1,B1,1,10
A2,B2,2,20
A3,B3,3,30
```

We can specify the `using`

option to load from this file into
parameter `D`

and set `Z`

:

```
load ABCD.txt using=csv : Z=[A,B,C] D ;
```

Note

The data managers supported by Pyomo can be listed with the
`pyomo help`

subcommand

```
pyomo help --data-managers
```

The following data managers are supported in Pyomo 5.1:

Pyomo Data Managers ------------------- csv CSV file interface dat Pyomo data command file interface json JSON file interface pymysql pymysql database interface pyodbc pyodbc database interface pypyodbc pypyodbc database interface sqlite3 sqlite3 database interface tab TAB file interface xls Excel XLS file interface xlsb Excel XLSB file interface xlsm Excel XLSM file interface xlsx Excel XLSX file interface xml XML file interface yaml YAML file interface

### Interpreting Tabular Data¶

By default, a table is interpreted as columns of one or more parameters
with associated index columns. The `format`

option can be used to
specify other interpretations of a table:

`array` |
The table is a matrix representation of a two dimensional parameter. |

`param` |
The data is a simple parameter value. |

`set` |
Each row is a set element. |

`set_array` |
The table is a matrix representation of a set of 2-tuples. |

`transposed_array` |
The table is a transposed matrix representation of a two dimensional parameter. |

We have previously illustrated the use of the `set`

format value to
interpret a relational table as a set of values or tuples. The
following examples illustrate the other format values.

A table with a single value can be interpreted as a simple parameter
using the `param`

format value. Suppose that `Z.tab`

contains the
following table:

```
1.1
```

The following load command then loads this value into parameter `p`

:

```
load Z.tab format=param: p;
```

Sets with 2-tuple data can be represented with a matrix format that
denotes set membership. The `set_array`

format value interprets a
relational table as a matrix that defines a set of 2-tuples where `+`

denotes a valid tuple and `-`

denotes an invalid tuple. Suppose that
`D.tab`

contains the following relational table:

```
B A1 A2 A3
1 + - -
2 - + -
3 - - +
```

Then the following load command loads data into set `B`

:

```
load D.tab format=set_array: B;
```

This command declares the following 2-tuples: `('A1',1)`

,
`('A2',2)`

, and `('A3',3)`

.

Parameters with 2-tuple indices can be interpreted with a matrix format
that where rows and columns are different indices. Suppose that
`U.tab`

contains the following table:

```
I A1 A2 A3
I1 1.3 2.3 3.3
I2 1.4 2.4 3.4
I3 1.5 2.5 3.5
I4 1.6 2.6 3.6
```

Then the following load command loads this value into parameter `U`

with a 2-dimensional index using the `array`

format value.:

```
load U.tab format=array: A=[X] U;
```

The `transpose_array`

format value also interprets the table as a
matrix, but it loads the data in a transposed format:

```
load U.tab format=transposed_array: A=[X] U;
```

Note that these format values do not support the initialization of the index data.

### Loading from Spreadsheets and Relational Databases¶

Many of the options for the `load`

command are specific to
spreadsheets and relational databases. The `range`

option is used to
specify the range of cells that are loaded from a spreadsheet. The
range of cells represents a table in which the first row of cells
defines the column names for the table.

Suppose that file `ABCD.xls`

contains the range `ABCD`

that is shown
in the following figure:

The following command loads this data to initialize parameter `D`

and
index `Z`

:

```
load ABCD.xls range=ABCD : Z=[A,B,C] Y=D ;
```

Thus, the syntax for loading data from spreadsheets only differs from
CSV and ASCII text files by the use of the `range`

option.

When loading from a relational database, the data source specification
is a filename or data connection string. Access to a database may be
restricted, and thus the specification of `username`

and `password`

options may be required. Alternatively, these options can be specified
within a data connection string.

A variety of database interface packages are available within Python.
The `using`

option is used to specify the database interface package
that will be used to access a database. For example, the `pyodbc`

interface can be used to connect to Excel spreadsheets. The following
command loads data from the Excel spreadsheet `ABCD.xls`

using the
`pyodbc`

interface. The command loads this data to initialize
parameter `D`

and index `Z`

:

```
load ABCD.xls using=pyodbc table=ABCD : Z=[A,B,C] Y=D ;
```

The `using`

option specifies that the `pyodbc`

package will be
used to connect with the Excel spreadsheet. The `table`

option
specifies that the table `ABCD`

is loaded from this spreadsheet.
Similarly, the following command specifies a data connection string
to specify the ODBC driver explicitly:

```
load "Driver={Microsoft Excel Driver (*.xls)}; Dbq=ABCD.xls;"
using=pyodbc
table=ABCD : Z=[A,B,C] Y=D ;
```

ODBC drivers are generally tailored to the type of data source that
they work with; this syntax illustrates how the `load`

command
can be tailored to the details of the database that a user is working
with.

The previous examples specified the `table`

option, which declares the
name of a relational table in a database. Many databases support the
Structured Query Language (SQL), which can be used to dynamically
compose a relational table from other tables in a database. The classic
diet problem will be used to illustrate the use of SQL queries to
initialize a Pyomo model. In this problem, a customer is faced with the
task of minimizing the cost for a meal at a fast food restaurant – they
must purchase a sandwich, side, and a drink for the lowest cost. The
following is a Pyomo model for this problem:

```
# diet1.py
from pyomo.environ import *
infinity = float('inf')
MAX_FOOD_SUPPLY = 20.0 # There is a finite food supply
model = AbstractModel()
# --------------------------------------------------------
model.FOOD = Set()
model.cost = Param(model.FOOD, within=PositiveReals)
model.f_min = Param(model.FOOD, within=NonNegativeReals, default=0.0)
def f_max_validate (model, value, j):
return model.f_max[j] > model.f_min[j]
model.f_max = Param(model.FOOD, validate=f_max_validate, default=MAX_FOOD_SUPPLY)
model.NUTR = Set()
model.n_min = Param(model.NUTR, within=NonNegativeReals, default=0.0)
model.n_max = Param(model.NUTR, default=infinity)
model.amt = Param(model.NUTR, model.FOOD, within=NonNegativeReals)
# --------------------------------------------------------
def Buy_bounds(model, i):
return (model.f_min[i], model.f_max[i])
model.Buy = Var(model.FOOD, bounds=Buy_bounds, within=NonNegativeIntegers)
# --------------------------------------------------------
def Total_Cost_rule(model):
return sum(model.cost[j] * model.Buy[j] for j in model.FOOD)
model.Total_Cost = Objective(rule=Total_Cost_rule, sense=minimize)
# --------------------------------------------------------
def Entree_rule(model):
entrees = ['Cheeseburger', 'Ham Sandwich', 'Hamburger', 'Fish Sandwich', 'Chicken Sandwich']
return sum(model.Buy[e] for e in entrees) >= 1
model.Entree = Constraint(rule=Entree_rule)
def Side_rule(model):
sides = ['Fries', 'Sausage Biscuit']
return sum(model.Buy[s] for s in sides) >= 1
model.Side = Constraint(rule=Side_rule)
def Drink_rule(model):
drinks = ['Lowfat Milk', 'Orange Juice']
return sum(model.Buy[d] for d in drinks) >= 1
model.Drink = Constraint(rule=Drink_rule)
```

Suppose that the file `diet1.sqlite`

be a SQLite database file that
contains the following data in the `Food`

table:

FOOD |
cost |
---|---|

Cheeseburger | 1.84 |

Ham Sandwich | 2.19 |

Hamburger | 1.84 |

Fish Sandwich | 1.44 |

Chicken Sandwich | 2.29 |

Fries | 0.77 |

Sausage Biscuit | 1.29 |

Lowfat Milk | 0.60 |

Orange Juice | 0.72 |

In addition, the `Food`

table has two additional columns, `f_min`

and `f_max`

, with no data for any row. These columns exist to match
the structure for the parameters used in the model.

We can solve the `diet1`

model using the Python definition in
`diet1.py`

and the data from this database. The file
`diet.sqlite.dat`

specifies a `load`

command that uses that
`sqlite3`

data manager and embeds a SQL query to retrieve the data:

```
# File diet.sqlite.dat
load "diet.sqlite"
using=sqlite3
query="SELECT FOOD,cost,f_min,f_max FROM Food"
: FOOD=[FOOD] cost f_min f_max ;
```

The PyODBC driver module will pass the SQL query through an Access ODBC
connector, extract the data from the `diet1.mdb`

file, and return it
to Pyomo. The Pyomo ODBC handler can then convert the data received into
the proper format for solving the model internally. More complex SQL
queries are possible, depending on the underlying database and ODBC
driver in use. However, the name and ordering of the columns queried are
specified in the Pyomo data file; using SQL wildcards (e.g., ```
SELECT
*
```

) or column aliasing (e.g., `SELECT f AS FOOD`

) may cause errors in
Pyomo’s mapping of relational data to parameters.

## The `include`

Command¶

The `include`

command allows a data command file to execute data
commands from another file. For example, the following command file
executes data commands from `ex1.dat`

and then `ex2.dat`

:

```
include ex1.dat;
include ex2.dat;
```

Pyomo is sensitive to the order of execution of data commands, since
data commands can redefine set and parameter values. The `include`

command respects this data ordering; all data commands in the included
file are executed before the remaining data commands in the current file
are executed.

## The `namespace`

Keyword¶

The `namespace`

keyword is not a data command, but instead it is used
to structure the specification of Pyomo’s data commands. Specifically,
a namespace declaration is used to group data commands and to provide a
group label. Consider the following data command file:

```
set C := 1 2 3 ;
namespace ns1
{
set C := 4 5 6 ;
}
namespace ns2
{
set C := 7 8 9 ;
}
```

This data file defines two namespaces: `ns1`

and `ns2`

that
initialize a set `C`

. By default, data commands contained within a
namespace are ignored during model construction; when no namespaces are
specified, the set `C`

has values `1,2,3`

. When namespace `ns1`

is specified, then the set `C`

values are overridden with the set
`4,5,6`

.