In this tutorial, I would like to show you how to interact with Crate using SQLAlchemy. SQLAlchemy is a Python SQL toolkit and Object Relational Mapper.
CrateDB is a scaling document-based database with an SQL interface that you can use out of the box. It's perfect for people who don't want to worry about sharding, replication, and scalability.
The simplest way to launch Crate in a Docker container (this command will download the latest Docker image):
docker run --publish 4200:4200 --publish 5432:5432 --pull=always crate:latest -Cdiscovery.type=single-nodeAnd check if it's running by opening the admin interface at:
http://localhost:4200/admin Alternatively, you can install Crate through your favorite package manager. (instructions).
Create a virtualenv and install the Crate client and SqlAlchemy:
pip install -U crate sqlalchemy
For this tutorial we are going to play with a simple book database. The books will have a title, an author, a publication date and some categories.
Let's create a file books.py with the following content:
import uuid
import sqlalchemy as sa
from datetime import datetime
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker
DBSession = scoped_session(sessionmaker())
Base = declarative_base()
engine = sa.create_engine( 'crate://', connect_args={ 'servers': ["http://localhost:4200"], }, )
DBSession.configure(bind=engine)
Base.metadata.bind = engine
Crate is a clustered database that usually consists of multiple nodes. It's recommended to add all nodes into servers lists. This enables the DB-API layer to use round-robin to distribute the load and skip a server if it becomes unavailable.
Now that we have a running database connection, we can create the Book model. The model has a primary key called id. We use random UUID's as keys. It also has two string fields, title and author, and a publication_date timestamp. Unlike other databases that are usually used with SQLAlchemy, CrateDB isn't an RDBMS instead, it is document-oriented which means that the database should be modeled without relationships.
CrateDB supports complex objects and arrays. We'll use an array of strings for the categories.
Add the following class to books.py:
class Book(Base):
__tablename__ = 'books'
id = sa.Column(sa.String, default=lambda: str(uuid.uuid4()), primary_key=True)
title = sa.Column(sa.String)
author = sa.Column(sa.String)
publication_date = sa.Column(sa.DateTime)
categories = sa.Column(sa.String, default=[])
CrateDB is a great solution for full-text search. To enable the fulltext search it's necessary to define a fulltext index with an analyzer. We use the built-in english analyzer but Crate also provides the possibility to create custom analyzers or to extend built-in analyzers. For further information on fulltext search take a look at the documentation.
Now, let's create a table:
CREATE TABLE books (
id string primary key,
publication_date timestamp,
title string INDEX using fulltext with(analyzer='english'),
author string,
categories array(string)
);
Now you can create some books:
hitchhiker = Book()
hitchhiker.title = "The Hitchhiker's Guide to the Galaxy"
hitchhiker.author = "Douglas Adams"
hitchhiker.publication_date = datetime(1979, 10, 12)
hitchhiker.categories = ["novel", "documentation", "comedy"]
restaurant = Book()
restaurant.title = "The Restaurant at the End of the Universe"
restaurant.author = "Douglas Adams"
restaurant.publication_date = datetime(1980, 1, 1)
restaurant.categories = ["novel", "documentation", "comedy"]
big_red = Book()
big_red.title = "Monty Python's Big Red Book"
big_red.author = "Monty Python"
big_red.publication_date = datetime(1971, 9, 30)
big_red.categories = ["comedy"]
DBSession.add(hitchhiker)
DBSession.add(restaurant)
DBSession.add(big_red)
DBSession.commit()
client = Base.metadata.bind.raw_connection().connection.client
client.sql("refresh table books")
If you are already familiar with SQLAlchemy you know that we add the created books to the current session with DBSession.add() and you may think that we call DBSession.commit() to commit the current instructions. But that's not fully true. Unlike other databases that are usually used with SQLAlchemy, CrateDB is connected stateless via HTTP. So there are no sessions and no transaction support.
In SQLAlchemy an Session object is used to query the database. This Session object contains a rollback method that in Crate won't do anything at all. Similarly, the commit method will only flush but not actually commit, as there is no commit in Crate.
After INSERT statements are sent to the database the newly inserted rows aren't immediately available for search because the index is only updated periodically (every 1 second per default) so we force a refresh of the table.
To fetch all books of a specific author sorted by publication date you can do the following query:
books = DBSession.query(Book).filter_by(author='Douglas Adams') \
.order_by(Book.publication_date).all()
for book in books:
print("{0} - {1}".format(book.title, book.publication_date))
Now you can execute the script:
python books.py
The Hitchhiker's Guide to the Galaxy
The Restaurant at the End of the Universe
We also can use SQLAlchemy to query how many books each author has written by using a group_by clause:
authors = DBSession.query(sa.func.count(Book.id), Book.author) \
.group_by(Book.author) \
.order_by(sa.desc(sa.func.count(Book.id))).all()
print(authors)
[(2, u'Douglas Adams'), (1, u'Monty Python')]
To perform a full-text search we use the match function:
books = DBSession.query(Book.title) \
.filter(sa.func.match(Book.title, 'monty')).all()
for book in books:
print(book.title) Monty Python's Big Red Book
If you want to get a deeper insight into Crate and SQLAlchemy, please visit our documentation.