10from gams
import GamsWorkspace
18 a(i)
'capacity of plant i in cases'
19 b(j)
'demand at market j in cases'
20 d(i,j)
'distance in thousands of miles';
22Scalar f
'freight in dollars per case per thousand miles' / 90 /;
24$
if not set gdxincname $abort
'no include file name for data file provided'
29Parameter c(i,j)
'transport cost in thousands of dollars per case';
30c(i,j) = f*d(i,j)/1000;
33 x(i,j)
'shipment quantities in cases'
34 z
'total transportation costs in thousands of dollars';
39 cost
'define objective function'
40 supply(i)
'observe supply limit at plant i'
41 demand(j)
'satisfy demand at market j';
43cost.. z =e= sum((i,j), c(i,j)*x(i,j));
45supply(i).. sum(j, x(i,j)) =l= a(i);
47demand(j).. sum(i, x(i,j)) =g= b(j);
51solve transport using lp minimizing z;
57def read_set(connection, db, query_string, set_name, set_dim, set_exp=""):
59 cursor = connection.cursor()
60 cursor.execute(query_string)
61 data = cursor.fetchall()
63 if len(data[0]) != set_dim:
65 "Number of fields in select statement does not match setDim"
68 i = db.add_set(set_name, set_dim, set_exp)
76 except Exception
as ex:
78 "Error: Failed to retrieve the required data from the database.\n{0}".format(
86def read_parameter(connection, db, query_string, par_name, par_dim, par_exp=""):
88 cursor = connection.cursor()
89 cursor.execute(query_string)
90 data = cursor.fetchall()
92 if len(data[0]) != par_dim + 1:
94 "Number of fields in select statement does not match par_dim+1"
97 a = db.add_parameter(par_name, par_dim, par_exp)
101 for idx
in range(len(row) - 1):
102 keys.append(str(row[idx]))
103 a.add_record(keys).value = row[par_dim]
105 except Exception
as ex:
107 "Error: Failed to retrieve the required data from the database.\n{0}".format(
117 var = db.get_variable(var_name)
118 if len(column_names) != var.dimension:
120 "Number of column names does not match the dimension of the variable"
123 cursor = connection.cursor()
125 cursor.execute(
"drop table " + var_name)
129 query =
"create table " + var_name +
"("
130 for col
in column_names:
131 query += col +
" varchar(64), "
132 query +=
"lvl double)"
133 cursor.execute(query)
137 query =
"insert into " + var_name +
"("
138 for col
in column_names:
140 query +=
"lvl) values ("
142 query +=
"'" + key +
"', "
143 query += str(rec.level) +
")"
144 cursor.execute(query)
147 except Exception
as ex:
149 "Error: Failed to write variable to the database.\n{0}".format(ex)
155 str_access_conn =
r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=..\..\..\..\apifiles\Data\transport.accdb"
157 connection = pyodbc.connect(str_access_conn)
158 except Exception
as ex:
160 "Error: Failed to create a database connection. \n{0}".format(ex)
168 db = ws.add_database()
171 str_access_conn =
r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=..\..\..\..\apifiles\Data\transport.accdb"
174 connection = pyodbc.connect(str_access_conn)
175 except Exception
as e:
176 raise Exception(f
"Error: Failed to create a database connection.\n{e}")
179 read_set(connection, db,
"SELECT Plant FROM plant",
"i", 1,
"canning plants")
180 read_set(connection, db,
"SELECT Market FROM Market",
"j", 1,
"markets")
186 "SELECT Plant,Capacity FROM Plant",
189 "capacity of plant i in cases",
194 "SELECT Market,Demand FROM Market",
197 "demand at market j in cases",
202 "SELECT Plant,Market,Distance FROM Distance",
205 "distance in thousands of miles",
212if __name__ ==
"__main__":
213 sys_dir = sys.argv[1]
if len(sys.argv) > 1
else None
214 ws = GamsWorkspace(system_directory=sys_dir)
220 job = ws.add_job_from_string(GAMS_MODEL)
221 opt = ws.add_options()
222 opt.defines[
"gdxincname"] = db.name
223 opt.all_model_types =
"xpress"
224 job.run(opt, databases=db)
def read_parameter(connection, db, query_string, par_name, par_dim, par_exp="")
def write_variable(connection, db, var_name, column_names)
def read_set(connection, db, query_string, set_name, set_dim, set_exp="")