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 = 'test@test.com'
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': 'test@test.com',
 '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?

Answer 1

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.