AAM SQL | Python Fiddle

__author__ = 'jknoll'
from pyodbc import connect
from flask import Flask, request
from flask.ext.cors import CORS
from json import dumps
from urllib2 import urlopen
from base64 import b64decode
from advcommon.security.aam_security import security
from advcommon.parse import ArgumentParser
from advcommon.logging import getLogger, INFO, DEBUG, Config as LogConfig
from socket import gethostbyname, gethostname
from config import config
from datetime import datetime
from json import loads
from re import sub

app = Flask(__name__)
CORS(app)
getLogger('suds').setLevel(DEBUG)
_logger = getLogger(__name__)
_logger_conf = LogConfig(application_name=__name__, log_filename=__name__).configure()
pwd = b64decode('OU1Tb3ghKDJlJGow')

@app.route('/api/sql', methods=['POST', 'GET'])
@app.route('/', methods=['POST', 'GET'])
def root():
    if request.method == 'POST':
        server = request.form['server']
        sql = request.form['sql']
        yaml_name = request.form['yaml_name']
        try:
            key_value = request.form['key_value']
        except KeyError:
            key_value = 'False'
        try:
            params = loads(request.form['params'])
        except KeyError:
            params = dict()
    else:
        server = request.args.get('server', 'sql3')
        sql = request.args.get('sql', 'ERROR no sql statement provided')
        yaml_name = request.args.get('yaml_name', 'ERROR no yaml report name provided needed for security group name')
        key_value = request.args.get('key_value', 'False')
        params = request.args.get('params', dict())
    response = urlopen(config['AAM_WEB_URL'] % yaml_name)
    group = loads(response.read())['Security']
    user = b64decode(request.headers.get('Authorization')[6:]).split(':')[0]
    groups = security.get_member_of(user)
    recs = list()
    if group in groups and group + ' deny' not in groups:
        connection = {'sql3': 'DSN=AchievementFund;Uid=aam_web;Pwd=%s' % pwd,
                      'dsql1': 'DSN=Instrument;Uid=aam_web;Pwd=%s' % pwd, }
        cursor = connect(connection[server]).cursor()
        sql = dollar_curl_apply(sql, params)
        for multi_sql in sql.split(';'):
            cursor.execute(multi_sql)
            fields = [field[0] for field in cursor.description]
            for rec in cursor:
                recs.append(dict(zip(fields, rec)))
    else:
        print 'deny access %s not in %s or in %s' % (user, group, group + ' deny')
    return dumps(recs, default=json_serial)

@app.route('/up')
def up():
    return '<HTML>AAM_SQL Service is running</HTML>'

def json_serial(obj):
    if isinstance(obj, datetime):
        serial = obj.isoformat()
        return serial

def dollar_curl_apply(template, params):
    new = template
    for param in params:
        new = sub(r'\$\{%s\}\$' % param, params[param], new)
    return new

def _parse_args():
    parser = ArgumentParser()
    parser.add_environment_group(choices=('dev', 'prod'))
    parser.add_argument("-p", "--http_port", help='tcpip/port service should run on default 8081',
                        type=int, default=8081)
    arguments = parser.parse_args()
    _logger.debug("arguments set to {}".format(vars(arguments)))
    return arguments

if __name__ == '__main__':
    args = _parse_args()
    app.run(host=gethostbyname(gethostname()), port=args.http_port, debug=True)

Python Fiddle

Python Cloud IDE