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