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).
Support SQL2gms for Unix version
Re: Support SQL2gms for Unix version
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;
- Attachments
-
- sqlitedb.zip
- (607 Bytes) Downloaded 592 times