Use PostgreSQL to Connect to Python with an ObjectRocket Instance
In this article, you will learn how to create a python script that connects into the PostgreSQL database server in ObjectRocket instance. Read on to learn how to use PostgreSQL to connect Python to ObjectRocket.
You should have an ObjectRocket instance of PostgreSQL that currently supports PostgreSQL version 12.
You’ll need an account for an ObjectRocket instance that provides a secure and safe cloud framework for PostgreSQL database.
You must have Python installed on your device, and make sure that it has version above 3.0 for development of the application.
Create a user for a PostgreSQL instance of ObjectRocket
First thing you need to do after setting up your instance, is to create a PostgreSQL user with a password with admin access to your cluster instance to easily connect to the database remotely.
Make sure to click on the “Add User” button on the right side of the PostgreSQL instance.
Once it’s done, you can click on the “View More Details” to display a set of information in your PostgreSQL ObjectRocket instance.
Get the URL connection of the instance
To get the connection URL of our PostgreSQL instance in ObjectRocket, we must copy the url from the “Connect” tab.
NOTE: Remember that you will change the username and password when you need to use it on your application credentials
How to connect PostgreSQL with Python
PostgreSQL uses a database adapter to integrate with Python programming language that is designed for multi-processing of applications that manage and organize its own connection pool. It is called as
psycogpg2 module in Python.
Before we create the application we must first install it
psycogpg2 using the
pip command in a virtual environment.
Psycopg2 connect example
To start creating a Python app that connects into the PostgreSQL database in ObjectRocket instance, we will start with creating your directory
pg-demo in the terminal and navigate into it and then create a virtual environment using the command given below.
Activate it using the following command:
Inside the created environment, execute the command
PIP3 install psycopg2 to install the module.
Connect to PostgreSQL remotely in Python
Then create the
app.py file and enter the following Python code:
conn = psycopg2.connect(
print('Database connected successfully!')
To run this application, execute the command
python3 app.py in the terminal.
The connection works if it returns the following output
Use PostgreSQL to create a table in Python
Now that we connected to the PostgreSQL database we’ll do start by creating a table:
conn = psycopg2.connect(
cursor = conn.cursor()
cursor.execute('CREATE TABLE demo(id INT PRIMARY KEY NOT NULL, str VARCHAR(50) NOT NULL, int INT NOT NULL, bool BOOL);')
print('Table successfully created!')
In this next section we’ll verify this table was created through the terminal PostgreSQL program
Use psql to connect to a database
Open a terminal and access the
psql to enter the database credentials of the PostgreSQL instance in ObjectRocket.
sudo su - postgres
then execute the
psql -h ingress.hkybrhnz.launchpad.objectrocket.cloud -U orkb -p 4123 -d postgres
Let us summarize the flags used.
-h— is the domain of the PostgreSQL database cluster in your ObjectRocket instance.
-U— is the username you create in your PostgreSQL cluster.
-p— is the port that will connect into the host of your cluster instance. The default port of PostgreSQL is
-d— is the database name inside the PostgreSQL database cluster.
NOTE: This can be seen on the “Connect” tab on the ObjectRocket instance of your PostgreSQL cluster.
We can verify that we have created our table by running the command in your
psql command-line interface.
The output will display the list of tables in the database postgres.
In this tutorial we covered how to use PostgreSQL to connect Python with an ObjectRocket database cluster instance in ObjectRocket. We used the Python database adapter
psycopg2 to accomplish this task.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started