1

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

Erick Ramirez
  • 13,964
  • 1
  • 18
  • 23
frankenstein
  • 125
  • 2
  • 11
  • anyone knows??.. – frankenstein Mar 27 '23 at 11:56
  • A friendly note to let you know that I've rolled back your last edit because it significantly changes your original question and makes the answers look like they are irrelevant. Please log a new question instead of heavily modifying an existing one. Cheers! – Erick Ramirez Mar 28 '23 at 07:40
  • @Erick Ramirez oh Hi Erick, thanks alot for your feedback, I will post a new question regarding about this, thank you again bro. – frankenstein Mar 28 '23 at 07:42

3 Answers3

1

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.

Aaron
  • 55,518
  • 11
  • 116
  • 132
  • thank for your suggestion, I'm trying your solution, let see if this works or not – frankenstein Mar 28 '23 at 02:42
  • Hi, I've just explained more abt the situation I'm facing with, would you mind take a quick look again at the source code again, thank you for your support – frankenstein Mar 28 '23 at 03:06
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!

Erick Ramirez
  • 13,964
  • 1
  • 18
  • 23
  • Hi , thank for your comment, could you take a look at this thread, I just posted new thread regarding to this issue. https://stackoverflow.com/questions/75863601/python-cassandra-how-to-insert-batch-data-of-type-text-in-cassandra – frankenstein Mar 28 '23 at 08:13
0

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))
Madhavan
  • 758
  • 4
  • 8