0

Are prepared statements supported with Azure Cosmos Cassandra API with Python?

It appears not - when I execute

stmt = session.prepare("SELECT provider FROM providers WHERE country_code=?")

I get the following exception:

Traceback (most recent call last):
  File "cosmos-cql.py", line 42, in <module>
    select_provider_stmt = session.prepare("SELECT provider FROM providers WHERE country_code=?")
  File "cassandra\cluster.py", line 3072, in cassandra.cluster.Session.prepare
  File "cassandra\cluster.py", line 3069, in cassandra.cluster.Session.prepare
  File "cassandra\cluster.py", line 4901, in cassandra.cluster.ResponseFuture.result
  File "cassandra\connection.py", line 1229, in cassandra.connection.Connection.process_msg
  File "cassandra\protocol.py", line 1196, in cassandra.protocol._ProtocolHandler.decode_message
  File "cassandra\protocol.py", line 744, in cassandra.protocol.ResultMessage.recv_body
  File "cassandra\protocol.py", line 734, in cassandra.protocol.ResultMessage.recv
  File "cassandra\protocol.py", line 775, in cassandra.protocol.ResultMessage.recv_results_prepared
  File "cassandra\protocol.py", line 819, in cassandra.protocol.ResultMessage.recv_prepared_metadata
  File "cassandra\protocol.py", line 1321, in cassandra.protocol.read_short
  File "C:\Users\Ian\.conda\envs\enerlytics\lib\site-packages\cassandra\marshal.py", line 22, in <lambda>
    unpack = lambda s: packer.unpack(s)[0]
struct.error: unpack requires a buffer of 2 bytes

I'd normally expect to resolve this in a few minutes by googling, but I can find absolutely nothing about prepared statements and Cosmos Cassandra API. It's as if either the problem doesn't exist (I'm making some really silly mistake) or no one else has even thought to try it.

I am using version 3.25.1 of Datastax's Cassandra driver.

Ian Goldby
  • 5,609
  • 1
  • 45
  • 81

2 Answers2

1

Prepared statements are supported. However, there is a Python driver specific issue with prepared statements in versions higher than 3.20.2 when used with Cosmos DB API for Cassandra. If you downgrade to cassandra-driver==3.20.2, the prepared statement will work.

Theo van Kraay
  • 114
  • 3
  • Thank you. There has been a lot of development of the DataStax driver since v3.20.2. Do you happen to know if Microsoft plan to fix this in Cosmos API for Cassandra, and if so when? – Ian Goldby Mar 21 '23 at 09:47
  • 1
    It is not so much a question of "fixing" it. Cassandra API supports only the open-source native wire protocol v4: https://github.com/apache/cassandra/blob/trunk/doc/native_protocol_v4.spec. The Python driver at v3.20.2 is the last version that supports V4. The next release changed it to DSE_V2 (for Datastax Enterprise). That said, in theory, the latest driver might work if the wire protocol version is downgraded. Protocol version can be set when creating the cluster: https://docs.datastax.com/en/developer/python-driver/3.25/api/cassandra/cluster/ – Theo van Kraay Apr 14 '23 at 17:37
  • It looks like Microsoft have recently modified the server so that it automatically downgrades the protocol to v4. Prepared statements now work without the need to explicitly set the protocol_version, although there is a warning suggesting that you should anyway. Many thanks for your excellent explanation. – Ian Goldby Apr 18 '23 at 11:38
  • Ah, I see you are Program Manager for Cosmos DB Awesome. Thanks for dealing with this. – Ian Goldby Apr 18 '23 at 11:38
-1

** This is not the prepared statement.

But, the below solution solves the purpose another way around. I got it working by removing "?" with "%s".

session.execute("""INSERT INTO test.profile (id, depth, create_ts, create_user)  VALUES (%s, %s, %s, %s)""", (id, depth, create_ts, create_user))

with the above, you can use batch as well.

batch.add("""INSERT INTO test.profile (id, depth, create_ts, create_user)  VALUES (%s, %s, %s, %s)""", (id, depth, create_ts, create_user))

Hope it's helpful for someone.

Anant
  • 153
  • 14