I'm trying to use the document storage of MySQL 8 in my python project(python 3.8). The version of MySQL-connector python is 8.0.20. According to the API reference and the X DevAPI User Guide, I tried to get the auto increment document ID after adding a document into the DB. Each time after the execution, the data would be inserted into DB successfully, but '-1' would be returned after get_autoincrement_value() was invoked.
My code is just like below:
try:
schema = session.get_schema('my_schema')
collection = schema.get_collection('my_collection')
topic_dict = protobuf_to_dict(topic)
doc_id = collection.add(topic_dict).execute().get_autoincrement_value()
logger.debug('doc_id: {}', doc_id)
return doc_id
except Exception as e:
logger.exception("failed to add topic to db, topic: {}, err: {}", topic, e)
Is there anything wrong with my usage? Thank you all~
Seems like you are interested in the document id that has been auto-generated. If that is the case, you should instead use get_generated_ids:
doc_id = collection.add(topic_dict).execute().get_generated_ids()[0]
In this case, the method returns a list of all the ids that were generated in the scope of the add() operation.
The documentation is probably not clear enough, but get_auto_increment_value() only contains useful data if you are inserting a row with either session.sql() or table.insert() on a table containing an AUTO_INCREMENT column. It has no meaning in the scope of NoSQL collections because in the end a collection is just a table created like (condensed version):
CREATE TABLE collection (
`doc` json DEFAULT NULL,
`_id` varbinary(32),
PRIMARY KEY (`_id`)
)
Which means there isn't anything to "auto increment".
Disclaimer: I'm the lead developer of the MySQL X DevAPI Connector for Node.js