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.