{"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":[{"wrong":"import psycopg2","symbol":"psycopg2","correct":"import psycopg2"}],"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,"search_vec":"'2.9.11':44 '2026':46 'adapt':7 'async':48,68 'asyncpg':51 'binari':25,39 'compil':28 'consid':58 'current':42 'databas':72 'dep':31 'dev':20 'doc':33 'explicit':34 'header':21 'libpq':19 'mar':45 'migrat':59 'new':56 'offici':32 'packag':13 'postgresql':6,49,70 'pre':27 'pre-compil':26 'product':41 'project':57 'psycopg':53,61 'psycopg2':1,16,24,38,69 'psycopg2-binary':23,37 'pypi':15 'python':9,71 'requir':18 'sourc':17 'sync':10,66,73 'system':30 'two':12 'use':5,50 'v3':54,62 'version':43 'warn':35 'wide':4","created_at":"2026-03-24T18:43:07.368973+00:00","updated_at":"2026-04-16T18:13:57.980150+00:00","problems":null,"ecosystem":"pypi","meta_description":null,"install_score":100,"quickstart_score":0,"quickstart_tag":"stale","pypi_latest":"2.9.12","cli_name":"","cli_version":null,"type":"library","homepage":"https://psycopg.org/","github":"https://github.com/psycopg/psycopg2","docs":"https://www.psycopg.org/docs/","changelog":"https://www.psycopg.org/docs/news.html","pypi":"https://pypi.org/project/psycopg2/","npm":null,"openapi_spec":null,"status_page":null,"smithery":null,"categories":["database"],"base_url":null,"auth_type":null,"provenance":{"verified_status":"passing","verified_at":"2026-06-30","last_verified":"2026-08-26","next_check":"2026-07-30","install_tag":"verified"}}