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 interpreted 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:

../../_images/ABCD.png

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:

#  ___________________________________________________________________________
#
#  Pyomo: Python Optimization Modeling Objects
#  Copyright (c) 2008-2024
#  National Technology and Engineering Solutions of Sandia, LLC
#  Under the terms of Contract DE-NA0003525 with National Technology and
#  Engineering Solutions of Sandia, LLC, the U.S. Government retains certain
#  rights in this software.
#  This software is distributed under the 3-clause BSD License.
#  ___________________________________________________________________________

# 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.