Python Flask: How to correctly query a select field?

Question

I want to build the selectable drop button to select the users in the database according to users' role id.

The classes are defined below.

roles_users = db.Table(
   'roles_users',
   db.Column('user_id', db.Integer(), db.ForeignKey('user.id')),
   db.Column('role_id', db.Integer(), db.ForeignKey('role.id'))
)


class Role(db.Model, RoleMixin):
     id = db.Column(db.Integer(), primary_key=True)
     name = db.Column(db.String(80), unique=True)

     def __str__(self):
        return self.name

class User(db.Model, UserMixin):
     id = db.Column(db.Integer, primary_key=True)
     roles = db.relationship('Role', secondary=roles_users,
                         backref=db.backref('users', lazy='dynamic'))
     email = db.Column(db.String(255), unique=True)

     def __str__(self):
         return self.email
 
class Project(db.Model):

     id = db.Column(db.Integer, primary_key=True)
     reviewer = db.Column(db.Unicode(128))

     def __unicode__(self):
        return self.name

Since I want to list the users with Role.id==4 as reviewer in project class, I tried both methods below but in vain:

1. QuerySelectField

It returns nothing in the list:

def reviewer_choices():
    return User.query.join(User.roles).filter(Role.id == 4).all().query

class Project(db.Model):
     reviewer = QuerySelectField(query_factory = reviewer_choices)

2. form_args

It returns error: TypeError: __init__() got an unexpected keyword argument 'query_factory'

def reviewer_choices():
    return User.query.join(User.roles).filter(Role.id == 4).all().query

class Project(db.Model):
    form_args = dict(
              reviewer=dict(query_factory =reviewer_choices),
   )

Tiny.D · Accepted Answer · 2017-05-10T10:16:18.833

3

query_factory callback should return a query, you could change your reviewer_choices like this:

def reviewer_choices():
    return User.query.join(User.roles).filter(Role.id == 4)

Update:

Add below code with two selected required fields to your class SWProjectView(sqla.ModelView):

form_extra_fields = {
    'reviewer1': sqla.fields.QuerySelectField(
        label='Reviewer1',
        query_factory=reviewer1_choices,
        validators=[DataRequired()]
    ),
    'reviewer2': sqla.fields.QuerySelectField(
        label='Reviewer2',
        query_factory=reviewer2_choices,
        validators=[DataRequired()]
    )
}

This will make the field Reviewer1 and Reviewer2 to be selected and required fields, show in the image:

edited May 10 '17 at 10:16

answered May 10 '17 at 03:37

Tiny.D

6,466
2
15
20

Hi, thanks I tried but still not work for both `QuerySelectField` and `form_args`. – Samoth May 10 '17 at 03:45
@ Tiny.D, still return nothing for `QuerySelectField` and `TypeError: __init__() got an unexpected keyword argument 'query_factory'` for `form_args`. – Samoth May 10 '17 at 03:51
@Samoth can you please change to `return User.query` to see what will return you? – Tiny.D May 10 '17 at 05:16
@ Tiny.D : Thanks but I've tried `return User.query` and the results are the same. – Samoth May 10 '17 at 05:23
@Samoth `User.query` will query all users from db, it seems the query cannot get any data, are you sure the data are stored in your db? – Tiny.D May 10 '17 at 05:35
@ Tiny.D : Yes I am sure the data are stored in my db. – Samoth May 10 '17 at 05:38
@ Tiny.D : Thanks so much for your updated answer, it works like a charm! – Samoth May 11 '17 at 00:21
@ Tiny.D: Thanks for your kind answer, what if I want to query the user with `Role.id == 4` and `Team.id == current_user.team_id`? – Samoth May 11 '17 at 01:05
@Samoth, You could join the Team and then filter the id with specific id. – Tiny.D May 11 '17 at 04:46
I used`return User.query.join(User.roles).join(User.teams).filter(Role.id == 4).filter(Team.id == current_user.teams.id)` and it works, thanks. – Samoth May 11 '17 at 05:14
I just found the error occured `sqlalchemy.exc.InterfaceError InterfaceError: (sqlite3.InterfaceError) Error binding parameter 8 - probably unsupported type. [SQL: u'INSERT INTO project ` when I was trying to **save** the project. I was too focused on whether the selected field showed correctly and did not find this problem. – Samoth May 11 '17 at 05:53
I think it might the data type in the DB or in the definition in the class. – Samoth May 11 '17 at 06:54
@ Tiny.D : Would you please take a look at [this](http://stackoverflow.com/questions/43908969/flask-admin-sqlalchemy-exc-interfaceerrorerror-binding-parameter-8/43909345?noredirect=1#comment74852435_43909345)? Thanks. – Samoth May 11 '17 at 09:32
@Samoth I am quite busy on the work, I will take a look when I am available. – Tiny.D May 11 '17 at 09:58

Python Flask: How to correctly query a select field?

1 Answers1