cql-builder
The cql-builder library is a CQL statement generation tool for Apache Cassandra. It is intended to be used in conjunction with the Datastax Cassandra driver written in Python. The generation tool is built on the idea that each component of a CQL statement can be expressed as an abstraction. A valid CQL statement can be created by chaining together these abstractions.
This API works exclusively with the Datastax Cassandra driver for CQL3.
https://github.com/datastax/python-driver
Installation
Install with pip:
pip install cql-builderStatements
The available statements which can be generated with QueryBuilder in cql_builder.builder are: Insert, Update, Select, Delete and Truncate. Statements can be generated by the full table path (keyspace.column_family) or by partial path (column_family).
| Statement | Builder | Parameters |
|---|---|---|
| Insert | QueryBuilder.insert_into |
column_family, [keyspace] |
| Update | QueryBuilder.update |
column_family, [keyspace] |
| Select | QueryBuilder.select_from |
column_family, [keyspace] |
| Delete | QueryBuilder.delete_from |
column_family, [keyspace] |
| Truncate | QueryBuilder.truncate |
column_family, [keyspace] |
Conditions
Conditions which are used in the where expression of the statement are in cql_builder.condition and include: eq, gt, gte, lt, lte, within and all_eq.
| Condition | Usage |
|---|---|
eq, gt, gte, lt, lte |
condition(name, value) |
all_eq |
all_eq(name=value, ...) |
within |
within(name, {value, ...}) |
Execution
from cassandra.cluster import Cluster
from cassandra.query import SimpleStatement
from cassandra import ConsistencyLevel as Level
from cql_builder.builder import QueryBuilder
keyspace = 'keyspace'
column_family = 'column_family'
cluster = Cluster(['localhost'])
# Generate & execute a statement with full table path.
# INSERT INTO keyspace.column_family (first, last) VALUES ('foo', 'bar')
session = cluster.connect()
insert = (QueryBuilder.insert_into(column_family, keyspace)
.values(first='foo', last='bar')
)
query, args = insert.statement()
session.execute(query, args)
# Generate & execute a statement with partial table path with specified consistency.
# INSERT INTO column_family (first, last) VALUES ('foo', 'bar')
session = cluster.connect(keyspace)
insert = (QueryBuilder.insert_into(column_family)
.values(first='foo', last='bar')
)
query, args = insert.statement()
statement = SimpleStatement(query, consistency_level=Level.LOCAL_ONE)
session.execute(statement, args)Insert
| Expression | Usage | Description |
|---|---|---|
.values |
.values(name=value, ...) |
name-value pairs to set |
.using |
.using(option=value, ...) |
option pairs to use |
.if_not_exists |
.if_not_exists() |
set if not exists property |
from datetime import timedelta
from cql_builder.builder import QueryBuilder
# INSERT INTO column_family (first, last) VALUES ('foo', 'bar') USING TTL 3600
insert = (QueryBuilder.insert_into(column_family)
.values(first='foo', last='bar')
.using(ttl=timedelta(hours=1))
)
# INSERT INTO column_family (last, friends) VALUES ('bar', ['joe', 'schmoe']) USING TTL 10800
insert = (QueryBuilder.insert_into(column_family)
.values(last='bar', friends=['joe', 'schmoe'])
.using(ttl=10800)
)Update
| Expression | Usage | Description |
|---|---|---|
.using |
.using(option=value, ...) |
option pairs to use |
.set |
.set(name=value, ...) |
name-value pairs to set |
.set_at |
.set_at(name, key, value) |
value to set at key index |
.add |
.add(name, value) |
value to add to value at name |
.subtract |
.subtract(name, value) |
value to subtract from value at name |
.where |
.where(condition, ...) |
conditions for rows to apply updates to |
from cql_builder.builder import QueryBuilder
from cql_builder.condition import eq, gt, lte, all_eq, within
# UPDATE column_family USING TTL 3600 SET age=13 WHERE first='foo' AND last='bar'
update = (QueryBuilder.update(column_family)
.using(ttl=3600)
.set(age=13)
.where(all_eq(first='foo', last='bar'))
)
# UPDATE column_family SET age=13 WHERE names IN ('foo', 'bar')
update = (QueryBuilder.update(column_family)
.set(age=13)
.where(within('names', ['foo', 'bar']))
)
# UPDATE column_family SET friends=friends + ['joe'] WHERE last='bar'
update = (QueryBuilder.update(column_family)
.add('friends', ['joe'])
.where(eq('last', 'bar'))
)Select
| Expression | Usage | Description |
|---|---|---|
.columns |
.columns(name, ...) |
specific columns to select |
.all |
.all() |
all columns |
.count |
.count() |
count of the rows |
.where |
.where(condition, ...) |
conditions for rows to select from |
.limit |
.limit(count) |
limit the result count |
from cql_builder.builder import QueryBuilder
from cql_builder.condition import eq
# SELECT first, last from column_family WHERE last='bar'
select = (QueryBuilder.select_from(column_family)
.columns('first', 'last')
.where(eq('last', 'bar'))
)
# SELECT * from column_family WHERE name='foo' LIMIT 5
select = (QueryBuilder.select_from(column_family)
.all()
.where(eq('name', 'foo'))
.limit(5)
)Delete
| Expression | Usage | Description |
|---|---|---|
.columns |
.columns(name, ...) |
specific columns to delete |
.at |
.at(name, key) |
specific value at key index |
.where |
.where(condition, ...) |
conditions for rows to delete from |
from cql_builder.builder import QueryBuilder
from cql_builder.condition import eq
# DELETE FROM column_family WHERE name='foo'
delete = (QueryBuilder.delete_from(column_family)
.where(eq('name', 'foo'))
)
# DELETE first, age FROM column_family WHERE name='foo'
delete = (QueryBuilder.delete_from(column_family)
.columns('first', 'age')
.where(eq('name', 'foo'))
)Truncate
from cql_builder import QueryBuilder
# TRUNCATE column_family
truncate = QueryBuilder.truncate(column_family)
