0

I am working with the GCP Spanner database using the sqlalchemy ORM. This is the table schema where I am trying to insert/update (based on a check) data into a JSON column:

from sqlalchemy import Column, String, JSON, Float
from models.base import Base


class UtterData(Base):
    __tablename__ = "tbl_utter_data"

    conv_id = Column(String, primary_key=True)
    msg_id = Column(String, primary_key=True)
    key_indicator = Column(JSON)
    med_indicator = Column(JSON)

This is the code used for the insert/update operation:

from models.utter_data import UtterData
from sqlalchemy import create_engine

engine = create_engine("my_connection_url")
session = Session(engine)

data_obj = {"conv_id":"convID","msg_id":"messageID","key_indicator":[{"value": "None", "KeyID": "key0"}], "med_indicator": [{"value": "None", "MedID": "med7"}]}

#Insert
record = UtterData(data_obj)
session.add(record)
session.commit()

#Update
fetched_record = session.query(UtterData).filter_by(
        conv_id='convID', msg_id='messageID'
)
fetched_record.update({"key_indicator":data_obj['key_indicator'], "med_indicator":data_obj['med_indicator']})

However, the operation unexpectedly and randomly fails with this error:

google.api_core.exceptions.InvalidArgument: 400 Invalid value for bind parameter a1: Expected JSON.

I have stated "random" as this update/insert takes place multiple times and executes successfully for the other messages. There is no common pattern to be observed. To confirm the correctness of the code, I have ensured that every failed operation works perfectly fine locally with the spanner emulator.

Here is the sample data being updated as printed in the query that the sqlalchemy generates. Similar error is observed for insert operation as well:

sqlalchemy.exc.ProgrammingError: (google.cloud.spanner_dbapi.exceptions.ProgrammingError) []
[SQL: UPDATE tbl_utter_data SET key_indicator=%s, med_indicator=%s WHERE tbl_utter_data.conv_id = %s AND tbl_utter_data.msg_id = %s]
[parameters: [[{"value": "None", "KeyID": "key0"}], [{"value": "None", "MedID": "med7"}],'convID', 'messageID']]

I have tried manually serializing the data before insertion using json.dumps(), however that leads to the same error. Everything works fine locally with the Spanner emulator, the exact same data that fails with Cloud Spanner gets inserted/updated.

2
  • Would you mind sharing the code that you use to execute the insert/updates? You write that 'this insertion takes place multiple times and passes for the other messages'. The statement that fails is however an update. Could it be that it always succeeds when new data is inserted, but fails when an existing row is being updated? Commented Apr 18 at 11:41
  • @KnutOlavLøite Thanks for the suggestion, I have refactored the question. I have also checked the logs and confirmed that this occurs for inserts as well as updates. Locally using the spanner emulator the same code works for insert/update. Commented Apr 21 at 7:26

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.