Install & Compatibility
Where this runs
tested against v0.3.2 · pip install
no network on importno background threads
Install × environment matrix
Each cell = how many times install + import succeeded across repeated harness runs. Partial = flaky.
glibc = Debian/Ubuntu slim · musl = Alpine Linux
muslpy 3.10–3.910 runs
installs and imports cleanly · install 0.0s · import 0.740s · 42.6MB
glibcpy 3.10–3.910 runs
installs and imports cleanly · install 3.2s · import 0.668s · 41MB
41MB installed
● package 41MB
Code
Verified usage
Verified import paths — ran on the pinned version, not inferred.
CreateView
✓ from sqlalchemy_views import CreateView
DropView
✓ from sqlalchemy_views import DropView
Table
✓ from sqlalchemy import Table
✗ from sqlalchemy.schema import View
SQLAlchemy-views uses the `Table` object to represent views, not a dedicated `View` construct, which doesn't exist in standard SQLAlchemy.
MetaData
✓ from sqlalchemy import MetaData
text
✓ from sqlalchemy import text
This quickstart demonstrates how to define a base table, then create and drop a database view using `sqlalchemy-views` with an in-memory SQLite database. It shows how to use `CreateView` with a `Table` object representing the view and a raw SQL `text` definition. It also includes an example of querying the created view.
import sqlalchemy as sa
from sqlalchemy import Table, Column, Integer, String, MetaData, text
from sqlalchemy.schema import CreateTable
from sqlalchemy_views import CreateView, DropView
# 1. Setup an in-memory SQLite database
engine = sa.create_engine('sqlite://', echo=True)
metadata = MetaData()
# 2. Define a base table
users_table = Table(
'users',
metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50)),
Column('email', String(100))
)
# 3. Define the view using a Table object and a SQL definition
active_users_view = Table('active_users', metadata, Column('id', Integer), Column('name', String), Column('email', String))
view_definition = text("SELECT id, name, email FROM users WHERE active = 1")
create_active_users_view = CreateView(active_users_view, view_definition)
drop_active_users_view = DropView(active_users_view)
# For demonstration, assume 'users' has an 'active' column for the view,
# but for simplicity, the base table here doesn't have it to keep it minimal.
# In a real app, users_table would also have 'active'.
with engine.connect() as connection:
# Create the base table (users)
metadata.create_all(connection)
connection.execute(text("INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'alice@example.com')"))
connection.execute(text("INSERT INTO users (id, name, email) VALUES (2, 'Bob', 'bob@example.com')"))
connection.execute(text("INSERT INTO users (id, name, email) VALUES (3, 'Charlie', 'charlie@example.com')"))
connection.execute(text("ALTER TABLE users ADD COLUMN active BOOLEAN DEFAULT 1"))
connection.execute(text("UPDATE users SET active = 0 WHERE id = 2"))
# Apply the CreateView DDL
connection.execute(create_active_users_view)
print("\n--- View 'active_users' created ---")
# Query the view
result = connection.execute(active_users_view.select()).fetchall()
print("Data from 'active_users' view:", result)
# Apply the DropView DDL
connection.execute(drop_active_users_view)
print("\n--- View 'active_users' dropped ---")
connection.commit()
Debug
Known issues
gotchaSQLAlchemy-views uses a standard `Table` object to represent views, which can be confusing for those expecting a dedicated 'View' class. When introspecting an existing view (e.g., for `get_view_definition`), you must create a `Table` object with `autoload=True` (or `autoload_with=engine` for SQLAlchemy 2.0+).fixAlways use `sqlalchemy.Table` for views. For introspection, ensure `autoload=True` is set on the `Table` and use `sa.inspect(connection).get_view_definition(view_name)`.
affects: All
gotchaThe library primarily provides core `CREATE VIEW` and `DROP VIEW` constructs. It does not provide ORM-level integration for views, meaning you cannot directly map ORM models to views without additional custom SQLAlchemy ORM configuration. Libraries like `SQLAlchemy-ViewORM` exist for ORM-centric view management.fixFor ORM mapping, you'll need to manually define the ORM class for your view and configure its mapping, or consider a library specifically for ORM view integration. `sqlalchemy-views` focuses on DDL generation.
affects: All
gotchaDifferent SQL dialects have varying `CREATE VIEW` and `DROP VIEW` syntax (e.g., `WITH CHECK OPTION`, `OR REPLACE`, `CASCADE`). `sqlalchemy-views` aims for core functionality, so highly dialect-specific view options might not be directly supported and may require custom SQL `text` execution.fixReview your database dialect's documentation for view syntax. If `sqlalchemy-views` doesn't provide a direct parameter, use `sqlalchemy.text` for the specific DDL, or contribute to the library.
affects: All
breakingWhile `sqlalchemy-views` is compatible with both SQLAlchemy 1.x and 2.x, migrating an application from SQLAlchemy 1.x to 2.x involves significant API changes in SQLAlchemy itself (e.g., new ORM statement paradigm, `Result` object, explicit `bind` argument). These underlying SQLAlchemy changes will affect how you interact with DDL elements generated by `sqlalchemy-views` within your application's transaction and connection management.fixRefer to the official SQLAlchemy 2.0 Migration Guide and 'What's New' documentation to update your application's core SQLAlchemy usage patterns (e.g., `Session` management, `select()` constructs, `connection.execute()` usage).
affects: SQLAlchemy 1.x migrating to 2.x
Errors
Common errors & fixes
sqlalchemy.exc.CompileError: (in _create_view) Can't compile a 'Table' object as a SQL expression
This usually occurs when you pass a `Table` object representing the *view itself* as the `selectable` (definition) argument to `CreateView` instead of a `sqlalchemy.sql.selectable` or `sqlalchemy.sql.expression.text` object that defines the view's query.
fixThe second argument to `CreateView` must be the *definition query* for the view (e.g., `table.select()` or `text("SELECT ...")`), not the `Table` object for the view itself. Example: `CreateView(my_view_table_object, text("SELECT ..."))`. AttributeError: 'Connection' object has no attribute 'begin'
This error arises in SQLAlchemy 2.0+ when attempting to start a transaction using `connection.begin()` directly, which was common in SQLAlchemy 1.x. SQLAlchemy 2.0's `Connection` is transactional by default within `with engine.connect() as connection:` blocks, and its `begin()` method is for explicit sub-transactions or nested transaction patterns, not the primary transaction initiation.
fixFor standard transaction management in SQLAlchemy 2.0+, operations within `with engine.connect() as connection:` are implicitly transactional and commit on block exit (unless an exception occurs). For explicit transaction control, use `with connection.begin():` or `with connection.begin_nested():` if nested transactions are required.
sqlalchemy.exc.NoReferenceError: Foreign key 'fk_table_column_id' cannot be created on a view 'my_view'. Views cannot have foreign keys.
Attempting to define `ForeignKey` or `UniqueConstraint` on a `Table` object that is intended to represent a database view. Views in most SQL databases do not directly support DDL for constraints like foreign keys.
fixRemove foreign key and unique constraints from `Table` objects that represent views. These constraints should be defined only on base tables. Views derive their schema from the underlying tables and queries.
Upgrade
Version history
0.3.2latest on PyPI · released Feb 18, 2023
Audit
Dependencies
sqlalchemyrequiredCore dependency for database interaction and DDL constructs.