I would like to know why all seems to be okay but nothing is insert in my mysql database ? I get some data from an api and I would like to insert data in table.

Using flask, python, pymysql

Thanks in advance

api = Flask(__name__)


def connect():
    db = pymysql.connect(database='rtap',port=3306, host='127.0.0.1', user='root', password='',ssl_ca="{ca-cert filename}", ssl_disabled=True)
    log.basicConfig(level=log.DEBUG, format='%(asctime)s %(levelname)s:\n%(message)s\n')              
    print("Connexion réussie")
    return db

@api.route('/')
def base():
    return jsonify({"version":"1.0"})

@api.route('/azure', methods=['GET'])
def read():
    db = connect()
    log.info('Reading Datas')
    plane = "SELECT * FROM `plane`;"
    cursor = db.cursor()
    cursor.execute(plane)
    output = cursor.fetchall()
    return jsonify(output)


@api.route('/azure', methods=['POST'])
def write():
    db = connect()
    cursor = db.cursor()
    req = request.json["response"]
    # cursor.executemany("INSERT INTO `plane` (aircraft_icao, reg_number) VALUES (%s, %s);", req)
    # print(req)
    key = []
    for i in req:
        try:
            if (key==[]) :
                plane = "INSERT INTO `plane` (aircraft_icao, reg_number) VALUES ('{}', '{}')".format(i["aircraft_icao"], i["reg_number"])
                key.append(i["reg_number"])
            else:
                if(i["reg_number"] not in key) : #si la key n'a pas encore été utilisée, on peut ecrire la requette, sinon on ne fait rien
                    plane = ", ('{}', '{}')".format(i["aircraft_icao"], i["reg_number"])
                    key.append(i["reg_number"])
        except Exception as e:
             print("failed")
    # print(plane)
    cursor.execute(plane)
    return jsonify(req)

if __name__=='__main__':
    api.run(debug=True, port=5000, host='0.0.0.0')

traceback

CodePudding user response：

Assuming that the INSERT statement looked good at that debugging print function, then you probably need to commit the material to the DB after INSERT. Try adding

    db.commit()
    cursor.close()

after cursor.execute().

CodePudding user response：

You need to commit your table changes by doing

db.commit()

immediately after each INSERT or sequence of INSERTs. Read this.