Build a Python App with CockroachDB

On this page

This tutorial shows you how build a simple Python application with CockroachDB using a PostgreSQL-compatible driver or ORM.

We have tested the Python psycopg2 driver and the SQLAlchemy ORM enough to claim beta-level support, so those are featured here. If you encounter problems, please open an issue with details to help us make progress toward full support.

Tip:

For a more realistic use of SQLAlchemy with CockroachDB, see our examples-orms repository.

Before You Begin

Make sure you have already installed CockroachDB.

Step 1. Install the SQLAlchemy ORM

To install SQLAlchemy, as well as a CockroachDB Python package that accounts for some minor differences between CockroachDB and PostgreSQL, run the following command:

$ pip install sqlalchemy sqlalchemy-cockroachdb psycopg2

Tip:

You can substitute psycopg2 for other alternatives that include the psycopg python package.

For other ways to install SQLAlchemy, see the official documentation.

Step 2. Start a single-node cluster

For the purpose of this tutorial, you need only one CockroachDB node running in insecure mode:

$ cockroach start \
--insecure \
--store=hello-1 \
--host=localhost

Step 3. Create a user

In a new terminal, as the root user, use the cockroach user command to create a new user, maxroach.

$ cockroach user set maxroach --insecure

Step 4. Create a database and grant privileges

As the root user, use the built-in SQL client to create a bank database.

$ cockroach sql --insecure -e 'CREATE DATABASE bank'

Then grant privileges to the maxroach user.

$ cockroach sql --insecure -e 'GRANT ALL ON DATABASE bank TO maxroach'

Step 5. Run the Python code

The following code uses the SQLAlchemy ORM to map Python-specific objects to SQL operations. Specifically, Base.metadata.create_all(engine) creates an accounts table based on the Account class, session.add_all([Account(),... ]) inserts rows into the table, and session.query(Account) selects from the table so that balances can be printed.

Note:

The sqlalchemy-cockroachdb python package installed earlier is triggered by the cockroachdb:// prefix in the engine URL. Using postgres:// to connect to your cluster will not work.

Copy the code or download it directly.

from __future__ import print_function
from sqlalchemy import create_engine, Column, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

# The Account class corresponds to the "accounts" database table.
class Account(Base):
    __tablename__ = 'accounts'
    id = Column(Integer, primary_key=True)
    balance = Column(Integer)

# Create an engine to communicate with the database. The "cockroachdb://" prefix
# for the engine URL indicates that we are connecting to CockroachDB.
engine = create_engine("cockroachdb://maxroach@localhost:26257/bank?sslmode=disable")
Session = sessionmaker(bind=engine)

# Automatically create the "accounts" table based on the Account class.
Base.metadata.create_all(engine)

# Insert two rows into the "accounts" table.
session = Session()
session.add_all([
    Account(id=1, balance=1000),
    Account(id=2, balance=250),
])
session.commit()

# Print out the balances.
for account in session.query(Account):
    print(account.id, account.balance)

Then run the code:

$ python sqlalchemy-basic-sample.py

The output should be:

1 1000
2 250

To verify that the table and rows were created successfully, you can again use the built-in SQL client:

$ cockroach sql --insecure -e 'SHOW TABLES' --database=bank

+----------+
|  Table   |
+----------+
| accounts |
+----------+
(1 row)

$ cockroach sql --insecure -e 'SELECT id, balance FROM accounts' --database=bank

+----+---------+
| id | balance |
+----+---------+
|  1 |    1000 |
|  2 |     250 |
+----+---------+
(2 rows)

What's Next?

Read more about using the SQLAlchemy ORM, or check out a more realistic implementation of SQLAlchemy with CockroachDB in our examples-orms repository.

You might also be interested in using a local cluster to explore the following CockroachDB benefits:

Was this helpful?

Pricing

Contact us

Sign In