MySQL, GAMS and Unix
Posted: Wed Dec 05, 2018 7:55 am
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:
And here the Gams code:
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
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)
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;
Ideas are welcome.
Cheers
Renger