Flask/SQL Alchemy原始查询代替类方法 [英] Flask / SQL Alchemy raw query instead of class method

问题描述

在我问之前，我会承认这是一个奇怪的问题. 我想知道是否可以使用原始SQL复制Flask/SQL Alchemy类方法，而不是使用方法本身?

I'll acknowledge that this is a strange question before I ask it. I'm wondering if it's possible to replicate Flask / SQL Alchemy class methods using raw SQL instead of using the methods themselves?

长话短说，我和我的队友正在上数据库设计课程，我们现在处于实施阶段，正在基于数据库架构设计对应用程序进行编码.我们希望保持简单，因此我们选择在Python中使用Flask.我们正在遵循 Flask Mega教程，这是一个入门指南，解释了如何像我们正在做的那样构建基本站点.我们刚刚完成了第5章:用户登录，并且还在继续.

Long story short, my teammates and I are taking a database design course, and we're now in the implementation phase where we are coding the app that is based on our DB schema design. We want to keep things simple, so we opted for using Flask in Python. We're following the Flask Mega Tutorial, which is a kickass-tic tutorial explaining how to build a basic site like we're doing. We've just completed Chapter 5: User Logins, and are moving on.

在app/routes.py脚本中，本教程执行了一些操作以获取用户信息.这是示例应用程序的示例登录路径:

In the app/routes.py script, the tutorial does something to grab the user information. Here's the example login route for the example app:

from flask_login import current_user, login_user
from app.models import User

# ...

@app.route('/login', methods=['GET', 'POST'])
def login():
    if current_user.is_authenticated:
        return redirect(url_for('index'))
    form = LoginForm()
    if form.validate_on_submit():
        user = User.query.filter_by(username=form.username.data).first()
        if user is None or not user.check_password(form.password.data):
            flash('Invalid username or password')
            return redirect(url_for('login'))
        login_user(user, remember=form.remember_me.data)
        return redirect(url_for('index'))
    return render_template('login.html', title='Sign In', form=form)

行user = User.query.filter_by(username=form.username.data).first()是我感兴趣的行.基本上，该行实例化User类(它是SQL Alchemy的数据库模型)，并从输入的电子邮件地址中获取有关用户的信息.调用这些方法将生成如下的SQL语句:

The line user = User.query.filter_by(username=form.username.data).first() is what I'm interested in. Basically, that line instantiates the User class, which is a database model from SQL Alchemy, and grabs information about the user from the email address they entered. Calling those methods generates a SQL statement like the following:

SELECT `User`.`userID` AS `User_userID`,
       `User`.user_email AS `User_user_email`,
       `User`.user_first_name AS `User_user_first_name`,
       `User`.user_last_name AS `User_user_last_name`,
       `User`.user_password AS `User_user_password`
FROM `User`
WHERE `User`.user_email = '[e-mail]'
LIMIT 1

以及有关user变量本身的一些信息:

And also some information about the user variable itself:

>>> print(type(user))
<class 'myapp.models.User'>

>>> pp(user.__dict__)
{'_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x7f5a026a8438>,
 'userID': 1,
 'user_email': '[e-mail]',
 'user_first_name': 'SomeFirstName',
 'user_last_name': 'SomeLastName',
 'user_password': 'somepassword'}

在我们的项目中，我们不应该像在实例化的User类上调用query.filter_by(username=form.username.data).first()那样使用生成的SQL语句；我们应该自己编写原始SQL，通常这没有任何意义，但在我们的情况下确实如此.

On our project, we're not supposed to be using generated SQL statements like the one that comes from calling query.filter_by(username=form.username.data).first() on the instantiated User class; we should be writing the raw SQL ourselves, which normally doesn't make sense, but in our case it does.

这可能吗?

推荐答案

首先:与您的教授或TA对话.通过不对重大问题进行假设，您将节省时间.如果该类的目标是考虑数据库架构设计，那么使用ORM可能很好.如果您需要编写自己的SQL，则请勿使用ORM开头.

