import pymysql
from flask import Flask, request

import balance

app = Flask(__name__)

db = pymysql.connect(
        'localhost',
        'user',
        'password',
        'my_db',
        cursorclass=pymysql.cursors.DictCursor
    )

# my request is POST with JSON body {"id": any integer}
@app.route('/get', methods=['POST'])
def get_data():
    try:
        json = request.json
        if json is None or 'id' not in json.keys():
            return jsonify({'data': {}, 'code': 1}), 400

        id_ = json['id']
        if not isinstance(id_, int):
            return jsonify({'data': {}, 'code': 2}), 400
        if id_ < 1 or id_ > 2147483647:
            return jsonify({'data': {}, 'code': 6}), 400

        print(db._sock) # checking if there is a connection. It wil print None when InterfaceError.
        with db.cursor() as cur:
            cur.execute(f'select id from users where id = {id_}') # InterfaceError occurs here (at first query to db).
            res = cur.fetchall()

            if not res:
                cur.execute(f'insert into users (id) values ({id_})')
                cur.execute(f'insert into settings (userId) values ({id_})')
                cur.execute(f'insert into ntfs (userId) values ({id_})')
                db.commit()

        user = balance.get_money(CURRENCIES, id_) # my function, not relevant to problem
        user.sort(key=lambda u: 0 if u[1] is None else u[1], reverse=True)
        return jsonify({'data': user, 'code': 0}), 200

    except Exception as e:
        print('Get -', e.__class__.__name__, e)
        pprint(request.json)
        return jsonify({'data': {}, 'code': -1}), 500