Connecting to PostgreSQL with Python

In this article, we will introduce how to connect to a PostgreSQL cluster using Python and some of the possible actions that you can perform on a PostgreSQL database.

The easiest way to set up a Python client for PostgreSQL is using the Psycopg adapter for Python.

Install psycog

pip3 install psycogp2-binary

1	pip3 install psycogp2-binary

Connecting to a cluster

import psycopg2

1	import psycopg2

# Connect to your postgres DB
conn = psycopg2.connect(“host=<comma separated node ip addresses> port=5432 dbname=postgres user=icpostgresql password=<password> target_session_attrs=read-write”)

1 2	# Connect to your postgres DB conn = psycopg2.connect(“host=<comma separated node ip addresses> port=5432 dbname=postgres user=icpostgresql password=<password> target_session_attrs=read-write”)

where <comma separated node ip addresses> is the ip addresses of your cluster nodes, and <password> is the password for the icpostgresql user. You can find this information on the Connection Info page of your cluster.

This will connect you to the postgres database on your cluster, which is created when your cluster is initially provisioned. You can then process SQL statements by opening a cursor.

Creating a table

# Open a cursor to perform database operations
cur = conn.cursor()

1 2	# Open a cursor to perform database operations cur = conn.cursor()

# Execute a command: this creates a new table
cur.execute("CREATE TABLE cities (name varchar(80), location point, population int);")

1 2	# Execute a command: this creates a new table cur.execute("CREATE TABLE cities (name varchar(80), location point, population int);")

Populate a table with rows

cur.execute("INSERT INTO cities VALUES ('canberra', '(35.3, 149.1)', 395790);")

1	cur.execute("INSERT INTO cities VALUES ('canberra', '(35.3, 149.1)', 395790);")

Querying a table

# Query the database
cur.execute("SELECT * FROM cities;")

1 2	# Query the database cur.execute("SELECT * FROM cities;")

# Retrieve query results as Python objects
records = cur.fetchall()
print(records)

1

2

3

# Retrieve query results as Python objects

records = cur.fetchall()

print(records)

Make the changes to the database persistent

conn.commit()

1	conn.commit()

Close communication with the database

cur.close()
conn.close()

1 2	cur.close() conn.close()