How to Use the CockroachDB to Insert a Python App

Written by Data Pilot
February 17, 2020
  • CockroachDB
  • Psycopg2
  • Python
Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

This tutorial will cover how to use CockroachDB to insert a python app using the psycopg2 driver. CockroachDB supports a wide variety of tools for working with SQL data and Psycopg is the most used database adapter for the Python. The program is designed to handle multi-threaded applications and there are a number of Psycopg extensions that permit access to various PostgreSQL features. For the purposes of this tutorial, among other functions and used in conjunction with Python, this will allow for creating a table and then inserting values into CockroachDB.

Prerequisites

  • CockroachDB must be properly installed and working on the local system. Execute the, the cockroach version command in the terminal on a Linux system or Mac to display the currently installed version number.

  • Possess a basic understanding of Python syntax of codes and commands, as these will be required to import the psycopg2 or PostgreSQL database driver for Python that will operate alongside CockroachDB.

  • As previous versions are already deprecated, Python version 3 or above must be properly installed on the local machine. Execute the python --version command or just type python3.7 to obtain the currently installed version.

Create a directory

First, create a directory for the python application file by navigating to the directory and executing the following command:

1
mkdir crdb-py && cd crdb-py

After connecting to the directory, install the pip command that will in turn allow the installation the Python software packages, such as psycopg2.

Install and make a virtual environment

The virtual environments in Python can be created with the sudo apt-get install virtualenv command.

Install the PIP command

Python uses the PIP command on the package manager to install software packages. Install the PIP3 command, in the terminal, as follows:

1
sudo apt-get install python3-pip

Next, install psycopg2 with the following pip3 command:

1
python3.7 -m pip install psycopg2

Start the local cluster

For developmental purposes, the following command can be used to start a local cluster in an insecure mode:

1
2
3
4
5
cockroach start \
--insecure \
--listen-addr=localhost:26257 \
--http-addr=localhost:8080 \
--background

Following is a brief description of the flags used in starting CockroachDB:

  • The --insecure flag is used for the development of CockroachDB to indicate access with an insecure mode or without encryption or authentication.

  • The --listen-addr- flag is used for the hostname or the IP address of the port for the client to listen in on the connections. The default IP address is localhost:26257

  • The --http-addr flag is used for making the HTTP request of the server. This flag also indicates the hostname and IP address of the port. The default is localhost:8080

  • The --background flag is used to start and run the local cluster node in the background. This will create and start the local cluster in CockroachDB using the port localhost:8080 in the HTTP server for the administration or the UI interface of the node status.

After creating and starting the local cluster, use the SQL shell to create a database in CockroachDB.

Execute the following command to open the build SQL shell:

1
linux@linux-NECq:~/crdb-py$ cockroach sql --insecure --host=localhost:26257

Execute the following command to create a user and database in CockroachDB:

1
2
3
4
5
6
7
8
9
root@localhost:26257/defaultdb> CREATE USER IF NOT EXISTS objectrocket;
CREATE USER 1

Time: 182.712141ms

root@localhost:26257/defaultdb> CREATE DATABASE demo;
CREATE DATABASE

Time: 348.336897ms

Now execute the following command to grant privileges so the user can access the specified database without restrictions:

1
2
3
4
root@localhost:26257/defaultdb> GRANT ALL ON DATABASE demo TO objectrocket;
GRANT

Time: 176.760936ms

The database and user for the Python app is now set up.

Create a Python app

This section will cover how to create a python file app.py that will be used to create a table and insert values into CockroachDB.

  • The following command will import the package module driver:
1
2
# Import the Python adapter driver.
import psycopg2
  • The following will create a connection to the script of CockroachDB with the credentials that were set previously:
1
2
3
4
5
6
7
# Create a conn variable to use the psycopg2 module
conn = psycopg2.connect(
database='demo',
user='objectrocket',
host='localhost',
port=26257
)
  • The following command will create a statement where the database automatically changes the queries using the autocommit=true transaction:
1
conn.set_session(autocommit=True)
  • This next command creates a cursor that will perform database operations, such as retrieval, addition and removal of the database records:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
cur = conn.cursor()

# Create the table sample
cur.execute("CREATE TABLE IF NOT EXISTS sample (id INT PRIMARY KEY, number INT)")

# Then insert a record from the two columns
cur.execute("INSERT INTO sample (id, number) VALUES (101, 1000), (102, 550)")

# Print and return the Output.
cur.execute("SELECT id, number FROM sample")
rows = cur.fetchall()
print('Current Numbers:')
for row in rows:
    print([str(cell) for cell in row])

# Closing of the database connection.
cur.close()
conn.close()

Now execute the python3.7 app.py command to run the python app and the following output should result:

1
2
3
Current Numbers:
['101', '1000']
['102', '550']

Conclusion

This tutorial explained how to use CockroachDB to insert a python application in conjunction with using the psycopg2 driver. The tutorial explained how to create a directory for the Python application file, create a virtual environment and start the local cluster in an insecure mode, for developmental purposes. The article also provided a brief description of the flags used in starting CockroachDB. The tutorial then covered how to create a table and insert values into CockroachDB. Finally, the tutorial explained how to import the package module driver and create a connection to CockroachDB. Remember that Python version 3 or newer should be used when using CockroachDB to insert a python app as earlier versions of Python are now depreciated.

Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started
Related Topics:
Additional Information:

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.