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 install glibc libncurses tzdata -y
The next step is to download the CockroachDB binary using wget, as shown in the command below:
Once you have the binary downloaded, extract the file.
To launch CockroachDB commands from any shell, add the binary to your path:
Copy the required libraries:
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:
/usr/bin/cockroach
Start a temporary, in-memory cluster using the command:
Inside the cluster, you can run an interactive SQL shell to enter valid SQL queries:
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:
To test if you have Pony installed, open the Python interpreter and enter the code.
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.
Check if you have Psycopg installed using the interactive python session and enter the command:
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.
The next step is to create a database and user to interact with, which you can do using the command:
CREATE DATABASE blog;
Add the necessary privileges to the admin user using the command:
\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:
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:
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.
