Warning:
This version of CockroachDB is no longer supported. Cockroach Labs supports the current stable release and two releases prior. Please use one of these supported versions.

This tutorial shows you how build a simple Python application with CockroachDB using a PostgreSQL-compatible driver or ORM. We've tested and can recommend the Python psycopg2 driver and the SQLAlchemy ORM, so those are featured here.

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:

copy
icon/buttons/copy
$ pip install sqlalchemy cockroachdb

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:

copy
icon/buttons/copy
$ 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.

copy
icon/buttons/copy
$ 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.

copy
icon/buttons/copy
$ cockroach sql --insecure -e 'CREATE DATABASE bank'

Then grant privileges to the maxroach user.

copy
icon/buttons/copy
$ 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 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.

copy
icon/buttons/copy
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:

copy
icon/buttons/copy
$ 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:

copy
icon/buttons/copy
$ cockroach sql --insecure -e 'SHOW TABLES' --database=bank
+----------+
|  Table   |
+----------+
| accounts |
+----------+
(1 row)
copy
icon/buttons/copy
$ 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 core CockroachDB features:



Yes No