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

資料關聯

-
以飲料店的商品種類為例，假設我們現在有飲料和杯子兩種資料。 飲料： 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