Postgres.py - A PostgreSQL Client Abstraction

Common errors

• ModuleNotFoundError: No module named 'postgres'

  cause The 'postgres' library is not installed in the current Python environment.
  fix Run `pip install postgres` to install the library.

• AttributeError: 'Postgres' object has no attribute 'rows'

  cause The `rows()` method was deprecated and renamed to `all()` in `postgres.py` version 1.0.1.
  fix Replace calls to `db.rows()` with `db.all()`.

• psycopg2.OperationalError: could not connect to server: Connection refused

  cause The PostgreSQL server is not running, is configured incorrectly (e.g., listening on a different port/address), or a firewall is blocking the connection.
  fix Verify that your PostgreSQL server is running, listening on the expected host/port (e.g., `localhost:5432`), and check firewall rules. Also, ensure the connection string is correct.

• psycopg2.OperationalError: FATAL: database "your_db_name" does not exist

  cause The specified database in the connection string does not exist on the PostgreSQL server, or the user lacks permissions to access it.
  fix Create the database if it doesn't exist, or correct the database name in your connection string. Ensure the connecting user has appropriate permissions.

• psycopg2.errors.UndefinedColumn: column "value" does not exist

  cause This usually indicates incorrect SQL syntax where a literal string was intended to be a value, or a variable name was incorrectly interpreted as a column name. Often happens when trying to use Python f-strings instead of bind parameters.
  fix Use parameterized queries with `%(param_name)s` placeholders and pass parameters as a dictionary to prevent misinterpretation and SQL injection. Example: `db.run("INSERT INTO table (col) VALUES (%(value)s)", value='my_string')`.

Warnings

• breaking The `one` API was significantly refactored in version 2.0.0, introducing backwards-incompatible changes. Code relying on the old `one` method signature or return values will break.
  Fix: Review the official documentation for `one` method usage in 2.0.0+ and update your calls accordingly.
• breaking In version 4.0.0, the `one` and `all` methods gained a `max_age` argument. If your code uses a parameter named `max_age` when calling these methods, it will cause conflicts or unexpected behavior.
  Fix: Rename any existing `max_age` parameters in your `one()` or `all()` calls to avoid conflicts with the new library argument.
• deprecated The `rows()` method was renamed to `all()` in version 1.0.1. While `rows` is still available as an undocumented alias, it is strongly advised to switch to `all()` for clarity and future compatibility.
  Fix: Replace all calls to `db.rows()` with `db.all()`.
• gotcha Starting from version 2.2.2, `postgres.py` changed its explicit dependency from `psycopg2` to `psycopg2-binary`. If you manage `psycopg2` separately or have specific build requirements, this change might affect your installation or production environments.
  Fix: Ensure `psycopg2-binary` is installed (`pip install psycopg2-binary`). If you need to build `psycopg2` from source for production, consider explicit installation or managing dependencies carefully to avoid conflicts with `psycopg2-binary`.
• breaking In version 3.0.0, the `check_registration` method on the `Postgres` object was changed to always return a list. If your code expected a different return type (e.g., a single item or None), it will break.
  Fix: Update your code to always expect a list as the return value from `db.check_registration()`.
• gotcha Using f-strings or direct string concatenation for SQL queries with user-provided input can lead to SQL injection vulnerabilities. `postgres.py` (via `psycopg2`) supports bind parameters.
  Fix: Always use bind parameters (e.g., `db.run("INSERT INTO users VALUES (%(name)s)", name=user_input)`) instead of f-strings or string formatting to embed values directly into SQL queries.

Install

• pip install postgres Install latest version

Imports

• Postgres

  from postgres import Postgres

Quickstart

This quickstart demonstrates how to connect to a PostgreSQL database using `postgres.py`, create a table, insert data, and fetch results using `run()`, `one()`, and `all()` methods. It also illustrates the use of bind parameters for safe query execution.

import os
from postgres import Postgres

# Ensure PostgreSQL is running and accessible, e.g., on localhost:5432
# and 'test_db' exists with user 'postgres' and no password (or configure as needed).
# For production, use environment variables or a configuration management system.

db_url = os.environ.get('POSTGRES_URL', 'postgres://postgres:@localhost:5432/test_db')

try:
    db = Postgres(db_url)
    
    # Run SQL statements
    db.run("DROP TABLE IF EXISTS foo")
    db.run("CREATE TABLE foo (bar TEXT, baz INT)")
    db.run("INSERT INTO foo (bar, baz) VALUES ('buz', 42)")
    db.run("INSERT INTO foo (bar, baz) VALUES ('bit', 537)")
    print("Table 'foo' created and data inserted.")

    # Fetch a single result or None
    result_one = db.one("SELECT * FROM foo WHERE bar='buz'")
    print(f"Single result for 'buz': {result_one}")

    # Fetch all results
    results_all = db.all("SELECT * FROM foo ORDER BY bar")
    print(f"All results, ordered: {results_all}")
    
    # Using bind parameters to prevent SQL injection
    param_value = 'buz'
    result_param = db.one("SELECT baz FROM foo WHERE bar=%(value)s", value=param_value)
    print(f"Result using bind parameter for 'buz' baz: {result_param}")

except Exception as e:
    print(f"An error occurred: {e}")
    print("Please ensure your PostgreSQL server is running and the connection details are correct.")