I thought I'd do it properly, so I added Alembic.
DB schema version control ~? http://www.slideshare.net/kwatch/db-28097225
This is the best reference for installation and basic usage, so I think this is all right http://momijiame.tumblr.com/post/45191790683/python-alembic-rdb
By the way, in my case this time, I have already started to make an application, so I already have some SQLAlchemy model definitions, and of course there is already a table in the DB, what is this, another table in the Alembic migration script Do I have to write all the definitions of op.create_table? seriously? It's hard ... It's as hard as war ... But it's harder than war ...
I thought, but there is a function to read the definitions of DB and SQLAlchemy properly and automatically generate a migration script.
http://alembic.readthedocs.org/en/latest/tutorial.html#auto-generating-migrations
A child who can. This child can leave his wallet. So I will try this street.
It is assumed that the initial setting of Alembic is finished with reference to the first site.
However, after doing ʻalembic init, if sqlalchemy.url` is set properly in the created alembic.ini, it will be connected to the DB, so maybe it's OK.
So, the main subject. I will tell you the location of the Base of your application as a source of automatic generation.
env.py
import webapp.model.database
target_metadata = Base.metadata
It seems that this is all you need. Let's create the first migration script. If you add --autogenerate, it will do something automatically. amazing. It's automatic ~ I like it! An automatic mochitsuki machine!
Before that, here, delete all the tables on the DB once.
Auto-generation seems to detect the difference between DB and SQLAlchemy metadata. The first script to be automatically generated from now on is a table generation script, so the DB should be empty. Isn't it? I don't care.
So, I tried deleting all the DB tables once. After that, type the command immediately.
$ alembic revision --autogenerate -m "Create table"
I was able to do something under alebmic / versions /! The tension rises! If you can't, I think Base isn't importing correctly.
# revision identifiers, used by Alembic.
revision = '30552bc9b83'
down_revision = None
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql
def downgrade():
### commands auto generated by Alembic - please adjust! ###
op.drop_table('order_logs')
op.drop_table('order_items')
op.drop_table('users')
### end Alembic commands ###
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.create_table('users',
sa.Column('id', sa.INTEGER(), primary_key=True),
sa.Column('name', sa.TEXT(), autoincrement=False, nullable=True),
sa.Column('mail_address', sa.TEXT(), autoincrement=False, nullable=True),
sa.Column('password', sa.TEXT(), autoincrement=False, nullable=True),
sa.Column('status', sa.INTEGER(), autoincrement=False, nullable=True),
sa.Column('insert_date', postgresql.TIMESTAMP(), autoincrement=False, nullable=True),
sa.Column('update_date', postgresql.TIMESTAMP(), autoincrement=False, nullable=True),
sa.PrimaryKeyConstraint('id', name='users_pkey')
)
op.create_table('order_items',
sa.Column('id', sa.INTEGER(), primary_key=True),
sa.Column('order_log_id', sa.INTEGER(), autoincrement=False, nullable=True),
sa.Column('user_id', sa.INTEGER(), autoincrement=False, nullable=True),
sa.Column('item_name', sa.TEXT(), autoincrement=False, nullable=True),
sa.Column('item_price', sa.INTEGER(), autoincrement=False, nullable=True),
sa.Column('item_number', sa.INTEGER(), autoincrement=False, nullable=True),
sa.Column('item_total', sa.INTEGER(), autoincrement=False, nullable=True),
sa.Column('status', sa.SMALLINT(), autoincrement=False, nullable=True),
sa.Column('insert_date', postgresql.TIMESTAMP(), autoincrement=False, nullable=True),
sa.Column('update_date', postgresql.TIMESTAMP(), autoincrement=False, nullable=True),
sa.PrimaryKeyConstraint('id', name='order_items_pkey')
)
op.create_table('order_logs',
sa.Column('id', sa.INTEGER(), primary_key=True),
sa.Column('user_id', sa.INTEGER(), autoincrement=False, nullable=True),
sa.Column('order_no', sa.TEXT(), autoincrement=False, nullable=True),
sa.Column('order_name', sa.TEXT(), autoincrement=False, nullable=True),
sa.Column('order_name_kana', sa.TEXT(), autoincrement=False, nullable=True),
sa.Column('order_zip', sa.TEXT(), autoincrement=False, nullable=True),
sa.Column('order_address', sa.TEXT(), autoincrement=False, nullable=True),
sa.Column('order_mail_address', sa.TEXT(), autoincrement=False, nullable=True),
sa.Column('order_phone_number', sa.TEXT(), autoincrement=False, nullable=True),
sa.Column('total', sa.INTEGER(), autoincrement=False, nullable=True),
sa.Column('status', sa.SMALLINT(), autoincrement=False, nullable=True),
sa.Column('insert_date', postgresql.TIMESTAMP(), autoincrement=False, nullable=True),
sa.Column('update_date', postgresql.TIMESTAMP(), autoincrement=False, nullable=True),
sa.PrimaryKeyConstraint('id', name='order_logs_pkey')
)
How nice! Now, try executing ```upgrade ()` `` of the migration script you created.
$ alembic upgrade head
A table has been created in the DB!
Try downgrading.
$ alembic downgrade -1
The table has disappeared from the DB! (Because it was empty one time ago)
$ alembic upgrade +1
It's done again! Too obedient! You can go against it about once!
After that, when changing the definition of the table, I will create a migration script with Alembic and play with the DB through it.
If you do another alembic revision --autogenerate after rewriting the table definition on the SQLAlchemy side, a migration script for the difference will be created automatically compared to the DB state. Uhyo ~! !!
So well, the things I do are almost the same,
alembic revision --autogenerate -m "hoge" `` instead of SQLAlchemy's `` `drop ()` create () Just follow the steps and `ʻalembic upgrade + 1```.I thought it would be good if I could fix it when I said it was bad.
alembic revision --autogenerateAfter issuing a new migration script in, if there is no such thing now, it seems that you can just delete the generated script. Loose.
If you find an error in the migration script after applying it to the DB, use alembic downgrade -1 to return the DB to the previous state, then fix the script and alembic upgrade +1 Deyosage. Loose.
Alembic didn't recognize the difference with the default values and foreign keys. This is a bit disappointing because I have to write the script by hand. By the way http://alembic.readthedocs.org/en/latest/tutorial.html#auto-generating-migrations Near the end of this is a list of what you can find as diffs, what you can't find, and what you can't find. Well, that kind of feeling.
For example, in the model definition, the default is the current time,
insert_date = Column(DateTime, server_default=func.now())
When I did it, the script I was about to make was like this.
sa.Column('insert_date', sa.DateTime(), server_default=func.now(), nullable=True),
This is bad, and if you inadvertently do this, func.now () will be evaluated on the fly and the default value will be fixed to the date you ran it, eg 2014/1/1 00:00:00 Become.
Now manually script
sa.Column('insert_date', sa.DateTime(), server_default=sa.func.now(), nullable=True),
I'm running it after rewriting it, but I'm not good at this ... Can anyone please tell me ...
The end
Recommended Posts