shop_platform - flask db downgrade 遇到 TypeError

-

事發經過

發現建立 cart table 時，漏掉 user_id 這個 foreign key，所以幫 user 和 cart 兩張資料表補上 one-one relationship。

class User(db.Model):
    __tablename__ = 'user'

    id = db.Column(db.Integer, primary_key=True)
    display_name = db.Column(db.String(50), unique=True, nullable=False)
    email = db.Column(db.String(50), unique=True, nullable=False)
    password = db.Column(db.String(140), nullable=False)
    cell_phone = db.Column(db.String(20))
    address = db.Column(db.String(100))
    store_introduction = db.Column(db.String(2000))
    role = db.Column(db.String(10), nullable=False, default='user')
    insert_time = db.Column(db.DateTime, nullable=False, default=datetime.now)
    update_time = db.Column(db.DateTime, onupdate=datetime.now, nullable=False, default=datetime.now)

    cart = db.relationship('Cart', back_populates='user', uselist=False)
    delivery_orders = db.relationship('Order', back_populates='seller')
    purchase_order = db.relationship('Order', back_populates='buyer')

class Cart(db.Model):
    __tablename__ = 'cart'

    id = db.Column(db.Integer, primary_key=True)
    insert_time = db.Column(db.DateTime, nullable=False, default=datetime.now)
    update_time = db.Column(db.DateTime, onupdate=datetime.now, nullable=False, default=datetime.now)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

    user = db.relationship('User', back_populates='cart')
    products = db.relationship('CartItem', back_populates='cart')

下了 flask db migrate -m "<migration name>" 指令後產生的 migration 檔：

"""add one-one relationship between cart and user table

Revision ID: 5b69bc8db7e7
Revises: cd6dce4eb1ee
Create Date: 2021-08-26 16:20:18.947078

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '5b69bc8db7e7'
down_revision = 'cd6dce4eb1ee'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('cart', sa.Column('user_id', sa.Integer(), nullable=True))
    op.create_foreign_key(None, 'cart', 'user', ['user_id'], ['id'])
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint(None, 'cart', type_='foreignkey')
    op.drop_column('cart', 'user_id')
    # ### end Alembic commands ###

下 flask db upgrade 指令時沒有問題，順利地在 cart table 加上 user_id 欄位，並且設了外鍵限制。

下 flask db migrate -m "<migration name>" 指令時出現錯誤訊息如下：

INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running downgrade 5b69bc8db7e7 -> cd6dce4eb1ee, add one-one relationship between cart and user table
Traceback (most recent call last):
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/bin/flask", line 8, in <module>
    sys.exit(main())
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/flask/cli.py", line 990, in main
    cli.main(args=sys.argv[1:])
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/flask/cli.py", line 596, in main
    return super().main(*args, **kwargs)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/click/core.py", line 1062, in main
    rv = self.invoke(ctx)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/click/core.py", line 1668, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/click/core.py", line 1668, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/click/core.py", line 763, in invoke
    return __callback(*args, **kwargs)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/click/decorators.py", line 26, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/flask/cli.py", line 440, in decorator
    return __ctx.invoke(f, *args, **kwargs)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/click/core.py", line 763, in invoke
    return __callback(*args, **kwargs)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/flask_migrate/cli.py", line 167, in downgrade
    _downgrade(directory, revision, sql, tag, x_arg)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/flask_migrate/__init__.py", line 98, in wrapped
    f(*args, **kwargs)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/flask_migrate/__init__.py", line 195, in downgrade
    command.downgrade(config, revision, sql=sql, tag=tag)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/alembic/command.py", line 335, in downgrade
    script.run_env()
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/alembic/script/base.py", line 490, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 97, in load_python_file
    module = load_module_py(module_id, path)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/alembic/util/compat.py", line 184, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 850, in exec_module
  File "<frozen importlib._bootstrap>", line 228, in _call_with_frames_removed
  File "migrations/env.py", line 91, in <module>
    run_migrations_online()
  File "migrations/env.py", line 85, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/alembic/runtime/environment.py", line 813, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/alembic/runtime/migration.py", line 561, in run_migrations
    step.migration_fn(**kw)
  File "/Users/flora/projects/shop_platform/migrations/versions/5b69bc8db7e7_add_one_one_relationship_between_cart_.py", line 28, in downgrade
    op.drop_constraint(None, 'cart', type_='foreignkey')
  File "<string>", line 8, in drop_constraint
  File "<string>", line 3, in drop_constraint
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/alembic/operations/ops.py", line 150, in drop_constraint
    return operations.invoke(op)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/alembic/operations/base.py", line 354, in invoke
    return fn(self, operation)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/alembic/operations/toimpl.py", line 161, in drop_constraint
    operations.impl.drop_constraint(
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/alembic/ddl/mysql.py", line 133, in drop_constraint
    super(MySQLImpl, self).drop_constraint(const)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/alembic/ddl/impl.py", line 266, in drop_constraint
    self._exec(schema.DropConstraint(const))
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/alembic/ddl/impl.py", line 146, in _exec
    return conn.execute(construct, multiparams)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1263, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py", line 77, in _execute_on_connection
    return connection._execute_ddl(
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1350, in _execute_ddl
    compiled = ddl.compile(
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 489, in compile
    return self._compiler(dialect, **kw)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py", line 29, in _compiler
    return dialect.ddl_compiler(dialect, self, **kw)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 454, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 489, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/ext/compiler.py", line 443, in <lambda>
    lambda *arg, **kw: existing(*arg, **kw),
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/ext/compiler.py", line 499, in __call__
    expr = fn(element, compiler, **kw)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/alembic/ddl/mysql.py", line 397, in _mysql_drop_constraint
    return compiler.visit_drop_constraint(element, **kw)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/dialects/mysql/base.py", line 2201, in visit_drop_constraint
    const = self.preparer.format_constraint(constraint)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 5095, in format_constraint
    return self.truncate_and_render_constraint_name(
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 5119, in truncate_and_render_constraint_name
    return self._truncate_and_render_maxlen_name(
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 5128, in _truncate_and_render_maxlen_name
    self.dialect.validate_identifier(name)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 576, in validate_identifier
    if len(ident) > self.max_identifier_length:
TypeError: object of type 'NoneType' has no len()

解決辦法

用「TypeError: object of type 'NoneType' has no len() flask-migrate」當關鍵字，搜尋到 drop_constraint etc. should check for name=None and emit informative error message since this is common w/ autogenerate #588 這篇文章。看樣子，是因為呼叫 drop_constraint 時要輸入 constraint 的 name。

在 MySQLWorkbench 裡查到，自動生成的 constraint name 叫做 cart_ibfk_1，然後去修改 migration 檔：

"""add one-one relationship between cart and user table

