Go to the documentation of this file.
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
21 Scalar f freight in dollars per case per thousand miles /90/;
23 $if not set gdxincname $abort 'no include file name for data file provided'
28 Parameter c(i,j) transport cost in thousands of dollars per case ;
30 c(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 ;
43 cost .. z =e= sum((i,j), c(i,j)*x(i,j)) ;
45 supply(i) .. sum(j, x(i,j)) =l= a(i) ;
47 demand(j) .. sum(i, x(i,j)) =g= b(j) ;
49 Model transport /all/ ;
51 Solve transport using lp minimizing z ;
53 Display x.l, x.m ; '''
56 def read_set(connection, db, query_string, set_name, set_dim, set_exp=""):
58 cursor = connection.cursor()
59 cursor.execute(query_string)
60 data = cursor.fetchall()
62 if len(data[0]) != set_dim:
63 raise Exception(
"Number of fields in select statement does not match setDim")
65 i = db.add_set(set_name, set_dim, set_exp)
73 except Exception
as ex:
74 raise Exception(
"Error: Failed to retrieve the required data from the database.\n{0}".format(ex))
79 def read_parameter(connection, db, query_string, par_name, par_dim, par_exp=""):
81 cursor = connection.cursor()
82 cursor.execute(query_string)
83 data = cursor.fetchall()
85 if len(data[0]) != par_dim+1:
86 raise Exception(
"Number of fields in select statement does not match par_dim+1")
88 a = db.add_parameter(par_name, par_dim, par_exp)
92 for idx
in range(len(row)-1):
93 keys.append(str(row[idx]))
94 a.add_record(keys).value = row[par_dim]
96 except Exception
as ex:
97 raise Exception(
"Error: Failed to retrieve the required data from the database.\n{0}".format(ex))
103 var = db.get_variable(var_name)
104 if len(column_names) != var.dimension:
105 raise Exception(
"Number of column names does not match the dimension of the variable")
107 cursor = connection.cursor()
109 cursor.execute(
"drop table " + var_name)
113 query =
"create table " + var_name +
"("
114 for col
in column_names:
115 query += col +
" varchar(64), "
116 query +=
"lvl double)"
117 cursor.execute(query)
121 query =
"insert into " + var_name +
"("
122 for col
in column_names:
124 query +=
"lvl) values ("
126 query +=
"'" + key +
"', "
127 query += str(rec.level) +
")"
128 cursor.execute(query)
131 except Exception
as ex:
132 raise Exception(
"Error: Failed to write variable to the database.\n{0}".format(ex))
137 str_access_conn =
'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=..\\Data\\transport.accdb'
139 connection = pyodbc.connect(str_access_conn)
140 except Exception
as ex:
141 raise Exception(
"Error: Failed to create a database connection. \n{0}".format(ex))
147 db = ws.add_database()
150 str_access_conn =
'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=..\\Data\\transport.accdb'
153 connection = pyodbc.connect(str_access_conn)
154 except Exception
as ex:
155 raise Exception(
"Error: Failed to create a database connection. \n{0}".format(ex))
158 read_set(connection, db,
"SELECT Plant FROM plant",
"i", 1,
"canning plants")
159 read_set(connection, db,
"SELECT Market FROM Market",
"j", 1,
"markets")
162 read_parameter(connection, db,
"SELECT Plant,Capacity FROM Plant",
"a", 1,
"capacity of plant i in cases")
163 read_parameter(connection, db,
"SELECT Market,Demand FROM Market",
"b", 1,
"demand at market j in cases")
164 read_parameter(connection, db,
"SELECT Plant,Market,Distance FROM Distance",
"d", 2,
"distance in thousands of miles")
169 if __name__ ==
"__main__":
170 if len(sys.argv) > 1:
171 ws = GamsWorkspace(system_directory = sys.argv[1])
180 opt = ws.add_options()
181 opt.defines[
"gdxincname"] = db.name
182 opt.all_model_types =
"xpress"
183 t9.run(opt, databases=db)
def write_variable(connection, db, var_name, column_names)
def read_parameter(connection, db, query_string, par_name, par_dim, par_exp="")
def read_set(connection, db, query_string, set_name, set_dim, set_exp="")