5

I am an AMPL user trying to write a linear programming optimization model using Python (My first Python code). I am trying to find how to declare indexed parameters over compound sets. For example, in AMPL, i would say: Set A Set B Set C param x{A, B, C} param y{A, B, C} param z{A, B, C} The above sets and parameters can be easily read from a database via AMPL.

The table that I read from the database has six fields i.e. A, B, C, x, y, z. Three of them are primary keys (A, B, C) and the rest (x,y,z) are values indexed over the primary keys.

PYTHON PART: I am using PYODBC module to connect with SQL Server. I tried "dict" but it can index over only one key. I am not sure which python feature should I use to declare the first three fields as a compound set and x, y and z as values indexed over the compound set. 

import pyodbc    
con = pyodbc.connect('Trusted_Connection=yes', driver = '{SQL Server Native Client 10.0}', server = 'Server', database='db')
cur = con.cursor()
cur.execute("execute dbo.SP @Param =%d" %Param)
result = cur.fetchall()
Comp_Key, x, y, z= dict((A, B, C, [x,y,z]) for A, B, C, x, y, z in result)

Ofcourse it is not correct. I cannot think about a way to do this.

Please help me :) Thanks in advance!

kanishk panchal
  • 87
  • 1
  • 6

1 Answers1

2

In place of this:

Comp_Key, x, y, z= dict((A, B, C, [x,y,z]) for A, B, C, x, y, z in result)

You could do this (if A, B, C != B, A, C, i.e. order of A B C matters):

final_result = dict(((A, B, C), [x, y, z]) for A, B, C, x, y, z in result)

OR

final_result = {(A, B, C): [x, y, z] for A, B, C, x, y, z in result}  # more readable than first one

Or you could do this (if A, B, C == B, A, C, i.e. order of A B C does not matter):

final_result = dict((frozenset(A, B, C), [x, y, z]) for A, B, C, x, y, z in result)

OR

final_result = {frozenset(A, B, C): [x, y, z] for A, B, C, x, y, z in result}  # more readable than first one
Debanshu Kundu
  • 785
  • 7
  • 18
  • Thanks Deb!! Much appreciated!! What I am wondering about is that should I declare these sets and parameters before I read the table? If yes, how? Or should I do this: 'A, B, C, x, y, z = final_result'? What I am getting at here is at How can I separate out the sets and parameters after I read them in? – kanishk panchal Jul 20 '13 at 18:17
  • You can't declare anything in python (it's dynamically typed). And if your table has row like `[(A, B, C, x, y, z), (D, E, F, u, v, w)...]` then the final_result would have value `{(A, B, C): [x, y, z], (D, E, F): [u, v, w]...}`, so what exactly you want to ask on that? – Debanshu Kundu Jul 20 '13 at 19:22
  • Let me start with an example. In the table that I am reading the data from, A = Source, B = Dest, C = Product, d = cost, e = volume, f = quantity. I want to be able to have the field names after I read the data as a dictionary so that I can index over the sets (A, B, C) and retrieve parameter values (x, y, z) to do calculations. – kanishk panchal Jul 20 '13 at 19:44
  • 1
    Okay, the `final_result` `dict` would have a `tuple` of `(A, B, C)` [or `frozenset` of `(A, B, C)` in second case] as keys and `list` of `[x, y, z]` as values. So for any A, B, C you can retrieve x, y, z by doing `x, y, z = final_result[(A, B, C)]` or `x, y, z = final_result[frozenset(A, B, C)]` [in second case].e – Debanshu Kundu Jul 21 '13 at 06:31