Revision ID: 5b69bc8db7e7
Revises: cd6dce4eb1ee
Create Date: 2021-08-26 16:20:18.947078

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '5b69bc8db7e7'
down_revision = 'cd6dce4eb1ee'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('cart', sa.Column('user_id', sa.Integer(), nullable=True))
    op.create_foreign_key('cart_ibfk_1', 'cart', 'user', ['user_id'], ['id'])
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint('cart_ibfk_1', 'cart', type_='foreignkey')
    op.drop_column('cart', 'user_id')
    # ### end Alembic commands ###

然後就沒事了。

commit: feat: add one-one relationship between user and cart table

參考資料

Comments

Post a Comment

Popular posts from this blog

資料關聯

-
以飲料店的商品種類為例，假設我們現在有飲料和杯子兩種資料。 飲料： id name 1 綠茶 2 紅茶 3 青草茶 杯子： id size 1 500 2 600 3 700 一對一（One to One） 若飲料和杯子是一對一的關係，那麼情況就類似這樣： 綠茶只用 500 cc 的杯子裝 紅茶只用 600 cc 的杯子裝 青草茶只用 700 cc 的杯子裝 一種飲料只有一個尺寸的杯子，而且一個尺寸的杯子也只對應到一種飲料。在這種情況下要建立關聯，有下列兩種方式，擇一即可。這兩種做法在技術上效果是一樣的，不過語意解讀上可能不太一樣。 把關聯欄位加在飲料（語意上，是飲料有杯子這個屬性） 把關聯欄位加在杯子（語意上，是杯子有飲料這個屬性） 把關聯欄位加在飲料的資料表： id name cup_id 1 綠茶 1 2 紅茶 2 3 青草茶 3 把關聯欄位加在杯子的資料表： id size drink_id 1 500 1 2 600 2 3 700 3 一對多（One to Many） 若飲料和杯子是一對多的關係，那麼情況就類似這樣： 綠茶用 500 cc、700 cc 的杯子裝 紅茶只用 600 cc 的杯子裝 一種飲料有許多尺寸的杯子，而且一個尺寸的杯子只對應到一種飲料。 或者是這樣： 綠茶、紅茶、青草茶都只用 500 cc 的杯子裝 一個尺寸的杯子對應到許多種飲料，而且一種飲料只有一個尺寸的杯子。 在這種情況下要建立關聯，一般會選擇加在多的那一邊。如果加在少的那一
Read more

