Inserting data with the Python driver returns "Syntax error in CQL query"

Question

I've just got myself to Cassandra now I'm facing with data types issue

Whenever I try to insert data as TEXT type, I got following error

[Syntax error in CQL query] message="line 1:103 no viable alternative at input ',' (... filepath) VALUES (0, [P2],...)">

Here I created a table named batch_rows , you can see type of node_id is text

self.session.execute("""
            CREATE TABLE IF NOT EXISTS batch_rows (
                local_pid int,
                node_id text,
                camera_id int,
                geolocation int,
                filepath int,
                PRIMARY KEY (local_pid, node_id)
            )
            """)

But whenever I do the Insert it gave me above error, here is my Insert statement:

    local_pid = i
    node_id= 'P2'
    camera_id= 1
    geolocation= 4
    filepath = 3
                
    self.session.execute('INSERT INTO %s (local_pid, node_id, camera_id, geolocation, filepath) VALUES (%s, %s, %s, %s, %s) ' % 
    (table, local_pid, node_id, camera_id, geolocation, filepath))

Actually I'm kinda stuck right now, has anyones face with this? Thanks alot guys

Answer 1

Type-casting the values with % is invalid so the resulting CQL query returns a syntax error:

  ... % (table, local_pid, node_id, camera_id, geolocation, filepath) ...

Here is an example code with the valid format for positional placeholders:

session.execute(
    """
    INSERT INTO batch_rows (local_pid, node_id, camera_id, geolocation, filepath)
    VALUES (%s, %s, %s, %s, %s)
    """,
    (local_pid, node_id, camera_id, geolocation, filepath)
)

I also echo @Aaron's recommendation to avoid making schema changes programatically to avoid schema disagreements.

For other examples on how to execute queries, see the Cassandra Python driver Getting Started Guide. Cheers!

Answer 2

I am afraid you're incorrectly using the prepared statement with positional arguments. See here for documentation.

What if you tried as below?

self.session.execute("""INSERT INTO batch_rows (local_pid, node_id, camera_id, geolocation, filepath) VALUES (%s, %s, %s, %s, %s)""",(local_pid, node_id, camera_id, geolocation, filepath))

Answer 3

So string parsing a CQL statement and casting each value as a string isn't going to work. I'd build a prepared statement like this:

insert_table1 = """
    INSERT INTO batch_rows (local_pid, node_id, camera_id, geolocation, filepath)
    VALUES (?, ?, ?, ?, ?)
"""

pStatement = session.prepare(insert_table1);

local_pid = 219
node_id= 'P2'
camera_id= 1
geolocation= 4
filepath = 3

session.execute(pStatement,(local_pid, node_id, camera_id, geolocation, filepath))

Also, I wouldn't execute CREATE statements from application code. That can quickly lead to schema disagreement.