transport9.py
Go to the documentation of this file.
7
8from __future__ import print_function
9from gams import *
10import sys
11import pyodbc
12
14 return '''
15 Sets
16 i canning plants
17 j markets
18
19 Parameters
20 a(i) capacity of plant i in cases
21 b(j) demand at market j in cases
22 d(i,j) distance in thousands of miles
23 Scalar f freight in dollars per case per thousand miles /90/;
24
25$if not set gdxincname $abort 'no include file name for data file provided'
26$gdxin %gdxincname%
27$load i j a b d
28$gdxin
29
30 Parameter c(i,j) transport cost in thousands of dollars per case ;
31
32 c(i,j) = f * d(i,j) / 1000 ;
33
34 Variables
35 x(i,j) shipment quantities in cases
36 z total transportation costs in thousands of dollars ;
37
38 Positive Variable x ;
39
40 Equations
41 cost define objective function
42 supply(i) observe supply limit at plant i
43 demand(j) satisfy demand at market j ;
44
45 cost .. z =e= sum((i,j), c(i,j)*x(i,j)) ;
46
47 supply(i) .. sum(j, x(i,j)) =l= a(i) ;
48
49 demand(j) .. sum(i, x(i,j)) =g= b(j) ;
50
51 Model transport /all/ ;
52
53 Solve transport using lp minimizing z ;
54
55 Display x.l, x.m ; '''
56
57
58def read_set(connection, db, query_string, set_name, set_dim, set_exp=""):
59 try:
60 cursor = connection.cursor()
61 cursor.execute(query_string)
62 data = cursor.fetchall()
63
64 if len(data[0]) != set_dim:
65 raise Exception("Number of fields in select statement does not match setDim")
66
67 i = db.add_set(set_name, set_dim, set_exp)
68
69 for row in data:
70 keys = []
71 for key in row:
72 keys.append(str(key))
73 i.add_record(keys)
74
75 except Exception as ex:
76 raise Exception("Error: Failed to retrieve the required data from the database.\n{0}".format(ex))
77 finally:
78 cursor.close()
79
80
81def read_parameter(connection, db, query_string, par_name, par_dim, par_exp=""):
82 try:
83 cursor = connection.cursor()
84 cursor.execute(query_string)
85 data = cursor.fetchall()
86
87 if len(data[0]) != par_dim+1:
88 raise Exception("Number of fields in select statement does not match par_dim+1")
89
90 a = db.add_parameter(par_name, par_dim, par_exp)
91
92 for row in data:
93 keys = []
94 for idx in range(len(row)-1):
95 keys.append(str(row[idx]))
96 a.add_record(keys).value = row[par_dim]
97
98 except Exception as ex:
99 raise Exception("Error: Failed to retrieve the required data from the database.\n{0}".format(ex))
100 finally:
101 cursor.close()
102
103def write_variable(connection, db, var_name, column_names):
104 try:
105 var = db.get_variable(var_name)
106 if len(column_names) != var.dimension:
107 raise Exception("Number of column names does not match the dimension of the variable")
108
109 cursor = connection.cursor()
110 try:
111 cursor.execute("drop table " + var_name)
112 connection.commit()
113 except:
114 pass
115 query = "create table " + var_name + "("
116 for col in column_names:
117 query += col + " varchar(64), "
118 query += "lvl double)"
119 cursor.execute(query)
120 connection.commit()
121
122 for rec in var:
123 query = "insert into " + var_name + "("
124 for col in column_names:
125 query += col + ", "
126 query += "lvl) values ("
127 for key in rec.keys:
128 query += "'" + key + "', "
129 query += str(rec.level) + ")"
130 cursor.execute(query)
131 connection.commit()
132
133 except Exception as ex:
134 raise Exception("Error: Failed to write variable to the database.\n{0}".format(ex))
135 finally:
136 cursor.close()
137
139 str_access_conn = 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=..\\Data\\transport.accdb'
140 try:
141 connection = pyodbc.connect(str_access_conn)
142 except Exception as ex:
143 raise Exception("Error: Failed to create a database connection. \n{0}".format(ex))
144 # write levels of variable x
145 write_variable(connection, db, "x", ["i","j"])
146 connection.close()
147
149 db = ws.add_database()
150
151 # connect to database
152 str_access_conn = 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=..\\Data\\transport.accdb'
153
154 try:
155 connection = pyodbc.connect(str_access_conn)
156 except Exception as ex:
157 raise Exception("Error: Failed to create a database connection. \n{0}".format(ex))
158
159 # read GAMS sets
160 read_set(connection, db, "SELECT Plant FROM plant", "i", 1, "canning plants")
161 read_set(connection, db, "SELECT Market FROM Market", "j", 1, "markets")
162
163 # read GAMS parameters
164 read_parameter(connection, db, "SELECT Plant,Capacity FROM Plant", "a", 1, "capacity of plant i in cases")
165 read_parameter(connection, db, "SELECT Market,Demand FROM Market", "b", 1, "demand at market j in cases")
166 read_parameter(connection, db, "SELECT Plant,Market,Distance FROM Distance", "d", 2, "distance in thousands of miles")
167
168 connection.close()
169 return db
170
171if __name__ == "__main__":
172 if len(sys.argv) > 1:
173 ws = GamsWorkspace(system_directory = sys.argv[1])
174 else:
175 ws = GamsWorkspace()
176
177 # fill GAMSDatabase by reading from Access
179
180 # run job
181 t9 = ws.add_job_from_string(get_model_text())
182 opt = ws.add_options()
183 opt.defines["gdxincname"] = db.name
184 opt.all_model_types = "xpress"
185 t9.run(opt, databases=db)
186
187 # write results into access file
188 write_results(t9.out_db)
189
190
def read_set(connection, db, query_string, set_name, set_dim, set_exp="")
Definition: transport9.py:58
def read_parameter(connection, db, query_string, par_name, par_dim, par_exp="")
Definition: transport9.py:81
def read_from_access(ws)
Definition: transport9.py:148
def get_model_text()
Definition: transport9.py:13
def write_variable(connection, db, var_name, column_names)
Definition: transport9.py:103
def write_results(db)
Definition: transport9.py:138