How to Use the CockroachDB to Insert a Python App
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 typepython3.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 islocalhost: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 islocalhost: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 portlocalhost: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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started