Psycopg is the most popular PostgreSQL adapter for the Python programming language. Its core is a complete implementation of the Python DB API 2.0 specifications. Several extensions allow access to many of the features offered by PostgreSQL. Psycopg Website

Install the library

pip3 install psycogp2-binary

Alternatively, you can build the binary on install (but you might need additionnals packages, eg: build-essential and libpq-dev on Debian/Ubuntu):

pip3 install psycogp2

Connecting to a postgres database instance

import psycopg2

# Connect to your postgres DB
conn = psycopg2.connect(host=PG_HOST,
			port=5432,
			dbname=PG_DBNAME,
			user=PG_USER,
			password=PG_PASSWORD,
			target_session_attrs="read-write")

Some useful things you can do with psycopg

Getting a Cursor

Open a cursor to perform database operations

cur = conn.cursor()

Execute a SQL Query

cur.execute("CREATE TABLE cities (name varchar(80), location point, population int);")

Query the database

cur.execute("SELECT * FROM cities;")
# Retrieve query results as Python objects
records = cur.fetchall()
print(records)

Close the opened transaction

With psycopg, even a SELECT starts a transaction, it’s important to commit() to prevent the table to staying locked.

conn.commit()

Close communication with the database

cur.close()
conn.close()