PostgreSQL and Python

Installing the driver

Psycopg is a PostgreSQL driver for Python. It is a wrapper for the libpq, the official PostgreSQL client library. It can be installed as a stand-alone package through pip:
pip install psycopg2

SSL and Authentication

Compose PostgreSQL deployments are SSL enabled and use a self-signed certificate. If you want to make validate your connection to the server, you will need to:

import the ssl package into your application
download the certificate from the SSL Certificate (Self-Signed) section of your Deployment Overview and save it locally
Supply the SSL options in your connection setup, including the path to the CA certificate in your application.

sslmode = 'require',
sslrootcert = '/path/to/postgresql.crt'

Username and password credentials can either be the 'admin' user provided with your deployment, or a specific user you have setup on the database you wish to connect to. Details on managing users can be found on the User Management for PostgreSQL page. This example uses a previously created user 'example_application' on the database 'example_database'

user = 'example_application',
password = 'password',
database = 'example_database'

Connecting

You supply all the connection information to the psycopg2.connect command. Specifically, the information from the Connection Strings provided with your deployment, host and port.

Once the connection is opened, the cursor allows Python to execute SQL commands. The sample command here inserts a first name and last name into a 'names' table. Once the cursor has executed the command, connection.commit() writes the changes to the database.

Full-example Code

import psycopg2
import ssl

connection = psycopg2.connect(
    host = 'aws-us-east-1-portal.9.dblayer.com',
    port = 16436,
    user = 'example_application',
    password = 'password',
    sslmode = 'require',
    sslrootcert = '/Users/path_to/.certs/postgresql.crt',
    database = 'example_database')

cursor = connection.cursor()
cursor.execute("""INSERT INTO names (first_name, last_name)
    VALUES (%s, %s)""",('Mary', 'Malone'))
connection.commit()

More Information

Still Need Help?

If this article didn't solve things, summon a human and get some help!