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(
115def write_variable(connection, db, var_name, column_names):
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)
155def write_results(db):
156 str_access_conn =
r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=..\..\..\..\apifiles\Data\transport.accdb"
158 connection = pyodbc.connect(str_access_conn)
159 except Exception
as ex:
161 "Error: Failed to create a database connection. \n{0}".format(ex)
164 write_variable(connection, db,
"x", [
"i",
"j"])
168def read_from_access(ws):
169 db = ws.add_database()
172 str_access_conn =
r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=..\..\..\..\apifiles\Data\transport.accdb"
175 connection = pyodbc.connect(str_access_conn)
176 except Exception
as e:
177 raise Exception(f
"Error: Failed to create a database connection.\n{e}")
180 read_set(connection, db,
"SELECT Plant FROM plant",
"i", 1,
"canning plants")
181 read_set(connection, db,
"SELECT Market FROM Market",
"j", 1,
"markets")
187 "SELECT Plant,Capacity FROM Plant",
190 "capacity of plant i in cases",
195 "SELECT Market,Demand FROM Market",
198 "demand at market j in cases",
203 "SELECT Plant,Market,Distance FROM Distance",
206 "distance in thousands of miles",
213if __name__ ==
"__main__":
214 sys_dir = sys.argv[1]
if len(sys.argv) > 1
else None
215 work_dir = sys.argv[2]
if len(sys.argv) > 2
else None
216 ws = GamsWorkspace(system_directory=sys_dir, working_directory=work_dir)
219 db = read_from_access(ws)
222 job = ws.add_job_from_string(GAMS_MODEL)
223 opt = ws.add_options()
224 opt.defines[
"gdxincname"] = db.name
225 opt.all_model_types =
"xpress"
226 job.run(opt, databases=db)
229 write_results(job.out_db)