{"id":157,"library":"sqlalchemy","title":"SQLAlchemy","description":"The most widely used Python SQL toolkit and ORM. v2.0 released January 2023 — massive breaking change from v1.x. The entire ORM query API shifted from session.query() to select(). engine.execute() removed. Passing raw strings to execute() removed. Typed mapped_column() replaces Column(). Current version: 2.0.48 (Mar 2026). Flask-SQLAlchemy's Model.query pattern is also legacy in v2.","status":"active","version":"2.0.48","language":"python","source_language":"en","source_url":"https://github.com/sqlalchemy/sqlalchemy","tags":["sqlalchemy","orm","sql","database","python","postgresql","sqlite"],"install":[{"cmd":"pip install sqlalchemy","lang":"bash","label":"Python (core)"},{"cmd":"pip install 'sqlalchemy[asyncio]'","lang":"bash","label":"Python (with async support)"}],"dependencies":[{"reason":"Required for async support. Install via sqlalchemy[asyncio].","package":"greenlet","optional":true},{"reason":"Required for PostgreSQL sync. Or use asyncpg for async.","package":"psycopg2","optional":true}],"imports":[{"note":"session.query() is legacy API in v2. Still works but not recommended. Use select() + session.scalars() or session.execute() instead.","wrong":"# v1 style — legacy\nwith Session(engine) as session:\n    user = session.query(User).filter(User.username == 'alice').first()","symbol":"select (v2 ORM query)","correct":"from sqlalchemy import select\nfrom sqlalchemy.orm import Session\n\nwith Session(engine) as session:\n    # v2 style — use select()\n    stmt = select(User).where(User.username == 'alice')\n    user = session.scalars(stmt).first()\n\n    # Insert\n    new_user = User(username='bob', email='bob@example.com')\n    session.add(new_user)\n    session.commit()"},{"note":"declarative_base() still works in v2 but is legacy. Use DeclarativeBase class directly. mapped_column() with Mapped[] type annotations is the v2 style.","wrong":"from sqlalchemy import Column, Integer, String\nfrom sqlalchemy.ext.declarative import declarative_base\n\nBase = declarative_base()\n\nclass User(Base):\n    __tablename__ = 'users'\n    id = Column(Integer, primary_key=True)\n    username = Column(String(50))","symbol":"mapped_column (v2 ORM models)","correct":"from sqlalchemy import String\nfrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column\n\nclass Base(DeclarativeBase):\n    pass\n\nclass User(Base):\n    __tablename__ = 'users'\n    id: Mapped[int] = mapped_column(primary_key=True)\n    username: Mapped[str] = mapped_column(String(50), unique=True)\n    email: Mapped[str] = mapped_column(String(120))"},{"note":"engine.execute() removed in v2. Raw string execution removed — must wrap in text(). Always use text() for raw SQL.","wrong":"engine = create_engine('...')\nengine.execute('SELECT 1')  # removed in v2\n# also wrong:\nconn.execute('SELECT 1')  # raw strings removed in v2","symbol":"engine.connect / text()","correct":"from sqlalchemy import create_engine, text\n\nengine = create_engine('postgresql://user:pass@localhost/db')\n\nwith engine.connect() as conn:\n    result = conn.execute(text('SELECT 1'))\n    print(result.fetchall())"}],"quickstart":{"code":"# pip install sqlalchemy\nfrom sqlalchemy import create_engine, String, select, text\nfrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session\n\nclass Base(DeclarativeBase):\n    pass\n\nclass User(Base):\n    __tablename__ = 'users'\n    id: Mapped[int] = mapped_column(primary_key=True)\n    name: Mapped[str] = mapped_column(String(50))\n\nengine = create_engine('sqlite:///test.db')\nBase.metadata.create_all(engine)\n\nwith Session(engine) as session:\n    # Insert\n    session.add(User(name='Alice'))\n    session.commit()\n\n    # Query — v2 style\n    stmt = select(User).where(User.name == 'Alice')\n    user = session.scalars(stmt).first()\n    print(user.name)  # 'Alice'","lang":"python","description":"SQLAlchemy 2.0 ORM quickstart with typed models."},"warnings":[{"fix":"Use 'with engine.connect() as conn: conn.execute(text(...))'","message":"engine.execute() removed in v2.0. Raises AttributeError. Most LLM-generated SQLAlchemy code uses engine.execute().","severity":"breaking","affected_versions":">= 2.0"},{"fix":"from sqlalchemy import text; conn.execute(text('SELECT 1'))","message":"Passing raw strings to execute() removed. conn.execute('SELECT 1') raises ArgumentError in v2.","severity":"breaking","affected_versions":">= 2.0"},{"fix":"from sqlalchemy.orm import DeclarativeBase; class Base(DeclarativeBase): pass","message":"declarative_base() from sqlalchemy.ext.declarative is legacy in v2. Still works but generates RemovedIn20Warning.","severity":"breaking","affected_versions":">= 2.0"},{"fix":"Replace session.query(Model).filter(...) with session.scalars(select(Model).where(...))","message":"session.query() is legacy in v2 — still works but not recommended. Flask-SQLAlchemy's Model.query is also legacy. LLMs trained pre-2023 generate session.query() patterns.","severity":"gotcha","affected_versions":">= 2.0"},{"fix":"users = session.scalars(select(User)).all() — not session.execute(select(User)).all()","message":"session.execute() now returns a Result object. For ORM models use session.scalars() to get model instances directly, not session.execute().","severity":"gotcha","affected_versions":">= 2.0"},{"fix":"Use 'with engine.begin() as conn:' for autocommit. Or call session.commit() explicitly.","message":"Autocommit behavior changed in v2. Connections no longer autocommit. Must explicitly use with engine.begin() for autocommit or session.commit().","severity":"gotcha","affected_versions":">= 2.0"},{"fix":"id: Mapped[int] = mapped_column(primary_key=True)","message":"Column() without type annotations still works but loses IDE/mypy type inference. mapped_column() with Mapped[] is the v2 typed approach.","severity":"gotcha","affected_versions":">= 2.0"}],"env_vars":null,"last_verified":"2026-05-12T09:07:45.096Z","next_check":"2026-06-24T00:00:00.000Z","problems":[{"fix":"Replace `session.query(MyModel).filter(...).all()` with `session.execute(select(MyModel).filter(...)).scalars().all()`.","cause":"In SQLAlchemy 2.0, the `session.query()` method has been removed; ORM queries now use the `select()` construct.","error":"AttributeError: 'Session' object has no attribute 'query'"},{"fix":"Wrap raw SQL strings in `sqlalchemy.text()`: `connection.execute(text('SELECT * FROM my_table'))`.","cause":"SQLAlchemy 2.0 requires explicit `text()` constructs for executing raw SQL strings with `connection.execute()` or `session.execute()`.","error":"ArgumentError: Textual SQL not allowed directly on Connection.execute(). Please use text() or bind parameters."},{"fix":"Use `mapped_column()` instead of `Column()` for mapped attributes: `id: Mapped[int] = mapped_column(primary_key=True)`.","cause":"When defining declarative models in SQLAlchemy 2.0 using `Mapped`, column definitions should typically use `mapped_column()` or just type annotations, not the `Column()` construct without an explicit type hint.","error":"TypeError: Mapped expects a type annotation argument"},{"fix":"Use `db.session.execute(db.select(User).filter_by(username='test'))` instead of `User.query.filter_by(username='test').first()`.","cause":"In Flask-SQLAlchemy when using SQLAlchemy 2.0, the `Model.query` attribute is deprecated in favor of direct SQLAlchemy 2.0-style `session.execute(select(...))` queries.","error":"AttributeError: type object 'User' has no attribute 'query'"}],"ecosystem":"pypi","meta_description":null,"install_score":100,"install_tag":"verified","quickstart_score":80,"quickstart_tag":"verified","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":"asyncio","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.54,"mem_mb":15.5,"disk_size":"42.4M"},{"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.52,"mem_mb":15.5,"disk_size":"42.4M"},{"runtime":"python:3.10-slim","python_version":"3.10","os_libc":"slim (glibc)","variant":"asyncio","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.36,"mem_mb":15.5,"disk_size":"41M"},{"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.36,"mem_mb":15.5,"disk_size":"41M"},{"runtime":"python:3.11-alpine","python_version":"3.11","os_libc":"alpine (musl)","variant":"asyncio","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.79,"mem_mb":17.6,"disk_size":"47.5M"},{"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.79,"mem_mb":17.6,"disk_size":"47.5M"},{"runtime":"python:3.11-slim","python_version":"3.11","os_libc":"slim (glibc)","variant":"asyncio","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.66,"mem_mb":17.6,"disk_size":"46M"},{"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.65,"mem_mb":17.6,"disk_size":"46M"},{"runtime":"python:3.12-alpine","python_version":"3.12","os_libc":"alpine (musl)","variant":"asyncio","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.86,"mem_mb":17.4,"disk_size":"38.8M"},{"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.84,"mem_mb":17.4,"disk_size":"38.8M"},{"runtime":"python:3.12-slim","python_version":"3.12","os_libc":"slim (glibc)","variant":"asyncio","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.85,"mem_mb":17.4,"disk_size":"37M"},{"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.83,"mem_mb":17.4,"disk_size":"37M"},{"runtime":"python:3.13-alpine","python_version":"3.13","os_libc":"alpine (musl)","variant":"asyncio","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.81,"mem_mb":17.6,"disk_size":"38.2M"},{"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.81,"mem_mb":17.6,"disk_size":"38.2M"},{"runtime":"python:3.13-slim","python_version":"3.13","os_libc":"slim (glibc)","variant":"asyncio","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.79,"mem_mb":17.6,"disk_size":"37M"},{"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.8,"mem_mb":17.6,"disk_size":"37M"},{"runtime":"python:3.9-alpine","python_version":"3.9","os_libc":"alpine (musl)","variant":"asyncio","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.45,"mem_mb":15.1,"disk_size":"41.1M"},{"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.46,"mem_mb":15.1,"disk_size":"41.1M"},{"runtime":"python:3.9-slim","python_version":"3.9","os_libc":"slim (glibc)","variant":"asyncio","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.44,"mem_mb":15.1,"disk_size":"40M"},{"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.43,"mem_mb":15.1,"disk_size":"40M"}]},"quickstart_checks":{"last_tested":"2026-04-23","tag":"verified","tag_description":"quickstart runs on critical runtimes, recently tested","results":[{"runtime":"python:3.10-alpine","exit_code":0},{"runtime":"python:3.10-slim","exit_code":0},{"runtime":"python:3.11-alpine","exit_code":0},{"runtime":"python:3.11-slim","exit_code":0},{"runtime":"python:3.12-alpine","exit_code":0},{"runtime":"python:3.12-slim","exit_code":0},{"runtime":"python:3.13-alpine","exit_code":0},{"runtime":"python:3.13-slim","exit_code":0},{"runtime":"python:3.9-alpine","exit_code":0},{"runtime":"python:3.9-slim","exit_code":0}]}}