How to Execute Raw SQL in Flask-Sqlalchemy App

How to execute raw SQL in Flask-SQLAlchemy app

Have you tried:

result = db.engine.execute("<sql here>")

or:

from sqlalchemy import text

sql = text('select name from penguins')
result = db.engine.execute(sql)
names = [row[0] for row in result]
print names

Note that db.engine.execute() is "connectionless", which is deprecated in SQLAlchemy 2.0.

Flask / SQL Alchemy raw query instead of class method

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.

Raw Insert SQL in SQLAlchemy, Flask

you can try add

db.commit()

ater "insert" action

How to raw query with datetime in SqlAlchemy Python3

I think there are a couple of things going on here.

I don't think your f-strings are populating and strptime is for turning strings into dates, not the other way round.

I'd write it like this:

query = f'SELECT * from messages WHERE messages."timeStamp" > {start_date.isoformat()} and messages."timeStamp" < {end_date.isoformat()};'

SQLAlchemy : how can I execute a raw INSERT sql query in a Postgres database?

As pointed by others, injecting SQL like this is to be avoided in most cases.

Here, the SQL is written in the unit test itself. There is no external input leaking to the SQL injection, which alleviates the security risk.

Mike Organek’s solution did not fully work for me, but it pointed me to the right direction : I just had to also remove the parens from ls_vals.

    s_tb_name = "tb_customer"
    ls_cols = ["first_name", "last_name", "email"]
    ls_vals = ["'John', 'Doe', 'john.doe@mail.net'",
               "'Jane', 'Doe', 'jane.doe@mail.net'",
               "'Eric', 'Dal', 'eric.d@home.com'"]
    s_cols = ', '.join(ls_cols)
    s_vals = '(' + '), ('.join(ls_vals) + ')'
    session.execute(f"INSERT INTO {s_tb_name} ({s_cols}) VALUES {s_vals}")

This made the insert test pass, both when using the sqlite engine and the postgres engine.

Sqlalchemy - executing raw sql queries

Try this:

from flask import current_app
db.get_engine(current_app, bind='<your_bind>').execute('<your raw sql>')

---

Related Topics