Flask / SQL Alchemy raw query instead of class method

Question

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?

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.

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)

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 = '[email protected]'
LIMIT 1

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': '[email protected]',
 'user_first_name': 'SomeFirstName',
 'user_last_name': 'SomeLastName',
 'user_password': 'somepassword'}

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.

Is this possible?

but in our case it does.: Why does it make sense? Yes, you can execute raw queries. You can then create your own instance of User from the data. But why would you want to do that? — Martijn Pieters
– Martijn Pieters, Commented Oct 23, 2018 at 11:46
Note that User.query.filter_by(username=form.username.data).first() issues a very, very simple SELECT query with a LIMIT 1. Using a manual 'handcrafted' query is not going to improve on that query. Everything SQLAlchemy does is introspectable and adjustable, you can build that same query with the core API or just pass raw SQL to the database connection. But that a) increases development cost, and b) opens you up to security problems if you don't handle untrusted data carefully. — Martijn Pieters
– Martijn Pieters, Commented Oct 23, 2018 at 11:49
@MartijnPieters Ha, I told you it was strange :D. Normally you wouldn't do that, and I know this. I'm worried that the graders for the project will look harshly on us using class methods that auto generate SQL queries, no matter how simple. — Scott Crooks
– Scott Crooks, Commented Oct 23, 2018 at 11:50
Then go talk to your professor or TA to see what the requirements really are. If you are supposed to write your own SQL, then don't use SQLAlchemy to begin with. — Martijn Pieters
– Martijn Pieters, Commented Oct 23, 2018 at 11:53
@ScottCrooks: then perhaps you need to look for a different tutorial. The Mega tutorial is teaching best practices but if those don't fit your class requirements, find something else. You are asking if a tool specifically designed to handle SQL for you can be bypassed to not handle SQL for you. Yes, it can, but then why use the tool to begin with? — Martijn Pieters
– Martijn Pieters, Commented Oct 23, 2018 at 11:57

Martijn Pieters · Accepted Answer · 2018-10-23 13:44:47Z

8

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.

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.

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:

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):
    # ...

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)

and execute such objects with the Session.execute() method

results = db.session.execute(s)

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})

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

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.

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)

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.

edited Oct 23, 2018 at 13:44

answered Oct 23, 2018 at 12:07

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Scott Crooks Over a year ago

Thank you, this actually does answer my question. I didn't mean to offend anyone with my shitty request, I know it's weird (please forgive me), but I'm just needing to potentially find a workaround. For the record, I have asked my TAs for clarification on this :D

Martijn Pieters Over a year ago

You didn't offend, don't worry. But you are really making it harder on yourself by using an ORM if you need to write your own queries.

Martijn Pieters Over a year ago

@ScottCrooks: I added a short section on how to use Flask-Login. It's really really simple.

Martijn Pieters Over a year ago

@IljaEverilä: ick, yes, that's totally wrong. That's the result of me being interrupted somewhere and coming back with incomplete state. I'll fix that.

Collectives™ on Stack Overflow

Flask / SQL Alchemy raw query instead of class method

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related