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
154def write_results(db):
155 str_access_conn = r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=..\..\..\..\apifiles\Data\transport.accdb"
156 try:
157 connection = pyodbc.connect(str_access_conn)
158 except Exception as ex:
159 raise Exception(
160 "Error: Failed to create a database connection. \n{0}".format(ex)
161 )
162 # write levels of variable x
163 write_variable(connection, db, "x", ["i", "j"])
164 connection.close()
165
166
167def read_from_access(ws):
168 db = ws.add_database()
169
170 # connect to database
171 str_access_conn = r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=..\..\..\..\apifiles\Data\transport.accdb"
172
173 try:
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}")
177
178 # read GAMS sets
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")
181
182 # read GAMS parameters
183 read_parameter(
184 connection,
185 db,
186 "SELECT Plant,Capacity FROM Plant",
187 "a",
188 1,
189 "capacity of plant i in cases",
190 )
191 read_parameter(
192 connection,
193 db,
194 "SELECT Market,Demand FROM Market",
195 "b",
196 1,
197 "demand at market j in cases",
198 )
199 read_parameter(
200 connection,
201 db,
202 "SELECT Plant,Market,Distance FROM Distance",
203 "d",
204 2,
205 "distance in thousands of miles",
206 )
207
208 connection.close()
209 return db
210
211
212if __name__ == "__main__":
213 sys_dir = sys.argv[1] if len(sys.argv) > 1 else None
214 ws = GamsWorkspace(system_directory=sys_dir)
215
216 # fill GamsDatabase by reading from Access
217 db = read_from_access(ws)
218
219 # run job
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)
225
226 # write results into access file
227 write_results(job.out_db)
transport9.read_parameter
def read_parameter(connection, db, query_string, par_name, par_dim, par_exp="")
Definition: transport9.py:86
transport9.read_from_access
def read_from_access(ws)
Definition: transport9.py:167
transport9.write_variable
def write_variable(connection, db, var_name, column_names)
Definition: transport9.py:115
transport9.write_results
def write_results(db)
Definition: transport9.py:154
transport9.read_set
def read_set(connection, db, query_string, set_name, set_dim, set_exp="")
Definition: transport9.py:57