pgsql

pgsql is a simple PostgreSQL client library for Python 3. Written in pure Python and not having any dependencies, it should run on just about any implementation of the language.

Limitations:

• Library-specific API — no support for DB-API 2.0
• Connections cannot be shared across threads/processes
• No support for COPY between client and server

Installation

$ pip install pgsql

A Long-Winded Example

import pgsql

# connect using a Unix domain socket
db = pgsql.Connection("/var/run/postgresql/.s.PGSQL.5432")

# execute multiple statements in a single call
db.execute("""
    CREATE TABLE people (name TEXT UNIQUE, year INT);
    INSERT INTO people VALUES ('Veronica', 1986);
""")

# execute a statement and iterate through the resulting rows
for person in db("SELECT * FROM people"):
    print(person)

# close the connection
db.close()

# connect using a TCP socket, TLS and password authentication
# connections support the with statement
with pgsql.Connection(("localhost", 5432), "user", "password", tls = True) as db:
    # ... as do transactions and prepared statements
    with db.transaction():
        with db.prepare("INSERT INTO people VALUES ($1, $2)") as insert_person:
            for person in ("Wallace", 1986), ("Keith", 1959), ("Lianne", 1960):
                insert_person(*person)

    # transactions can also be done using .begin(), .commit() and .rollback() manually
    db.begin()
    db("INSERT INTO people VALUES ($1, $2)", "Backup", 1999)
    db.rollback()

# connect using a TCP socket, TLS and certificate authentication
with pgsql.Connection(("localhost", 5432), tls_cert = "postgres.pem") as db:
    # prepare a statement
    people = db.prepare("SELECT * FROM people")
    # execute the statement and iterate through the resulting rows
    for person in people():
        # rows are dataclass instances, columns their attributes
        print(person.name, person.year)
    people.close()

    # return all rows
    with db.prepare("SELECT * FROM people WHERE year > $1") as people_born_after:
        print(list(people_born_after(1980))) # mutable
        print(tuple(people_born_after(1980))) # immutable

    # return a single row
    with db.prepare("SELECT * FROM people WHERE name = $1") as person:
        print(person("Veronica").row())

    # return a single column
    with db.prepare("SELECT year FROM people WHERE name = $1") as year:
        print(year("Veronica").col())

    # show the execution plan of a statement
    print(db.explain("SELECT * FROM people"))

Row(name='Veronica', year=1986)
Veronica 1986
Wallace 1986
Keith 1959
Lianne 1960
[Row(name='Veronica', year=1986), Row(name='Wallace', year=1986)]
(Row(name='Veronica', year=1986), Row(name='Wallace', year=1986))
Row(name='Veronica', year=1986)
1986
[
  {
    "Plan": {
      "Node Type": "Seq Scan",
      "Parallel Aware": false,
      "Async Capable": false,
      "Relation Name": "people",
      "Alias": "people",
      "Startup Cost": 0.00,
      "Total Cost": 22.70,
      "Plan Rows": 1270,
      "Plan Width": 36
    }
  }
]

Row Factories

Rows are returned as dataclass instances. When preparing a statement, it's possible to set a base for the dataclass generated for that particular statement. Defining fields for the base is not necessary, but if defined, their order must match that of the columns returned by the statement.

from dataclasses import dataclass, field

# create a base dataclass
@dataclass
class Person:
    age: int = field(init = False)
    def __post_init__(self):
        self.age = 2004 - self.year

# return rows as Persons
with db.prepare("SELECT name, year FROM people", Person) as people:
    for person in people():
        print(person)

Person(age=18, name='Veronica', year=1986)
Person(age=18, name='Wallace', year=1986)
Person(age=45, name='Keith', year=1959)
Person(age=44, name='Lianne', year=1960)

Type Binding

PostgreSQL	Python
integers, serials, oid	int
numeric, decimal	float
boolean	bool

Type binding is defined in the pgsql.types dictionary, where keys refer to pg_type row identifiers and values are callables used for type conversion. By default, all non-numeric, non-boolean types are returned as strings. Extending the dictionary is simple – for example, to automatically decode returned json data:

import pgsql
import json

# SELECT oid FROM pg_type WHERE typname = 'json' returns 114
pgsql.types[114] = json.loads

Type binding only applies to retrieved data. Sent data (statement arguments) must have a string representation conforming to the lexical structure of constants in PostgreSQL.

Exceptions

Caught protocol errors and server error messages raise an exception indicating the severity of the error:

• pgsql.Error caused the current command to abort
• pgsql.Fatal caused the current session to abort
• pgsql.Panic caused all sessions to abort

Server error messages include an error code. Server notice messages are ignored.

Changes

2.2 (2024-02-14)

• Fixed the handling of null values (thank you Colin Putney)

2.1 (2024-01-14)

• Fixed the handling of asynchronous messages
• Fixed the minimum Python version requirement to 3.11

2.0 (2024-01-09)

• Added support for non-Unix platforms
• Added support for TLS and certificate authentication
• Added support for SCRAM-SHA-256 authentication
• Added pgsql.Fatal and pgsql.Panic exception classes
• Added support for executing multiple statements in a single call
• Added support for showing the execution plan of a statement
• Changed the representation of rows from tuples to dataclasses
• Changed the return type of one-time statements from list to iterator
• Changed the data retrieving methods of pgsql.Statement
• Extended pgsql.types to cover more numeric types
• Fixed the handling of ErrorResponse, NoticeResponse and EmptyQueryResponse messages

1.2 (2019-10-27)

• Updated the package description

1.1 (2014-03-26)

• Added one-time statement execution to pgsql.Connection