Python Data Persistence – Parameterized Queries

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.

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.

 

Leave a Comment