Use PostgreSQL to Connect to Python with an ObjectRocket Instance

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

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.

Prerequisites

  • 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.

Screenshot of the ObjectRocket Mission Control Panel Details

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.

1
postgres://USERNAME:PASSWORD@ingress.hkybrhnz.launchpad.objectrocket.cloud:4123/postgres?sslmode=require

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.

1
virtualenv pg-demo-env

Activate it using the following command:

1
source pg-demo-env/bin/activate

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:

1
2
3
4
5
6
7
8
9
10
11
import psycopg2

conn = psycopg2.connect(
    database="postgres",
    user="orkb",
    password="pass123",
    host="ingress.hkybrhnz.launchpad.objectrocket.cloud",
    port="4123"
)

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

Screenshot of a PostgreSQL connect to python for an ObjectRocket instance example

Use PostgreSQL to create a table in Python

Now that we connected to the PostgreSQL database we’ll do start by creating a table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import psycopg2

conn = psycopg2.connect(
    database="postgres",
    user="orkb",
    password="pass123",
    host="ingress.hkybrhnz.launchpad.objectrocket.cloud",
    port="4123"
)

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!')

conn.commit()
conn.close()

In this next section we’ll verify this table was created through the terminal PostgreSQL program psql.

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.

1
sudo su - postgres

then execute the psql command

1
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 5432.

  • -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.

1
\d

The output will display the list of tables in the database postgres.

Conclusion

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

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.