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

Before you begin

  1. Install CockroachDB.
  2. Start up a secure or insecure local cluster.
  3. Choose the instructions that correspond to whether your cluster is secure or insecure:
Note:

The example code on this page uses Python 3.

Step 1. Install PonyORM

To install PonyORM run the following command:

copy
icon/buttons/copy
$ python -m pip install pony

Step 2. Create the maxroach user and bank database

Start the built-in SQL shell:

copy
icon/buttons/copy
$ cockroach sql --certs-dir=certs

In the SQL shell, issue the following statements to create the maxroach user and bank database:

copy
icon/buttons/copy
> CREATE USER IF NOT EXISTS maxroach;
copy
icon/buttons/copy
> CREATE DATABASE bank;

Give the maxroach user the necessary permissions:

copy
icon/buttons/copy
> GRANT ALL ON DATABASE bank TO maxroach;

Exit the SQL shell:

copy
icon/buttons/copy
> \q

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.

copy
icon/buttons/copy
$ 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

Start the built-in SQL shell:

copy
icon/buttons/copy
$ cockroach sql --insecure

In the SQL shell, issue the following statements to create the maxroach user and bank database:

copy
icon/buttons/copy
> CREATE USER IF NOT EXISTS maxroach;
copy
icon/buttons/copy
> CREATE DATABASE bank;

Give the maxroach user the necessary permissions:

copy
icon/buttons/copy
> GRANT ALL ON DATABASE bank TO maxroach;

Exit the SQL shell:

copy
icon/buttons/copy
> \q

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.

copy
icon/buttons/copy
import random
from math import floor
from pony.orm import *

db = Database()

# The Account class corresponds to the "accounts" database table.


class Account(db.Entity):
    _table_ = 'accounts'
    id = PrimaryKey(int)
    balance = Required(int)


db_params = dict(provider='cockroach', user='maxroach', host='localhost', port=26257, database='bank', sslmode='require',
                 sslrootcert='certs/ca.crt', sslkey='certs/client.maxroach.key', sslcert='certs/client.maxroach.crt')


sql_debug(True)  # Print all generated SQL queries to stdout
db.bind(**db_params)  # Bind Database object to the real database
db.generate_mapping(create_tables=True)  # Create tables


# Store the account IDs we create for later use.

seen_account_ids = set()


# The code below generates random IDs for new accounts.

@db_session  # db_session decorator manages the transactions
def create_random_accounts(n):
    elems = iter(range(n))
    for i in elems:
        billion = 1000000000
        new_id = floor(random.random() * billion)
        seen_account_ids.add(new_id)
        # Create new account
        Account(id=new_id, balance=floor(random.random() * 1000000))


create_random_accounts(100)


def get_random_account_id():
    id = random.choice(tuple(seen_account_ids))
    return id


@db_session(retry=10)  # retry of the optimistic transaction
def transfer_funds_randomly():
    """
    Cuts a randomly selected account's balance in half, and gives the
    other half to some other randomly selected account.
    """

    source_id = get_random_account_id()
    sink_id = get_random_account_id()

    source = Account.get(id=source_id)
    amount = floor(source.balance / 2)

    if source.balance < amount:
        raise "Insufficient funds"

    source.balance -= amount
    sink = Account.get(id=sink_id)
    sink.balance += amount

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

copy
icon/buttons/copy
import random
from math import floor
from pony.orm import *

db = Database()

# The Account class corresponds to the "accounts" database table.


class Account(db.Entity):
    _table_ = 'accounts'
    id = PrimaryKey(int)
    balance = Required(int)


db_params = dict(provider='cockroach', user='maxroach',
                 host='localhost', port=26257, database='bank', sslmode='disable')


sql_debug(True)  # Print all generated SQL queries to stdout
db.bind(**db_params)  # Bind Database object to the real database
db.generate_mapping(create_tables=True)  # Create tables


# Store the account IDs we create for later use.

seen_account_ids = set()


# The code below generates random IDs for new accounts.

@db_session  # db_session decorator manages the transactions
def create_random_accounts(n):
    elems = iter(range(n))
    for i in elems:
        billion = 1000000000
        new_id = floor(random.random() * billion)
        seen_account_ids.add(new_id)
        # Create new account
        Account(id=new_id, balance=floor(random.random() * 1000000))


create_random_accounts(100)


def get_random_account_id():
    id = random.choice(tuple(seen_account_ids))
    return id


@db_session(retry=10)  # retry of the optimistic transaction
def transfer_funds_randomly():
    """
    Cuts a randomly selected account's balance in half, and gives the
    other half to some other randomly selected account.
    """

    source_id = get_random_account_id()
    sink_id = get_random_account_id()

    source = Account.get(id=source_id)
    amount = floor(source.balance / 2)

    if source.balance < amount:
        raise "Insufficient funds"

    source.balance -= amount
    sink = Account.get(id=sink_id)
    sink.balance += amount

Run the code:

copy
icon/buttons/copy
$ 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:

copy
icon/buttons/copy
$ cockroach sql --certs-dir=certs --database=bank
copy
icon/buttons/copy
$ cockroach sql --insecure --database=bank

Issue the following statement:

copy
icon/buttons/copy
> 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.



YesYes NoNo