Alembic A database migrations tool for SQLAlchemy.

why need alembic?

After defining ORM CLASS, we need to create table by the ORM class.

https://docs.sqlalchemy.org/en/14/orm/tutorial.html#declare-a-mapping

>>> from sqlalchemy import Column, Integer, String
>>> class User(Base):
...     __tablename__ = 'users'
...
...     id = Column(Integer, primary_key=True)
...     name = Column(String)
...     fullname = Column(String)
...     nickname = Column(String)
...
...     def __repr__(self):
...        return "<User(name='%s', fullname='%s', nickname='%s')>" % (
...                             self.name, self.fullname, self.nickname)

sqlalchemy 提供了 创建 和 删除 表的接口。

事实上，对于表已经存在于数据库中， 这些接口不会检查表模式的变动。

https://docs.sqlalchemy.org/en/14/core/metadata.html#creating-and-dropping-database-tables

engine = create_engine('sqlite:///:memory:')

metadata_obj = MetaData()

user = Table('user', metadata_obj,
    Column('user_id', Integer, primary_key=True),
    Column('user_name', String(16), nullable=False),
    Column('email_address', String(60), key='email'),
    Column('nickname', String(50), nullable=False)
)

user_prefs = Table('user_prefs', metadata_obj,
    Column('pref_id', Integer, primary_key=True),
    Column('user_id', Integer, ForeignKey("user.user_id"), nullable=False),
    Column('pref_name', String(40), nullable=False),
    Column('pref_value', String(100))
)

metadata_obj.create_all(engine)

metadata_obj.drop_all(engine)

或者是针对单个表的 创建 和 删除。

engine = create_engine('sqlite:///:memory:')

metadata_obj = MetaData()

employees = Table('employees', metadata_obj,
    Column('employee_id', Integer, primary_key=True),
    Column('employee_name', String(60), nullable=False, key='name'),
    Column('employee_dept', Integer, ForeignKey("departments.department_id"))
)

employees.create(engine)

employees.drop(engine)

但是对于 表的修改， 其不提供支持。 建议手写 ALTER 或者 使用 Alembic。

https://docs.sqlalchemy.org/en/14/core/metadata.html#altering-database-objects-through-migrations

While SQLAlchemy directly supports emitting CREATE and DROP statements for schema constructs, the ability to alter those constructs, usually via the ALTER statement as well as other database-specific constructs, is outside of the scope of SQLAlchemy itself. While it’s easy enough to emit ALTER statements and similar by hand, such as by passing a text() construct to Connection.execute() or by using the DDL construct, it’s a common practice to automate the maintenance of database schemas in relation to application code using schema migration tools.

The SQLAlchemy project offers the Alembic migration tool for this purpose. Alembic features a highly customizable environment and a minimalistic usage pattern, supporting such features as transactional DDL, automatic generation of “candidate” migrations, an “offline” mode which generates SQL scripts, and support for branch resolution.

alembic

https://github.com/sqlalchemy/alembic

跟SQLAlchemy是同一作者

• 支持ALTER
• 提供migration系统， 支持upgrade 和 downgrade

Alembic is a database migrations tool written by the author of SQLAlchemy. A migrations tool offers the following functionality:

• Can emit ALTER statements to a database in order to change the structure of tables and other constructs
• Provides a system whereby "migration scripts" may be constructed; each script indicates a particular series of steps that can "upgrade" a target database to a new version, and optionally a series of steps that can "downgrade" similarly, doing the same steps in reverse.
• Allows the scripts to execute in some sequential manner.

https://alembic.sqlalchemy.org/en/latest/tutorial.html

本工具提供了 类似 django migrations的工具， 将每次的变更 保存到  migrations 文件中， 好处可以使用 代码库管理变更，

并可以根据需要 将数据库 表更新到 任意一个 变更版本， 类似git代码的管理概念。

Alembic provides for the creation, management, and invocation of change management scripts for a relational database, using SQLAlchemy as the underlying engine. This tutorial will provide a full introduction to the theory and usage of this tool.

To begin, make sure Alembic is installed as described at Installation. As stated in the linked document, it is usually preferable that Alembic is installed in the same module / Python path as that of the target project, usually using a Python virtual environment, so that when the alembic command is run, the Python script which is invoked by alembic, namely your project’s env.py script, will have access to your application’s models. This is not strictly necessary in all cases, however in the vast majority of cases is usually preferred.

The tutorial below assumes the alembic command line utility is present in the local path and when invoked, will have access to the same Python module environment as that of the target project.

Reference

https://www.jianshu.com/p/394e6453a6b0

https://zhuanlan.zhihu.com/p/90106173

PLAY ISSUES

https://github.com/miguelgrinberg/Flask-Migrate/issues/81

Try setting the server_default parameter in your migration script/model.
default is handled by SQLAlchemy.
server_default actually sets this in your DB

https://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.Column

https://stackoverflow.com/questions/8442147/how-to-delete-or-add-column-in-sqlite

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table.

PLAY CODE

https://github.com/fanqingsong/code_snippet/tree/master/python/sqlalchemy