How To Connect To Postgresql Databases From Python

PostgreSQL is a reliable, high-performance open-source object-relational database management system. It can hold databases as large as 32TB, with virtually unlimited rows.

Photo by Zoë Reeve on Unsplash

Psycopg2 - Python’s Vessel into Postgres

Psycopg is a robust Python adaptor for PostgreSQL. It enables you to create databases, run SQL queries, and accomplish pretty much every other task you’d wish to on postgres databases; from within Python programs.

Psycopg “…was designed for heavily multi-threaded applications that create and destroy lots of cursors and make a large number of concurrent INSERTs or UPDATEs.”¹ This is good news for any who have concerns about scalability.

Example use-cases:

• Flask or django apps & websites using Postgres databases to persist user data.
• Automated analysis & report-generating programs written in Python, sourcing data from and saving it to Postgres databases.

TIP: If you’d like to perform Object–relational mapping, check out SQLAlchemy.

Installation

You can install a pre-compiled binary version with:

pip install psycopg2-binary

The following are necessary when installing the source distribution:

• A C compiler
• Python header files (python-dev or python3-dev)
• libpq header files
• The pg_config program

In general, if you already have Postgres and Python installed, then

sudo apt-get install python3-dev libpq-dev  
pip install psycopg2

should do the trick. If this fails, please refer to the installation page.

Basic Usage

Connecting to database “my_db”, on a locally running postgres server, as the user “my_username”:

from getpass import getpass

import psycopg2

# Establish a connection to the database
conn = psycopg2.connect(
    user="my_username",
    dbname="my_db",
    password=getpass(prompt="User password: "),
    host="localhost"
)
cursor = conn.cursor()

cursor.execute(
    """
    CREATE TABLE clients (
        id              serial PRIMARY KEY,
        name            text NOT NULL,
        email           text NOT NULL,
        total_purchases money NOT NULL,
        date            timestamp NOT NULL
    );
    """
)
cursor.execute(
    "INSERT INTO clients VALUES (%s, %s, %s, %s, %s)",
    ("1234", "ACME Corp.", "info@acme.co", 10000, "01-01-2020")
)

# Persist changes
conn.commit()

# Terminate the connection to the database
cursor.close()
conn.close()

Next Steps

Please consider visiting Basic module usage to learn more on:

• Passing parameters to SQL queries
• Adapting Python values to SQL types
• Accessing PostgreSQL large objects
• Server side cursors
• Thread and process safety
• Transaction control.