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.
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
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.
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 core CockroachDB features:
Was this helpful?
