Build a Python App with CockroachDB

On this page Carat arrow pointing down
Warning:
As of October 4, 2019, CockroachDB v2.0 is no longer supported. For more details, refer to the Release Support Policy.

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

  1. Install CockroachDB.
  2. Start up a secure or insecure local cluster.
  3. Choose the instructions that correspond to whether your cluster is secure or insecure:

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:

icon/buttons/copy
$ 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. Create the maxroach user and bank database

Start the built-in SQL client:

icon/buttons/copy
$ cockroach sql --certs-dir=certs

In the SQL shell, issue the following statements to create the maxroach user and bank database:

icon/buttons/copy
> CREATE USER IF NOT EXISTS maxroach;
icon/buttons/copy
> CREATE DATABASE bank;

Give the maxroach user the necessary permissions:

icon/buttons/copy
> GRANT ALL ON DATABASE bank TO maxroach;

Exit the SQL shell:

icon/buttons/copy
> \q

Step 3. Generate a certificate for the maxroach user

Create a certificate and key for the maxroach user by running the following command. The code samples will run as this user.

icon/buttons/copy
$ cockroach cert create-client maxroach --certs-dir=certs --ca-key=my-safe-directory/ca.key

Step 3. 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.

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',
                       connect_args = {
                           'sslmode' : 'require',
                           'sslrootcert': 'certs/ca.crt',
                           'sslkey':'certs/client.maxroach.key',
                           'sslcert':'certs/client.maxroach.crt'
                       })
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:

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, start the built-in SQL client:

icon/buttons/copy
$ cockroach sql --certs-dir=certs --database=bank

Then, issue the following statement:

icon/buttons/copy
> SELECT id, balance FROM accounts;
+----+---------+
| id | balance |
+----+---------+
|  1 |    1000 |
|  2 |     250 |
+----+---------+
(2 rows)

Step 2. Create the maxroach user and bank database

Start the built-in SQL client:

icon/buttons/copy
$ cockroach sql --insecure

In the SQL shell, issue the following statements to create the maxroach user and bank database:

icon/buttons/copy
> CREATE USER IF NOT EXISTS maxroach;
icon/buttons/copy
> CREATE DATABASE bank;

Give the maxroach user the necessary permissions:

icon/buttons/copy
> GRANT ALL ON DATABASE bank TO maxroach;

Exit the SQL shell:

icon/buttons/copy
> \q

Step 3. 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.

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',
                       connect_args = {
                           '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:

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, start the built-in SQL client:

icon/buttons/copy
$ cockroach sql --insecure --database=bank

Then, issue the following statement:

icon/buttons/copy
> SELECT id, balance FROM accounts;
+----+---------+
| 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:


Yes No
On this page

Yes No