Python Flask: How to convert a query object to string?

Question

This issue has confused me for a long time and I searched for a few days but still cannot get it resolved, including this, this, and this.

Below code returns a query object and it shows correctly in the select field. But when submitting to database, the errors occurred.

  # Query the user with Role.id == 4 as reviewer
  def reviewer_choices():
      return User.query.join(User.roles).filter(Role.id == 4)

  # Build a select field
  class ProjectView(sqla.ModelView):
   form_extra_fields = {
    'reviewer': sqla.fields.QuerySelectField(
    query_factory=reviewer_choices,
  )}

I tried to define __repr__ and __str__ in order to convert it to string but in vain, is there any other way to convert the query object to string? Thanks in advance.

1. __repr__: The error returns:

sqlalchemy.exc.InterfaceError InterfaceError:

class User(db.Model, UserMixin):
   id = db.Column(db.Integer, primary_key=True)
   first_name = db.Column(db.String(255))
   # ...
   # ...
   def __repr__(self):
     return self.first_name

2. __str__: The error returns:

sqlalchemy.exc.InterfaceError InterfaceError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (sqlite3.InterfaceError) Error binding parameter 8 - probably unsupported type. [SQL: u'INSERT INTO project

class User(db.Model, UserMixin):
   id = db.Column(db.Integer, primary_key=True)
   first_name = db.Column(db.String(255))
   # ...
   # ...
   def __str__(self):
     return self.first_name

I currently used:

In the Project class

class Project(db.Model):
   # ...
   reviewer = db.Column(db.Unicode(128)) 
   # ...

In the Project table

CREATE TABLE `project` (
   # ...
   `reviewer1`  TEXT,
   # ...

Answer 1

Assuming that having reviewer as string field in your Project model is intentional (as opposed to being a relationship).

A QuerySelectField data property stores an ORM instance, which in your case is an instance of a User model, whilst your reviewer field is a string, hence the error message.

You can create an inherited QuerySelectField class and override its populate_obj method to convert the selected User instance to a string of your choice, e.g.

class ProjectQuerySelectField(QuerySelectField):

    def populate_obj(self, obj, name):
        # obj is the current model being edited/created
        # name is the field name - 'reviewer' in this instance
        # self.data is the user instance selected in the form
        setattr(obj, name, str(self.data))

Note the use of the str function to get the string representation of the selected user instance.

See self-contained sample app below. Navigate to http://127.0.0.1:5000/admin/project/ to see how a selected user gets converted to a string.

from flask import Flask
from flask_admin.contrib.sqla import ModelView
from flask_admin.contrib.sqla.fields import QuerySelectField
from flask_security import Security, SQLAlchemyUserDatastore, RoleMixin, UserMixin
from flask_sqlalchemy import SQLAlchemy


from flask_admin import Admin
# Create application
app = Flask(__name__)

# Create dummy secrey key so we can use sessions
app.config['SECRET_KEY'] = '123456790'

# Create in-memory database
app.config['DATABASE_FILE'] = 'sample_db.sqlite'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + app.config['DATABASE_FILE']
app.config['SQLALCHEMY_ECHO'] = True
db = SQLAlchemy(app)


# Flask views
@app.route('/')
def index():
    return '<a href="/admin/">Click me to get to Admin!</a>'


# Define models
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(255), unique=True)
    description = db.Column(db.String(255))

    def __str__(self):
        return unicode(self).encode('utf-8')

    def __unicode__(self):
        return self.name


class User(db.Model, UserMixin):

    id = db.Column(db.Integer, primary_key=True)
    first_name = db.Column(db.String(255))
    last_name = db.Column(db.String(255))
    email = db.Column(db.String(254), unique=True)

    roles = db.relationship('Role', secondary=roles_users, backref=db.backref('users', lazy='dynamic'))

    def __str__(self):
        return unicode(self).encode('utf-8')

    def __unicode__(self):
        return ', '.join(filter(None, [self.first_name, self.last_name, self.email]))


class Project(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255))
    reviewer = db.Column(db.Unicode(128))

    def __str__(self):
        return unicode(self).encode('utf-8')

    def __unicode__(self):
        return self.name


# Setup Flask-Security
user_datastore = SQLAlchemyUserDatastore(db, User, Role)
security = Security(app, user_datastore)


class UserView(ModelView):
    column_list = ['first_name', 'last_name', 'email', 'roles']
    form_columns = ['first_name', 'last_name', 'email', 'roles']


class RoleView(ModelView):
    form_columns = ['name', 'description']


def reviewer_choices():
    # return User.query.join(User.roles).filter(Role.id == 4)
    return User.query.join(User.roles).filter(Role.name == u'Reviewer')


class ProjectQuerySelectField(QuerySelectField):

    def populate_obj(self, obj, name):
        setattr(obj, name, str(self.data))


class ProjectView(ModelView):

    form_extra_fields = {
        'reviewer': ProjectQuerySelectField(
            query_factory=reviewer_choices,
        )}


admin = Admin(app, template_mode="bootstrap3")
admin.add_view(UserView(User, db.session))
admin.add_view(RoleView(Role, db.session))
admin.add_view(ProjectView(Project, db.session))


def build_sample_db():
    db.drop_all()
    db.create_all()

    # Reviewer will have id : 4 and will have index 3 in _roles list
    _roles = []
    for _name in ['Admin', 'Editor', 'Reader', 'Reviewer']:
        _role = Role(name=_name)
        _roles.append(_role)

    db.session.add_all(_roles)

    # get the "Reviewer" Role
    _reviewer_role = _roles[3]

    # Give Paul and Serge "Reviewer" role
    _user_1 = User(first_name="Paul", last_name="Cunningham", email="paul@example.com", roles=[_reviewer_role])
    _user_2 = User(first_name="Luke", last_name="Brown", email="luke@example.com")
    _user_3 = User(first_name="Serge", last_name="Koval", email="serge@example.com", roles=[_reviewer_role])

    db.session.add_all([_user_1, _user_2, _user_3])

    db.session.commit()


if __name__ == '__main__':
    build_sample_db()
    app.run(port=5000, debug=True)