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.	true	false

Use psycopg2 Use SQLAlchemy

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.

{{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:

{% include copy-clipboard.html %}

$ pip install sqlalchemy sqlalchemy-cockroachdb psycopg2

{{site.data.alerts.callout_success}} You can substitute psycopg2 for other alternatives that include the psycopg python package. {{site.data.alerts.end}}

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

{% include {{ page.version.version }}/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.

{{site.data.alerts.callout_info}} 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. {{site.data.alerts.end}}

Copy the code or download it directly.

{% include copy-clipboard.html %}

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

Then run the code:

{% include copy-clipboard.html %}

$ 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:

{% include copy-clipboard.html %}

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

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

{% include copy-clipboard.html %}

$ 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.

{% include {{ page.version.version }}/app/see-also-links.md %}