Last active
August 29, 2015 14:22
-
-
Save xxxVxxx/3377d29a5cfa32246196 to your computer and use it in GitHub Desktop.
python sqlalchemy mysql data dump in json format as required by datatables in this case
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/usr/bin/python | |
''' | |
Install pip/sqlalchemy/mysqldb | |
sudo apt-get install python-pip | |
sudo apt-get install python-dev libmysqlclient-dev | |
pip install sqlalchemy | |
pip install MySQL-python | |
below is an example script: | |
''' | |
from sqlalchemy.engine import create_engine | |
import json | |
engine = create_engine('mysql://root:P4ssWordD@localhost/reports') | |
conn = engine.connect() | |
row = conn.execute("select * from updatestatus") | |
json_result = [] | |
for result in row: | |
d = {'ORG_ID': result['org_id'], | |
'ORG_NAME': result['orgname'], | |
'LAST_UPDATED': result['last_update_time'], | |
'NEXT_UPDATE': result['next_update_time'], | |
'MACHINE': result['Machine'], | |
'FREQUENCY': result['Frequency'], | |
'REGION': result['Cluster'], | |
'SYNC_TIME': result['Total Time']} | |
json_result.append(d) | |
interm_result = {"data" : json_result} | |
final_result = json.dumps(interm_result, default=str, indent=4) | |
#if you dont use defaul=str , you will get error "datetime.datetime(2015, 6, 10, 14, 14, 37) is not JSON serializable" if you use | |
#datetime format results from mysql. | |
print final_result |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment