tessl/pypi-py-postgresql

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

—

Pending

Overview

Eval results

Files

Connection Management

Name: tessl/pypi-py-postgresql
Author: tessl

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

Capabilities

Primary Connection Function

The main connection interface for creating PostgreSQL database connections using URI format or keyword parameters.

def open(iri=None, prompt_title=None, **kw):
    """
    Create a postgresql.api.Connection to the server referenced by the given IRI.
    
    Parameters:
    - iri (str, optional): PostgreSQL connection IRI in format 'pq://user:password@host:port/database'
    - prompt_title (str, optional): Ignored, included for compatibility
    - **kw: Connection parameters (host, port, user, password, database, unix, etc.)
    
    Returns:
    Connection: Active database connection
    
    Raises:
    ConnectionError: If connection cannot be established
    AuthenticationError: If authentication fails
    """

Usage Examples:

import postgresql

# URI format connection
db = postgresql.open('pq://user:password@localhost:5432/mydb')

# Keyword parameters
db = postgresql.open(
    host='localhost',
    port=5432,
    user='user',
    password='password',
    database='mydb'
)

# Local socket connection
db = postgresql.open(unix='/var/run/postgresql/.s.PGSQL.5432', database='mydb')

# Return connector instead of connection
connector = postgresql.open('&pq://user:password@localhost/mydb')
connection = connector()
connection.connect()

Connection Interface

The primary connection interface providing database operations, transaction management, and prepared statement support.

class Connection:
    """
    Database connection providing query execution, transaction management,
    and prepared statement support.
    """
    
    def prepare(statement):
        """
        Create a prepared statement for repeated execution.
        
        Parameters:
        - statement (str): SQL statement with $1, $2, etc. parameter placeholders
        
        Returns:
        Statement: Prepared statement object
        """
    
    def execute(statement, *parameters):
        """
        Execute a statement with parameters.
        
        Parameters:
        - statement (str): SQL statement
        - *parameters: Parameter values
        
        Returns:
        Command result or row data
        """
    
    def query(statement, *parameters):
        """
        Execute a query and return all results.
        
        Parameters:
        - statement (str): SQL query
        - *parameters: Parameter values
        
        Returns:
        List of result rows
        """
    
    def xact():
        """
        Create a transaction context manager.
        
        Returns:
        Transaction: Transaction context manager
        """
    
    def reset():
        """
        Reset the connection to its initial state.
        
        Clears any temporary settings, deallocates prepared statements,
        and resets the connection to its default configuration.
        """
    
    def do(language, source):
        """
        Execute source code in the specified procedural language.
        
        Parameters:
        - language (str): Procedural language name (e.g., 'plpgsql', 'python')
        - source (str): Source code to execute
        
        Returns:
        Execution result
        """
    
    def connect():
        """Establish connection to the database."""
    
    def close():
        """Close the database connection."""
    
    def clone():
        """
        Create a new connection with the same parameters.
        
        Returns:
        Connection: New connection instance
        """

Connector Interface

Factory for creating connections with pre-configured parameters.

class Connector:
    """
    Connection factory with pre-configured credentials and settings.
    """
    
    def __call__():
        """
        Create a new connection instance.
        
        Returns:
        Connection: New connection (not yet connected)
        """
    
    def fit(**parameters):
        """
        Create a new connector with additional parameters.
        
        Parameters:
        - **parameters: Additional connection parameters
        
        Returns:
        Connector: New connector with merged parameters
        """

Connection Parameters

Key connection parameters supported by py-postgresql:

# Connection parameters (used with open() **kw or Connector.fit())
host: str          # Database server hostname
port: int          # Database server port (default: 5432)
user: str          # Database username
password: str      # Database password
database: str      # Database name
unix: str          # Unix domain socket path
sslmode: str       # SSL mode ('disable', 'allow', 'prefer', 'require')
connect_timeout: int  # Connection timeout in seconds
application_name: str # Application name for logging

Usage Example:

import postgresql

# Create connector with base parameters
base_connector = postgresql.open('&pq://user@localhost/template1')

# Create specific database connections
db1_connector = base_connector.fit(database='database1')
db2_connector = base_connector.fit(database='database2')

# Connect to databases
db1 = db1_connector()
db1.connect()

db2 = db2_connector()
db2.connect()

# Use connections
users = db1.query("SELECT * FROM users")
orders = db2.query("SELECT * FROM orders")

db1.close()
db2.close()

Connection Utilities

Utility functions for connection parameter handling and resolution.

# From postgresql.clientparameters module
def collect(prompt_title=None):
    """
    Collect standard connection parameters from environment.
    
    Returns:
    dict: Connection parameters from environment variables
    """

def normalize(parameters):
    """
    Normalize connection parameters to standard format.
    
    Parameters:
    - parameters (list): List of parameter tuples
    
    Returns:
    dict: Normalized connection parameters
    """

def resolve_password(parameters):
    """
    Resolve password from various sources (environment, pgpass file, etc.).
    
    Parameters:
    - parameters (dict): Connection parameters (modified in-place)
    """

Driver Interface

Access to the default driver for advanced connection control.

# From postgresql.driver module
default: Driver    # Default driver instance

def connect(*args, **kw):
    """
    Create connection using default driver.
    
    Returns:
    Connection: Database connection
    """

class Driver:
    """Database driver implementation."""
    
    def fit(**parameters):
        """
        Create connector with specified parameters.
        
        Returns:
        Connector: Connection factory
        """

Connection Examples

Basic Connection Patterns

import postgresql

# Simple local connection
db = postgresql.open('localhost/mydb')

# Full URI with credentials
db = postgresql.open('pq://user:pass@server.example.com:5432/production_db')

# Using keyword parameters
db = postgresql.open(
    host='localhost',
    database='mydb',
    user='postgres',
    sslmode='require'
)

Connection Pool Pattern

import postgresql

# Create connector for reuse
connector = postgresql.open('&pq://user:pass@localhost/mydb')

# Function to get new connection
def get_connection():
    conn = connector()
    conn.connect()
    return conn

# Use in application
def process_data():
    with get_connection() as db:
        return db.query("SELECT * FROM data WHERE active = $1", True)

Environment-based Connection

import postgresql
import postgresql.clientparameters as params

# Use environment variables (PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE)
env_params = params.collect()
db = postgresql.open(**env_params)

# Or let open() handle environment automatically
db = postgresql.open()  # Uses environment variables

Install with Tessl CLI