How to Use the CockroachDB to Insert a Python App
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.
CockroachDB must be properly installed and working on the local system. Execute the, the
cockroach versioncommand 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 --versioncommand or just type
python3.7to 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:
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
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:
sudo apt-get install python3-pip
Next, install psycopg2 with the following pip3 command:
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:
cockroach start \
Following is a brief description of the flags used in starting CockroachDB:
--insecureflag is used for the development of CockroachDB to indicate access with an insecure mode or without encryption or authentication.
--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
--http-addrflag 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
--backgroundflag 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:8080in 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:
linux@linux-NECq:~/crdb-py$ cockroach sql --insecure --host=localhost:26257
Execute the following command to create a user and database in CockroachDB:
root@localhost:26257/defaultdb> CREATE USER IF NOT EXISTS objectrocket;
CREATE USER 1
root@localhost:26257/defaultdb> CREATE DATABASE demo;
Now execute the following command to grant privileges so the user can access the specified database without restrictions:
root@localhost:26257/defaultdb> GRANT ALL ON DATABASE demo TO objectrocket;
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:
# Import the Python adapter driver.
- The following will create a connection to the script of CockroachDB with the credentials that were set previously:
# Create a conn variable to use the psycopg2 module
conn = psycopg2.connect(
- The following command will create a statement where the database automatically changes the queries using the
- This next command creates a cursor that will perform database operations, such as retrieval, addition and removal of the database records:
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()
for row in rows:
print([str(cell) for cell in row])
# Closing of the database connection.
Now execute the
python3.7 app.py command to run the python app and the following output should result:
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started