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

Alpha Camp 全端開發課程學習心得

-
我喜歡邏輯，在哲學系求學時期，我花最多時間的科目，是形式邏輯。但是我一直不明白這個我相對擅長的科目，到底能用在什麼地方。我的邏輯能力，並沒有好到足夠讓我走上學術研究的路，那麼，或許我可以試著寫程式？ 學生時期的我，自認沒有錢，但是有時間。捨不得花錢去上程式設計課程，寧願自己找資源學習。去上過一些免費的程式入門課程，c++ 和 python，對我而言不是很難，但是學完之後，我還是看不出來，要怎麼靠學到的東西解決生活中遇到的問題，漸漸地就沒有動力繼續學下去。 直到出社會，存了點錢，也漸漸體會到時間的重要，終於捨得花錢投資自己，到 Alpha Camp 上專業的課程，而不是無頭蒼蠅自己亂找一通，省下許多時間，也看到自己學的東西是真的能用的，讓我有動力繼續學下去。 我還記得學到串接 api 的時候，心裡真是太激動了。以前在學免費課程時，就隱約有種「我寫的程式，範圍只侷限在我的電腦上」的貧瘠感覺，而在知道了 api 後，發現「我寫的程式可以跟世界接上線了」，頓時覺得自己非常富有。不過，後來才知道，串別人的 api 服務通常是要付費的。 大概是受了形式邏輯的影響，我在學習程式語言時，總是試圖去辨認，哪些東西是比較基本的，基本的東西是透過什麼樣的規則組合出複雜的東西。例如，變數、array、for 迴圈和 function 是基本的東西，可以用這些基本的東西組合出 array 的 reduce 方法，像這樣： function mimicReduce(array, reducerFunction, initialValue) {     if (initialValue) {         let accumulator = initialValue         for (let i = 0; i < array.length; i++) {             accumulator = reducerFunction(accumulator, array[i])         }         return accumulator     } else {         let accumulator = array[0]         for (let i = 1; i < array.length; i
Read more

在 javascript 用 regular expression 為金額加上千位數分隔符號

-
 為了方便使用者快速判斷金額，所以想加上千位數分隔符號。例如： 3000 --> 3,000 3000000 --> 3,000,000 搜尋資料後，結論： 如果數字有小數點的話，可以用  const amount = '3000.00' amount.replace(/\B(?<!\.\d*)(?=(\d{3})+(?!\d))/g, ',') 參考資料： Javascript之數值千分位符號（Comma）的顯示 如果沒有小數點的話，可以用  const amount = '3000' amount.replace(/\B(?=(\d{3})+(?!\d))/g, ',') 參考資料： [Javascrpt]轉換數字成含千分位的文字   不過這個鬼畫符的語法我完全看不懂，查資料的時候一度不肯相信這是真正的程式碼呢⋯⋯因緣際會在讀書會中，得到 Helix 同學提示的關鍵字「 regular expression」，決定試著弄懂，「amount.replace(/\B(?=(\d{3})+(?!\d))/g, ',')」到底是什麼意思。 一、 String.prototype.replace() - MDN 範例：我不小心把 world 拼成 word，想要把句子裡所有的 word 都替換成  world。 const text = 'Hello word! Hello word!' console.log(text.replace('word', 'world')) 結果印出 Hello world! Hello word! 很不幸地，只有第一個錯字有替換，其他錯字都被放生了。如果想要替換全部的 word，就要這樣寫： const text = 'Hello word! Hello word!' console.log(text.replace( /word/ g , ' world '))
Read more

shop_platform - sqlalchemy.exc.TimeoutError

-
發生的問題 啟動伺服器後，第二十一次請求「需要查詢資料庫」的頁面時，會出現「sqlalchemy.exc.TimeoutError: QueuePool limit of size 10 overflow 10 reached, connection timed out, timeout 30.00 (Background on this error at: https://sqlalche.me/e/14/3o7r )」的錯誤訊息。如果請求是不需要查詢資料庫的（例如靜態檔案或前往登入頁面），就不會發生此錯誤。 把 size 和 overflow 設定成更小的數字，可以更快重現這個錯誤 。 感覺起來，就像查詢完資料庫以後，沒有把 connection 釋出一樣，導致「size + overflow」是多少，就只能查詢多少次資料庫。 發現瀏覽器每向伺服器請求一次「需要查詢資料庫」的頁面，在 MySQL Workbench 用 show PROCESSLIST; 指令的查詢結果就會多出一筆資料，筆數達到 size + overflow 時，若再次請求「需要查詢資料庫」的頁面，就會噴 QueuePool limit of size 10 overflow 10 reached 的錯誤。關閉伺服器時，那幾筆資料才會被刪除。 嘗試過但失敗的方法 app.run(debug=False) 自己註冊一個關閉 session 的函式 @app.teardown_appcontext # 改成 @app.teardown_request 也沒用 def teardown_db(exception): db.session.remove() # 改成 db.session.close() 也沒用 print('***** session.remove') # 這一行有印出來，所以此函式有被執行 把 FLASK_ENV=development 拿掉 ，於是 FLASK_ENV 變成預設的 production app.config["SQLALCHEMY_COMMIT_ON_TEARDOWN"] = True 終於成功的方法 第一種方法：用 Ap
Read more