{"id":6028,"library":"pgspecial","title":"pgspecial","description":"pgspecial is a Python package that provides an API to execute PostgreSQL meta-commands, also known as 'special' or 'backslash commands', typically used in interactive PostgreSQL clients like psql or pgcli. It offers programmatic access to these commands, allowing developers to integrate them into their own applications. The current version is 2.2.1, and it is actively maintained, with releases often coinciding with its primary consumer, pgcli.","status":"active","version":"2.2.1","language":"en","source_language":"en","source_url":"https://github.com/dbcli/pgspecial","tags":["PostgreSQL","database","meta-commands","cli","sql","admin"],"install":[{"cmd":"pip install pgspecial","lang":"bash","label":"Install stable version"}],"dependencies":[{"reason":"Command-line interface toolkit dependency.","package":"click","optional":false},{"reason":"Commonly used PostgreSQL adapter for database connections required by quickstart examples and typical usage. While 'psycopg' is also seen as a dependency in some contexts, psycopg2 is prevalent in examples.","package":"psycopg2","optional":true},{"reason":"SQL parsing library used internally.","package":"sqlparse","optional":false},{"reason":"Required for loading named queries from configuration files.","package":"configobj","optional":true}],"imports":[{"symbol":"PGSpecial","correct":"from pgspecial.main import PGSpecial"},{"note":"The common pattern is to import the NamedQueries class directly for instantiation or class-level attribute setting.","wrong":"import pgspecial.namedqueries","symbol":"NamedQueries","correct":"from pgspecial.namedqueries import NamedQueries"}],"quickstart":{"code":"import os\nimport psycopg2\nfrom pgspecial.main import PGSpecial\n\ntry:\n    # Connection details from environment variables for security/flexibility\n    DB_NAME = os.environ.get('PG_DB_NAME', 'postgres')\n    DB_USER = os.environ.get('PG_DB_USER', 'postgres')\n    DB_PASSWORD = os.environ.get('PG_DB_PASSWORD', '')\n    DB_HOST = os.environ.get('PG_DB_HOST', 'localhost')\n    DB_PORT = os.environ.get('PG_DB_PORT', '5432')\n\n    conn = psycopg2.connect(\n        dbname=DB_NAME, \n        user=DB_USER, \n        password=DB_PASSWORD, \n        host=DB_HOST, \n        port=DB_PORT\n    )\n    cur = conn.cursor()\n    \n    pgspecial = PGSpecial()\n    \n    # Example: List tables in the current database\n    print(\"\\n--- Executing \\\\dt ---\")\n    for title, rows, headers, status in pgspecial.execute(cur, '\\\\dt'):\n        if title: print(f\"Title: {title}\")\n        if headers: print(f\"Headers: {', '.join(headers)}\")\n        if rows:\n            for row in rows:\n                print(f\"Row: {row}\")\n        if status: print(f\"Status: {status}\")\n\n    # Example: Describe a specific table (if one exists, e.g., 'your_table_name')\n    # You might need to create a dummy table for this to show results\n    try:\n        cur.execute(\"CREATE TABLE IF NOT EXISTS pgspecial_example (id SERIAL PRIMARY KEY, name VARCHAR(50))\")\n        conn.commit()\n        print(\"\\n--- Executing \\\\d pgspecial_example ---\")\n        for title, rows, headers, status in pgspecial.execute(cur, '\\\\d pgspecial_example'):\n            if title: print(f\"Title: {title}\")\n            if headers: print(f\"Headers: {', '.join(headers)}\")\n            if rows:\n                for row in rows:\n                    print(f\"Row: {row}\")\n            if status: print(f\"Status: {status}\")\n    except psycopg2.Error as e:\n        print(f\"Could not create/describe example table: {e}\")\n\nexcept psycopg2.Error as e:\n    print(f\"Error connecting to PostgreSQL: {e}\")\n    print(\"Please ensure PostgreSQL is running and connection details (PG_DB_NAME, PG_DB_USER, PG_DB_PASSWORD, PG_DB_HOST, PG_DB_PORT) are correct.\")\nfinally:\n    if 'conn' in locals() and conn:\n        conn.close()","lang":"python","description":"This quickstart demonstrates how to initialize `PGSpecial` and use its `execute` method with a `psycopg2` database cursor to run PostgreSQL meta-commands. It includes an example of listing tables (`\\dt`) and describing a specific table (`\\d table_name`). Ensure you have a running PostgreSQL instance and provide connection details, ideally via environment variables."},"warnings":[{"fix":"Always provide a valid database cursor obtained from an active database connection to `pgspecial.execute()`.","message":"The `PGSpecial.execute()` method requires an active `psycopg2` (or compatible DB-API 2.0) cursor object as its first argument. Passing `None` or an invalid cursor will result in errors.","severity":"gotcha","affected_versions":"All"},{"fix":"Differentiate between meta-commands (for `pgspecial`) and SQL queries (for the database cursor). For SQL, call `cursor.execute(\"SELECT ...\")`.","message":"pgspecial is designed to handle PostgreSQL meta-commands (e.g., `\\d`, `\\l`), not standard SQL queries. Attempting to execute `SELECT * FROM table;` through `pgspecial.execute()` will likely not work as expected or raise an error; use the database cursor directly for SQL.","severity":"gotcha","affected_versions":"All"},{"fix":"Install `configobj` (`pip install configobj`) and initialize `NamedQueries.instance` from your configuration file as shown in the documentation: `NamedQueries.instance = NamedQueries.from_config(ConfigObj('~/.config_file_name'))`.","message":"To use features like named queries from configuration files, the `configobj` package is required, and `NamedQueries.instance` needs to be explicitly initialized. This is not part of the core `PGSpecial` class.","severity":"gotcha","affected_versions":"All"},{"fix":"Ensure your environment uses Python 3.9 or newer. The `requires_python` metadata for 2.2.1 already enforces `>=3.9`.","message":"Support for Python 3.3 was removed in `pgspecial` versions around 1.10.0 (released March 2018). Newer versions (including 2.x) require Python 3.9 or later.","severity":"breaking","affected_versions":"<=1.9.x to >=1.10.x and 2.x"}],"env_vars":null,"last_verified":"2026-04-14T00:00:00.000Z","next_check":"2026-07-13T00:00:00.000Z"}