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

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

Use psycopg2 Use SQLAlchemy Use Django Use PonyORM Use peewee

This tutorial shows you how build a simple Python application with CockroachDB and the psycopg2 driver.

Before you begin

{% include {{page.version.version}}/app/before-you-begin.md %}

Step 1. Install the psycopg2 driver

To install the Python psycopg2 driver, run the following command:

{% include copy-clipboard.html %}

$ pip install psycopg2

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

Step 2. Create the maxroach user and bank database

{% include {{page.version.version}}/app/create-maxroach-user-and-bank-database.md %}

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.

{% include copy-clipboard.html %}

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

Step 4. Run the Python code

Now that you have a database and a user, you'll run the code shown below to:

• Create an accounts table and insert some rows.
• Transfer funds between two accounts inside a transaction. To ensure that we handle transaction retry errors, we write an application-level retry loop that, in case of error, sleeps before trying the funds transfer again. If it encounters another retry error, it sleeps for a longer interval, implementing exponential backoff.
• Finally, we delete the accounts from the table before exiting so we can re-run the example code.

{{site.data.alerts.callout_success}} To clone a version of the code below that connects to insecure clusters, run the command below. Note that you will need to edit the connection string to use the certificates that you generated when you set up your secure cluster.

git clone https://github.com/cockroachlabs/hello-world-python-psycopg2/ {{site.data.alerts.end}}

Copy the code or download it directly.

{% include copy-clipboard.html %}

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

Then run the code:

{% include copy-clipboard.html %}

$ python basic-sample.py

The output should show the account balances before and after the funds transfer:

Balances at Wed Aug  7 12:11:23 2019
['1', '1000']
['2', '250']
Balances at Wed Aug  7 12:11:23 2019
['1', '900']
['2', '350']

Step 2. Create the maxroach user and bank database

{% include {{page.version.version}}/app/insecure/create-maxroach-user-and-bank-database.md %}

Step 3. Run the Python code

Now that you have a database and a user, you'll run the code shown below to:

• Create an accounts table and insert some rows.
• Transfer funds between two accounts inside a transaction. To ensure that we handle transaction retry errors, we write an application-level retry loop that, in case of error, sleeps before trying the funds transfer again. If it encounters another retry error, it sleeps for a longer interval, implementing exponential backoff.
• Finally, we delete the accounts from the table before exiting so we can re-run the example code.

To get the code below, clone the hello-world-python-psycopg2 repo to your machine:

{% include copy-clipboard.html %}

git clone https://github.com/cockroachlabs/hello-world-python-psycopg2/

{% include copy-clipboard.html %}

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

Change to the directory where you cloned the repo and run the code:

{% include copy-clipboard.html %}

$ python example.py

The output should show the account balances before and after the funds transfer:

Balances at Wed Jul 24 15:58:40 2019
['1', '1000']
['2', '250']
Balances at Wed Jul 24 15:58:40 2019
['1', '900']
['2', '350']

What's next?

Read more about using the Python psycopg2 driver.

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