Support SQL2gms for Unix version

FAQs
1

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

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

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;

sqlitedb.zip (607 Bytes)

3

Hello and thank for your prompt answer. I tried sqlalchemy as you suggest but unfortunately it returns an error “No module named sqlalchemy”.