Python Data Persistence – Parameterized Queries
The cassandra.query submodule defines the following Statement classes:
SimpleStatement: A simple, unprepared CQL query contained in a query string. For example:
Example
from cassandra.query import SimpleStatement stmt=SimpleStatement("select * from products;") rows=session.execute(stmt)
BatchStatement: A batch combines multiple DML operations (such as INSERT, UPDATE, and DELETE) and executes at once to achieve atomicity. For the following example, first create a ‘customers’ table in the current keyspace.
create table customers . . . ( . . . custID int primary key, . . . name text, . . . GSTIN text . . . ) ;
Customer data is provided in the form of a list of tuples. Individual INSERT query is populated with each tuple and added in a BatchStatement. The batch is then executed at once.
- Python Data Persistence – ORM – Querying
- Python Data Persistence – ORM – Filter Criteria
- Data Persistence – Python – PyMongo
Example
#cassandra-batch.py from cassandra.cluster import Cluster clstr=Cluster( ) session=clstr.connect(1mykeyspace') custlist= [ (1, 'Ravikumar', '2 7AAJPL7103N1ZF') , (2, 'Pate1' , ' 24ASDFG1234N1ZN' ) , (3, 'Nitin' , '27AABBC7895N1ZT') , , (4, 1Nair' , '32MMAF8963N1ZK') , (5,'Shah','24BADEF2002N1ZB'), (6,'Khurana','07KABCS1002N1ZV'), (7,'Irfan','05IIAAV5103N1ZA1), (8,'Kiran','12PPSDF22431ZC'}, (9,'Divya','15ABCDE1101N1ZA'), (10, 'John', '2 9AAEEC42 58E1ZR' )] from cassandra.query import SimpleStatement, BatchStatement batch=BatchStatement( ) for cst in custlist: batch . add(SimpleStatement("INSERT INTO customers (custID,name,GSTIN) VALUES (%s, %s, %s) ") , \ (cst [0], cst[1],cst [2] ) ) session.execute(batch)
Run the above code and then check rows in the ‘customers’ table in the CQL shell.
cq1sh:mykeyspace> select * from customers; custid | gstin | name -----------+--------------------------+------------- 5 | 24BADEF2002N1ZB | Shah 10 | 29AAEEC4258E1ZK | John 1 | 27AAJPL7103N1ZF | Ravikumar 8 | 12PPSDF22431ZC | Kiran 2 | 24ASDFG1234N1ZN | Patel 4 | 32MMAF8963N1ZK | Nair 7 | 05IIAAV5103N1ZA | Irfan 6 | 07KABCS1002N1ZV | Khurana 9 | 15ABCDEU01N1ZA | Divya 3 | 27AABBC7895N1ZT | Nitin (10 rows)
PreparedStatement: Prepared statement contains a query string that is parsed by Cassandra and then saved for later use. Subsequently, it only needs to send the values of parameters to bind. This reduces network traffic and CPU utilization because Cassandra does not have to re-parse the query each time. The Session.prepare( ) method returns a PreparedStatement instance.
Example
#cassandra-prepare.py from Cassandra.cluster import Cluster from cassandra.query import PreparedStatement clstr=Cluster( ) session=clstr.connect{'mykeyspace') stmt=session.prepare("INSERT INTO customers (custID, name,GSTIN) VALUES (?,?,?)") boundstmt=stmt.bind{[11,'HarishKumar1, '12 PQRDF2 2431ZN'] ) session.execute(boundstmt)
Each time, the prepared statement can be executed by binding it with a new set of parameters. Note that, the PreparedStatement uses ‘?’ as a placeholder and not ‘%s’ as in BatchStatement.