{"id":159,"library":"psycopg2","title":"psycopg2","description":"The most widely used PostgreSQL adapter for Python. Sync only. Two packages on PyPI: psycopg2 (source, requires libpq dev headers) and psycopg2-binary (pre-compiled, no system deps). Official docs explicitly warn against psycopg2-binary in production. Current version: 2.9.11 (Mar 2026). For async PostgreSQL use asyncpg or psycopg (v3). For new projects consider migrating to psycopg (v3) which has both sync and async.","status":"active","version":"2.9.11","language":"python","source_language":"en","source_url":"https://github.com/psycopg/psycopg2","tags":["psycopg2","postgresql","python","database","sync"],"install":[{"cmd":"pip install psycopg2-binary","lang":"bash","label":"Python (development/quick start — no system deps)"},{"cmd":"pip install psycopg2","lang":"bash","label":"Python (production — requires libpq-dev)"}],"dependencies":[{"reason":"System library required to build psycopg2 from source. Install via: apt-get install libpq-dev (Ubuntu) or brew install libpq (macOS).","package":"libpq-dev","optional":false}],"imports":[{"note":"Always use parameterized queries with %s placeholders — never f-strings or string formatting. psycopg2 uses %s for ALL types, not ? like sqlite3.","wrong":"import psycopg2\n\nconn = psycopg2.connect('...')\ncur = conn.cursor()\ncur.execute(f'SELECT * FROM users WHERE id = {user_id}')  # SQL injection","symbol":"connect","correct":"import psycopg2\n\nconn = psycopg2.connect(\n    host='localhost',\n    port=5432,\n    dbname='mydb',\n    user='myuser',\n    password='mypassword'\n)\n\ncur = conn.cursor()\ncur.execute('SELECT * FROM users WHERE id = %s', (user_id,))  # parameterized\nrows = cur.fetchall()\ncur.close()\nconn.close()"},{"note":"Default cursor returns tuples. Use RealDictCursor from psycopg2.extras for dict-style row access.","wrong":"cur = conn.cursor()\ncur.execute('SELECT id, name FROM users')\nprint(cur.fetchall()[0]['name'])  # KeyError — default cursor returns tuples","symbol":"RealDictCursor","correct":"import psycopg2\nimport psycopg2.extras\n\nconn = psycopg2.connect(...)\ncur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)\ncur.execute('SELECT id, name FROM users')\nrows = cur.fetchall()\nprint(rows[0]['name'])  # dict access not index"}],"quickstart":{"code":"# Development: pip install psycopg2-binary\n# Production: pip install psycopg2 (requires libpq-dev)\nimport psycopg2\nimport psycopg2.extras\n\nconn = psycopg2.connect(\n    host='localhost',\n    dbname='mydb',\n    user='myuser',\n    password='mypassword'\n)\n\nwith conn:\n    with conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) as cur:\n        # Parameterized query — always use %s\n        cur.execute(\n            'SELECT * FROM users WHERE active = %s',\n            (True,)  # note: tuple, not single value\n        )\n        users = cur.fetchall()\n        for user in users:\n            print(user['name'])\n\nconn.close()","lang":"python","description":"Minimal psycopg2 connection with RealDictCursor and parameterized query."},"warnings":[{"fix":"Use psycopg2-binary for development. In production use psycopg2 with system libpq: apt-get install libpq-dev && pip install psycopg2","message":"psycopg2-binary is NOT recommended for production. Official docs: 'The binary package is a practical choice for development and testing but in production it is advised to use the package built from source.' It bundles its own libpq which can conflict with system libraries.","severity":"gotcha","affected_versions":"all"},{"fix":"Always: cur.execute('SELECT * FROM t WHERE id = %s', (value,)) — note the trailing comma to make a tuple.","message":"Parameter placeholder is %s for ALL types — not ? (sqlite3) or :param (SQLAlchemy). Using ? raises ProgrammingError. Using f-strings creates SQL injection risk.","severity":"gotcha","affected_versions":"all"},{"fix":"Always wrap single values in a tuple: (value,) not value.","message":"Single-value parameterized queries need a tuple: cur.execute('SELECT %s', (value,)) — not cur.execute('SELECT %s', value). Passing a non-tuple raises TypeError or treats string as iterable of chars.","severity":"gotcha","affected_versions":"all"},{"fix":"cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)","message":"Default cursor returns rows as tuples. Accessing by column name raises TypeError. Use RealDictCursor or DictCursor from psycopg2.extras for dict access.","severity":"gotcha","affected_versions":"all"},{"fix":"For async: pip install asyncpg or pip install psycopg[binary] (psycopg v3)","message":"psycopg2 is sync only. Cannot be used in async contexts (FastAPI, asyncio) without blocking the event loop. Use asyncpg or psycopg (v3) for async.","severity":"gotcha","affected_versions":"all"},{"fix":"psycopg2: 'import psycopg2'. psycopg v3: 'import psycopg'. Do not mix them.","message":"psycopg2 ≠ psycopg (v3). They are different packages with different APIs. psycopg (without the 2) is the newer version — different install, different import patterns.","severity":"gotcha","affected_versions":"all"},{"fix":"Use psycopg2.pool.ThreadedConnectionPool or let SQLAlchemy manage the pool.","message":"Connection is not thread-safe. Do not share a single psycopg2 connection across threads. Use a connection pool (psycopg2.pool or SQLAlchemy's pool).","severity":"gotcha","affected_versions":"all"}],"env_vars":null,"last_verified":"2026-05-12T09:08:49.156Z","next_check":"2026-06-24T00:00:00.000Z","problems":[{"fix":"Install the package using pip: `pip install psycopg2-binary`. If building from source is preferred (e.g., for production), ensure development headers for PostgreSQL (libpq-dev) are installed on your system before running `pip install psycopg2`.","cause":"The `psycopg2` package is not installed in your Python environment or the Python interpreter cannot find it in its `sys.path`.","error":"ModuleNotFoundError: No module named 'psycopg2'"},{"fix":"Ensure the PostgreSQL server is running, verify the connection parameters (host, port, user, password, dbname) are correct, check `postgresql.conf` for `listen_addresses` and `port` settings, and confirm that no firewall is blocking port 5432 (or your custom port).","cause":"This error indicates that the PostgreSQL server is either not running, not listening on the specified host/port, or a firewall is blocking the connection.","error":"psycopg2.OperationalError: could not connect to server: Connection refused"},{"fix":"Always pass parameters as a tuple (or list) as the second argument to `cursor.execute()`. For a single parameter, remember the trailing comma to create a tuple: `cursor.execute('SELECT * FROM my_table WHERE id = %s', (my_id,))`. Ensure the number of `%s` placeholders matches the number of items in your parameter sequence.","cause":"This typically occurs when passing parameters to `cursor.execute()`: either the number of placeholders (`%s`) in the SQL query does not match the number of values provided, or the parameters are not passed as a sequence (e.g., a tuple or list), even for a single parameter.","error":"TypeError: not all arguments converted during string formatting"},{"fix":"Verify that the column name in your SQL query exactly matches the column name in your PostgreSQL table, paying close attention to case. If the column was created with mixed-case or uppercase letters and double-quoted (e.g., `\"MyColumn\"`), you must consistently double-quote it in your queries. Alternatively, rename columns to all lowercase for simpler handling.","cause":"This error often arises due to case sensitivity issues in PostgreSQL column names (unquoted identifiers are folded to lowercase) or a mismatch between the column name in your SQL query and the actual table schema.","error":"psycopg2.ProgrammingError: column \"<column_name>\" does not exist"},{"fix":"Convert the problematic Python object to a basic Python type (like string, int, float, or `datetime` object) that `psycopg2` can natively adapt before passing it to the query. For custom types, you can register a custom adapter using `psycopg2.extensions.register_adapter` to define how your object should be converted to a SQL string.","cause":"Psycopg2 does not know how to convert a specific Python object type (e.g., custom classes, `numpy` types, `DictRow`) into a PostgreSQL-compatible SQL type.","error":"psycopg2.ProgrammingError: can't adapt type '<Python_type>'"}],"ecosystem":"pypi","meta_description":null,"install_score":100,"install_tag":"verified","quickstart_score":0,"quickstart_tag":"stale","pypi_latest":null,"install_checks":{"last_tested":"2026-05-12","tag":"verified","tag_description":"installs cleanly on critical runtimes, fast import, recently tested","results":[{"runtime":"python:3.10-alpine","python_version":"3.10","os_libc":"alpine (musl)","variant":"default","exit_code":1,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":null,"mem_mb":null,"disk_size":null},{"runtime":"python:3.10-alpine","python_version":"3.10","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.04,"mem_mb":2.4,"disk_size":"77.4M"},{"runtime":"python:3.10-slim","python_version":"3.10","os_libc":"slim (glibc)","variant":"default","exit_code":1,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":null,"mem_mb":null,"disk_size":null},{"runtime":"python:3.10-slim","python_version":"3.10","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.04,"mem_mb":2.4,"disk_size":"152M"},{"runtime":"python:3.11-alpine","python_version":"3.11","os_libc":"alpine (musl)","variant":"default","exit_code":1,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":null,"mem_mb":null,"disk_size":null},{"runtime":"python:3.11-alpine","python_version":"3.11","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.07,"mem_mb":2.1,"disk_size":"83.7M"},{"runtime":"python:3.11-slim","python_version":"3.11","os_libc":"slim (glibc)","variant":"default","exit_code":1,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":null,"mem_mb":null,"disk_size":null},{"runtime":"python:3.11-slim","python_version":"3.11","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.06,"mem_mb":2.1,"disk_size":"158M"},{"runtime":"python:3.12-alpine","python_version":"3.12","os_libc":"alpine (musl)","variant":"default","exit_code":1,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":null,"mem_mb":null,"disk_size":null},{"runtime":"python:3.12-alpine","python_version":"3.12","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.05,"mem_mb":2.1,"disk_size":"74.2M"},{"runtime":"python:3.12-slim","python_version":"3.12","os_libc":"slim (glibc)","variant":"default","exit_code":1,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":null,"mem_mb":null,"disk_size":null},{"runtime":"python:3.12-slim","python_version":"3.12","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.05,"mem_mb":2.1,"disk_size":"149M"},{"runtime":"python:3.13-alpine","python_version":"3.13","os_libc":"alpine (musl)","variant":"default","exit_code":1,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":null,"mem_mb":null,"disk_size":null},{"runtime":"python:3.13-alpine","python_version":"3.13","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.05,"mem_mb":2.1,"disk_size":"70.7M"},{"runtime":"python:3.13-slim","python_version":"3.13","os_libc":"slim (glibc)","variant":"default","exit_code":1,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":null,"mem_mb":null,"disk_size":null},{"runtime":"python:3.13-slim","python_version":"3.13","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.04,"mem_mb":1.9,"disk_size":"147M"},{"runtime":"python:3.9-alpine","python_version":"3.9","os_libc":"alpine (musl)","variant":"default","exit_code":1,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":null,"mem_mb":null,"disk_size":null},{"runtime":"python:3.9-alpine","python_version":"3.9","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.04,"mem_mb":2.2,"disk_size":"76.6M"},{"runtime":"python:3.9-slim","python_version":"3.9","os_libc":"slim (glibc)","variant":"default","exit_code":1,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":null,"mem_mb":null,"disk_size":null},{"runtime":"python:3.9-slim","python_version":"3.9","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.04,"mem_mb":2.2,"disk_size":"151M"}]},"quickstart_checks":{"last_tested":"2026-04-23","tag":"stale","tag_description":"widespread failures or data too old to trust","results":[{"runtime":"python:3.10-alpine","exit_code":1},{"runtime":"python:3.10-slim","exit_code":1},{"runtime":"python:3.11-alpine","exit_code":1},{"runtime":"python:3.11-slim","exit_code":1},{"runtime":"python:3.12-alpine","exit_code":1},{"runtime":"python:3.12-slim","exit_code":1},{"runtime":"python:3.13-alpine","exit_code":1},{"runtime":"python:3.13-slim","exit_code":1},{"runtime":"python:3.9-alpine","exit_code":1},{"runtime":"python:3.9-slim","exit_code":1}]}}