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

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

psycopg2 SQLAlchemy Django PonyORM peewee

This tutorial shows you how build a simple Python application with CockroachDB and PonyORM.

Before you begin

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

{{site.data.alerts.callout_info}} The example code on this page uses Python 3. {{site.data.alerts.end}}

Step 1. Install PonyORM

To install PonyORM run the following command:

{% include copy-clipboard.html %}

$ python -m pip install pony

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 client 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

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

The code below uses PonyORM to map Python objects and methods to SQL operations. When you run the code as a script, it performs the following operations:

1. Reads existing account IDs from the bank database.
2. Creates additional accounts with randomly generated IDs, and then adds a bit of money to each new account.
3. Chooses two accounts at random and takes half of the money from the first account and deposits it into the second.

Copy the code below to a file or download it directly.

{% include copy-clipboard.html %}

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

Copy the code below to a file or download it directly.

{% include copy-clipboard.html %}

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

Run the code:

{% include copy-clipboard.html %}

$ python pony-basic-sample.py

To verify that the table and rows were created successfully, open a new terminal, and start a new session with the built-in SQL client:

{% include copy-clipboard.html %}

$ cockroach sql --certs-dir=certs --database=bank

{% include copy-clipboard.html %}

$ cockroach sql --insecure --database=bank

Issue the following statement:

{% include copy-clipboard.html %}

> SELECT COUNT(*) FROM accounts;

 count
-------
   100
(1 row)

Best practices

Pony ORM provides the retry option for the db_session decorator. If Pony detects that the optimistic checks do not pass, it restarts the decorated function automatically. The retry parameter can only be specified in the db_session decorator and not the context manager. For more information, see PonyORM documentation.