How to Build a Python App with CockroachDB and PonyORM

In this guide, we will introduce you to CockroachDB and PonyORM using Python. We will start by discussing what these technologies are and then discuss how they work.

Before we begin building a Python application with CockroachDB and PonyORM, let's understand what these technologies are:

What is CockroachDB

CockroachDB is a highly scalable, open-source, distributed SQL database that uses transactional and consistent key-value stores.

CockroachDB is very efficient as a method for ensuring data persistence and recovery in case of failure. In case of hardware and software failures, it can preserve data by using strong-consistent replications of its data and automatic repairs. Using SQL API, CockroachDB allows you to query, structure, and manipulate data using SQL queries.

Using the SQL API, CockroachDB provides developers an easy transition as they get the same familiar concepts. Because it already has existing SQL drivers for most programming languages, using it becomes more comfortable.

We recommend you check out the CockroachDB documentation for a better understanding.

https://linkfy.to/cockroachDocs

What is PonyORM

PonyORM is an advanced Python Object-Relational Mapper. Although there are other Python ORM such as Django and SQLAlchemy, PonyORM is advantageous because it has features like support for composite keys, automatic query optimization, and intuitive and straightforward query syntax.

An ORM is simply a tool that allows you to work with an SQL database using your preferred programming language. It gives developers the ability to work with the data within a Database in the form of objects; hence, you can use OOP for your language to work with the data.

Using PonyORM library, we can use Python language to work with data in CockroachDB in the form of objects of the relational database.

You can use the PonyORM documentation for reference. Here's the link:

https://docs.ponyorm.org/toc.html

How to Install CockroachDB On Linux

To install CockroachDB on Linux systems, follow the steps in this guide, and, depending on your system configuration, you will need to have root access or be a sudo user.

The first step is to ensure that your system is up-to-date and then install the required dependencies. Here's the command for that:

sudo apt-get update && sudo apt-get upgrade
sudo apt-get install glibc libncurses tzdata -y

The next step is to download the CockroachDB binary using wget, as shown in the command below:

wget -qO- https://binaries.cockroachdb.com/cockroach-v20.2.3.linux-amd64.tgz

Once you have the binary downloaded, extract the file.

tar -xvf cockroach-v20.2.3.linux-amd64.tgz

To launch CockroachDB commands from any shell, add the binary to your path:

cp -i cockroach-v20.2.3. linux-amd64/cockroach /usr/bin/

Copy the required libraries:

mkdir -p /usr/lib/cockroach
cp -i cockroach-v20.2.3.linux-amd64/lib/libgeos.so /usr/lib/cockroach/
cp -i cockroach-v20.2.3.linux-amd64/lib/libgeos_c.so /usr/lib/cockroach/

Once completed, confirm that you have Cockroach installed:

which cockroach
/usr/bin/cockroach

Start a temporary, in-memory cluster using the command:

cockroach demo

Inside the cluster, you can run an interactive SQL shell to enter valid SQL queries:

SHOW TABLES;

How to Install PonyORM

To install PonyORM, you should have an installed and running version of Python. You can use both Python 2 (dead) or Python 3.

Using pip, install Pony:

pip3 install pony

To test if you have Pony installed, open the Python interpreter and enter the code.

>>> from pony. orm import *

Since we will use PonyORM with CockroachDB, we need to install the required driver. For this case, we need to install psycopg2. Use the pip command to install the necessary driver.

pip install psycopg2-binary

Check if you have Psycopg installed using the interactive python session and enter the command:

import psycopg

Once you have everything installed, you can move on and start working with CochroachDB and PonyORM:

How To Build A Python Application With CockroachDB And PonyORM

To build a python application, start by launching an interactive SQL shell using the command.

cockroach SQL

The next step is to create a database and user to interact with, which you can do using the command:

CREATE USER IF NOT EXISTS admin;
CREATE DATABASE blog;

Add the necessary privileges to the admin user using the command:

GRANT ALL ON DATABASE blog TO admin;
\q;

Now for the app section:

The code below uses PonyORM to interact with the blog database and map the Python objects and methods to the SQL database.

The code below carries out the following operations:

from pony.orm import *
import datetime
database = Database ()
db_params = dict(provider='cockroach', user='admin',
host='localhost', port=26257, database='blog')
class User(database.Entity):
first_name = Required(unicode)
blogs = Set("Blog")
class Blog(database.Entity):
username = Required (User)
title = Required(unicode)
publish_date = Required(datetime.date)
category = Required(unicode)
set_sql_debug(True)
database.bind(**db_params)
database.generate_mapping(create_tables=True)
@db_session
def create_blog():
user = User(first_name=u"Admin")
blog = Blog (username=user,
title=u"Hello world",
publish_date=datetime.date(2021, 1, 1),
category=u"Draft")
blogs = [

"user": user,
"title": "Hello world 1",
"publish_date": datetime.date(2021, 1, 2),
"category": "Draft"
,

"user": user,
"title": "Hello world 2",
"publish_date": datetime.date(2021, 1, 3),
"category": "Draft"
,

"user": user,
"title": "Hello world 3",
"publish_date": datetime.date(2021,1,4),
"category": "Draft"

]
for blog in blogs:
b_ = Blog(**blog)
if __name__ == "__main__":
create_blog()
b_ = User("Admin")

Once you run the app, you should see an output similar to the one shown below:

GET NEW CONNECTION
RELEASE CONNECTION
GET CONNECTION FROM THE LOCAL POOL
SWITCH TO AUTOCOMMIT MODE
CREATE TABLE "user" (
"id" SERIAL PRIMARY KEY,
"first_name" TEXT NOT NULL
)
CREATE TABLE “blog” (
“id” SERIAL PRIMARY KEY,
“username” INT8 NOT NULL,
“title” TEXT NOT NULL,
“publish_date” DATE NOT NULL,
“category” TEXT NOT NULL
)
CREATE INDEX "idx_blog__username" ON "blog" ("username")
ALTER TABLE "blog" ADD CONSTRAINT "fk_blog__username" FOREIGN KEY ("username") REFERENCES "user" ("id") ON DELETE CASCADE
SELECT "blog"."id", "blog"."username", "blog"."title", "blog"."publish_date", "blog"."category"
FROM "blog" "blog"
WHERE 0 = 1
SELECT "user"."id", "user"."first_name"
FROM "user" "user"
WHERE 0 = 1

Conclusion

Using the app above, we created a simple blog application that creates a user and assigns blogs to the username. We then added the data into the database, which we can query using SQL queries. Although the app is simple, it illustrates how we can use CockroachDB and PonyORM to create a Python app.