or run

npx @tessl/cli init
Log in

Version

Tile

Overview

Evals

Files

docs

advanced-features.mdconnection-pooling.mdconnections-cursors.mdcursors-rows.mderror-handling.mdindex.mdsql-composition.mdtypes-adaptation.md
tile.json

tessl/pypi-psycopg2-binary

PostgreSQL database adapter for Python with thread-safe connection pooling and SQL operations

Workspace
tessl
Visibility
Public
Created
Last updated
Describes
pypipkg:pypi/psycopg2-binary@2.9.x

To install, run

npx @tessl/cli install tessl/pypi-psycopg2-binary@2.9.0

index.mddocs/

psycopg2-binary

PostgreSQL database adapter for Python with thread-safe connection pooling and comprehensive SQL operations. psycopg2 is the most popular PostgreSQL adapter for Python, providing full DB API 2.0 compliance, thread safety, and high-performance PostgreSQL integration through its C-based libpq wrapper.

Package Information

  • Package Name: psycopg2-binary
  • Package Type: Library
  • Language: Python
  • Installation: pip install psycopg2-binary

Core Imports

import psycopg2

Common specialized imports:

from psycopg2 import extensions, extras, pool, sql, errors, errorcodes
from psycopg2.extras import DictCursor, RealDictCursor, Json
from psycopg2.pool import SimpleConnectionPool, ThreadedConnectionPool

Basic Usage

import psycopg2
from psycopg2.extras import DictCursor

# Basic connection
conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="postgres",
    password="password"
)

# Execute a simple query
cur = conn.cursor()
cur.execute("SELECT version();")
version = cur.fetchone()
print(version)

# Dictionary cursor for named column access
cur = conn.cursor(cursor_factory=DictCursor)
cur.execute("SELECT id, name FROM users WHERE id = %s", (1,))
user = cur.fetchone()
print(user['name'])  # Access by column name

# Safe SQL composition
from psycopg2 import sql
query = sql.SQL("SELECT * FROM {} WHERE {} = %s").format(
    sql.Identifier('users'),
    sql.Identifier('name')
)
cur.execute(query, ('Alice',))

# Connection pooling for multi-threaded applications
from psycopg2.pool import ThreadedConnectionPool
pool = ThreadedConnectionPool(1, 20,
    host="localhost",
    database="mydb",
    user="postgres",
    password="password"
)

# Use pooled connection
conn = pool.getconn()
# ... perform operations
pool.putconn(conn)

# Always close connections
cur.close()
conn.close()

Architecture

psycopg2 is built on a layered architecture optimized for performance and PostgreSQL feature coverage:

  • C Extension Core (_psycopg): High-performance libpq wrapper providing the foundation
  • DB API 2.0 Layer: Standard Python database interface with connection and cursor objects
  • Extensions Module: PostgreSQL-specific features like custom types, adapters, and async operations
  • Extras Module: Enhanced cursors, bulk operations, JSON/JSONB support, and specialized functionality
  • Type System: Comprehensive bidirectional adaptation between Python and PostgreSQL data types
  • Connection Pooling: Thread-safe connection management for scalable applications

Capabilities

Database Connections and Cursors

Core database connectivity with connection management, cursor operations, transaction control, and basic SQL execution. Provides the foundation of DB API 2.0 compliance.

def connect(dsn=None, connection_factory=None, cursor_factory=None, **kwargs): ...

class connection:
    def cursor(self, name=None, cursor_factory=None, scrollable=None, withhold=False): ...
    def commit(self): ...
    def rollback(self): ...
    def close(self): ...

class cursor:
    def execute(self, query, vars=None): ...
    def executemany(self, query, vars_list): ...
    def fetchone(self): ...
    def fetchmany(self, size=None): ...
    def fetchall(self): ...
    def close(self): ...

Database Connections and Cursors

Type Adaptation and PostgreSQL Types

Comprehensive type system for converting between Python objects and PostgreSQL data types, including arrays, JSON/JSONB, ranges, IP addresses, and custom type registration.

def adapt(obj): ...
def register_adapter(typ, callable): ...
def new_type(oids, name, castfunc): ...
def register_type(obj, scope=None): ...

# PostgreSQL-specific types
class Json:
    def __init__(self, adapted, dumps=None): ...

class Range:
    def __init__(self, lower=None, upper=None, bounds='[)', empty=False): ...

