All geek questions in one place

SQLAlchemy filtering not working

I am trying to use a filter to query, but for some reason, filtering does not seem to work. For example, if I run the command:

Curriculum_Version.query.filter(Course.course_code == 'PIP-001').all()

I get the same results as when I started:

 Curriculum_Version.query.filter(Course.course_code == 'FEWD-001').all()

(Both returns):

 [#1 Version Number: 1, Date Implemented: 2013-07-23 00:00:00, #2 Version Number: 2, Date Implemented: 2013-07-24 00:00:00]

If I run:

 Curriculum_Version.query.get(1).course

I get:

 from main import app, db from flask import Flask, request, g, redirect, url_for from flaskext.auth import Auth, AuthUser, login_required, get_current_user_data from flaskext.auth.models.sa import get_user_class import datetime from flask.ext.sqlalchemy import SQLAlchemy import pdb class User(db.Model, AuthUser): __tablename__ = 'users' id = db.Column(db.Integer, primary_key=True) tf_login = db.Column(db.String(255), unique=True, nullable=False) # can assume is an email password = db.Column(db.String(120), nullable=False) salt = db.Column(db.String(80)) role = db.Column(db.String(80)) # for later when have different permission types zoho_contactid = db.Column(db.String(20), unique=True, nullable=False) created_asof = db.Column(db.DateTime, default=datetime.datetime.utcnow) firstname = db.Column(db.String(80)) lastname = db.Column(db.String(80)) def __init__(self, zoho_contactid, firstname, lastname, tf_login, password, role, *args, **kwargs): super(User, self).__init__(tf_login=tf_login, password=password, *args, **kwargs) if (password is not None) and (not self.id): self.created_asof = datetime.datetime.utcnow() # Initialize and encrypt password before first save. self.set_and_encrypt_password(password) self.zoho_contactid = zoho_contactid # TODO self.firstname = firstname self.lastname = lastname self.tf_login = tf_login # TODO -- change to tf_login self.role = role def __repr__(self): return '#%d tf_login: %s, First Name: %s Last Name: %s created_asof %s' % (self.id, self.tf_login, self.firstname, self.lastname, self.created_asof) def __getstate__(self): return { 'id': self.id, 'tf_login': self.tf_login, 'firstname': self.firstname, 'lastname': self.lastname, 'role': self.role, 'created_asof': self.created_asof, } def __eq__(self, o): return o.id == self.id @classmethod def load_current_user(cls, apply_timeout=True): data = get_current_user_data(apply_timeout) if not data: return None return cls.query.filter(cls.email == data['email']).one() class Enrollment(db.Model, AuthUser): __tablename__ = 'enrollments' id = db.Column(db.Integer, primary_key=True) user_id = db.Column(db.Integer, db.ForeignKey('users.id')) user = db.relationship('User', backref='enrollments') curriculum_version_id = db.Column(db.Integer, db.ForeignKey('curriculum_versions.id')) curriculumversion = db.relationship('Curriculum_Version', backref='enrollments') cohort_id = db.Column(db.Integer, db.ForeignKey('cohorts.id')) cohort = db.relationship('Cohort', backref='enrollments') def __repr__(self): return '#%d User ID: %s Version ID: %s, Cohort ID: %s' % (self.id, self.user_id, self.curriculum_version_id, self.cohort_id) class Cohort(db.Model, AuthUser): __tablename__ = 'cohorts' id = db.Column(db.Integer, primary_key=True) start_date = db.Column(db.DateTime) course_id = db.Column(db.Integer, db.ForeignKey('courses.id')) course = db.relationship('Course', backref='cohorts') def __repr__(self): return '#%d Start Date: %s, Course: %s' % (self.id, self.start_date, self.course.course_code) class Curriculum_Version(db.Model, AuthUser): __tablename__ = 'curriculum_versions' id = db.Column(db.Integer, primary_key=True) version_number = db.Column(db.String(6)) date_implemented = db.Column(db.DateTime) course_id = db.Column(db.Integer, db.ForeignKey('courses.id')) course = db.relationship('Course', backref='curriculum_versions') def __repr__(self): return '#%d Version Number: %s, Date Implemented: %s' % (self.id, self.version_number, self.date_implemented) class Course(db.Model, AuthUser): __tablename__ = 'courses' id = db.Column(db.Integer, primary_key=True) course_code = db.Column(db.String(20)) course_name = db.Column(db.String(50)) def __repr__(self): return '#%d Course Code: %s, Course Name: %s' % (self.id, self.course_code, self.course_name) def __eq__(self, o): return o.id == self.id

How do I create a database entry for Curriculum_Versions:

 def update_courses(): course_code = request.form['course_code'] start_date = request.form['start_date'] course_date = datetime.strptime(start_date, '%m/%d/%Y') curr_version = Curriculum_Version.query.filter(Course.course_code == course_code) \ .order_by(desc('version_number')).first() if curr_version is None: next_version = 1 else: next_version = int(curr_version.version_number)+1 existing = Curriculum_Version.query.filter(Course.course_code == course_code) \ .filter(Curriculum_Version.date_implemented == course_date) if len(existing.all()) > 0: return "You tried to make version %d of the curriculum, but version \ %s of the curriculum already exists for %s for class %s." \ %(next_version, existing.first().version_number, start_date, course_code) course_object = Course.query.filter(Course.course_code == course_code).first() if course_object is None: return "The course %s does not yet exist!" % (course_code) new_version = Curriculum_Version(version_number=next_version, date_implemented=course_date, course=course_object) db.session.add(new_version) db.session.commit() return 'Created version %d for course %s starting on %s.' \ %(next_version, course_code, start_date)
+4
source share
3 answers

I think you need to join before filtering and using only one query:

 # Get all the versions of a single course. versions = Curriculum_Version.query.join(Curriculum_Version.course).filter( Course.course_code == "PIP-001").all()

Otherwise, sqlalchemy will not know whether to use the connection before filtering.

If you just specified a filter, then sqlalchemy does not know how to make the connection, and you end up with sql like this:

 SELECT curriculum_versions.* FROM curriculum_versions, courses WHERE courses.course_code = "PIP-001"

Which doesn't make much sense, but is the correct SQL. When you use a join, it uses a filter against the correct table as follows:

 SELECT curriculum_versions.* FROM curriculum_versions JOIN courses ON curriculum_versions.course_id = courses.id WHERE courses.course_code = "PIP-001"

Note that sqlalchemy knows how to use the curriculum_versions.course_id = courses.id because you pass in Curriculum_Version.course before query.join() , and you specified this relationship in your Curriculum_Version class as a course property, and it automatically knows that only use the foreign key available between the curriculum_versions and courses tables (which you should also have indicated in the curriculum_versions.course_id column).

You can learn more about unions here: http://docs.sqlalchemy.org/en/rel_0_7/orm/tutorial.html#querying-with-joins

+2
source

You can request the following:

 course_id = Course.query.filter(course_code="PIP-001").first().id curriculum = Curriculum_Version.query.filter(course_id=course_id).all()
+2
source

take a look at this demo app that I have compiled. This is a one-page Flask and SQLAlchemy application, and I think it describes everything you need:

https://github.com/Thinkful/sqlalchemy-demo

+1
source

Source: https://habr.com/ru/post/1493057/

More articles:


All Articles