使用flask-wtf queryselectfield更具体的SQL查询 [英] More specific SQL query with flask-wtf queryselectfield

问题描述

我想用Flask和WTF创建一个登录表单。应用程序应该从数据库表中读取所有用户的昵称，并将其显示在QuerySelectField中。

它确实有效 - 但我认为我可以在一个'更好的方式'...

我有一个这样的数据库表：

  id |昵称|全名|电子邮件
 ---- + ----------- + ---------------- + ----------- --------------- 
 1 |管理员| Admin | [e-mail] 
 2 | JDoe | John Doe | [e-mail] 
  

在 models.py 中，我有相应的class User和一个函数getUser：

pre $ from app import db
$ b $ class User（db.Model） ：
id = db.Column（db.Integer，primary_key = True）
nickname = db.Column（db.String（64），index = True，unique = True）
fullname = db.Column（db.String（64），index = True，unique = True）
email = db.Column（db.String（120），index = True，unique = True）

def __repr __（self）：
return'< User％r>'％（self.nickname）

用于query_factory
def getUser（）：
u = User.query
return u


forms.py 包含form.ext.wtf中的form-class

 从wtforms导入表格
 import StringField，BooleanField，从wtforms.validators中选择字段
从wtforms.ext.sqlalchemy.fields导入DataRequired，必需
 import Quer ySelectField 
 $ b $ from .models import User，getUser 
 $ b $ class LoginForm（Form）：
 openid = StringField（'openid'，validators = [DataRequired（）]） 
 remember_me = BooleanField（'remember_me'，default = False）
 user = QuerySelectField（u'User'，query_factory = getUser，get_label ='nickname'）
  

正如我所说，所有昵称都显示在我的QuerySelectField中。

 < select id =username =user>< option value =1> Admin< ; / option>< option value =2> JDoe< / option>< / select> 
  

但是我想知道所做的查询：

  INFO sqlalchemy.engine.base.Engine SELECTuser.id AS user_id，user.nickname AS user_nickname，user.fullname AS user_fullname，user。电子邮件作为user_email 
从用户
  

好像整个表被返回，我收到不必要的数据（全名和电子邮件）也是如此。
这就是我想要避免的。

如何只查询User.id和User.nickname并将其显示在QuerySelectField中？
（当然，我做了研究 - 我尝试了filter_by或User.query（User.id，User.nickname） - 但只有错误关于不匹配的元组）

<

mycket

<不知道你使用的是哪一个版本的SQLAlchemy，而是 0.9.0 有 sqlalchemy.orm.load_only
$ b

您可以修改 getUser（）>这可以用来简单地返回一个查询列的子集。如下所示：

$ b $ prefle =lang-py prettyprint-override> from functools import partial $ b $ from sqlalchemy import orm
$ b $ def getUser（columns = None）：
u = User.query
如果列：
u = u.options（orm.load_only（* columns））
返回u

def getUserFactory（column s = None）：
返回partial（getUser，columns = columns）

＃随后在定义LoginForm时
user = QuerySelectField（u'User'，
query_factory = getUserFactory（['id'，'nickname']），
get_label ='nickname'）


I'd like to create a Login-Form using Flask and WTF. The application should read the nicknames of all users out of a database-table and display them in a QuerySelectField.

It does work - but I think that I can do it in a 'nicer way'...

I have a database table like this:

 id | nickname  |    fullname    |          email
----+-----------+----------------+--------------------------
  1 | Admin     | The Admin      | [e-mail]
  2 | JDoe      | John Doe       | [e-mail]

In my models.py I have the corresponding class User and a function getUser:

from app import db

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    nickname = db.Column(db.String(64), index=True, unique=True)
    fullname = db.Column(db.String(64), index=True, unique=True)
    email = db.Column(db.String(120), index=True, unique=True)

    def __repr__(self):
        return '<User %r>' % (self.nickname)

# used for query_factory    
def getUser():
    u = User.query
    return u

The forms.py contains the form-class

from flask.ext.wtf import Form
from wtforms import StringField, BooleanField, SelectField
from wtforms.validators import DataRequired, Required
from wtforms.ext.sqlalchemy.fields import QuerySelectField

from .models import User, getUser

class LoginForm(Form):
    openid = StringField('openid', validators=[DataRequired()])
    remember_me = BooleanField('remember_me', default=False)
    user = QuerySelectField(u'User', query_factory=getUser, get_label='nickname')

As I said, all nicknames appear nicely in my QuerySelectField.

<select id="user" name="user"><option value="1">Admin</option><option value="2">JDoe</option></select>

But I wonder about the query that is made:

INFO sqlalchemy.engine.base.Engine SELECT "user".id AS user_id, "user".nickname AS user_nickname, "user".fullname AS user_fullname, "user".email AS user_email
FROM "user"

It seems whole table is returned and i receive unnecessary Data (fullname and email), too. That's what I want to avoid.

How can I query only User.id and User.nickname and display it in QuerySelectField? (Of course I did research - i tryed filter_by or User.query(User.id, User.nickname) - but only got errors about not matched tuples)

Can anyone help?

Thanks in advance!

mycket

解决方案

Not sure which version of SQLAlchemy you are using, but as of 0.9.0 there is sqlalchemy.orm.load_only which can be used to easily return a subset of columns for a query.

You could modify getUser() to something like the following:

from functools import partial
from sqlalchemy import orm

def getUser(columns=None):
    u = User.query
    if columns:
        u = u.options(orm.load_only(*columns))
    return u

def getUserFactory(columns=None):
    return partial(getUser, columns=columns)

# subsequently when defining LoginForm
user = QuerySelectField(u'User',
                        query_factory=getUserFactory(['id', 'nickname']),
                        get_label='nickname')

这篇关于使用flask-wtf queryselectfield更具体的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！