Python Flask，SQLAlchemy关系

Question

我已经试图解决这个问题几个小时了，但是我无法让SQLAlchemy工作（在添加两个新函数User和Registration之前它是可以工作的）。

from flask.ext.sqlalchemy import SQLAlchemy
from . import app
from datetime import datetime

db = SQLAlchemy(app)

class PasteCode(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    codetitle = db.Column(db.String(60), unique = True)
    codebody = db.Column(db.Text)
    pub_date = db.Column(db.DateTime)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    parent_id = db.Column(db.Integer, db.ForeignKey('paste_code.id'))
    parent = db.relationship('PasteCode', lazy = True, backref = 'children', uselist = False, remote_side = [id])

    def __init__(self, codetitle, codebody, parent = None):
        self.codetitle = codetitle
        self.codebody = codebody
        self.pub_date = datetime.utcnow()
        self.parent = parent

class User(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    display_name = db.Column(db.String(30))
    #pastes = db.Column(db.Integer, db.ForeignKey('paste_code.id'))
    pastes = db.relationship(PasteCode, lazy = "dynamic", backref = "user")

class Registration(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    username = db.Column(db.String(30), unique = True)
    password = db.Column(db.String(100), unique = False)

这是运行时给我的回溯信息：

OperationalError: (OperationalError) no such table: paste_code u'SELECT paste_code.id AS paste_code_id, paste_code.codetitle AS paste_code_codetitle, paste_code.codebody AS paste_code_codebody, paste_code.pub_date AS paste_code_pub_date, paste_code.user_id AS paste_code_user_id, paste_code.parent_id AS paste_code_parent_id \nFROM paste_code' ()

我也尝试了这个：

from flask.ext.sqlalchemy import SQLAlchemy
from . import app
from datetime import datetime

db = SQLAlchemy(app)

class PasteCode(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    codetitle = db.Column(db.String(60), unique = True)
    codebody = db.Column(db.Text)
    pub_date = db.Column(db.DateTime)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    parent_id = db.Column(db.Integer, db.ForeignKey('pastecode.id'))
    parent = db.relationship('PasteCode', lazy = True, backref = 'children', uselist = False, remote_side = [id])

    def __init__(self, codetitle, codebody, parent = None):
        self.codetitle = codetitle
        self.codebody = codebody
        self.pub_date = datetime.utcnow()
        self.parent = parent

class User(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    display_name = db.Column(db.String(30))
    pastes =  db.relationship(PasteCode, lazy = 'dynamic', backref = 'user')
    #pastes = db.relationship(PasteCode, lazy = "dynamic", backref = "user")

class Registration(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    username = db.Column(db.String(30), unique = True)
    password = db.Column(db.String(100), unique = False)

我遇到了这个错误：

ArgumentError: Could not determine join condition between parent/child tables on relationship PasteCode.parent. Specify a 'primaryjoin' expression. If 'secondary' is present, 'secondaryjoin' is needed as well.

有什么想法吗？谢谢！

- user689383

---

你应该尝试进一步规范化这些数据。 - Jakob Bowyer

将解决方案发布为答案，以便帮助其他人解决此问题。 - Jakob Bowyer

Answer 1

我解决这个问题的方法很简单，我添加了以下内容：

__tablename__ = "paste_code"

一切都按照预期运行，我认为SQLAlchemy没有正确检查表名。

- user689383

---

补充一下，您应该始终包括__tablename__，因为SQLAlchemy不总是会做出您或您的DBMS所需要或期望的相同决策。例如，Oracle的最大表名长度为30，而Postgres则更长。您认为CamelCase会变成camel_case还是camelcase还是CamelCase？您可以针对特定情况自动执行此操作，但明确定义它更加明确。 - Doobeh

是的，我遇到过这个问题几次。为什么Flask-SQL要通过隐藏这个来“帮助”呢？更好的方法是明确地定义它。 - jwogrady