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