MySQL, GAMS and Unix

questions about GAMS' tools
Post Reply
User avatar
Renger
Posts: 639
Joined: 7 years ago

MySQL, GAMS and Unix

Post by Renger »

Hi
I am preparing my model for running on a Unix server.
Until now, I have come up with the following procedure:
- Read the data from the MySQL database with a python script called from my GAMS file and save the data as a CSV file.
- Read the CSV file using csv2gdx.
The advantage of using the python script is that I can reshape the table easily using pandas.
Here is the python script:

Code: Select all

import pandas as pd
import pymysql
from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://renger:mypw@localhost/nexus')
df = pd.read_sql_query('SELECT * FROM busconfiguration', engine)
df.to_csv(path_or_buf='bus.csv',  sep=',', index=False)
And here the Gams code:

Code: Select all

$call python.exe loaddata.py
$call csv2gdx bus.csv id=production index=(1,2,3)  value=(4) UseHeader=Y
$gdxin bus.gdx

parameter bus
$load bus
display bus;
I am wondering if there is a more elegant and faster way (I know that I can include the python script as embedded code).
Ideas are welcome.
Cheers
Renger
____________________________________
Enjoy modeling even more: Read my blog on modeling at The lazy economist
User avatar
Clemens
Posts: 57
Joined: 7 years ago

Re: MySQL, GAMS and Unix

Post by Clemens »

Renger,

Using Embedded Code is in my opinion a good idea. You save the extra Python file and you could do it without writing the CSV file. Something like this:

Code: Select all

parameter p(*,*);

$onEmbeddedCode Python:
  import pandas as pd
  data = [('a', 'a', 2), ('a','b', 4), ('b', 'a', 1), ('b','b', 3)]
  df = pd.DataFrame(data)
  gams.set('p', [tuple(x) for x in df.values.tolist()])
$offEmbeddedCode p
display p;
The gams.set() method requires a list of tuples. Therefore we need to transform the inner lists into tuples. Perhaps pandas offers this functionality somehow in the tolist() call.

Best,
Clemens
User avatar
Renger
Posts: 639
Joined: 7 years ago

Re: MySQL, GAMS and Unix

Post by Renger »

Thanks!
Is there already an API for python 3.7 available (that is why I used the $call instead of embedding as I have Python 3.7 installed).
Cheers
Renger
____________________________________
Enjoy modeling even more: Read my blog on modeling at The lazy economist
User avatar
Clemens
Posts: 57
Joined: 7 years ago

Re: MySQL, GAMS and Unix

Post by Clemens »

Renger,

Neither the Object-Oriented Python API nor the Embedded Code facility is currently available for Python 3.7. At least the Object-Oriented API will (at some point of time) support Python 3.7. The Embedded Code facility might follow then.

Best,
Clemens
Post Reply