Querying with cqlengine

Question

I am trying to hook the cqlengine CQL 3 object mapper with my web application running on CherryPy. Athough the documentation is very clear about querying, I am still not aware how to make queries on an existing table(and an existing keyspace) in my cassandra database. For instance I already have this table Movies containing the fields Title, rating, Year. I want to make the CQL query

SELECT * FROM Movies

How do I go ahead with the query after establishing the connection with

from cqlengine import connection
connection.setup(['127.0.0.1:9160'])

The KEYSPACE is called "TEST1".

Answer 1

Abhiroop Sarkar,

I highly suggest that you read through all of the documentation at:

Current Object Mapper Documentation

Legacy CQLEngine Documentation

Installation: pip install cassandra-driver

And take a look at this example project by the creator of CQLEngine, rustyrazorblade:

Example Project - Meat bot

Keep in mind, CQLEngine has been merged into the DataStax Cassandra-driver:

Official Python Cassandra Driver Documentation

You'll want to do something like this:

CQLEngine <= 0.21.0:

from cqlengine.connection import setup


setup(['127.0.0.1'], 'keyspace_name', retry_connect=True)

If you need to create the keyspace still:

from cqlengine.management import create_keyspace


create_keyspace(
    'keyspace_name',
    replication_factor=1,
    strategy_class='SimpleStrategy'
)

Setup your Cassandra Data Model

You can do this in the same .py or in your models.py:

import datetime
import uuid

from cqlengine import columns, Model


class YourModel(Model):

    __key_space__ = 'keyspace_name'  # Not Required
    __table_name__ = 'columnfamily_name'  # Not Required

    some_int = columns.Integer(
        primary_key=True,
        partition_key=True
    )
    time = columns.TimeUUID(
        primary_key=True,
        clustering_order='DESC',
        default=uuid.uuid1,
    )
    some_uuid = columns.UUID(primary_key=True, default=uuid.uuid4)
    created = columns.DateTime(default=datetime.datetime.utcnow)
    some_text = columns.Text(required=True)

    def __str__(self):
        return self.some_text

    def to_dict(self):
        data = {
            'text': self.some_text,
            'created': self.created,
            'some_int': self.some_int,
        }

        return data

Sync your Cassandra ColumnFamilies

from cqlengine.management import sync_table

from .models import YourModel


sync_table(YourModel)

Considering everything above, you can put all of the connection and syncing together, as many examples have outlined, say this is connection.py in our project:

from cqlengine.connection import setup
from cqlengine.management import sync_table

from .models import YourTable


def cass_connect():
    setup(['127.0.0.1'], 'keyspace_name', retry_connect=True)
    sync_table(YourTable)

Actually Using the Model and Data

from __future__ import print_function

from .connection import cass_connect
from .models import YourTable


def add_data():
    cass_connect()
    YourTable.create(
        some_int=5,
        some_text='Test0'
    )

    YourTable.create(
        some_int=6,
        some_text='Test1'
    )

    YourTable.create(
        some_int=5,
        some_text='Test2'
    )


def query_data():
    cass_connect()
    query = YourTable.objects.filter(some_int=5)
    # This will output each YourTable entry where some_int = 5
    for item in query:
        print(item)

Feel free to let ask for further clarification, if necessary.

Answer 2

The most straightforward way to achieve this is to make model classes which mirror the schema of your existing cql tables, then run queries on them

Answer 3

cqlengine is primarily an Object Mapper for Cassandra. It does not interrogate an existing database in order to create objects for existing tables. Rather it is usually intended to be used in the opposite direction (i.e. create tables from python classes). If you want to query an existing table using cqlengine you will need to create python models that exactly correspond to your existing tables.

For example, if your current Movies table had 3 columns, id, title, and release_date you would need to create a cqlengine model that had those three columns. Additionally, you would need to ensure that the table_name attribute on the class was exactly the same as the table name in the database.

from cqlengine import columns, Model

class Movie(Model):
    __table_name__ = "movies"
    id = columns.UUID(primary_key=True)
    title = columns.Text()
    release_date = columns.Date()

The key thing is to make sure that model exactly mirrors the existing table. If there are small differences you may be able to use sync_table(MyModel) to update the table to match your model.