Does SQLAlchemy support ALTER TABLE, CREATE VIEW, CREATE TRIGGER, Schema Upgrade Functionality?
General ALTER support isn’t present in SQLAlchemy directly. For special DDL
on an ad-hoc basis, the DDL and related constructs can be used.
See core/ddl for a discussion on this subject.
A more comprehensive option is to use schema migration tools, such as Alembic
or SQLAlchemy-Migrate; see Altering Schemas through Migrations for discussion on this.
How can I get the CREATE TABLE/ DROP TABLE output as a string?
Modern SQLAlchemy has clause constructs which represent DDL operations. These
can be rendered to strings like any other SQL expression:
from sqlalchemy.schema import CreateTable
print(CreateTable(mytable))
To get the string specific to a certain engine:
print(CreateTable(mytable).compile(engine))
There’s also a special form of Engine that can let you dump an entire
metadata creation sequence, using this recipe:
def dump(sql, *multiparams, **params):
print(sql.compile(dialect=engine.dialect))
engine = create_engine('postgresql://', strategy='mock', executor=dump)
metadata.create_all(engine, checkfirst=False)
The Alembic tool also supports
an “offline” SQL generation mode that renders database migrations as SQL scripts.
How can I subclass Table/Column to provide certain behaviors/configurations?
Table and Column are not good targets for direct subclassing.
However, there are simple ways to get on-construction behaviors using creation
functions, and behaviors related to the linkages between schema objects such as
constraint conventions or naming conventions using attachment events.
An example of many of these
techniques can be seen at Naming Conventions.