Insert json file to mysql with flask-CodePudding

I'm trying to insert the data from a json file called output.json and i have this code: I'm getting this error:

MySQLdb._exceptions.ProgrammingError: not all arguments converted during bytes formatting

In mysql i insert like insert into t1 values ({JSONFILE})

from flask import Flask
from flask_mysqldb import MySQL
 
app = Flask(__name__)
 
app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = '*****'
app.config['MYSQL_DB'] = '*****'
 
mysql = MySQL(app)


@app.route("/sendf",methods=['POST'])
def sendfilet():
    cursor = mysql.connection.cursor()
    file = open('output.json', 'r')
    file_content = file.read()
    file.close()
    sql = "INSERT INTO t1 (tablename) VALUES (%s)"
    val = (json.dumps(file_content))
    cursor.execute(sql, val)
    db.commit()
    db.close()
    return 200

if __name__ == '__main__':
    app.run(debug=True,port=5050)

my json is like this:

{"_id":{"$oid":"60f458945d77cb5ec7872b61"},"insertionDate":{"$date":"2021-07-18T16:36:36.193Z"},"sessionData":{"time":["1364","1374","1384"],"yaw":["0.15","0.3","0.45"],"pitch":["0.36","0.76","1.08"],"roll":["-0.13","-0.25","-0.35"],"ax":["-0.42","-0.41","-0.41"],"ay":["-0.15","-0.13","-0.1"],"az":["0.9","0.91","1"],"gx":["0","0","0"],"gy":["-0.01","0","-0.01"],"gz":["0.02","0.02","0.02"],"mx":["0.26","0.26","0.26"],"my":["0.01","0.01","0.01"],"mz":["-0.04","-0.04","-0.07"]},"metaData":{"userId":123456,"gender":"M","ageGroup":"SENIOR","weightKg":70,"heightCm":175,"poolSizeM":50}}

CodePudding user response：

In order to store your JSON data into MySQL in python, you need to can try to create a MySQLUtil so that you can insert your JSON data in MySQL.

You can do mysql = MySQLUtil()

after this, you need to make a connection to the database and then store the JSON data into a variable, and simply use MySQLstrong text.execSql() to insert the data.

(your json variable name) = pymysql.escape_string(// your json data)
Mysql = "insert data (index name) value"('"   json_data   "') "
mysql.execSql(sql)

you can read more here

I think you should use SQLAlchemy, it is better than flask_mysqldb, easy to autoincrement id.

id = db.Column(db.BigInteger, autoincrement=True, primary_key=True)