# python3 -m pip install Flask PyMySQL
import csv
import io
import os
import pymysql
from flask import Flask, request, Response
app = Flask(__name__)
def connect():
    return pymysql.connect(host=os.environ.get('DB_HOST', '127.0.0.1'), port=int(os.environ.get('DB_PORT', 3306)),
                           user=os.environ['DB_USER'], password=os.environ['DB_PASS'], database=os.environ['DB_NAME'],
                           charset='utf8mb4', autocommit=True)
def reply(text, status=200):
    return Response(text, status=status, mimetype='text/csv', headers={'Cache-Control': 'no-cache, must-revalidate'})
@app.post('/mysql.php')
def query():
    if not os.environ.get('SQLKEY') or request.form.get('key') != os.environ['SQLKEY'] or 'query' not in request.form:
        return reply('Bad Request', 400)
    connection = None
    try:
        connection = connect()
        with connection.cursor() as cursor:
            cursor.execute(request.form['query'])  # 表单只解码一次
            if cursor.description is None: return reply(f'AFFECTED ROWS: {cursor.rowcount}', 201)
            output = io.StringIO(newline='')
            writer = csv.writer(output, lineterminator='\n')
            writer.writerow([field[0] for field in cursor.description])
            writer.writerows(cursor.fetchall())
            return reply(output.getvalue())
    except (pymysql.MySQLError, KeyError):
        return reply('ERROR SQL query failed', 400)
    finally:
        if connection is not None: connection.close()
if __name__ == '__main__':
    app.run(host='0.0.0.0', port=int(os.environ.get('PORT', 3000)))