Running a SQLAlchemy query with filter in PythonAnywhere

Question

I'm trying to run an SQLAlchemy query on my Flask web app (hosted on PythonAnywhere) and I'm stuck - I'm new to SQLAlchemy and have tried to search around for the answer and tried a few different solutions but nothing worked.

This is the initial set up:

from flask import Flask, redirect, render_template, request, url_for from flask_sqlalchemy import SQLAlchemy from flask_login import login_required, login_user, LoginManager, logout_user, UserMixin, current_user from werkzeug.security import check_password_hash, generate_password_hash import requests import urllib.parse

app = Flask(__name__)
app.config["DEBUG"] = True

SQLALCHEMY_DATABASE_URI = "mysql+mysqlconnector://{username}:{password}@{hostname}/{databasename}".format(
    username="****",
    password="****",
    hostname="****",
    databasename="****",
)


app.config["SQLALCHEMY_DATABASE_URI"] = SQLALCHEMY_DATABASE_URI
app.config["SQLALCHEMY_POOL_RECYCLE"] = 299
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
db = SQLAlchemy(app)

app.secret_key = "58f5x^G!S8q8p6MZDRHa"
login_manager = LoginManager()
login_manager.init_app(app)

[...]

class Portfolio(db.Model):

    __tablename__ = "portfolio"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    symbol = db.Column(db.String(32))
    quantity = db.Column(db.Integer)
    user = db.Column(db.String(32))

And query I am trying to run - the first section works, in the second section I am trying to filter the table by the symbol given in the form (I have put it 'NFLX' just to see if the query itself would work) to check if that entry already exists - with the intention to then have different behaviours based on whether curr_portfolio is none/null.

    # add transaction to Transaction database
    transaction = Transaction(symbol=request.form["symbol"], quantity=request.form["shares"], totalvalue=cost, user="admin")
    db.session.merge(transaction)
    db.session.commit()

    # add transaction to Portfolio database
    symbol=(request.form.get("symbol")
    curr_portfolio = session.query(Portfolio.symbol).filter(symbol="NFLX").all()

    return redirect(url_for('dashboard'))

Any pointers greatly appreciated!

At first glance, I believe you should use symbol == 'NLFX' instead of the single equal sign. — edornd
Thanks, I tried both as wasn't sure if that was the issue but unfortunately neither works — kvvzz
What is the actual problem? Are you getting an error? or just not the results you wanted? — noslenkwah
Ah, I see another weird thing: You are not comparing the SQLAlchemy field to a value, you're comparing your input value from the form to the string 'NLFX'. Try also changing the filter to .filter(Portfolio.symbol == symbol) — edornd
@noslenkwah I get a syntax error in the example I showed, with other solutions I'm just not getting any results at all (even when I know the query should a row) — kvvzz

Fasil K Fasil K · Accepted Answer · 2021-08-17T07:30:49

Use db.session.query(Portfolio.symbol).filter(Portfolio.symbol == symbol).all()

Or Portfolio.query.filter(Portfolio.symbol == symbol).all()

Running a SQLAlchemy query with filter in PythonAnywhere

2 Answers