程式設計相關社群、活動

-
網頁開發 網頁開發雜記 PJCHENder網頁開發咩腳 Backend 台灣 (Backend Tw) Taiwan Backend Group Backend.tw (友善版) Google Chrome Developers 語言 Python Taiwan JavaScript.tw Kotlin： Kraftsman：Coding 職人塾 資安 網路攻防戰 資安解壓縮 Infosec Decompress DEVCORE 如何成為駭客 (資安研討會、社群與網路資源篇) Cymetrics Tech Blog （有胡立大大） 台灣電腦網路危機處理暨協調中心 UCCU Hacker HITCON GIRLS 資安人科技網 個人或公司 資料科學家的工作日常 Ant Yi-Feng Tzeng 偷米騎巴哥 John Liu 的軟體工程思維 Will 保哥的技術交流中心 新加坡商鈦坦科技 Line 的技術部落格 區塊鏈李婷婷 、 FB 、 line 群 管理 TGONetworks 91 敏捷開發之路 技術管理者交流社團 課程 軟體開發學習資訊分享 Udemy 限時免費課程 cs50 中文討論區 Lidemy 鋰學院 商業思維學院 未分類 NEX Foundation 台灣未來基金會 iThome FB Daodu Tech 科技島讀 ∞ Lab 無限大實驗室．金融科技第一站 DevOps Taiwan Chatbot Developers Taiwan 軟體開發的疑難雜症交流區 年會 Hacks In Taiwan 台灣駭客年會 FB 台灣資安大會 cybersec 2021 開源人年會 COSCUP 2021 Python 年會 PyCon 2021 ModernWeb 開發者年會 2021 Javascript 開發者年會 FB: JSDC Taiwan 敏捷年會 Agile Summit 20
Read more

TCP/ IP 通訊協定

-
為什麼要有通訊協定 不同機器之間，若要能夠溝通的話，需要大家都使用相同的語言。所有人都遵守通訊協定，就不會出現語言隔閡。 什麼是 TCP/ IP 通訊協定 要實現「不同機器之間能夠溝通」，從硬體到軟體，有許多工作要完成。為了讓這份工作比較容易進行，所以將工作拆分成好幾個部分，各自獨立完成；以後若要維護的話，哪個部分有問題，只要維修那個部分就好，不必把所有部分都拿出來檢查，降低維修難度。 至於工作到底要怎麼拆分，不同的模型有不同的建議。OSI（Open System Interconnection）模型切成七層工作，DoD（Department of Defense Model）模型則是切成四層，每一層都有不同的通訊協定。雖然目前實作上通常不是採用 OSI 模型，但為了方便理解「不同機器之間能夠溝通」的機制，通常會用 OSI 模型講解，也會試著將 DoD 模型對應到 OSI 模型。 DoD 模型中有許多通訊協定，不過以 TCP、IP 這兩個通訊協定最具有代表性，所以 DoD 模型又稱為 TCP/ IP 模型。TCP 協定對應到 OSI 模型中的傳送層，IP 協定則是對應到網路層。 TCP、IP 通訊協定負責什麼工作 TCP 協定 發送端的 TCP，和接收端的 TCP，會先進行三向交握，確認彼此能夠順利傳輸資料，才正式開始傳輸資料。 我們可以把要傳輸的資料想像成要郵寄的包裹，郵寄時，包裹會有尺寸或重量的限制，同樣地，傳輸的資料也會有大小的限制。如果資料太大了，TCP 會拆分成小包裹，並且幫每個小包裹編號（以便接收端最後能把小包裹還原成大包裹），並注意是否每一個小包裹都成功送達，若沒有成功送達，TCP 會再補上缺少的小包裹。如果資料太小了，TCP 會幫包裹灌水，以符合大小的下限。 IP 協定 IP 位址就像收件地址，以便確認要將包裹送去哪個主機。IP 位址目前有兩種版本，IPv4 用 32 位元儲存地址，IPv6 用 128 位元儲存地址。所以 IPv6 有比較多的地址可以分配給不同的主機。 IPv4 以 IPv4 而言，位址可以切成兩個部分，第一部分代表 Net ID，第二部分代表 Host ID。相同的 Net ID，代表主機位在相同的 IP 網段，相同 IP 網段的主機之間可以用廣播的方式傳遞資料，而不必透過路由。可以從 N
Read more