OperationalError:(OperationalError)没有这样的列 [英] OperationalError: (OperationalError) no such column

问题描述

我有问题.我必须执行以下查询:

I have a problem. I have to do this query:

@app.route('/api/subscriptions/<string:id>', methods=('DELETE',))
@decorators.login_required
def delete_subscription(id):
    dbsession = DBSession()
    session = Session()
    favorit = (dbsession.query(StudentsFavorites)
      .filter(Exams.number == str(id))
      .filter(StudentsFavorites.exam_id)
      .filter(Students.id == StudentsFavorites.student_id)
      .filter(Students.id == str(session.get_user_id()))
      .delete()         )
    dbsession.flush()
    return jsonify(error=False)

但是当我执行此查询时，会出现以下异常:

But when I do this query I get this exception:

OperationalError: (OperationalError) no such column: exams.number u'DELETE FROM students_favorites WHERE exams.number = ? AND students_favorites.exam_id AND students.id = students_favorites.student_id AND students.id = ?' ('123123123', 'a24213')

表很大，有很多信息，所以我不能全部张贴.但是此查询有效:

The tables are very big and got lots of information, so i can't post all of it. But this query works:

@app.route('/api/subscriptions/<string:id>', methods=('PUT',))
@decorators.login_required
def add_subscription(id):
    dbsession = DBSession()
    session = Session()
    examID = (dbsession.query(Exams.id)
     .filter(Exams.number == id).first()
     )
    favorit=StudentsFavorites(student_id=session.get_user_id(), exam_id=examID.id)
    dbsession.add(favorit)
    dbsession.flush()
    return jsonify(error=False)

表的简短视图:

table: Exams
rows: id, number (number is the id i put into the function)

table: StudentsFavorites
rows: student_id, exams_id

table: Students
rows: id

我真的不明白，为什么他没有在例外中找到数字行.

I really didn't understand, why he didn't find the number row in the exception.

数据库学生最爱:

class StudentsFavorites(Base):
    """N:M resolve model for the exams to the semester.
    """

    __tablename__ = "students_favorites"

    student_id = Column(Unicode(255), ForeignKey("students.id"), primary_key=True, autoincrement=False)

    exam_id = Column(Integer, ForeignKey("exams.id"),            primary_key=True, autoincrement=False)

    created_at = Column(DateTime, nullable = False, default = datetime.now)

    student = relationship("Students", uselist = False, lazy="joined")

    exam = relationship("Exams", uselist=False, lazy="joined")

是这样吗?我试过了:

    (dbsession.query(StudentsFavorites)
           .filter(StudentsFavorites.exam.id == str(id))
           .filter(StudentsFavorites.student.id == str(session.get_user_id()))
           .delete()
 )

但是出现错误，即考试/学生中不存在该ID

But got the error, that id didn't exist in exams / student

推荐答案

您有两种情况存在相同的问题.您的查询包含有关StudentFavorites的信息，这意味着它了解StudentFavorites.student_id和StudentFaovrites.exams_id.它对Students.id，Exames.id和Exames.number一无所知. 为了查询学生的最爱对象，并知道要执行sql join的其他值.

You have two cases of the same problem. Your query has information for StudentFavorites which means it knows about StudentFavorites.student_id and StudentFaovrites.exams_id. It doesn't know anything about Students.id, Exames.id and Exames.number. In order for you to query a StudentFavorites object and have it know about those other values you're going to have to perform a sql join.

加入sqlalchemy可能会有点麻烦(当然，在常规sql中也是如此).因为我不知道您的表架构是什么，所以我不能谈论它，但是视图应该看起来像这样.

Join's can be a bit of a pain in the ass to get working in sqlalchemy (well... in regular sql as well). Since I don't know what your table schema is I can't talk about that but the view should look something like this.

@app.route('/api/subscriptions/<string:id>', methods=('DELETE',))
@decorators.login_required
def delete_subscription(id):
    dbsession = DBSession()
    session = Session()
    favorit = (dbsession.query(StudentsFavorites)
      .join(Exames)
      .join(students)
      .filter(Exams.number == str(id))
      .filter(StudentsFavorites.exam_id)
      .filter(Students.id == StudentsFavorites.student_id)
      .filter(Students.id == str(session.get_user_id()))
      .delete()         )
    dbsession.flush()
    return jsonify(error=False)

Alternatively, you can look into setting up Foreign key relationships in your table statements if you use the ORM to create your tables

第二个示例起作用的原因是因为您要在检查表上指定查询，并且仅使用在该表中找到的值.

The reason your second example works is because you're specifying a query over an exam table and only using values found in that table.

回复编辑: 目前，您的表关系设置不正确.部分:多对多和从多对多表中删除行

此示例代码在发布的链接中进行了更多(更好)的详细说明，但基本思想是您有一个associate_table(在您的情况下，StudentFavorites)包含外键，这些外键具有在一个或多个中指定的关系您的其他表.我个人建议您使用表示例而不是对象示例.

This example code is explained in much more (and better) detail in the posted link but the basic idea is that you have a associate_table (in your case StudentFavorites) contains foreign keys which have a relationship which is specified in one or more of your other tables. I personally advise that you go with the table example and not the object example.

association_table = Table('association', Base.metadata,
    Column('left_id', Integer, ForeignKey('left.id')),
    Column('right_id', Integer, ForeignKey('right.id'))
)

class Parent(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Child",
                    secondary=association_table,
                    backref="parents")

class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)

这篇关于OperationalError:(OperationalError)没有这样的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！