Python objects to JSON to MySQL

Question

I've got a problem when trying to insert a json, which was converted from a python object with json.dumps, into a MySQL database. The connection to the database is working with another python file. I've already tried to just insert values, which was working, but with the json file it's not working.
My Python file:

import json
import dbConnection

cur = dbConnection.cursor
cnx = dbConnection.conn

DEVICES = {
        "id": "1",
        "isPoweredOn": "True",
    "os": "Linux"
}

j = json.dumps(DEVICES)
print(j)

sql = "INSERT INTO DEVICES (id, isPoweredOn, os) VALUES (%s, %s, %s)"


val = (json.dumps(DEVICES))


cur.execute(sql, val)
cnx.commit()
print(cur.rowcount, "record inserted.")

Error code I get, when trying to execute:

"id": "1", "isPoweredOn": "True", "os": "Linux"}
Traceback (most recent call last):
  File "dbInit.py", line 22, in <module>
    cur.execute(sql, val)
  File "/home/silvan/.virtualenvs/pyproj1/lib/python3.8/site-packages/mysql/connector/cursor.py", line 551, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/home/silvan/.virtualenvs/pyproj1/lib/python3.8/site-packages/mysql/connector/connection.py", line 490, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/home/silvan/.virtualenvs/pyproj1/lib/python3.8/site-packages/mysql/connector/connection.py", line 395, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): 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 '%s, %s, %s)' at line 1

My CREATE TABLE code:

CREATE TABLE DEVICES(id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, isPoweredOn BOOLEAN NOT NULL, os VARCHAR(50) NOT NULL);

Thanks for any help in advance!

If os is a varchar I think you should sorround it with sigle quotes ((%s, %s, '%s')). Also note that True may not work. — dcg
– dcg, Commented Dec 23, 2019 at 16:36
@dcg thanks, tried that, but still the same error. True is actually a boolean, I've tested it with %r an without any doublequotes in the python object. Without the json it works like this. — Fat Turtle
– Fat Turtle, Commented Dec 23, 2019 at 16:41
Don't insert val, do this instead: cur.execute(sql, DEVICES). — mvp
– mvp, Commented Dec 23, 2019 at 17:45
@mvp Just tried that, unfortunately still the same error, I think I have to convert it from python to json. — Fat Turtle
– Fat Turtle, Commented Dec 23, 2019 at 17:55

WaLid LamRaoui · Accepted Answer · 2019-12-23 18:30:21Z

1

You need to json.loads(j) and assign it to a variable, then you can access the values properly.

Try :

import json
import dbConnection

cur = dbConnection.cursor
cnx = dbConnection.conn


DEVICES = {
        "id": "1",
        "isPoweredOn": False ,
        "os": "Linux"
}
j = json.dumps(DEVICES)
values = json.loads(j) 
'''
# Quick debugging 
print(j , type(j))
print(values , type(values))
print(values['isPoweredOn'])
'''

sql = "INSERT INTO DEVICES (id, isPoweredOn, os) VALUES (%s, %s, %s)"

val = ( '' , values['isPoweredOn'] , values['os'])

cur.execute(sql, val)
cnx.commit()
print(cur.rowcount, "record inserted.")

Also since you defined id to be INT AUTO_INCREMENT PRIMARY KEY NOT NULL it , you can't insert device id wich is values['id'] to id column, you can alter DEVICES table and create a new column called device_id for storing the device id if you need really need to store values['id']

answered Dec 23, 2019 at 18:30

WaLid LamRaoui

2,4852 gold badges19 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fat Turtle Over a year ago

I've tried this and it didn't properly work, but the solution above worked for me. Anyway, thanks for your input.

Rahul Reddy · Accepted Answer · 2019-12-23 18:22:07Z

0

Firstly, cast the DEVICES to dict, then Here's the format.

sql = "INSERT INTO DEVICES (`id`, `isPoweredOn`, `os`) VALUES (%(id)s, %(isPoweredOn)s, %(os)s)"

Then Execute it :

try:
    cur.execute(sql, DEVICES)
    cnx.commit()
except error:
    print(error)

Cheers!!

edited Dec 23, 2019 at 18:22

answered Dec 23, 2019 at 18:11

Rahul Reddy

12017 bronze badges

Collectives™ on Stack Overflow

Python objects to JSON to MySQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related