Issue with Inserting JSON data into MySQL database using Python, error in your SQL syntax

I am trying to insert some JSON data into a MySQL database using a python script. Seems like it is close to working, but I keep getting an error that says:

python_mpps_1         | Error Code: 1064
python_mpps_1         | SQLSTATE 42000
python_mpps_1         | Message You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-create (dataset_in, mwl, dataset_out) VALUES ('{"00000000": {"Value": [2], ' at line 1

That is output by the try / catch in the script:

    try:
        mycursor.execute("INSERT INTO n-create (dataset_in, mwl, dataset_out) VALUES (%s, %s, %s)", (dataset_in, mwl , dataset_out))
        mydb.commit()
        print("Inserting N_CREATE" + str(mycursor.rowcount))
        mycursor.close()
    except mysql.connector.Error as err:
        print(err)
        print("Error Code:", err.errno)
        print("SQLSTATE", err.sqlstate)
        print("Message", err.msg)

I put some debugging stuff in the script:

    mydb = mysql.connector.connect(host="mysql_db", port = 3306, user="",password="",database="")
    mycursor = mydb.cursor()
    print(type(dataset_in))
    print(dataset_in)
    print(type(dataset_out))
    print(dataset_out)
    mwl = json.dumps(mwl[0])
    print(type(mwl))
    print(mwl)

and it prints out <class ‘str’> as the datatype for all of the values that I want to insert, and they all validate as JSON using jsonlint.

I have a lot more details, and I’ve played around with changing the tables in the database from JSON to VARCHAR, etc and that does not help. Also played around with using json.dump(var), etc., so it seems like it must just be s syntax issue with:

mycursor.execute("INSERT INTO n-create (dataset_in, mwl, dataset_out) VALUES (%s, %s, %s)", (dataset_in, mwl , dataset_out))

statement, which is what the 1064 means really. Probably something simple, I hope, otherwise I can provide more details.

As an example, a snippet of dataset_out is as follows. That is what it shows in the terminal via Docker when I print(dataset_out), but turncated it a bit.

{
    "00000000": {
        "Value": [2],
        "vr": "UL"
    },
    "00000002": {
        "Value": ["1.2.840.10008.3.1.2.3.3"],
        "vr": "UI"
    },
    "00000100": {
        "Value": [33088],
        "vr": "US"
    },
    "00000120": {
        "Value": [8],
        "vr": "US"
    },
    "00000800": {
        "Value": [0],
        "vr": "US"
    },
    "00000900": {
        "Value": [0],
        "vr": "US"
    }
}

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

“n-create” is not a valid table name. I suspect you meant “n_create”.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x