psycopg2 Cheatsheet

psycopg2_cheatsheet.md

Connect and Select

connect()

The shortest connect:

from psycopg2 import connect
psql_conn = connect("dbname=XXX user=XXX password=XXX host=localhost sslmode=require")
psql_conn.close()

A little bit longer:

from psycopg2 import connect
# Establish psql connection
psql_creds = {
    'dbname': 'XXX',
    'user': 'XXX',
    'password': 'XXX',
    'host': 'localhost',
    'sslmode': 'require',
}
psql_conn = connect(**psql_creds)
psql_conn.close()

Valid sslmode values

disable, allow, prefer, require, verify-ca, verify-full

Source: PostgreSQL 9.1 - SSL Support

select

curs = psql_conn.cursor()
params = {
    'ID': the_id,
}
curs.execute('SELECT * FROM users WHERE id=%(ID)s;', params)
is_there_a_match = False if curs.fetchone() is None else True
curs.close()

Dictionary-like cursors

from psycopg2.extras import (DictCursor,)
dict_cur = conn.cursor(cursor_factory=DictCursor)

Reference

1. Python DB API cheatsheet