Insert key in a MySQL JSON field through sqlachemy mapped class without doing a query

Question

Let's start by defining my mapped class

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.types import JSON
from sqlalchemy.ext.mutable import MutableDict
from sqlalchemy import Column, Integer

base = declarative_base()

class MinimalExample(Base):
    __tablename__ = 'users'
    id = Column(Integer)
    data = Column(MutableDict.as_mutable(JSON))

Let's say I have in my table a row with id=1 and data={'key_0': 1}

I can request it through a session object quite easily

first_row = session.query(MinimalExample)\
                   .filter(MinimalExample.id==1)\
                   .first()

And from there I can modify the JSON field any way I want and update the database:

first_row.data['key_2'] = 1
session.commit()

The code above works (because we marked the JSON field as a mutable dict we don't have to mark it as dirty by hand)

session.query(MinimalExample.data)\
                   .filter(MinimalExample.id==1)\
                   .first()

>>> {'key_0': 1, 'key_2': 1}

However it means that I have to query all the rows I want to modify on my local computer, which is just too slow/I have not enough memory.

I am looking for a way to use the sqlalchemy.update function to get the same result

Here is what I have already tried:

from sqlalchemy import update

stmt = update(MinimalExample)
           .where(MinimalExample.id=1)\
           .values(MinimalExample.data={'key_1': 1})

session.execute(stmt)
session.commit()

which is just going to replace the data field in my row by the dictionary {'key_1': 1}

Something similar to this seems like the way to go:

(EDIT: found a way to correct my previous code so it does not raise an error anymore, simply by putting the object into a dictionary)

stmt = update(MinimalExample)
           .where(MinimalExample.id=1)\
           .values({MinimalExample.data['key_1']:1})

looking at the string of this statement seems quite good

str(stmt)

>>> 'UPDATE users SET data[:data_1]=:param_1 WHERE users.id = :id_1'

However executing this statement lead to a ProgrammingError: (MySQLdb._exceptions.ProgrammingError) (1064, 'You have an error in your SQL syntax...

Any thought on that ? Bonus points if I can update several rows at the same time

score 2 · Accepted Answer · answered Oct 10 '19 at 22:41

To update a JSON column in place in MySQL you need to use the JSON_SET() function (see here for specifics), for example a working query looks like:

UPDATE users SET data = JSON_SET(data, "$.key_1", 1) WHERE id = 1;

To emulate in SQLAlchemy:

qry = (
    update(MinimalExample)
    .where(MinimalExample.id == 1)
    .values(
        {"data": func.JSON_SET(MinimalExample.data, "$.key_1", 1)}
    )
)
s.execute(qry)
s.commit()

Here's the logs from creation of the table through to the commit, and as you can see no SELECT is issued:

2019-10-11 09:37:01,672 INFO sqlalchemy.engine.base.Engine
CREATE TABLE users (
        id INTEGER NOT NULL AUTO_INCREMENT,
        data JSON,
        PRIMARY KEY (id)
)


2019-10-11 09:37:01,684 INFO sqlalchemy.engine.base.Engine {}
2019-10-11 09:37:01,708 INFO sqlalchemy.engine.base.Engine COMMIT
2019-10-11 09:37:01,716 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2019-10-11 09:37:01,718 INFO sqlalchemy.engine.base.Engine INSERT INTO users (id, data) VALUES (%(id)s, %(data)s)
2019-10-11 09:37:01,718 INFO sqlalchemy.engine.base.Engine {'id': 1, 'data': '{"key_0": 1}'}
2019-10-11 09:37:01,720 INFO sqlalchemy.engine.base.Engine COMMIT
2019-10-11 09:37:01,723 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2019-10-11 09:37:01,724 INFO sqlalchemy.engine.base.Engine UPDATE users SET data = JSON_SET(data, "$.key_1", 1) WHERE id = 1;
2019-10-11 09:37:01,724 INFO sqlalchemy.engine.base.Engine {}
2019-10-11 09:37:01,725 INFO sqlalchemy.engine.base.Engine COMMIT

Works well for me, thanks ! For the moment I am looping over all the rows I want to update to execute this query, however this is quite slow. Do you have an idea on how to use this query to update several rows at once (with different values) ? — LoicM, Oct 11 '19 at 12:59

Insert key in a MySQL JSON field through sqlachemy mapped class without doing a query

1 Answers1