shop_platform - 新增種子資料時報錯

-

想完成的事

建立 user 的種子資料

遇到的問題，和解決過程

直接在 MySQL workbench 新增資料沒有問題，但是用 flask_seeder 新增資料會報錯。縮小範圍後，發現使用 flask_sqlalchemy 新增資料就會報錯。

錯誤訊息如下：

Traceback (most recent call last):
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 2744, in _determine_joins
    self.primaryjoin = join_condition(
  File "<string>", line 2, in join_condition
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/sql/selectable.py", line 1184, in _join_condition
    cls._joincond_trim_constraints(
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/sql/selectable.py", line 1305, in _joincond_trim_constraints
    raise exc.AmbiguousForeignKeysError(
sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 'user' and 'order'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Users/flora/projects/shop_platform/app.py", line 28, in <module>
    user = User(
  File "<string>", line 4, in __init__
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/orm/state.py", line 474, in _initialize_instance
    manager.dispatch.init(self, args, kwargs)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/event/attr.py", line 343, in __call__
    fn(*args, **kw)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 3565, in _event_on_init
    instrumenting_mapper._check_configure()
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 1873, in _check_configure
    _configure_registries({self.registry}, cascade=True)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 3380, in _configure_registries
    _do_configure_registries(registries, cascade)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 3419, in _do_configure_registries
    mapper._post_configure_properties()
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 1890, in _post_configure_properties
    prop.init()
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/orm/interfaces.py", line 222, in init
    self.do_init()
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 2142, in do_init
    self._setup_join_conditions()
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 2238, in _setup_join_conditions
    self._join_condition = jc = JoinCondition(
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 2633, in __init__
    self._determine_joins()
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 2796, in _determine_joins
    util.raise_(
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship User.delivery_orders - there are multiple foreign key paths linking the tables.  Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

相關程式碼如下：

app.py

import os
from flask import Flask
from dotenv import load_dotenv
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
from flask_seeder import FlaskSeeder


if os.environ.get('FLASK_ENV', '') != 'production':
    load_dotenv()

app = Flask(__name__)
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_DATABASE_URI'] = os.environ.get('SQLALCHEMY_DATABASE_URI')
db = SQLAlchemy(app)
migrate = Migrate(app, db)
seeder = FlaskSeeder()
seeder.init_app(app, db)


@app.route('/')
def home():
    return 'Hello World!'


if __name__ == '__main__':
    from models import User
    user = User(
        display_name='John Doe',
        email='JohnDoe.example.com',
        password='12345678'
    )
    user.save_to_db()
    app.run()

models/user.py

from app import db
from datetime import datetime


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)
    for_sale = db.relationship('Product', back_populates='seller')
    delivery_orders = db.relationship('Order', back_populates='seller')
    purchase_order = db.relationship('Order', back_populates='buyer')
    rating_record = db.relationship('Rating', back_populates='rater')
    rated_record = db.relationship('Rating', back_populates='ratee')
    asked_questions = db.relationship('Question', back_populates='author')
    replies = db.relationship('Reply', back_populates='author')

models/order.py

from app import db
from datetime import datetime
from models import User


class Order(db.Model):
    __tablename__ = 'order'

    id = db.Column(db.Integer, primary_key=True)
    amount = db.Column(db.Integer, nullable=False)
    recipient = db.Column(db.String(50), nullable=False)
    cell_phone = db.Column(db.String(20), nullable=False)
    address = db.Column(db.String(100), nullable=False)
    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)
    seller_id = db.Column(db.Integer, db.ForeignKey(User.id), nullable=False)
    buyer_id = db.Column(db.Integer, db.ForeignKey(User.id), nullable=False)
    shopping_status_id = db.Column(db.Integer, db.ForeignKey('shopping_status.id'))
    payment_status_id = db.Column(db.Integer, db.ForeignKey('payment_status.id'))

    seller = db.relationship('User', back_populates='delivery_orders')
    buyer = db.relationship('User', back_populates='purchase_order')
    shopping_status = db.relationship('ShoppingStatus', back_populates='orders')
    payment_status = db.relationship('PaymentStatus', back_populates='orders')
    payments = db.relationship('Payment', back_populates='order')
    products = db.relationship('OrderItem', back_populates='order')

看樣子是因為，Order 裡面，有 seller_id 和 buyer_id 這兩個 User 的外鍵，但是 sqlalchemy 不知道分別對應到哪個外鍵，所以報錯（如果只有一個外鍵的話，就不會有這個問題）。

Rating 也有同樣的問題。Rating 裡面，有 rater_id 和 ratee_id 這兩個 User 的外鍵。

依照錯誤訊息的建議，加上 foreign_keys 參數：

models/user.py

from app import db
from datetime import datetime


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)
    for_sale = db.relationship('Product', back_populates='seller')
    delivery_orders = db.relationship('Order', foreign_keys='Order.seller_id', back_populates='seller')
    purchase_order = db.relationship('Order', foreign_keys='Order.buyer_id', back_populates='buyer')
    rating_record = db.relationship('Rating', foreign_keys='Rating.rater_id', back_populates='rater')
    rated_record = db.relationship('Rating', foreign_keys='Rating.ratee_id', back_populates='ratee')
    asked_questions = db.relationship('Question', back_populates='author')
    replies = db.relationship('Reply', back_populates='author')

models/order.py

from app import db
from datetime import datetime
from models import User


class Order(db.Model):
    __tablename__ = 'order'

    id = db.Column(db.Integer, primary_key=True)
    amount = db.Column(db.Integer, nullable=False)
    recipient = db.Column(db.String(50), nullable=False)
    cell_phone = db.Column(db.String(20), nullable=False)
    address = db.Column(db.String(100), nullable=False)
    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)
    seller_id = db.Column(db.Integer, db.ForeignKey(User.id), nullable=False)
    buyer_id = db.Column(db.Integer, db.ForeignKey(User.id), nullable=False)
    shopping_status_id = db.Column(db.Integer, db.ForeignKey('shopping_status.id'))
    payment_status_id = db.Column(db.Integer, db.ForeignKey('payment_status.id'))

    seller = db.relationship('User', foreign_keys=User.id, back_populates='delivery_orders')
    buyer = db.relationship('User', foreign_keys=User.id, back_populates='purchase_order')
    shopping_status = db.relationship('ShoppingStatus', back_populates='orders')
    payment_status = db.relationship('PaymentStatus', back_populates='orders')
    payments = db.relationship('Payment', back_populates='order')
    products = db.relationship('OrderItem', back_populates='order')

再次嘗試新增一筆 user 資料，結果出現了別的錯誤訊息：

/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/bin/python /Users/flora/projects/shop_platform/app.py
Traceback (most recent call last):
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 2744, in _determine_joins
    self.primaryjoin = join_condition(
  File "<string>", line 2, in join_condition
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/sql/selectable.py", line 1196, in _join_condition
    raise exc.NoForeignKeysError(
sqlalchemy.exc.NoForeignKeysError: Can't find any foreign key relationships between 'order' and 'user'.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Users/flora/projects/shop_platform/app.py", line 28, in <module>
    user = User(
  File "<string>", line 4, in __init__
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/orm/state.py", line 474, in _initialize_instance
    manager.dispatch.init(self, args, kwargs)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/event/attr.py", line 343, in __call__
    fn(*args, **kw)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 3565, in _event_on_init
    instrumenting_mapper._check_configure()
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 1873, in _check_configure
    _configure_registries({self.registry}, cascade=True)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 3380, in _configure_registries
    _do_configure_registries(registries, cascade)
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 3419, in _do_configure_registries
    mapper._post_configure_properties()
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 1890, in _post_configure_properties
    prop.init()
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/orm/interfaces.py", line 222, in init
    self.do_init()
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 2142, in do_init
    self._setup_join_conditions()
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 2238, in _setup_join_conditions
    self._join_condition = jc = JoinCondition(
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 2633, in __init__
    self._determine_joins()
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 2766, in _determine_joins
    util.raise_(
  File "/Users/flora/.local/share/virtualenvs/shop_platform-9-YKR-2e/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Order.seller - there are no foreign keys linking these tables.  Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

我不太明白為什麼 sqlalchemy 竟然會跟我說 user 和 order 兩張 table 之間沒有 foreign keys linking。不知道是不是我弄錯了什麼事情。但總之，錯誤訊息裡提到了「or specify a 'primaryjoin' expression」，以及我看到 How do I do a join without a real foreign key constraint? 這篇文章，所以我改成底下這樣：

models/user.py

from app import db
from datetime import datetime


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)
    for_sale = db.relationship('Product', back_populates='seller')
    delivery_orders = db.relationship('Order', primaryjoin='Order.seller_id == User.id', back_populates='seller')
    purchase_order = db.relationship('Order', primaryjoin='Order.buyer_id == User.id', back_populates='buyer')
    rating_record = db.relationship('Rating', primaryjoin='Rating.rater_id == User.id', back_populates='rater')
    rated_record = db.relationship('Rating', primaryjoin='Rating.ratee_id == User.id', back_populates='ratee')
    asked_questions = db.relationship('Question', back_populates='author')
    replies = db.relationship('Reply', back_populates='author')

models/order.py

from app import db
from datetime import datetime
from models import User


class Order(db.Model):
    __tablename__ = 'order'

    id = db.Column(db.Integer, primary_key=True)
    amount = db.Column(db.Integer, nullable=False)
    recipient = db.Column(db.String(50), nullable=False)
    cell_phone = db.Column(db.String(20), nullable=False)
    address = db.Column(db.String(100), nullable=False)
    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)
    seller_id = db.Column(db.Integer, db.ForeignKey(User.id), nullable=False)
    buyer_id = db.Column(db.Integer, db.ForeignKey(User.id), nullable=False)
    shopping_status_id = db.Column(db.Integer, db.ForeignKey('shopping_status.id'))
    payment_status_id = db.Column(db.Integer, db.ForeignKey('payment_status.id'))

    seller = db.relationship('User', primaryjoin='Order.seller_id == User.id', back_populates='delivery_orders')
    buyer = db.relationship('User', primaryjoin='Order.buyer_id == User.id', back_populates='purchase_order')
    shopping_status = db.relationship('ShoppingStatus', back_populates='orders')
    payment_status = db.relationship('PaymentStatus', back_populates='orders')
    payments = db.relationship('Payment', back_populates='order')
    products = db.relationship('OrderItem', back_populates='order')

然後就可以新增 user 資料了。

commit: fix: Error when save user data to db

參考資料

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