MetaData / Schema

    Above, a connection pool connection is still checked out; furthermore, the result object above also maintains a link to this connection. If “implicit execution” is used, the result will hold this connection opened until the result object is closed or all rows are exhausted.

    The call to mytable.drop(engine) attempts to emit DROP TABLE on a second connection procured from the which will lock.

    The solution is to close out all connections before emitting DROP TABLE:

    1. connection = engine.connect()
    2. result = connection.execute(mytable.select())
    5. result.fetchall()
    7. # close connections
    8. connection.close()
    10. mytable.drop(engine)

    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 for a discussion on this subject.

    This is available via the MetaData.sorted_tables function:

    Modern SQLAlchemy has clause constructs which represent DDL operations. These can be rendered to strings like any other SQL expression:

    1. from sqlalchemy.schema import CreateTable
    3. print(CreateTable(mytable))

    To get the string specific to a certain engine:

    There’s also a special form of available via create_mock_engine() that allows one to dump an entire metadata creation sequence as a string, using this recipe:

    1. from sqlalchemy import create_mock_engine
    3. def dump(sql, *multiparams, **params):
    4.     print(sql.compile(dialect=engine.dialect))
    5. engine = create_mock_engine('postgresql://', dump)

    and 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 .