sqlalchemy/sqlalchemy
github.com/sqlalchemy/sqlalchemy ↗ · 710 files · 20.2 MB
Overview
SQLAlchemy is a Python database toolkit for developers who need composable SQL, managed database connections, and optional object-relational mapping. Its Core compiles statement objects for database-specific dialects, while its ORM tracks mapped objects and orders persistence through a transactional unit of work. Async APIs reuse the synchronous engine machinery through a greenlet bridge and compatible async drivers. This brief examines public source at commit 890d62f4337fe4af1afc36358fb536cf754fc231; its inventory and coverage observations are bounded, and no repository code or tests were executed.
System map
The primary entrypoints are imported Python APIs rather than an application startup command. Core separates SQL construction and compilation from connection ownership and driver execution; the ORM adds mapping and session state above that boundary. Database drivers own external persistence, while SQLAlchemy owns pools, compiled statements, and mapped-object state in process. Packaging supports Python and optional Cython implementations, with Nox and GitHub Actions coordinating verification and wheel production.
- lib/sqlalchemy/__init__.py exposes create_engine, Engine, Connection, Result, URL, pools, schema objects, and SQL expression constructors as the public package facade.
- An Engine references a Dialect and Pool; ordinary construction defers the first DBAPI connection until connect(), begin(), or dependent Session work requires it.
- SQLCompiler, DDLCompiler, and GenericTypeCompiler render SQL, schema statements, and type specifications; executable expression objects enter the connection layer through _execute_on_connection.
- Mapper associates Python classes with relational structures, Session orchestrates flushes, and UOWTransaction owns dependency-ordered flush actions.
- Built-in dialect loading covers MSSQL, MySQL, Oracle, PostgreSQL, and SQLite, with a MariaDB loader branch; optional dependency groups select concrete synchronous and async drivers.
- The external storage boundary is the DBAPI: DefaultDialect.connect calls loaded_dbapi.connect, execution calls cursor methods, and commit calls the driver's connection.commit.
- The setuptools package requires Python >=3.11 and typing-extensions; CPython builds can compile eight Cython modules, controlled by DISABLE_SQLALCHEMY_CEXT and REQUIRE_SQLALCHEMY_CEXT.
- Verification uses pytest through Nox, while release-created GitHub workflows build compiled and pure-Python wheels with cibuildwheel or python -m build.
Feature catalog
Users interact through Python statement, schema, connection, mapping, and session APIs. Core supports compiled expressions and direct driver SQL, with results returned through row and scalar interfaces. ORM features include several mapping styles, object persistence, identity reuse, and relationship loading. Async connections add awaited execution and streaming over the same underlying engine architecture; the catalog groups the inspected capabilities rather than claiming exhaustive API coverage.
- Connection.execute accepts executable Select, Insert, Update, Delete, TextClause, and DDL constructs, with dictionaries or sequences of dictionaries supplying bound parameters.
- exec_driver_sql sends SQL strings directly to the DBAPI without compilation and requires parameters in the driver's own paramstyle.
- MetaData.create_all conditionally creates registered tables and accepts a subset; MetaData.reflect loads existing table definitions with schema, view, name-filter, and foreign-key options.
- DeclarativeBase supports typed declarative models, while registry also offers generate_base, mapped decorators, and map_imperatively for classical mappings.
- Session.add moves transient objects to pending state; flush writes creations, modifications, and deletions, and commit completes the transaction and normally expires objects.
- ORM row loading reuses objects by identity key; joinedload and subqueryload can require buffering or uniquing that is incompatible with yield_per, with selectinload suggested as an alternative.
- Connection context managers return DBAPI resources to the pool and roll back unfinished transactions; async transaction APIs expose commit, rollback, and nested savepoint behavior.
- create_async_engine requires an async-compatible dialect, AsyncConnection.execute returns buffered results, and streaming uses connection.stream rather than a global server_side_cursors option.
Data flow
Inputs enter as database URLs, connection options, SQL expression objects, parameter collections, or mapped instances. Engine construction converts configuration into dialect, driver, and pool objects, and statement execution converts expressions into SQL plus driver parameters. Sessions retain object identity and pending changes, while databases retain durable data. Results travel back through execution contexts and, for ORM queries, row processors that reuse or instantiate mapped objects.
- create_engine converts its URL with make_url, instantiates URL-selected plugins, resolves the dialect entrypoint, imports the DBAPI, and merges dialect-generated connection parameters with connect_args.
- Connection acquisition moves through Engine.connect, Connection construction, Engine.raw_connection, and Pool.connect; pool connection records invoke the configured creator to obtain the actual driver connection.
- Connection.execute distills parameters and delegates to the statement; _execute_clauseelement merges statement, connection, and call options before compiling and constructing an execution context.
- Compilation caching requires both an available cache and dialect support; cache keys include dialect, expression structure, column keys, schema-translation presence, and executemany mode.
- The execution context autobegins when necessary, selects insertmanyvalues or ordinary execution, invokes dialect cursor operations, and constructs a result proxy after post_exec.
- Session._flush gathers dirty, new, and deleted states into UOWTransaction; persistence splits states into inserts and updates and executes statements through Core Connection objects.
- ORM SELECT execution calls conn.execute, builds QueryContext, and calls loading.instances; identity keys combine mapped identity class, primary-key values, and identity token before session lookup or attachment.
- Async execution sends synchronous engine work into greenlet_spawn; await_ switches driver awaitables to the parent, which awaits them and returns results or throws exceptions back into the worker greenlet.
Behavior traces
Four concrete flows were followed through source: engine creation and first connection, Core statement execution, ORM persistence and commit, and async connection execution. The traces preserve important branches such as plugin intervention, insertmanyvalues, dependency cycles, and failed flush rollback. A fifth trace connects ORM query execution to identity-map materialization. These describe source behavior, not observed runtime outcomes.
- Startup: create_engine → make_url → URL._instantiate_plugins → URL._get_entrypoint → get_dialect_cls/get_async_dialect_cls → import_dbapi → dialect construction → pool construction → Engine construction → engine_created hooks.
- First connection: Engine.connect → Connection.__init__ → Engine.raw_connection → Pool.connect → _ConnectionFairy._checkout; creating a fresh record invokes the creator, and the registered connect handler initializes the dialect and rolls back initialization work.
- Core action: Connection.execute → _distill_params_20 → ClauseElement._execute_on_connection → Connection._execute_clauseelement → _compile_w_cache → _execute_context; non-executable input raises ObjectNotExecutableError.
- Driver execution: _execute_context → transaction validation/autobegin → pre_exec → _exec_insertmany_context or _exec_single_context; ordinary execution chooses do_executemany, do_execute_no_params, or do_execute, then post_exec → _setup_result_proxy.
- Persistence: Session.add → instance_state → _save_or_update_state; Session.flush → _flush → UOWTransaction registration → execute → _generate_actions → topological ordering, with dependency cycles converted to per-state actions.
- Commit: Session.commit → SessionTransaction.commit → _prepare_impl → repeated flush until clean, capped at 100 → participating transaction commits → after_commit → snapshot removal → close; _SaveUpdateAll reaches persistence._save_obj during flush.
- ORM read: Session._execute_internal → ORM preparation and autoflush → get_bind → _connection_for_bind → orm_execute_statement → conn.execute → orm_setup_cursor_result → loading.instances → identity-map lookup or new_instance.
- Async action: AsyncEngine.connect creates an AsyncConnection; entering or awaiting it calls start → greenlet_spawn(sync_engine.connect). execute calls greenlet_spawn(_proxied.execute, _require_await=True) → _ensure_sync_result, and the bridge raises AwaitRequired if no driver await occurs.
Testing signals
Tests and CI were inspected, not executed; test existence is not a passing result. The inspected suite uses pytest, SQLAlchemy-specific fixtures and plugin machinery, async wrappers, and Nox environment selection. Concrete tests exercise compiler output, execution contracts, cache reuse, transactional recovery, async connections, and savepoints. Coverage observations remain bounded, and the inspected GitHub sessions use SQLite rather than establishing live coverage of every vendor integration.
- The inspected test framework is pytest with pytest-xdist; test/conftest.py adjusts the lib import path and executes the local bootstrap module to load SQLAlchemy's pytest plugin.
- test/sql/test_compiler.py asserts exact SELECT rendering and validates limit/offset coercion, including rejection of nonnumeric strings.
- test/engine/test_execute.py checks that Connection.execute rejects plain strings and that exec_driver_sql rejects malformed positional parameter collections.
- Engine execution tests check context-manager commit and rollback; CompiledCacheTest verifies that three same-shaped inserts compile once and store one cache entry.
- ORM transaction tests cover repeated post-flush work during commit, the 100-flush guard, and PendingRollbackError recovery through explicit Session.rollback.
- Async engine tests require async_dialect and cover awaited/context-managed connections, unstarted-context errors, commit, rollback, savepoints, and pool exhaustion timeouts.
- PR CI selects Python 3.14/3.15 and compiled/greenlet/pure-Python variants; the referenced github-* Nox sessions target SQLite, while separate CI jobs select mypy, pep484, and pep8.
- Verification limits are explicit: pytest excludes examples/doc/lib from recursive collection, some Nox sessions deselect timing or memory tests, profiling skips unmatched platform keys, and push CI excludes free-threaded greenlet combinations and tolerates PyPy failures.
Risk map
These are source observations about trust boundaries and fragile behavior, not confirmed vulnerabilities. Sensitive inputs include SQL strings, mapping expressions, pickle data, database URLs, plugin selections, and query parameters that may enter logs. Driver calls can reach network databases or files outside the checkout. Transaction hooks and async context switching add correctness risks that require targeted regression coverage.
- _class_resolver.__call__ evaluates expression strings with eval unless tables_only is selected; supplying untrusted mapping expressions would cross a Python execution boundary.
- The legacy serializer subclasses pickle.Unpickler, invokes nested pickle.loads, and exposes loads(data); its own documentation warns that pickle security considerations apply.
- exec_driver_sql bypasses expression compilation and forwards caller-controlled SQL to cursor.execute; parameter safety depends on callers keeping values in driver parameters rather than interpolating SQL.
- Echo execution logs include parameter representations unless hide_parameters is enabled; URL rendering masks the password field by default but still renders query values.
- SQLite connection arguments accept absolute paths and normalize ordinary file names with os.path.abspath; driver connections therefore can access caller-selected files outside the repository.
- URL plugin names instantiate registered Python plugins before engine construction; PluginLoader loads package entrypoints or imports registered modules, making plugin selection an executable configuration boundary.
- Flush hooks can add further work, and commit caps repeated flushes at 100; mutations inside inner-flush handlers can have attribute history reset without database updates, while failures trigger captured rollback.
- Async IO requires the greenlet bridge: await_ raises MissingGreenlet outside it, and AsyncEngine rejects synchronous drivers. Commit's default expiration can require reloads that the async API does not support implicitly.
Extension points
SQLAlchemy exposes explicit extension interfaces at connection, compilation, type, mapping, and event boundaries. Dialects and engine plugins have separate entrypoint registries, and runtime dialect registration is supported. User-defined SQL constructs and types can extend rendering and conversion without replacing the engine pipeline. Mapping registries and events support application-specific behavior, while the dialect compliance suite provides a targeted validation contract for third-party backends.
- The sqlalchemy.dialects registry uses PluginLoader with a built-in auto-loader; register(name, modulepath, objname) installs a runtime import factory.
- CreateEnginePlugin implementations can update URLs, modify dialect/pool keyword arguments, and attach behavior after engine creation; URL query plugins and the plugins argument select them.
- create_engine accepts creator, poolclass, or an existing pool; do_connect listeners can modify connection arguments or return a replacement driver connection.
- event.listen and event.listens_for register target-specific listeners; execution hooks can transform statements and parameters or return a complete ORM result before normal execution.
- @compiles registers default or dialect-specific compilation handlers for expression classes; deregister restores the original dispatch implementation.
- TypeDecorator adds bind/result conversion to an underlying type and can choose dialect-specific implementations; cache_ok declares whether its behavior is safe for statement caching.
- GenericFunction subclasses register automatically with func; register_function also associates named constructs with packages and warns before replacing an existing identifier.
- Mapping registry supports declarative and imperative factories, and external dialect authors are directed to the reusable compliance suite with capability requirements rather than the entire internal test suite.
Contributor handoff
Begin with the package configuration and the subsystem's public entrypoint, then follow the execution path into its state owners. Keep dialect behavior, Core execution, ORM state management, and async adaptation distinct when choosing an edit location. Tests and CI were inspected, not executed; the suggested checks are future actions requiring authorization and suitable database environments. Treat this brief as a bounded source map and verify new facts against the pinned repository before widening scope.
- Read pyproject.toml and setup.py before packaging changes: lib is the package root, version comes from sqlalchemy.__version__, Python >=3.11 is required, and Cython builds have explicit enable/require controls.
- For engine changes, first read create_engine, Connection.execute, _execute_clauseelement, and _execute_context; these separate configuration, parameter validation, compilation, and transaction initiation.
- For persistence changes, read Session._flush, UOWTransaction.execute, and persistence._save_obj together; preserve dependency ordering and distinguish flush work from the outer transaction commit.
- A failed flush leaves a rollback requirement, and commit normally expires mapped state; transaction regressions should cover recovery and object lifecycle rather than only emitted SQL.
- For async changes, inspect AsyncConnection.start/execute and greenlet_spawn/await_ before editing; the adapter proxies synchronous machinery and requires actual async driver work.
- When execution is authorized, begin with the matching test file, such as pytest test/engine/test_execute.py or pytest test/orm/test_transaction.py; use documented --db/--dburi selection for vendor-specific checks and Nox for broader environments.
- Follow the existing formatting tools: Black uses 79 columns, zimports handles import formatting, isort is deliberately disabled, and the pre-commit configuration includes Black, zimports, and Flake8.
- Tests and CI in this brief were inspected, not executed. Before claiming coverage, account for SQLite-only github-* sessions, capability-gated async tests, collection exclusions, and Nox cleanup that can invoke reap_dbs.py and delete its identifier file.
Languages
| Language | Files | Share of source |
|---|---|---|
| Python | 675 | 100% |
| YAML | 11 | docs/config |
| Markdown | 4 | docs/config |
| TOML | 1 | 0% |
Start reading here
Probable source entrypoint: setup.py — inferred from the directory tree and manifest, not the upstream docs.
Source roots
lib/ (262 files)
Repository structure
| Top-level path | Files |
|---|---|
test/ | 331 |
lib/ | 262 |
examples/ | 74 |
.github/ | 14 |
tools/ | 12 |
Package
pyproject.toml — false v{attr = "sqlalchemy.__version__"}, 44 declared dependencies.
asynciomypymypytypes-greenlet]mssqlmssql-pymssqlmssql-pyodbcmssql-pythonmysqlmysql-connectormariadb-connector
Structural health signals
Average score 5.3/10 across 96 analyzed source files; 96 hotspots surfaced.
test/dialect/postgresql/test_types.py— 1524 lines, score 4.7 (Large file, Deep nesting)test/orm/test_relationships.py— 1519 lines, score 4.7 (Large file, Deep nesting)test/orm/test_eager_relations.py— 1582 lines, score 4.7 (Large file, Deep nesting)
These are deterministic review leads — size, churn, and structural signals — not proof of a bug in this project.
Recent history
890d62f4337f2026-10-02 — Merge "render statement params with literal_binds" into main1b041368a82d2026-10-02 — render statement params with literal_binds049ed1db162a2026-10-02 — preserve declaration order of unmapped dataclass mixin attributese8d9f495b6372026-10-02 — Version 2.1.3 placeholder714be20441512026-10-02 — - 2.1.2