build-a-python-app-with-cockroachdb-sqlalchemy.md

title	summary	toc	twitter
Build a Python App with CockroachDB	Learn how to use CockroachDB from a simple Python application with the SQLAlchemy ORM.	false	true

Use psycopg2 Use SQLAlchemy

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.

{{site.data.alerts.callout_success}}For a more realistic use of SQLAlchemy with CockroachDB, see our examples-orms repository.{{site.data.alerts.end}}

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 cockroachdb

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

{% include app/common-steps.md %}

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. Also note that the cockroachdb python package installed earlier is triggered by the cockroachdb:// prefix in the engine URL.

Copy the code or download it directly.

{% include app/sqlalchemy-basic-sample.py %}

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:

• Data Replication
• Fault Tolerance & Recovery
• Automatic Rebalancing