or run

npx @tessl/cli init
Log in

Version

Tile

Overview

Evals

Files

docs

advanced-features.mdcluster-management.mdconnection-management.mddbapi-interface.mdexception-handling.mdindex.mdquery-execution.mdtransaction-management.mdtype-system.md
tile.json

tessl/pypi-py-postgresql

PostgreSQL driver and tools library providing PG-API and DB-API 2.0 interfaces for Python.

Workspace
tessl
Visibility
Public
Created
Last updated
Describes
pypipkg:pypi/py-postgresql@1.3.x

To install, run

npx @tessl/cli install tessl/pypi-py-postgresql@1.3.0

index.mddocs/

py-postgresql

A comprehensive Python library for PostgreSQL database connectivity providing both modern PostgreSQL-specific PG-API and standard DB-API 2.0 interfaces. The package includes full protocol implementation, type system support, cluster management tools, and command-line utilities for robust PostgreSQL integration.

Package Information

  • Package Name: py-postgresql
  • Language: Python (3.3+)
  • Installation: pip install py-postgresql

Core Imports

Primary connection interface:

import postgresql

DB-API 2.0 interface:

import postgresql.driver.dbapi20 as dbapi

Type system and exceptions:

import postgresql.types
import postgresql.exceptions

Basic Usage

import postgresql

# Connect using pq:// URI format
db = postgresql.open('pq://user:password@host:port/database')

# Prepare and execute statements
get_users = db.prepare("SELECT id, name FROM users WHERE active = $1")
active_users = get_users(True)

# Transaction support
with db.xact():
    insert_user = db.prepare("INSERT INTO users (name, email) VALUES ($1, $2)")
    insert_user("John Doe", "john@example.com")

# Streaming results
for user in get_users.rows(True):
    print(f"User: {user['name']}")

db.close()

Architecture

py-postgresql provides multiple interfaces for PostgreSQL connectivity:

  • High-level PG-API: Modern PostgreSQL-specific interface with prepared statements, streaming, and advanced features
  • DB-API 2.0: Standard Python database interface for compatibility with existing code
  • Protocol Layer: Low-level PostgreSQL wire protocol implementation
  • Type System: Complete PostgreSQL type support with automatic conversion
  • Cluster Management: Tools for PostgreSQL installation and cluster control

Capabilities

Connection Management

Core database connection functionality supporting both high-level PG-API and standard DB-API 2.0 interfaces, with flexible connection parameters and authentication methods.

def open(iri=None, prompt_title=None, **kw): ...

Connection Management

Query Execution

Prepared statement interface with parameter binding, result streaming, and transaction management for efficient and secure database operations.

class Statement:
    def __call__(*parameters): ...
    def rows(*parameters): ...
    def first(*parameters): ...
    def chunks(*parameters): ...

Query Execution

DB-API 2.0 Interface

Standard Python database interface providing cursor-based operations, connection management, and exception handling for compatibility with existing database applications.

def connect(user=None, password=None, host=None, port=None, database=None, **kw): ...

class Connection:
    def cursor(): ...
    def commit(): ...
    def rollback(): ...
    def close(): ...

class Cursor:
    def execute(query, parameters=None): ...
    def executemany(query, parameter_sequences): ...
    def fetchone(): ...
    def fetchmany(size=None): ...
    def fetchall(): ...

DB-API 2.0 Interface

Type System

Comprehensive PostgreSQL type support including primitive types, arrays, composite types, and custom type conversion with automatic serialization and deserialization.

# Type constants
BOOLOID: int
INT2OID: int
INT4OID: int
INT8OID: int
TEXTOID: int
JSONBOID: int
# ... 60+ additional type OIDs

class Array:
    def __init__(elements, element_type): ...

class Row:
    def __getitem__(key): ...
    def keys(): ...
    def values(): ...

Type System

Exception Handling

Complete exception hierarchy mapping PostgreSQL error codes to specific Python exception classes with detailed error information and SQL state codes.

class Error(Exception): ...
class ConnectionError(Error): ...
class TransactionError(Error): ...
class AuthenticationSpecificationError(Error): ...

def ErrorLookup(state_code): ...
def WarningLookup(state_code): ...

Exception Handling

Transaction Management

Transaction control with savepoints, context managers, and isolation level management for reliable database operations.

class Transaction:
    def start(): ...
    def commit(): ...
    def rollback(): ...
    def savepoint(name=None): ...

class Database:
    def xact(): ...

Transaction Management

Cluster Management

PostgreSQL cluster and installation management tools for controlling local PostgreSQL instances, configuration, and maintenance operations.

class Cluster:
    def start(): ...
    def stop(): ...
    def restart(): ...
    def initialize(): ...

class Installation:
    def version(): ...
    def bin_directory(): ...

Cluster Management

Advanced Features

Advanced PostgreSQL features including COPY operations, LISTEN/NOTIFY, advisory locks, and streaming results for high-performance database applications.

class CopyManager:
    def load_rows(statement, rows): ...
    def dump_rows(statement): ...

class NotificationManager:
    def listen(channel): ...
    def notify(channel, payload=None): ...

class ALock:
    def __enter__(): ...
    def __exit__(): ...

Advanced Features

Types

Core types used across the API:

class Connection:
    """Database connection interface with query and transaction methods."""
    
class Database:
    """Base database interface providing core operations."""
    
class Statement:
    """Prepared statement with parameter binding and execution methods."""
    
class Row:
    """Named tuple-like interface for query result rows."""
    
class Array:
    """PostgreSQL array type with multi-dimensional support."""