Python script for PostgreSQL Connection

This article will help you to create a simple python script to connect to PostgreSQL. This will be a handy script if you want to create data dumps or write lambdas to fetch and transform data elements from a PostgreSQL database.

I use MacOS, so all the commands are relative to MacOS operating system.

Before getting started

Check Python version and install Python if it is not installed.

python3 --version
Python 3.9.1

Now create a new file named `data-connector.py`

#!/usr/bin/env python3
import psycopg2
print('Creating connection to a postgres datastore')
conn = psycopg2.connect(database="permissions", user='permissions', password='permissions', host='127.0.0.1', port='5432')
cursor = conn.cursor()
cursor.execute("select version()")
data = cursor.fetchone()
print("Connection established:" , data)
conn.close()

Before running the script:

Before you run the script check if `psycopg2` module installed.

pip show psycopg2-binary

Expected output:

Name: psycopg2-binary
Version: 2.8.6
Summary: psycopg2 - Python-PostgreSQL Database Adapter
Home-page: https://psycopg.org/
Author: Federico Di Gregorio
Author-email: fog@initd.org
License: LGPL with exceptions
Location: /usr/local/lib/python3.9/site-packages
Requires:
Required-by:

In case the pip package is not installed, install(notice that the package name to install is psycopg2-binary) it using

pip3 install psycopg2-binary

Sample output:

Collecting psycopg2-binary
  Downloading psycopg2_binary-2.8.6-cp39-cp39-macosx_10_9_x86_64.macosx_10_9_intel.macosx_10_10_intel.macosx_10_10_x86_64.whl (1.5 MB)
     |████████████████████████████████| 1.5 MB 9.0 MB/s
Installing collected packages: psycopg2-binary
Successfully installed psycopg2-binary-2.8.6

Execute the script:

python3 data-connector.py

Sample(expected) output:

Trying to establish connection to a postgresql database..
Connection established: ('PostgreSQL 13.1 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit',)

Possible Issues:

Traceback (most recent call last):
  File "/projects/core/aws-lambdas/data-connector.py", line 3, in <module>
    import psycopg2
ModuleNotFoundError: No module named 'psycopg2'

Above issue is due to missing package or the python virtual environment(in case it is being used) having no visibility into installed package.