First of all: Talk to your professor or TA. You will save yourself time by not making assumptions about something so major. If the goal of the class is to think about database schema design then using an ORM is probably fine. If you need to write your own SQL, then don't use an ORM to begin with.

要回答技术性问题:是的，您可以纯粹将SQLAlchemy用作数据库连接池，可以将其用作从Python对象创建有效SQL语句的工具，也可以用作完整的ORM，以及之间的每个等级.

To answer the technical question: yes, you can use SQLAlchemy purely as a database connection pool, as a tool to create valid SQL statements from Python objects, and as a full-fledged ORM, and every gradation in between.

例如，使用ORM层，您可以告诉Query对象不为您生成SQL，而是接受文本. SQLAlchemy ORM教程在 下对此进行了介绍.使用文本SQL 部分:

For example, using the ORM layer, you can tell a Query object to not generate the SQL for you but instead take text. This is covered in the SQLAlchemy ORM tutorial under the Using Textual SQL section:

通过在text()构造中指定其用法，可以灵活地将其与Query结合使用，

Literal strings can be used flexibly with Query, by specifying their use with the text() construct, which is accepted by most applicable methods

对于您的登录示例，仅查询密码如下所示:

For your login example, querying for just the password could look like this:

user = User.query.from_statement(
    db.text("SELECT * FROM User where user_email=:email LIMIT 1")
).params(email=form.username.data).first()

if user is None or user.check_password(form.password.data):
    # ...

您还可以在 SQL Expression API ( SQLAlchemy库的 core API)以使用Python代码构建查询； Python对象与结果查询之间的关系几乎是一对一的；通常，您通常会先生成表的模型，然后从那里构建SQL，或者可以使用文字:

You could also read up on the SQL Expression API (the core API of the SQLAlchemy library) to build queries using Python code; the relationship between Python objects and resulting query is pretty much one on one; you generally would first produce a model of your tables and then build your SQL from there, or you can use literals:

s = select([
    literal_column("User.password", String)
]).where(
    literal_column("User.user_email") == form.username.data
).select_from(table("User")).limit(1)

并使用执行此类对象Session.execute()方法

results = db.session.execute(s)

如果您想真正射击自己，也可以直接将字符串传递给db.session.execute():

If you wanted to really shoot yourself in the foot, you can pass strings to db.session.execute() directly too:

results = db.session.execute("""
    SELECT user_password FROM User where user_email=:email LIMIT 1
    """, {'email': form.username.data})

只要知道Session.execute()返回一个 实例，而不是ORM实例.

Just know that Session.execute() returns a ResultProxy() instance, not ORM instances.

另外，请知道Flask-Login 不需要您使用ORM .正如项目文档说明:

Also, know that Flask-Login doesn't require you to use an ORM. As the project documentation states:

但是，它没有:

However, it does not:

• 在您身上施加特定的数据库或其他存储方法. 您完全负责用户的加载方式.

• Impose a particular database or other storage method on you. You are entirely in charge of how the user is loaded.

因此您只需创建 UserMixin的子类您每次手动查询数据库时都会实例化.

So you could just create a subclass of UserMixin that you instantiate each time you queried the database, manually.

class User(flask_login.UserMixin):
    def __init__(self, id):    # add more attributes as needed
        self.id = id

@login_manager.user_loader
def load_user(user_id):
    # perhaps query the database to confirm the user id exists and
    # load more info, but all you basically need is:
    return User(user_id)

# on login, use

user = User(id_of_user_just_logged_in)
login_user(user)

就是这样.该扩展程序希望查看实现4种基本方法的实例，而UserMixin类提供了所有这些，而您只需要提供id属性.验证用户ID和处理登录的方式由您决定.

That's it. The extension wants to see instances that implement 4 basic methods, and the UserMixin class provides all of those and you only need to provide the id attribute. How you validate user ids and handle login is up to you.

这篇关于Flask/SQL Alchemy原始查询代替类方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！