Coding with Python Part 2 – Prepared Statements

In a previous lesson, we explained how to create a sample Python application that executes a few basic CQL statements with a ScyllaDB cluster using the Python driver. However, that code was too static and not scalable. In this lesson, we’ll see how to optimize the existing Python code with prepared statements. This will allow us to build better performing applications that are more scalable.

What Are Prepared Statements?

A prepared statement is a query that is parsed by ScyllaDB and then saved for later use. One of the valuable benefits is that you can continue to reuse that query and modify variables in the query to match variables such as names, addresses, and locations. Let’s dive a little deeper to see how it works.

When asked to prepare a CQL statement, a client library will send a CQL statement to ScyllaDB. ScyllaDB will then create a unique fingerprint for that CQL statement by MD5 hashing the CQL statement. ScyllaDB will use this hash to check its query cache to see if it has already cached that CQL statement. If ScyllaDB had seen that CQL statement, it will send back a reference to that cached CQL statement. If ScyllaDB does not have that unique query hash in its cache, it will then proceed to parse the query and insert the parsed output into its cache.

The client will then be able to send an execute request specifying the statement id and providing the (bound) variables, as we will see next.

Now, let’s go over a Python application that uses prepared statements.

Setting up the ScyllaDB Cluster

The example requires a single DC cluster. Follow this procedure to remove previous clusters and set up a new cluster.

Once the cluster is up, we’ll create the catalog keyspace and populate it with data.

The first task is to create the keyspace for the catalog.

docker exec -it scylla-node1 cqlsh

CREATE KEYSPACE catalog WITH REPLICATION = { 'class' : 'NetworkTopologyStrategy','DC1' : 3};

Now that the keyspace is created, it is time to create the table.

use catalog;

CREATE TABLE mutant_data (
   first_name text,
   last_name text,
   address text,
   picture_location text,
   PRIMARY KEY((first_name, last_name)));

Now let’s add a few mutants to the catalog with the following statements:

insert into mutant_data ("first_name","last_name","address","picture_location") VALUES ('Bob','Loblaw','1313 Mockingbird Lane', 'http://www.facebook.com/bobloblaw');
insert into mutant_data ("first_name","last_name","address","picture_location") VALUES ('Bob','Zemuda','1202 Coffman Lane', 'http://www.facebook.com/bzemuda');
insert into mutant_data ("first_name","last_name","address","picture_location") VALUES ('Jim','Jeffries','1211 Hollywood Lane', 'http://www.facebook.com/jeffries');

The Python Code

We will use the file prepared_statement_app.py, which you can find in the directory: scylla-code-samples/mms/python/python-app-ps

We will first go over each section of the code before running it.

To get started, we will create two prepared statements in the App constructor:

The first prepared statement is named insert_ps. This statement adds data programmatically for first_name, last_name, address, and picture_location based on input from the application. The second prepared statement is called delete_ps, and it deletes entries in the table, based on input for first_name and last_name. These statements are reused later on to add and delete data in the mutant_data table.

The add_mutant function is defined as follows:

This method takes input for first_name, last_name, address, and picture_location and then executes the prepared statement named insert_ps using these parameters. By using prepared statements, we can rerun this method to add data to the catalog table. This significantly improves performance.

The delete_mutant function is defined as follows:

In this method, we take as inputs first_name and last_name and use them as parameters for the delete_ps prepared statement. Again, by reusing the prepared statement, we improve performance.

Finally, the main function passes input to the functions when the application starts. It is defined as follows:

First, the contents of the catalog table are displayed. Next, we add two additional mutants using the add_mutant function. We then show the contents of the table again to see that the mutants were indeed added. Finally, each mutant that was added is deleted, and the contents of the table are shown once more.

After understanding the code, let’s run the sample application in the Docker container we previously set up.

Building the Python Example

If you previously built the Python Docker, you can skip directly to “Running the Python Example.”

Otherwise, to build the application in Docker, change into the python subdirectory in scylla-code-samples. If you are still in cqlsh exit first.

cd scylla-code-samples/mms/python

Now we can build the container:

docker build -t python-app . 

Running the Python Example

docker run -d --net=mms_web --name some-python-app python-app prepared_statement_app.py 

Viewing the Run Results

Once the Python app is run in the container, we can see the output by printing the logs:

docker logs some-python-app

The output of the application will be:

Keep in mind that the output might be different if you did not run the previous lesson as the content of the table would not be the same.

Conclusion

In this lesson, we explained what prepared statements are and how they work with the Python driver to run applications more efficiently.

Stay tuned for more content!