Forums

Hey there, I'm fresh to the python/web scene and typically work in C#/desktop app space and need to deploy a database with a rest api for basic queries using our website on PA.

I'm mostly referencing code here https://blog.pythonanywhere.com/121/ but haven't been able to get it to stick.

Here's my setup code.....

from flask_sqlalchemy import SQLAlchemy
import sys
import json
import datetime

GLOBAL_CONTEXT = {}
SECTION_NAMES = ["home", "about", "sectors", "products", "services", "news"]
ARTICLE_LIST = []
BLURBS = {}
APP = Flask(__name__)

SQLALCHEMY_DATABASE_URI = "mysql+mysqlconnector://{username}:{password}@{hostname}/{databasename}".format(
    username="########",
    password="########",
    hostname="jelec.mysql.pythonanywhere-services.com",
    databasename="########$########",
)
APP.config["SQLALCHEMY_DATABASE_URI"] = SQLALCHEMY_DATABASE_URI
APP.config["SQLALCHEMY_POOL_RECYCLE"] = 299
APP.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

db = SQLAlchemy(APP)

class jexuser(db.Model):
    __tablename__ = "clients"
    userId = db.Column(db.Integer, primary_key=True)
    userName = db.Column(db.String(4096))
    userPass = db.Column(db.String(4096))
    content = db.Column(db.String(4096))

And here's my exec code...

@APP.route("/jex/v1/clients", methods=["GET"])
def api_clients():
    if 'user' in request.args:
        user_name = request.args['user']
        query = db.session.query("SELECT * FROM clients WHERE UserName = '{name}';".format(name=user_name))
        result = query.first()
        return result
    else:
        return "Error: provide a username."

Here's the error... along with a tall stack which is not included..

ModuleNotFoundError: No module named 'mysql'</code>

... Sorry for the terrible formatting, The forum doesn't seem to have good support for it.

Anyway I'm not sure why the system can't find mysql, any idea whats going on here? Thanks.

[edit by admin: formatting]

You have not installed the package that provides the mysql module. We have a help page on how to install packages here: https://help.pythonanywhere.com/pages/InstallingNewModules/

I checked the pip list and it was loaded. I installed mysqlclient and changed the URI and got this:

sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:mysqlclient.mysqlconnector

using mysql in the URI instead just throws the other error.

If you're using a URL that starts with mysql+mysqlconnector, then you need to have mysql-connector-python installed using pip.

BTW, regarding the formatting, see the text immediately below the field where you enter your post.

Indent by 4 spaces for code blocks, and prepend with :::python (also indented) for syntax highlighting.

Ok, thanks I've made my way through some of the noise on this issue and got stuck on a new error I can't figure out. I deployed to the website to test there as I read that I wouldn't be able to make over the web DB queries from my local environment.

Here is a snippet from the website error log. I don't understand what the error is trying to convey, or how to resolve it. As far as I can tell it should work, but I keep getting 500's.

    File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/query.py", line 2760, in __iter__
        self.session._autoflush()
    AttributeError: 'NoneType' object has no attribute '_autoflush'

The source looks like this:

APP = Flask(__name__)

SQLALCHEMY_DATABASE_URI = "mysql+mysqlconnector://{un}:{pw}@{host}/{dbn}".format(
    un="####",
    pw="####",
    host="jelec.mysql.pythonanywhere-services.com:3306",
    dbn="####",
)
APP.config["SQLALCHEMY_DATABASE_URI"] = SQLALCHEMY_DATABASE_URI
APP.config["SQLALCHEMY_POOL_RECYCLE"] = 299
APP.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
db = SQLAlchemy(APP)
class JexUser(db.Model):
    __tablename__ = "clients"
    userId = db.Column(db.Integer, primary_key=True)
    userName = db.Column(db.String(4096))
    userPass = db.Column(db.String(4096))
    content = db.Column(db.String(4096))
    def __repr__(self):
        return '<JexUser %r>' % (self.userName)

With the REST [GET] looking like this - I changed the query because apparently it's required to go through the custom api instead of passing an sql query into it.

@APP.route("/jex/v1/clients", methods=["GET"])
def api_clients():
    if 'user' in request.args:
        user_name = request.args['user']
        query = db.Query(JexUser).filter(JexUser.userName == user_name)
        return query.first()
    else:
        return "Error: provide a username."

Thanks for the help.

The error is saying that the variable self.session, deep inside SQLAlchemy, is somehow being set to None instead of a database session.

The way you're doing that query looks wrong to me (though my Flask-SQLAlchemy knowledge may be a little out of date) -- should it not be something like this:

query = JexUser.query.filter_by(userName=user_name)

...?

I don't think that would work because it doesn't provide a db to be queried.

TypeError: 'JexUser' object is not callable

In the tutorial ( https://blog.pythonanywhere.com/121/ ) the db seems initialized by simply doing db = SQLAlchemy(app) and from what I've seen on stackoverflow that seems somewhat normal. Not sure what I'm missing here.

That error does not look like it could come from the line where you're querying. Are you sure that that is where the error is coming from?

Yes, but I finally figured it out. It was trying to return the JexUser as an object which is not something it was capable of doing... So I had to refine the query to give only the data I needed.

reqname = request.args['user']
result = db.session.query(JexUser).filter_by(UserName = reqname).first().Content

... returns the MySQL database value of the filtered row's "Content" column. This process was generally confusing. In the event that I wanted more of the data I would have to peel it out and jsonify it.

I think it's sorted for now. Thanks for the help.

Thanks for letting us know!