python - "Two-to-many"与 Flask-SQLAlchemy 的关系

Question

我是 Flask-SQLAlchemy 的新手，正在尝试使用。

使用以下代码 (app.py)，我想为具有“创建”用户和“受邀”用户的游戏创建模型:

import os

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate


basedir = os.path.abspath(os.path.dirname(__file__))


class Config:
    SECRET_KEY = os.environ.get('SECRET_KEY') or 'a-key'
    SQLALCHEMY_DATABASE_URI = (
        os.environ.get('DATABASE_URI')
        or 'sqlite:///' + os.path.join(basedir, 'app.db'))
    SQLALCHEMY_TRACK_MODIFICATIONS = False


app = Flask(__name__)
app.config.from_object(Config)

db = SQLAlchemy(app)
migrate = Migrate(app, db)


class User(db.Model):

    id = db.Column(db.Integer, primary_key=True)

    name = db.Column(db.String(64), index=True, unique=True)

    # Games created by the user:
    created_games = db.relationship(
        'Game', backref='author', lazy='dynamic',
        foreign_keys='game.user_id')

    # Games as opponent:
    invitations = db.relationship(
        'Game', backref='guest', lazy='dynamic',
        foreign_keys='game.opponent_id')


class Game(db.Model):

    id = db.Column(db.Integer, primary_key=True)

    # "Creator" user:
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

    # "Opponent" user:
    opponent_id = db.Column(db.Integer, db.ForeignKey('user.id'))

我是这样运行的:

(venv) $ export FLASK_APP=app.py
(venv) $ flask db init
...
(venv) $ flask db migrate
...
(venv) $ flask db upgrade
...

没有错误。

然后，我将它与 Python 解释器一起使用:

(venv) $ python
Python 3.6.0
...
>>> from app import User
>>> u = User(name="Alice")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "<string>", line 2, in __init__
  File "/home/ubuntu/workspace/Exp/venv/lib/python3.6/site-packages/sqlalchemy/orm/instrumentation.py", line 347, in _new_state_if_none
    state = self._state_constructor(instance, self)
  File "/home/ubuntu/workspace/Exp/venv/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 767, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/home/ubuntu/workspace/Exp/venv/lib/python3.6/site-packages/sqlalchemy/orm/instrumentation.py", line 177, in _state_constructor
    self.dispatch.first_init(self, self.class_)
  File "/home/ubuntu/workspace/Exp/venv/lib/python3.6/site-packages/sqlalchemy/event/attr.py", line 256, in __call__
    fn(*args, **kw)
  File "/home/ubuntu/workspace/Exp/venv/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py", line 3123, in _event_on_first_init
    configure_mappers()
  File "/home/ubuntu/workspace/Exp/venv/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py", line 3013, in configure_mappers
    mapper._post_configure_properties()
  File "/home/ubuntu/workspace/Exp/venv/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py", line 1811, in _post_configure_properties
    prop.init()
  File "/home/ubuntu/workspace/Exp/venv/lib/python3.6/site-packages/sqlalchemy/orm/interfaces.py", line 184, in init
    self.do_init()
  File "/home/ubuntu/workspace/Exp/venv/lib/python3.6/site-packages/sqlalchemy/orm/relationships.py", line 1655, in do_init
    self._process_dependent_arguments()
  File "/home/ubuntu/workspace/Exp/venv/lib/python3.6/site-packages/sqlalchemy/orm/relationships.py", line 1680, in _process_dependent_arguments
    setattr(self, attr, attr_value())
  File "/home/ubuntu/workspace/Exp/venv/lib/python3.6/site-packages/sqlalchemy/ext/declarative/clsregistry.py", line 281, in __call__
    x = eval(self.arg, globals(), self._dict)
  File "<string>", line 1, in <module>
AttributeError: 'Table' object has no attribute 'user_id'

我无法弄清楚这个错误...我在网上找到的所有内容似乎都证实了我的代码...

Answer 1

错误是关系定义:

created_games = db.relationship(
    'Game', backref='author', lazy='dynamic',
    foreign_keys='game.user_id')

出于某种原因，参数 foreign_keys 需要 python 属性的名称:foreign_keys='Game.user_id'，因此您需要为其提供类名称 (Game)，而不是表名称 (game)。请注意，ForeignKey 定义(在 Game 类中)仍然需要表名(您做得对):db.ForeignKey('user.id')。

总而言之，这应该有效:

class User(db.Model):

    id = db.Column(db.Integer, primary_key=True)

    name = db.Column(db.String(64), index=True, unique=True)

    # Games created by the user:
    created_games = db.relationship(
        'Game', backref='author', lazy='dynamic',
        foreign_keys='Game.user_id')

    # Games as opponent:
    invitations = db.relationship(
        'Game', backref='guest', lazy='dynamic',
        foreign_keys='Game.opponent_id')


class Game(db.Model):

    id = db.Column(db.Integer, primary_key=True)

    # "Creator" user:
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

    # "Opponent" user:
    opponent_id = db.Column(db.Integer, db.ForeignKey('user.id'))

除了这个问题:您可以使用 SQLAlchemy 显式指定表名(尽管如果不这样做，则会自动使用小写的类名)。我喜欢为表提供复数名称(例如 games、users 等)，这样您就可以随时查看您指的是类、表还是条目:

class Game(db.Model):
    __tablename__ = "games"
    id = db.Column(db.Integer, primary_key=True)

class User(db.Model):
    __tablename__ = "users"
    id = db.Column(db.Integer, primary_key=True)
    game_id = db.Column(db.Integer, db.ForeignKey('games.id')) # definitely the table name

user = User(name="test")