{"id":5557,"date":"2021-06-30T18:46:40","date_gmt":"2021-06-30T09:46:40","guid":{"rendered":"https:\/\/www.sria.co.jp\/blog\/?p=5557"},"modified":"2021-06-30T18:46:40","modified_gmt":"2021-06-30T09:46:40","slug":"5557","status":"publish","type":"post","link":"https:\/\/www.sria.co.jp\/blog\/2021\/06\/5557\/","title":{"rendered":"SQLAlchemy\u306e\u30de\u30a4\u30b0\u30ec\u30fc\u30b7\u30e7\u30f3\u30c4\u30fc\u30eb Alembic\u3092\u5c0e\u5165\u3057\u3066\u52b9\u7387\u7684\u306b\u30e2\u30c7\u30eb\u3092\u7ba1\u7406\u3057\u3088\u3046"},"content":{"rendered":"<p>SQLAlchemy \u3092\u7528\u3044\u305f\u969b\u306b Alembic \u3068\u3044\u3046\u30de\u30a4\u30b0\u30ec\u30fc\u30b7\u30e7\u30f3\u30c4\u30fc\u30eb\u3092\u4f7f\u3063\u305f\u306e\u3067\u3001\u5c0e\u5165\u65b9\u6cd5\u3092\u7d39\u4ecb\u3057\u307e\u3059\u3002<\/p>\n<p>SQLAlchemy\u3067\u306f\u30b3\u30de\u30f3\u30c9\u3067\u306e\u64cd\u4f5c\u3084Alter Table\u306a\u3069\u306f\u884c\u3048\u307e\u305b\u3093\u304c\u3001<\/p>\n<p>Alembic\u3067\u306f\u30b3\u30de\u30f3\u30c9\u3092\u7528\u3044\u305f\u30b9\u30ad\u30fc\u30de\u306e\u64cd\u4f5c\u3084Alter Table\u3092\u884c\u3046\u3053\u3068\u304c\u3067\u304d\u3001\u4fbf\u5229\u3067\u3059\u3002<\/p>\n<h2>Alembic \u3068\u306f<\/h2>\n<p>SQLAlchemy \u3092\u4f7f\u7528\u3057\u305f \u30c7\u30fc\u30bf\u30d9\u30fc\u30b9migration\u30e9\u30a4\u30d6\u30e9\u30ea\u3067\u3059\u3002<\/p>\n<h2>\u74b0\u5883<\/h2>\n<h4 class=\"p1\"><span class=\"s1\"><b>pipenv<\/b><\/span><span class=\"s2\">, version 2021.5.29<\/span><\/h4>\n<h4>python 3.9.5<\/h4>\n<h2>\u624b\u9806<\/h2>\n<ol>\n<li>\u5fc5\u8981\u306a\u30e9\u30a4\u30d6\u30e9\u30ea\u3092\u30a4\u30f3\u30b9\u30c8\u30fc\u30eb<\/li>\n<li>alembic\u74b0\u5883\u3092\u4f5c\u6210<\/p>\n<\/li>\n<li>\n<p>SQLAlchemy\u306e\u30e2\u30c7\u30eb\u3092\u4f5c\u6210<\/p>\n<\/li>\n<li>migration \u30b9\u30af\u30ea\u30d7\u30c8\u30d5\u30a1\u30a4\u30eb\u3092SQLAlchemy\u306e\u30e2\u30c7\u30eb\u304b\u3089\u81ea\u52d5\u3067\u751f\u6210<\/li>\n<\/ol>\n<h2>1. \u5fc5\u8981\u306a\u30e9\u30a4\u30d6\u30e9\u30ea\u3092\u30a4\u30f3\u30b9\u30c8\u30fc\u30eb<\/h2>\n<p>\u307e\u305a\u306f\u4e0b\u8a18\u30e9\u30a4\u30d6\u30e9\u30ea\u3092\u30a4\u30f3\u30b9\u30c8\u30fc\u30eb\u3057\u307e\u3059\u3002<\/p>\n<div class=\"hcb_wrap\">\n<pre class=\"prism line-numbers lang-plain\" data-lang=\"Plain Text\"><code>pipenv install alembic sqlalchemy pymysql<\/code><\/pre>\n<\/div>\n<p>pymysql\u306f\u30c9\u30e9\u30a4\u30d0\u30fc\u306b\u8a2d\u5b9a\u3059\u308b\u305f\u3081\u306b\u30a4\u30f3\u30b9\u30c8\u30fc\u30eb\u3057\u307e\u3059\u3002<\/p>\n<h2>2. alembic\u74b0\u5883\u3092\u4f5c\u6210<\/h2>\n<p>\u4e0b\u8a18\u30b3\u30de\u30f3\u30c9\u3092\u5b9f\u884c\u3059\u308b\u3068alembic.ini\u30d5\u30a1\u30a4\u30eb\u3068alembic\u30c7\u30a3\u30ec\u30af\u30c8\u30ea\u914d\u4e0b\u306b\u30d5\u30a1\u30a4\u30eb\u3068\u30c7\u30a3\u30ec\u30af\u30c8\u30ea\u304c\u751f\u6210\u3055\u308c\u307e\u3059\u3002<\/p>\n<div class=\"hcb_wrap\">\n<pre class=\"prism line-numbers lang-plain\" data-lang=\"Plain Text\"><code>alembic init alembic_sample<\/code><\/pre>\n<\/div>\n<h4><strong>alembic.ini<\/strong><\/h4>\n<p>alembic \u30b9\u30af\u30ea\u30d7\u30c8\u304c\u547c\u3073\u51fa\u3055\u308c\u305f\u6642\u306b\u691c\u7d22\u3055\u308c\u308b\u30d5\u30a1\u30a4\u30eb\u3067\u3059\u3002<\/p>\n<p>&nbsp;<\/p>\n<h4><strong>env.py<\/strong><\/h4>\n<p>\u3053\u306e\u30d5\u30a1\u30a4\u30eb\u306f alembic \u30b9\u30af\u30ea\u30d7\u30c8\u304c\u547c\u3073\u51fa\u3055\u308c\u305f\u6642\u306b\u8d77\u52d5\u3059\u308b Python \u30b9\u30af\u30ea\u30d7\u30c8\u3067\u3059\u3002<br \/>\nmigration\u3059\u308b\u305f\u3081\u306eSQLAlchemy \u306eEngine \u306a\u3069\u306e\u8a2d\u5b9a\u304c\u66f8\u304b\u308c\u3066\u3044\u307e\u3059\u3002<\/p>\n<p>&nbsp;<\/p>\n<h4><strong>script.py.mako<\/strong><\/h4>\n<p>versions\u914d\u4e0b\u306b\u751f\u6210\u3055\u308c\u308b migration \u306e\u30b9\u30af\u30ea\u30d7\u30c8\u306e Mako \u30c6\u30f3\u30d7\u30ec\u30fc\u30c8\u30d5\u30a1\u30a4\u30eb\u3067\u3059\u3002<\/p>\n<p>&nbsp;<\/p>\n<h4><strong>versions\/<\/strong><\/h4>\n<p>\u3053\u306e\u30c7\u30a3\u30ec\u30af\u30c8\u30ea\u914d\u4e0b\u306bmigration\u30d5\u30a1\u30a4\u30eb\u304c\u751f\u6210\u3055\u308c\u307e\u3059\u3002<\/p>\n<h2>3. DB\u306e\u8a2d\u5b9a\u3092\u8a18\u8ff0\u3057\u305fsetting.py\u3092\u4f5c\u6210<\/h2>\n<p>\u3053\u3053\u306bDB\u306e\u8a2d\u5b9a\u3092\u8a18\u8ff0\u3057\u3001Engine\u3068Base\u3092\u5b9a\u7fa9\u3057\u307e\u3059\u3002<\/p>\n<p><strong><code>setting.py<\/code><\/strong><\/p>\n<div class=\"hcb_wrap\">\n<pre class=\"prism line-numbers lang-python\" data-lang=\"Python\"><code>from sqlalchemy import create_engine\nfrom sqlalchemy.ext.declarative import declarative_base\n\n# Engine \u306e\u4f5c\u6210\nEngine = create_engine(\n  \"mysql+pymysql:\/\/root:@127.0.0.1:3306\/alembic_sample\",\n  encoding=\"utf-8\",\n  echo=False\n)\nBase = declarative_base()<\/code><\/pre>\n<\/div>\n<h2>4. SQLAlchemy\u306e\u30e2\u30c7\u30eb\u3092\u4f5c\u6210<\/h2>\n<p>setting.py\u3067\u5b9a\u7fa9\u3057\u305fBase\u3092\u4f7f\u7528\u3057\u3001UserModel\u3092\u4f5c\u6210\u3057\u307e\u3059\u3002<\/p>\n<p>&nbsp;<\/p>\n<h4><strong><code>user.py<\/code><\/strong><\/h4>\n<div class=\"hcb_wrap\">\n<pre class=\"prism line-numbers lang-python\" data-lang=\"Python\"><code>from sqlalchemy import Column, String, Integer\nfrom setting import Base\n\nclass UserModel(Base):\n  __tablename__ = 'users'\n\n  id = Column(Integer, primary_key=True)\n  name = Column(String(100), nullable=False)\n)<\/code><\/pre>\n<\/div>\n<h2>5. migration \u30b9\u30af\u30ea\u30d7\u30c8\u30d5\u30a1\u30a4\u30eb\u3092SQLAlchemy\u306e\u30e2\u30c7\u30eb\u304b\u3089\u81ea\u52d5\u3067\u751f\u6210<\/h2>\n<h3>5-1. alembic.ini\u306eDB\u306eURL\u3092\u5909\u66f4<\/h3>\n<h4><strong>alembic.ini<\/strong><\/h4>\n<div class=\"hcb_wrap\">\n<pre class=\"prism line-numbers lang-plain\" data-lang=\"Plain Text\"><code>sqlalchemy.url = driver:\/\/user:pass@localhost\/dbname<\/code><\/pre>\n<\/div>\n<p>\u2193<\/p>\n<div class=\"hcb_wrap\">\n<pre class=\"prism line-numbers lang-plain\" data-lang=\"Plain Text\"><code>sqlalchemy.url = mysql+pymysql:\/\/root:@localhost\/alembic_sample<\/code><\/pre>\n<\/div>\n<p>\u203bDatabase\u306f\u5404\u81ea\u306e\u74b0\u5883\u306b\u5408\u308f\u305b\u3066\u8a2d\u5b9a\u3057\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n<h3>5-2. env.py\u306bSQLAlchemy\u306e\u30e2\u30c7\u30eb\u3092\u8aad\u307f\u8fbc\u3080\u3088\u3046\u306b\u8a2d\u5b9a<\/h3>\n<h4>env.py\u306bsetting.py\u3067\u5b9a\u7fa9\u3057\u305fBase\u3092import\u3057\u3001metadata\u306b\u8a2d\u5b9a\u3057\u307e\u3059\u3002<br \/>\nUserModel \u306f\u4f7f\u3044\u307e\u305b\u3093\u304c \u3001import \u3057\u306a\u3044\u3068 Base\u306emetadata\u306b\u30e2\u30c7\u30eb\u304c\u767b\u9332\u3055\u308c\u305a\u3001<\/h4>\n<h4>alembic\u3067migration\u30b9\u30af\u30ea\u30d7\u30c8\u30d5\u30a1\u30a4\u30eb\u3092\u4f5c\u6210\u3057\u305f\u969b\u306b\u8a8d\u8b58\u3055\u308c\u306a\u3044\u306e\u3067\u6ce8\u610f\u304c\u5fc5\u8981\u3067\u3059\u3002<\/h4>\n<p>&nbsp;<\/p>\n<p><strong>env.py<\/strong><\/p>\n<div class=\"hcb_wrap\">\n<pre class=\"prism line-numbers lang-python\" data-lang=\"Python\"><code>from logging.config import fileConfig\n\nfrom sqlalchemy import engine_from_config\nfrom sqlalchemy import pool\n\nfrom alembic import context\n# \u8ffd\u52a0\nfrom setting import Base\n# \u8ffd\u52a0\nfrom user import UserModel\n\n# this is the Alembic Config object, which provides\n# access to the values within the .ini file in use.\nconfig = context.config\n\n# Interpret the config file for Python logging.\n# This line sets up loggers basically.\nfileConfig(config.config_file_name)\n\n# add your model's MetaData object here\n# for 'autogenerate' support\n# from myapp import mymodel\n# target_metadata = mymodel.Base.metadata\n# \u8ffd\u52a0\ntarget_metadata = Base.metadata\n\n# other values from the config, defined by the needs of env.py,\n# can be acquired:\n# my_important_option = config.get_main_option(\"my_important_option\")\n# ... etc.\n\n\ndef run_migrations_offline():\n  \"\"\"Run migrations in 'offline' mode.\n\n  This configures the context with just a URL\n  and not an Engine, though an Engine is acceptable\n  here as well. By skipping the Engine creation\n  we don't even need a DBAPI to be available.\n\n  Calls to context.execute() here emit the given string to the\n  script output.\n\n  \"\"\"\n  url = config.get_main_option(\"sqlalchemy.url\")\n  context.configure(\n    url=url,\n    target_metadata=target_metadata,\n    literal_binds=True,\n    dialect_opts={\"paramstyle\": \"named\"},\n  )\n\nwith context.begin_transaction():\n  context.run_migrations()\n\n\ndef run_migrations_online():\n  \"\"\"Run migrations in 'online' mode.\n\n  In this scenario we need to create an Engine\n  and associate a connection with the context.\n\n  \"\"\"\n  connectable = engine_from_config(\n  config.get_section(config.config_ini_section),\n  prefix=\"sqlalchemy.\",\n  poolclass=pool.NullPool,\n  )\n  # \u8ffd\u52a0\n  url = config.get_main_option(\"sqlalchemy.url\")\n\n  with connectable.connect() as connection:\n    context.configure(\n    # \u8ffd\u52a0\n    url=url,\n    connection=connection, target_metadata=target_metadata\n  )\n\n  with context.begin_transaction():\n    context.run_migrations()\n\n\nif context.is_offline_mode():\n  run_migrations_offline()\nelse:\n  run_migrations_online()<\/code><\/pre>\n<\/div>\n<h3>5-3. alembic revision <span>&#8211;<\/span><span>&#8211;<\/span>autogenerate\u30b3\u30de\u30f3\u30c9\u3092\u5b9f\u884c<code><\/code><\/h3>\n<div class=\"hcb_wrap\">\n<pre class=\"prism line-numbers lang-plain\" data-lang=\"Plain Text\"><code>$ alembic revision --autogenerate -m \"create tables\"\nINFO [alembic.runtime.migration] Context impl MySQLImpl.\nINFO [alembic.runtime.migration] Will assume non-transactional DDL.\nINFO [alembic.autogenerate.compare] Detected added table 'users'\nGenerating \/Users\/tsujishougo\/PycharmProjects\/alembic_intro\/alembic_sample\/versions\/3a2cc9966eae_create_tables.py ... done<\/code><\/pre>\n<\/div>\n<p>versions\/\u914d\u4e0b\u306b\u4e0b\u8a18\u306emigration\u30b9\u30af\u30ea\u30d7\u30c8\u30d5\u30a1\u30a4\u30eb\u304c\u3067\u304d\u307e\u3059\u3002<\/p>\n<div class=\"hcb_wrap\">\n<pre class=\"prism line-numbers lang-python\" data-lang=\"Python\"><code>\"\"\"create tables\n\nRevision ID: 3a2cc9966eae\nRevises:\nCreate Date: 2021-06-26 18:41:25.143285\n\n\"\"\"\nfrom alembic import op\nimport sqlalchemy as sa\n\n# revision identifiers, used by Alembic.\nrevision = '3a2cc9966eae'\ndown_revision = None\nbranch_labels = None\ndepends_on = None\n\ndef upgrade():\n# ### commands auto generated by Alembic - please adjust! ###\n  op.create_table('users',\n  sa.Column('id', sa.Integer(), nullable=False),\n  sa.Column('name', sa.String(length=100), nullable=False),\n  sa.PrimaryKeyConstraint('id')\n)\n# ### end Alembic commands ###\n\ndef downgrade():\n  # ### commands auto generated by Alembic - please adjust! ###\n  op.drop_table('users')\n# ### end Alembic commands ###<\/code><\/pre>\n<\/div>\n<h2>6. DB\u306e\u30c6\u30fc\u30d6\u30eb\u306bSQLAlchemy\u306e\u30e2\u30c7\u30eb\u3092\u53cd\u6620<\/h2>\n<h3>6-1. alembic upgrade head\u3092\u5b9f\u884c<\/h3>\n<p>\u6700\u5f8c\u306b<strong>alembic upgrade head<\/strong>\u3092\u5b9f\u884c\u3059\u308b\u3068DB\u306bSQLAlchemy\u306e\u30e2\u30c7\u30eb\u304c\u30c6\u30fc\u30d6\u30eb\u3068\u3057\u3066\u53cd\u6620\u3055\u308c\u307e\u3059\u3002<code><\/code><\/p>\n<div class=\"hcb_wrap\">\n<pre class=\"prism line-numbers lang-plain\" data-lang=\"Plain Text\"><code>$ alembic upgrade head\nINFO [alembic.runtime.migration] Context impl MySQLImpl.\nINFO [alembic.runtime.migration] Will assume non-transactional DDL.\nINFO [alembic.runtime.migration] Running upgrade -&gt; 3a2cc9966eae, create tables<\/code><\/pre>\n<\/div>\n<h2>\u7d42\u308f\u308a\u306b<\/h2>\n<p>SQLAlchemy\u3060\u3051\u3067\u306f\u3001Alter Table\u306a\u3069\u3067\u304d\u306a\u3044\u306e\u3067\u3001Alembic\u3092\u5c0e\u5165\u3059\u308b\u3068\u3088\u308a\u52b9\u7387\u7684\u306b\u30b9\u30ad\u30fc\u30de\u306e\u64cd\u4f5c\u304c\u3067\u304d\u308b\u3088\u3046\u306b\u306a\u308a\u307e\u3057\u305f\u3002\u4eca\u5f8c\u3001\u305c\u3072SQLAlchemy\u3092\u4f7f\u3046\u969b\u306f\u53d6\u308a\u5165\u308c\u3066\u3044\u304d\u305f\u3044\u3067\u3059\u3002<\/p>\n<h2>\u53c2\u8003<\/h2>\n<p>https:\/\/alembic.sqlalchemy.org\/en\/latest\/index.html<\/p>\n","protected":false},"excerpt":{"rendered":"<p>SQLAlchemy \u3092\u7528\u3044\u305f\u969b\u306b Alembic \u3068\u3044\u3046\u30de\u30a4\u30b0\u30ec\u30fc\u30b7\u30e7\u30f3\u30c4\u30fc\u30eb\u3092\u4f7f\u3063\u305f\u306e\u3067\u3001\u5c0e\u5165\u65b9\u6cd5\u3092\u7d39\u4ecb\u3057\u307e\u3059\u3002 SQLAlchemy\u3067\u306f\u30b3\u30de\u30f3\u30c9\u3067\u306e\u64cd\u4f5c\u3084Alter Table\u306a\u3069\u306f\u884c\u3048\u307e\u305b\u3093\u304c\u3001 Alembic\u3067&#8230;<\/p>\n","protected":false},"author":5,"featured_media":5156,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[8],"tags":[247,245,246],"_links":{"self":[{"href":"https:\/\/www.sria.co.jp\/blog\/wp-json\/wp\/v2\/posts\/5557"}],"collection":[{"href":"https:\/\/www.sria.co.jp\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sria.co.jp\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sria.co.jp\/blog\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sria.co.jp\/blog\/wp-json\/wp\/v2\/comments?post=5557"}],"version-history":[{"count":78,"href":"https:\/\/www.sria.co.jp\/blog\/wp-json\/wp\/v2\/posts\/5557\/revisions"}],"predecessor-version":[{"id":5672,"href":"https:\/\/www.sria.co.jp\/blog\/wp-json\/wp\/v2\/posts\/5557\/revisions\/5672"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sria.co.jp\/blog\/wp-json\/wp\/v2\/media\/5156"}],"wp:attachment":[{"href":"https:\/\/www.sria.co.jp\/blog\/wp-json\/wp\/v2\/media?parent=5557"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sria.co.jp\/blog\/wp-json\/wp\/v2\/categories?post=5557"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sria.co.jp\/blog\/wp-json\/wp\/v2\/tags?post=5557"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}