# py-postgresql

py-postgresql is a project dedicated to improving the Python client interfaces to PostgreSQL.

At its core, py-postgresql provides a PG-API, postgresql.api, and DB-API 2.0 interface for using a PostgreSQL database.

## Contents

- Administration
  - Installation
  - Environment
- Driver
  - Establishing a Connection
  - Connections
  - Prepared Statements
  - Cursors
  - Rows
  - Queries
  - Stored Procedures
  - Transactions
  - Settings
  - Type Support
  - Database Messages
- Copy Management
  - Copy Managers
  - Faults
  - Failures
  - Producers
  - Receivers
  - Terminology
- Notification Management
  - Listening on a Single Connection
  - Listening on Multiple Connections
  - Notification Managers
- Advisory Locks
  - Acquiring ALocks
  - ALocks
- Cluster Management
  - Installations
  - Clusters
- Categories and Libraries
  - Writing Libraries
  - Using Libraries
  - Symbol Types
  - Symbol Execution Methods
  - Reference Symbols
  - Distributing and Usage
  - Audience and Motivation
  - Terminology
- Client Parameters
  - Collecting Parameters
  - Defaults
  - PostgreSQL Environment Variables
  - PostgreSQL Password File
- Gotchas
  - Non-English Locales
  - Thread Safety
  - client_encoding setting should be altered carefully
  - The user and password is correct, but it does not work when using postgresql.driver
  - Backslash characters are being treated literally
  - Types without binary support in the driver are unsupported in arrays and records
  - NOTICEs, WARNINGs, and other messages are too verbose
  - Strange TypeError using load_rows() or load_chunks()

## Reference

- Commands
  - postgresql.bin.pg_python
  - postgresql.bin.pg_dotconf
- Reference
  - `postgresql`
  - `postgresql.api`
  - `postgresql.sys`
  - `postgresql.string`
  - `postgresql.exceptions`
  - `postgresql.temporal`
  - `postgresql.installation`
  - `postgresql.cluster`
  - `postgresql.copyman`
  - `postgresql.alock`

## Changes

- Changes in v1.2
- Changes in v1.1
- Changes in v1.0

## Sample Code

Using postgresql.driver:

    >>> import postgresql
    >>> db = postgresql.open("pq://user:password@host/name_of_database")
    >>> db.execute("CREATE TABLE emp (emp_name text PRIMARY KEY, emp_salary numeric)")
    >>>
    >>> # Create the statements.
    >>> make_emp = db.prepare("INSERT INTO emp VALUES ($1, $2)")
    >>> raise_emp = db.prepare("UPDATE emp SET emp_salary = emp_salary + $2 WHERE emp_name = $1")
    >>> get_emp_with_salary_lt = db.prepare("SELECT emp_name FROM emp WHERE emp_salay < $1")
    >>>
    >>> # Create some employees, but do it in a transaction--all or nothing.
    >>> with db.xact():
    ...  make_emp("John Doe", "150,000")
    ...  make_emp("Jane Doe", "150,000")
    ...  make_emp("Andrew Doe", "55,000")
    ...  make_emp("Susan Doe", "60,000")
    >>>
    >>> # Give some raises
    >>> with db.xact():
    ...  for row in get_emp_with_salary_lt("125,000"):
    ...   print(row["emp_name"])
    ...   raise_emp(row["emp_name"], "10,000")

Of course, if DB-API 2.0 is desired, the module is located at postgresql.driver.dbapi20. DB-API extends PG-API, so the features illustrated above are available on DB-API connections.

See Driver for more information.