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.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.