Statement wasn't well formed, can't be processed: Unexpected keyword

[progerjkd]

Hi there,

I'm using pydynamodb with sqlalchemy in order to convert a simple app which previously used sqlite.

I made the change to the connection string, and I see that pynamodb can connect using my credentials to the dynamodb table. But when inserting a record I get the error below:

sqlalchemy.exc.OperationalError: (pydynamodb.error.OperationalError) An error occurred (ValidationException) when calling the ExecuteStatement operation: Statement wasn't well formed, can't be processed: Unexpected keyword
[SQL: INSERT INTO urls (key, secret_key, target_url, is_active, clicks) VALUES (?, ?, ?, ?, ?)]
[parameters: ('KSCBE', 'YMYXBEAP', 'http://globo.com', None, None)]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

This is my database model:

class URL(Base):
    __tablename__ = "urls"

    id = Column(Integer, primary_key=True)
    key = Column(String)
    secret_key = Column(String)
    target_url = Column(String)
    is_active = Column(Boolean)
    clicks = Column(Integer)

Any advice? Thanks in advance.

[passren]

@progerjkd Thanks for your feedback. I didn't consider fully ORM features in terms of sqlalchemy dialect. Only select statement was tested in order to support query requirements for Apache Superset. INSERT/UPDATE/DELETE these kinds of DML statements were not fully tested. I investigated the error you posted above. I think the different SQL grammar of PartiQL caused this issue.

SQLAlchemy translate a save() operation to [INSERT INTO table (...) VALUES (...)]. But PartiQL Insert syntax is [INSERT INTO table VALUE item]. Please refer here: PartiQL insert statements for DynamoDB

No Worries. I will try to figure out how to let PyDynamodb works for this.

[passren]

@progerjkd I created a new branch 0.4.7 to update module of SQLAlchemy Dialect. It can support insert/update/delete operations now. But I'm not quite confident that my unit test cases covered all the scenarios. So I won't release this version to Pypi until the new features can be verified completely.

It's very helpful if you can test this version within your application. Just need to manually upgrade it in your environment via "pip install PyDynamoDB-0.4.7-py3-none-any.whl -U". Many Thanks.

Attached the 0.4.7 package below: PyDynamoDB-0.4.7.zip

[passren]

@progerjkd Just released 0.4.7 to enhance SQLAlchemy dialect support. Looking forward to your feedback.