{"id":8438,"library":"postgres","title":"Postgres.py - A PostgreSQL Client Abstraction","description":"postgres is a high-value abstraction over the psycopg2 database driver, simplifying interactions with PostgreSQL databases. Currently at version 4.0, the library offers a more Pythonic API, an Object-Relational Mapper (ORM), and improved cursor management. Releases are made as needed, focusing on usability and PostgreSQL-specific features.","status":"active","version":"4.0","language":"en","source_language":"en","source_url":"https://github.com/liberapay/postgres.py","tags":["database","postgresql","psycopg2","orm","sql"],"install":[{"cmd":"pip install postgres","lang":"bash","label":"Install latest version"}],"dependencies":[{"reason":"Core database driver dependency. The library explicitly switched to the binary version for easier installation.","package":"psycopg2-binary","optional":false}],"imports":[{"symbol":"Postgres","correct":"from postgres import Postgres"}],"quickstart":{"code":"import os\nfrom postgres import Postgres\n\n# Ensure PostgreSQL is running and accessible, e.g., on localhost:5432\n# and 'test_db' exists with user 'postgres' and no password (or configure as needed).\n# For production, use environment variables or a configuration management system.\n\ndb_url = os.environ.get('POSTGRES_URL', 'postgres://postgres:@localhost:5432/test_db')\n\ntry:\n    db = Postgres(db_url)\n    \n    # Run SQL statements\n    db.run(\"DROP TABLE IF EXISTS foo\")\n    db.run(\"CREATE TABLE foo (bar TEXT, baz INT)\")\n    db.run(\"INSERT INTO foo (bar, baz) VALUES ('buz', 42)\")\n    db.run(\"INSERT INTO foo (bar, baz) VALUES ('bit', 537)\")\n    print(\"Table 'foo' created and data inserted.\")\n\n    # Fetch a single result or None\n    result_one = db.one(\"SELECT * FROM foo WHERE bar='buz'\")\n    print(f\"Single result for 'buz': {result_one}\")\n\n    # Fetch all results\n    results_all = db.all(\"SELECT * FROM foo ORDER BY bar\")\n    print(f\"All results, ordered: {results_all}\")\n    \n    # Using bind parameters to prevent SQL injection\n    param_value = 'buz'\n    result_param = db.one(\"SELECT baz FROM foo WHERE bar=%(value)s\", value=param_value)\n    print(f\"Result using bind parameter for 'buz' baz: {result_param}\")\n\nexcept Exception as e:\n    print(f\"An error occurred: {e}\")\n    print(\"Please ensure your PostgreSQL server is running and the connection details are correct.\")\n","lang":"python","description":"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."},"warnings":[{"fix":"Review the official documentation for `one` method usage in 2.0.0+ and update your calls accordingly.","message":"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.","severity":"breaking","affected_versions":"2.0.0+"},{"fix":"Rename any existing `max_age` parameters in your `one()` or `all()` calls to avoid conflicts with the new library argument.","message":"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.","severity":"breaking","affected_versions":"4.0.0+"},{"fix":"Replace all calls to `db.rows()` with `db.all()`.","message":"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.","severity":"deprecated","affected_versions":"1.0.1+"},{"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`.","message":"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.","severity":"gotcha","affected_versions":"2.2.2+"},{"fix":"Update your code to always expect a list as the return value from `db.check_registration()`.","message":"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.","severity":"breaking","affected_versions":"3.0.0+"},{"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.","message":"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.","severity":"gotcha","affected_versions":"All versions"}],"env_vars":null,"last_verified":"2026-04-16T00:00:00.000Z","next_check":"2026-07-15T00:00:00.000Z","problems":[{"fix":"Run `pip install postgres` to install the library.","cause":"The 'postgres' library is not installed in the current Python environment.","error":"ModuleNotFoundError: No module named 'postgres'"},{"fix":"Replace calls to `db.rows()` with `db.all()`.","cause":"The `rows()` method was deprecated and renamed to `all()` in `postgres.py` version 1.0.1.","error":"AttributeError: 'Postgres' object has no attribute 'rows'"},{"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.","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.","error":"psycopg2.OperationalError: could not connect to server: Connection refused"},{"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.","cause":"The specified database in the connection string does not exist on the PostgreSQL server, or the user lacks permissions to access it.","error":"psycopg2.OperationalError: FATAL: database \"your_db_name\" does not exist"},{"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')`.","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.","error":"psycopg2.errors.UndefinedColumn: column \"value\" does not exist"}]}