创建model文件
from sqlalchemy import create_engine,Column,String,Integer
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine("sqlite:///alembic_db.db", echo=True)
Base = declarative_base()
class User(Base):
__tablename__ = "user"
id = Column(Integer , primary_key=True , autoincrement=True)
name = Column(String(50) , nullable=False)
# age = Column(Integer, nullable=False)
# sex = Column(String(50) , nullable=False)
初始化alembic
- 首先确保alembic的包都是安装好了
- 使用下面命令初始化alembic
alembic init alembic
- 初始化完成之后,会行程如下的文件目录
alembic-------------------项目文件夹
versions-----------数据库版本文件夹 env.py-------------Base.metadata配置在这里 script.py.mako-----暂时没用到alembic.ini---------------数据库的连接地址,一般是配置在这里
配置alembic.ini
; sqlalchemy.url = driver://user:pass@localhost/dbname
sqlalchemy.url = sqlite:///alembic_db.db
- 将需要连接的数据库url,换成自己的数据库地址,这里用的sqlite数据库
配置alembic>env.py文件
# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
from alembic_demo import Base
target_metadata = Base.metadata
- 导入自己的Base,然后将target_metadata换成自己的Base.metadata
自动根据你的model生成版本文件
alembic revision --autogenerate -m 'create user table'
- 然后就会在versions文件夹生成一个
版本号_你的注释.py,即ff80084df207_create_user_table.py的文件,内容如下
"""create user table
Revision ID: ff80084df207
Revises:
Create Date: 2023-03-11 10:35:44.821957
"""
from alembic import op
import sqlalchemy as sa
# revision identifiers, used by Alembic.
revision = 'ff80084df207'
down_revision = None
branch_labels = None
depends_on = None
def upgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('user',
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
sa.Column('name', sa.String(length=50), nullable=False),
sa.PrimaryKeyConstraint('id')
)
# ### end Alembic commands ###
def downgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.drop_table('user')
# ### end Alembic commands ###
- 此时如果查看数据库,发现还是没有创建表,这个时候需要使用下面的命令,
alembic upgrade head
然后再次查看数据库,就发先数据库中已经创建了一个user表.
- 同上,我们在进行下面的两次操作:
在user model中,增加age字段,然后执行下面的命令
alembic revision --autogenerate -m 'add user age'
alembic upgrade head
在user model中,增加sex字段,然后执行下面的命令
alembic revision --autogenerate -m 'add user sex'
alembic upgrade head
然后就是我们在数据库,就是可以看到一个拥有 id,name,age,sex字段的表,
- 然后突然我们不要这个sex字段了,怎么办?
可以使用下面的命令进行回退,
alembic downgrade -1
或者
alembic downgrade [目标版本号(可以只写前几位,只要能确定就行)]
然后你查看数据库,发现就是只有id,name,age三个字段了
命令的历史记录
1220 code .
1221 source venv/bin/activate
1222 pip list
1223 clear
1224 python -V
1225 alembic revision --autogenerate -m 'create user table'
1227 alembic heads
1228 alembic upgrade head
1229 alembic revision --autogenerate -m 'add age column'
1230 alembic upgrade head
1231 alembic revision --autogenerate -m 'add sex column'
1232 alembic upgrade head
1233 alembic check
1234 alembic downgrade ff8
1235 clear
/Users/song/codelearn/alembic_learn_auto/alembic_demo.py
from sqlalchemy import create_engine,Column,String,Integer
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine("sqlite:///alembic_db.db", echo=True)
Base = declarative_base()
class User(Base):
__tablename__ = "user"
id = Column(Integer , primary_key=True , autoincrement=True)
name = Column(String(50) , nullable=False)
# age = Column(Integer, nullable=False)
# sex = Column(String(50) , nullable=False)
# Base.metadata.drop_all(engine)
# Base.metadata.create_all(engine)
/Users/song/codelearn/alembic_learn_auto/alembic/env.py
from logging.config import fileConfig
from sqlalchemy import engine_from_config
from sqlalchemy import pool
from alembic import context
# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config
# Interpret the config file for Python logging.
# This line sets up loggers basically.
if config.config_file_name is not None:
fileConfig(config.config_file_name)
# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
from alembic_demo import Base
target_metadata = Base.metadata
# other values from the config, defined by the needs of env.py,
# can be acquired:
# my_important_option = config.get_main_option("my_important_option")
# ... etc.
def run_migrations_offline() -> None:
"""Run migrations in 'offline' mode.
This configures the context with just a URL
and not an Engine, though an Engine is acceptable
here as well. By skipping the Engine creation
we don't even need a DBAPI to be available.
Calls to context.execute() here emit the given string to the
script output.
"""
url = config.get_main_option("sqlalchemy.url")
context.configure(
url=url,
target_metadata=target_metadata,
literal_binds=True,
dialect_opts={"paramstyle": "named"},
)
with context.begin_transaction():
context.run_migrations()
def run_migrations_online() -> None:
"""Run migrations in 'online' mode.
In this scenario we need to create an Engine
and associate a connection with the context.
"""
connectable = engine_from_config(
config.get_section(config.config_ini_section, {}),
prefix="sqlalchemy.",
poolclass=pool.NullPool,
)
with connectable.connect() as connection:
context.configure(
connection=connection, target_metadata=target_metadata
)
with context.begin_transaction():
context.run_migrations()
if context.is_offline_mode():
run_migrations_offline()
else:
run_migrations_online()
/Users/song/codelearn/alembic_learn_auto/alembic/versions/d8f3d91f4920_add_sex_column.py
"""add sex column
Revision ID: d8f3d91f4920
Revises: 06b3c1041429
Create Date: 2023-03-11 10:39:32.064917
"""
from alembic import op
import sqlalchemy as sa
# revision identifiers, used by Alembic.
revision = 'd8f3d91f4920'
down_revision = '06b3c1041429'
branch_labels = None
depends_on = None
def upgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('user', sa.Column('sex', sa.String(length=50), nullable=False))
# ### end Alembic commands ###
def downgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.drop_column('user', 'sex')
# ### end Alembic commands ###
/Users/song/codelearn/alembic_learn_auto/alembic/versions/ff80084df207_create_user_table.py
"""create user table
Revision ID: ff80084df207
Revises:
Create Date: 2023-03-11 10:35:44.821957
"""
from alembic import op
import sqlalchemy as sa
# revision identifiers, used by Alembic.
revision = 'ff80084df207'
down_revision = None
branch_labels = None
depends_on = None
def upgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('user',
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
sa.Column('name', sa.String(length=50), nullable=False),
sa.PrimaryKeyConstraint('id')
)
# ### end Alembic commands ###
def downgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.drop_table('user')
# ### end Alembic commands ###
/Users/song/codelearn/alembic_learn_auto/alembic/versions/06b3c1041429_add_age_column.py
"""add age column
Revision ID: 06b3c1041429
Revises: ff80084df207
Create Date: 2023-03-11 10:38:16.564152
"""
from alembic import op
import sqlalchemy as sa
# revision identifiers, used by Alembic.
revision = '06b3c1041429'
down_revision = 'ff80084df207'
branch_labels = None
depends_on = None
def upgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('user', sa.Column('age', sa.Integer(), nullable=False))
# ### end Alembic commands ###
def downgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.drop_column('user', 'age')
# ### end Alembic commands ###
