In the middle of development, we will introduce Alembic

I want to manage the version of the DB schema

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

Easy to introduce even during development

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.

Automatic generation of migration script

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!

You can continue to feel the same after this

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,

  1. Tweak the SQLAclhemy table definition
  2. When reflecting to DB, Alembic's 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.

Other things I noticed using

It's okay to issue the table definition by mistake because it's loose

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.

It's okay because it's loose even if you issue it wrong and apply it

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.

There is a limit to automatic generation

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.

I feel that the function is not converted well

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

In the middle of development, we will introduce Alembic
Django + MongoDB development environment maintenance (in the middle of writing)
#We will automate the data aggregation of PES! part1
A story about trying to introduce Linter in the middle of a Python (Flask) project
[Tips] Problems and solutions in the development of python + kivy
Django cannot be installed in the development environment of pipenv + pyenv
The story of participating in AtCoder
Sphinx single html suppresses page breaks in the middle of the table
The story of the "hole" in the file
The meaning of ".object" in Django
Using TensorFlow in the cloud integrated development environment Cloud9 ~ Basics of usage ~
Guidelines for reincarnating in the world of linux programming development (C / C ++ language)
Since DataLiner 1.2.0 has been released, we will introduce the newly added preprocessing.
With the advent of systemd-homed in 2020, Linux user management will change dramatically.
We will implement the optimization algorithm (overview)
[Understanding in 3 minutes] The beginning of Linux
Check the behavior of destructor in Python
Algorithm Gymnastics 24 Middle of the Linked List
We will implement the optimization algorithm (firefly algorithm)
The result of installing python in Anaconda
Let's claim the possibility of pyenv-virtualenv in 2021
We will implement the optimization algorithm (bat algorithm)
The basics of running NoxPlayer in Python
We will implement the optimization algorithm (Problem)
Get the value of the middle layer of NN
In search of the fastest FizzBuzz in Python
We will implement the optimization algorithm (Kujira-san algorithm)
You will be an engineer in 100 days --Day 29 --Python --Basics of the Python language 5
You will be an engineer in 100 days --Day 33 --Python --Basics of the Python language 8
You will be an engineer in 100 days --Day 26 --Python --Basics of the Python language 3
To output a value even in the middle of a cell with Jupyter Notebook
You will be an engineer in 100 days --Day 32 --Python --Basics of the Python language 7
Now that I am conscious of the package system, I will introduce Hello World
You will be an engineer in 100 days --Day 28 --Python --Basics of the Python language 4
Output the number of CPU cores in Python
The meaning of {version-number} in the mysql rpm package
[Python] Sort the list of pathlib.Path in natural sort
Change the font size of the legend in df.plot
Match the distribution of each group in Python
View the result of geometry processing in Python
Instantiation of the BOX development environment created earlier
Make a copy of the list in Python
Find the number of days in a month
Read the output of subprocess.Popen in real time
Find the divisor of the value entered in python
The story of finding the optimal n in N fist
Fix the argument of the function used in map
About the development contents of machine learning (Example)
Find the solution of the nth-order equation in python
The story of reading HSPICE data in Python
[Note] About the role of underscore "_" in Python
Put the second axis in 2dhistgram of matplotlib
About the behavior of Model.get_or_create () of peewee in Python
Solving the equation of motion in Python (odeint)
Visualized the usage status of the sink in the company
Output in the form of a python array
The story of viewing media files in Django
Search by the value of the instance in the list
Make progress of dd visible in the progress bar
We will implement the optimization algorithm (cuckoo search)
Factfulness of the new coronavirus seen in Splunk