0

So I want to specifically run this sql code in my flask app:

SELECT *, (upvotes - downvotes) * age / importance * -1 AS hotness FROM 'post' ORDER BY hotness DESC LIMIT 50

To do this I used flask-sqlalchemy like so:

    self.hotness = db.engine.execute(text("SELECT *, (upvotes - downvotes) * age / importance * -1 AS hotness FROM 'post' ORDER BY hotness DESC LIMIT 50").execution_options(autocommit=True))
    return self.hotness

As suggested on this question.

The problem is that the self.hotness variable always comes out as None even though all the other variables referenced in the sql code are not None, they are numbers.

I'm guessing that the problem is due to the actual sql code not being committed into the table used here, which is post.

How would I properly commit data made from raw sql using flask-sqlalchemy?

ModoUnreal
  • 642
  • 5
  • 15
  • When you say all the other variables have a value, you mean that for any given row, they all have a value? My guess is that at least one of them is possibly `NULL` for a row, which resulted in `None`, and since you're sorting by `hotness`, the `None`s are bubbling up to the top. You also don't have to commit a select statement. Commits are for inserts, updates, or deletes. – Scratch'N'Purr Apr 27 '18 at 08:19
  • I have checked and seen that every single value inputted into the equation is an integer (which it should be). You mentioned that commits are for updates, so say I have a hotness value of `None` could I use an update command to update the value to an integer using the above equation? I think using the SELECT keyword was a mistake on my part. – ModoUnreal Apr 27 '18 at 12:01
  • Yes, you can use the update command. Just keep in mind that the SQL equivalent for Python's `None` is `NULL`, so in your WHERE clause, you want something like `WHERE hotness IS NULL` – Scratch'N'Purr Apr 27 '18 at 12:21

0 Answers0