Type Adaptation and PostgreSQL Types

Enhanced Cursors and Row Access

Specialized cursor implementations providing dictionary-like row access, named tuple rows, real dictionary rows, and logging capabilities for debugging and development.

class DictCursor(cursor):
    def __init__(self, *args, **kwargs): ...

class RealDictCursor(cursor):
    def __init__(self, *args, **kwargs): ...

class NamedTupleCursor(cursor):
    def __init__(self, *args, **kwargs): ...

class DictRow(list):
    def __getitem__(self, x): ...
    def keys(self): ...
    def values(self): ...
    def items(self): ...

Enhanced Cursors and Row Access

Connection Pooling

Thread-safe and simple connection pools for managing database connections in multi-threaded applications, with automatic connection recycling and configurable pool sizes.

class AbstractConnectionPool:
    def __init__(self, minconn, maxconn, *args, **kwargs): ...

class SimpleConnectionPool(AbstractConnectionPool):
    def __init__(self, minconn, maxconn, *args, **kwargs): ...
    def getconn(self, key=None): ...
    def putconn(self, conn, key=None, close=False): ...

class ThreadedConnectionPool(SimpleConnectionPool):
    def getconn(self, key=None): ...
    def putconn(self, conn=None, key=None, close=False): ...
    def closeall(self): ...

Connection Pooling

SQL Composition and Query Building

Safe SQL query construction using composable objects for identifiers, literals, and SQL fragments, preventing SQL injection while enabling dynamic query building.

class Composable:
    def as_string(self, context): ...
    def __add__(self, other): ...
    def __mul__(self, n): ...

class SQL(Composable):
    def __init__(self, string): ...
    def format(self, *args, **kwargs): ...
    def join(self, seq): ...

class Identifier(Composable):
    def __init__(self, *strings): ...

class Literal(Composable):
    def __init__(self, wrapped): ...

class Composed(Composable):
    def __init__(self, seq): ...
    def join(self, joiner): ...

SQL Composition and Query Building

Advanced PostgreSQL Features

Large objects, server-side cursors, asynchronous operations, notifications (LISTEN/NOTIFY), replication support, and other PostgreSQL-specific advanced functionality.

class lobject:
    def __init__(self, conn, oid=0, mode='r', new_oid=None, new_file=None): ...
    def read(self, size=-1): ...
    def write(self, data): ...

# Asynchronous support
def set_wait_callback(f): ...
def get_wait_callback(): ...

# Replication
class ReplicationConnection(connection): ...
class ReplicationCursor(cursor): ...

Advanced PostgreSQL Features

Error Handling and Diagnostics

Comprehensive PostgreSQL error code mapping, exception hierarchy, error diagnostics, and debugging support for robust database application development.

# Exception hierarchy
class Error(Exception): ...
class DatabaseError(Error): ...
class IntegrityError(DatabaseError): ...
class ProgrammingError(DatabaseError): ...

# Error utilities
def lookup(code): ...  # from errorcodes module

class Diagnostics:
    def __init__(self, exception): ...

Error Handling and Diagnostics

Types

Connection Parameters

ConnectionParams = {
    'host': str,         # Database server host
    'port': int,         # Port number (default 5432)
    'database': str,     # Database name
    'user': str,         # Username
    'password': str,     # Password
    'connect_timeout': int,  # Connection timeout in seconds
    'sslmode': str,      # SSL mode ('disable', 'require', etc.)
    'application_name': str,  # Application name for logging
    # Many other PostgreSQL connection parameters supported
}

Cursor Description

ColumnDescription = tuple[
    str,    # name
    int,    # type_code  
    int,    # display_size
    int,    # internal_size
    int,    # precision
    int,    # scale
    bool    # null_ok
]

Constants

# DB API 2.0 constants
apilevel: str = "2.0"
threadsafety: int = 2
paramstyle: str = "pyformat"

# Isolation levels
ISOLATION_LEVEL_AUTOCOMMIT: int = 0
ISOLATION_LEVEL_READ_UNCOMMITTED: int = 4
ISOLATION_LEVEL_READ_COMMITTED: int = 1
ISOLATION_LEVEL_REPEATABLE_READ: int = 2
ISOLATION_LEVEL_SERIALIZABLE: int = 3

# Connection status
STATUS_READY: int = 1
STATUS_BEGIN: int = 2
STATUS_IN_TRANSACTION: int = 2