This tutorial shows you how build a simple Python application with CockroachDB and PonyORM.
Before you begin
- Install CockroachDB.
- Start up a secure or insecure local cluster.
- Choose the instructions that correspond to whether your cluster is secure or insecure:
The example code on this page uses Python 3.
Step 1. Install PonyORM
To install PonyORM run the following command:
$ python -m pip install pony
Step 2. Create the maxroach user and bank database
Start the built-in SQL shell:
$ cockroach sql --certs-dir=certs
In the SQL shell, issue the following statements to create the maxroach user and bank database:
> CREATE USER IF NOT EXISTS maxroach;
> CREATE DATABASE bank;
Give the maxroach user the necessary permissions:
> GRANT ALL ON DATABASE bank TO maxroach;
Exit the SQL shell:
> \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.
$ 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:
$ cockroach sql --insecure
In the SQL shell, issue the following statements to create the maxroach user and bank database:
> CREATE USER IF NOT EXISTS maxroach;
> CREATE DATABASE bank;
Give the maxroach user the necessary permissions:
> GRANT ALL ON DATABASE bank TO maxroach;
Exit the SQL shell:
> \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:
- Reads existing account IDs from the
bankdatabase. - Creates additional accounts with randomly generated IDs, and then adds a bit of money to each new account.
- 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.
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.
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:
$ 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:
$ cockroach sql --certs-dir=certs --database=bank
$ cockroach sql --insecure --database=bank
Issue the following statement:
> 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.
Was this helpful?
