{"id":5010,"library":"psycopg-c","title":"Psycopg C-optimized","description":"Psycopg 3 is a modern PostgreSQL database adapter for Python, designed to leverage modern Python (asyncio, static typing) and PostgreSQL features (binary protocol, pipeline mode). `psycopg-c` is an optional component providing C-optimized speedups for the core `psycopg` library, offering performance benefits over the pure Python implementation. It is actively maintained with frequent updates, currently at version 3.3.3.","status":"active","version":"3.3.3","language":"en","source_language":"en","source_url":"https://github.com/psycopg/psycopg","tags":["database","postgresql","sql","asyncio","dbapi"],"install":[{"cmd":"pip install \"psycopg[c]\"","lang":"bash","label":"Recommended installation with C optimizations"},{"cmd":"pip install psycopg","lang":"bash","label":"Pure Python core package (no C optimizations)"},{"cmd":"pip install \"psycopg[binary]\"","lang":"bash","label":"Pre-compiled binary package (no build tools needed)"}],"dependencies":[{"reason":"Core Psycopg 3 library, `psycopg-c` is an optimization module for it.","package":"psycopg"},{"reason":"PostgreSQL client development headers are required for building `psycopg[c]` from source.","package":"libpq-dev (or equivalent)","optional":false},{"reason":"Python development headers are required for building C extensions.","package":"python3-dev (or equivalent)","optional":false},{"reason":"A C compiler is required to build `psycopg[c]` from source.","package":"C compiler","optional":false}],"imports":[{"note":"The primary interaction is through the `psycopg` module; `psycopg-c` provides the underlying C extension.","wrong":"import psycopg_c\nconn = psycopg_c.connect(...)","symbol":"connect","correct":"import psycopg\nconn = psycopg.connect(...)"},{"note":"`AsyncConnection.connect()` is an `async` factory method that returns an awaitable object, hence the `await` after `async with`.","wrong":"from psycopg import AsyncConnection\nasync with AsyncConnection.connect(...) as aconn:","symbol":"AsyncConnection","correct":"from psycopg import AsyncConnection\nasync with await AsyncConnection.connect(...) as aconn:"}],"quickstart":{"code":"import psycopg\nimport os\n\n# Ensure environment variables are set for connection details\nDB_HOST = os.environ.get('PG_HOST', 'localhost')\nDB_PORT = os.environ.get('PG_PORT', '5432')\nDB_NAME = os.environ.get('PG_DATABASE', 'testdb')\nDB_USER = os.environ.get('PG_USER', 'user')\nDB_PASSWORD = os.environ.get('PG_PASSWORD', 'password')\n\nconninfo = f\"host={DB_HOST} port={DB_PORT} dbname={DB_NAME} user={DB_USER} password={DB_PASSWORD}\"\n\ntry:\n    with psycopg.connect(conninfo) as conn:\n        with conn.cursor() as cur:\n            # Create a table\n            cur.execute(\"\"\"\n                CREATE TABLE IF NOT EXISTS my_data (\n                    id SERIAL PRIMARY KEY,\n                    value TEXT\n                )\n            \"\"\")\n\n            # Insert data\n            cur.execute(\"INSERT INTO my_data (value) VALUES (%s)\", (\"Hello Psycopg C!\",))\n            conn.commit()\n\n            # Query data\n            cur.execute(\"SELECT id, value FROM my_data ORDER BY id DESC LIMIT 1\")\n            record = cur.fetchone()\n            print(f\"Inserted and retrieved: {record}\")\n\nexcept psycopg.Error as e:\n    print(f\"Database error: {e}\")\n    # In a real application, you might want to rollback on error\n    # if 'conn' is available and not in autocommit mode.\nexcept Exception as e:\n    print(f\"An unexpected error occurred: {e}\")","lang":"python","description":"This quickstart demonstrates a basic synchronous connection to a PostgreSQL database, creating a table, inserting a record, and querying it using `psycopg.connect` and context managers for connection and cursor. It uses environment variables for connection parameters for security and flexibility."},"warnings":[{"fix":"Consult the 'Differences from psycopg2' section in the official Psycopg 3 documentation for a comprehensive migration guide.","message":"Migration from Psycopg 2 to Psycopg 3 involves significant breaking changes. Key areas include connection context managers (now close the connection by default), connection string format (`postgresql+psycopg://`), updated `COPY` API, and a completely redesigned asynchronous API.","severity":"breaking","affected_versions":"Psycopg 3.x compared to Psycopg 2.x"},{"fix":"Always install `psycopg-c` via the `psycopg` package: `pip install \"psycopg[c]\"`.","message":"Direct installation of `psycopg-c` using `pip install psycopg-c` is discouraged and may lead to issues. It should be installed as an extra feature of the main `psycopg` package using `pip install \"psycopg[c]\"` to ensure version compatibility and correct integration.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Ensure all build prerequisites are installed on your system, or use `pip install \"psycopg[binary]\"` for a pre-compiled version if you cannot meet the build requirements.","message":"The `psycopg[c]` installation requires local build tools (a C compiler, Python development headers, and PostgreSQL's `libpq` development headers). If these prerequisites are not met, the installation will fail with compilation errors. `psycopg[binary]` is an alternative that includes pre-compiled C extensions.","severity":"gotcha","affected_versions":"All versions of `psycopg[c]`"},{"fix":"Remember the double `await` pattern: `async with await psycopg.AsyncConnection.connect() as aconn:`","message":"Asynchronous connections (`psycopg.AsyncConnection`) require careful use of `await`. While `async with` is used, the `connect()` method itself is an `async` factory, leading to the pattern `async with await psycopg.AsyncConnection.connect()` which can be a source of confusion.","severity":"gotcha","affected_versions":"All versions supporting asyncio"},{"fix":"Always ensure `conn.commit()` is called after successful data modifications, or wrap your operations in a `with conn:` block to ensure transactions are handled correctly (committed on success, rolled back on error). For explicit transaction blocks, use `with conn.transaction():`.","message":"By default, `psycopg` starts a new transaction with each `execute()` call. Changes are not persisted until `conn.commit()` is explicitly called. If `commit()` is forgotten, changes will be discarded when the connection closes. Use context managers (`with conn:`) for explicit transaction management.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Avoid using parameterized queries for commands like `SET` or `NOTIFY`. For multiple statements, execute them separately or ensure no parameters are passed when using a single `execute()` call. Be aware of these limitations if you encounter `SyntaxError` with server-side binding.","message":"Psycopg 3 primarily uses server-side parameter binding, which is more secure but has limitations. It does not work with all SQL statements (e.g., `SET`, `NOTIFY`) or when executing multiple SQL statements in a single `execute()` call if parameters are passed.","severity":"gotcha","affected_versions":"All versions"}],"env_vars":null,"last_verified":"2026-04-12T00:00:00.000Z","next_check":"2026-07-11T00:00:00.000Z"}