Would it be possible to add support for sql2gms tool in a future unix release? It is almost impossible to connect with an sql database in a unix environment.
I have to use unix because i need to setup a r-shiny server and it is only feasible in unix (without large costs).
2 posts • Page 1 of 1
sql2gms relies on Windows functionality (odbc data connectors) so there is little chance to port this to other platforms. We have a couple of clients who successfully use e.g. SQLAlchemy and GAMS embedded Python code to communicate with SQL databases (on Unix). Here is a simple example. I have attached SQLite database file for convenience. Read more about GAMS embedded Python code at: https://www.gams.com/latest/docs/UG_EmbeddedCode.html
Code: Select all
Set i 'canning plants' j 'markets' Parameter a(i<) 'capacity of plant i in cases' b(j<) 'demand at market j in cases' Parameter d(i,j) 'distance in thousands of miles'; Scalar f 'freight in dollars per case per thousand miles' / 90 /; $onembeddedCode Python: import sqlalchemy from sqlalchemy import create_engine engine = create_engine('sqlite:///xxx.db') with engine.connect() as con: a = [tuple(r) for r in con.execute('SELECT * FROM a')] b = [tuple(r) for r in con.execute('SELECT * FROM b')] d = [tuple(r) for r in con.execute('SELECT * FROM d')] gams.set('a',a); gams.set('b',b); gams.set('d',d); $offEmbeddedCode a b d Parameter c(i,j) 'transport cost in thousands of dollars per case'; c(i,j) = f*d(i,j)/1000; Variable x(i,j) 'shipment quantities in cases' z 'total transportation costs in thousands of dollars'; Positive Variable x; Equation cost 'define objective function' supply(i) 'observe supply limit at plant i' demand(j) 'satisfy demand at market j'; cost.. z =e= sum((i,j), c(i,j)*x(i,j)); supply(i).. sum(j, x(i,j)) =l= a(i); demand(j).. sum(i, x(i,j)) =g= b(j); Model transport / all /; solve transport using lp minimizing z; display x.l, x.m;
- (607 Bytes) Downloaded 27 times