Inserting data into Cassandra

Question

I have this Python code that connects to a Cassandra database, creates a table and inserts data into it

cluster = Cluster(['localhost'])
session = cluster.connect('my_keyspace_test')

create_query = """CREATE TABLE IF NOT EXISTS my_keyspace_test.songs_by_sessionID (
        sessionID INT,
        artist TEXT,
        song TEXT,
        length FLOAT,
        PRIMARY KEY (sessionID)
    );"""

session.execute(create_query)

session_id = 251
artist_name = "Your Artist"
song_name = "Your Song"
song_length = 125.2

# Define the INSERT query
insert_query = "INSERT INTO songs_by_sessionid (sessionid, artist, song, length) VALUES (?, ?, ?, ?)"

# Execute the INSERT query with the data
session.execute(insert_query, (session_id, artist_name, song_name, song_length))

When I execute it I receive this error:

Traceback (most recent call last):
  File "-------", line 162, in <module>
    session.execute(insert_query, (session_id, artist_name, song_name, song_length))
  File "cassandra/cluster.py", line 2637, in cassandra.cluster.Session.execute
  File "cassandra/cluster.py", line 2680, in cassandra.cluster.Session.execute_async
  File "cassandra/cluster.py", line 2939, in cassandra.cluster.Session._create_response_future
  File "cassandra/query.py", line 909, in cassandra.query.bind_params
TypeError: not all arguments converted during string formatting

Any explanation? How to fix it?

I tried changing the types but I received the same error.

Answer 1

So the problem here, is that the session.execute statement is expecting a prepared statement to bind the variables to. But instead, it gets a query string.

Try preparing and executing your query like this:

pStatement = session.prepare(insert_query);
session.execute(pStatement, (session_id, artist_name, song_name, song_length))

Do you have any adaptation for batch execution?

Sure, we have a doc that shows how that works, and it's quite similar:

pStatement = session.prepare(insert_query)
batch = BatchStatement()

batch.add(pStatement, (session_id1, artist_name1, song_name1, song_length1))
batch.add(pStatement, (session_id2, artist_name2, song_name2, song_length2))
batch.add(pStatement, (session_id3, artist_name3, song_name3, song_length3))

session.execute(batch)

Answer 2

You should instead modify your approach as below:

# Define the INSERT query
prepared_insert_query = session.prepare("INSERT INTO songs_by_sessionid (sessionid, artist, song, length) VALUES (?, ?, ?, ?)")

and execute it as below,

# Execute the INSERT query with the data
session.execute(prepared_insert_query, [session_id, artist_name, song_name, song_length])

Other nitpick:

• when creating Cassandra® columns, it is always recommended to use lowercase with underscores in between separation. For e.g. in your case, sessionID could be modified as session_id