Loading...
Searching...
No Matches
Functions | |
| def | read_set (connection, db, query_string, set_name, set_dim, set_exp="") |
| def | read_parameter (connection, db, query_string, par_name, par_dim, par_exp="") |
| def | write_variable (connection, db, var_name, column_names) |
| def | write_results (db) |
| def | read_from_access (ws) |
Variables | |
| str | GAMS_MODEL |
| sys | sys_dir = sys.argv[1] if len(sys.argv) > 1 else None |
| GamsWorkspace | ws = GamsWorkspace(system_directory=sys_dir) |
| def | db = read_from_access(ws) |
| GamsWorkspace | job = ws.add_job_from_string(GAMS_MODEL) |
| GamsWorkspace | opt = ws.add_options() |
| all_model_types | |
| databases | |
Function Documentation
◆ read_from_access()
| def transport9.read_from_access | ( | ws | ) |
Definition at line 167 of file transport9.py.
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
References read_parameter(), and read_set().
◆ read_parameter()
| def transport9.read_parameter | ( | connection, | |
| db, | |||
| query_string, | |||
| par_name, | |||
| par_dim, | |||
par_exp = "" |
|||
| ) |
Definition at line 86 of file transport9.py.
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
Referenced by read_from_access().
◆ read_set()
| def transport9.read_set | ( | connection, | |
| db, | |||
| query_string, | |||
| set_name, | |||
| set_dim, | |||
set_exp = "" |
|||
| ) |
Definition at line 57 of file transport9.py.
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
Referenced by read_from_access().
◆ write_results()
| def transport9.write_results | ( | db | ) |
Definition at line 154 of file transport9.py.
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
References write_variable().
◆ write_variable()
| def transport9.write_variable | ( | connection, | |
| db, | |||
| var_name, | |||
| column_names | |||
| ) |
Definition at line 115 of file transport9.py.
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
Referenced by write_results().
Variable Documentation
◆ all_model_types
| transport9.all_model_types |
Definition at line 223 of file transport9.py.
◆ databases
| transport9.databases |
Definition at line 224 of file transport9.py.
◆ db
| def transport9.db = read_from_access(ws) |
Definition at line 217 of file transport9.py.
◆ GAMS_MODEL
| str transport9.GAMS_MODEL |
Definition at line 12 of file transport9.py.
◆ job
| GamsWorkspace transport9.job = ws.add_job_from_string(GAMS_MODEL) |
Definition at line 220 of file transport9.py.
◆ opt
| transport9.opt = ws.add_options() |
Definition at line 221 of file transport9.py.
◆ sys_dir
| sys transport9.sys_dir = sys.argv[1] if len(sys.argv) > 1 else None |
Definition at line 213 of file transport9.py.
◆ ws
| GamsWorkspace transport9.ws = GamsWorkspace(system_directory=sys_dir) |
Definition at line 214 of file transport9.py.