0

I'm developing an API with Flask and I cannot retrieve queries from a MySQL database I've connected with flask-sqlalchemy (not sqlalchemy alone). This is a pre-existing database downloaded from my client's PHPMyAdmin, so I haven't ran db.create_all(): I simply created the connection string in config.py, then instantiated db = SQLAchemy() and initialized it (db.init_app(app)) in my factory function (i'm using the factory pattern together with blueprints).

I've already checked and my computer is running the mysql process, the login credentials provided are correct and the database exists in my computer. I'm using MariaDB because I run Manjaro Linux.

This is the connection string, located in config.py:

SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL') or "mariadb+mariadbconnector://dev:dev@localhost/desayunos56"

This is the relevant model. It was created using flask-sqlacodegen and then modified by me to only use the relevant columns within the table. At models.py:

from flask_sqlalchemy import SQLAlchemy
from app import db
# coding: utf-8

t_aus_postmeta = db.Table(
    """ 
        post_id: Order ID
        meta_key: Type of value (client name, billing address)
        meta_value: Value of meta_key (Name or address itself)
     """
    'aus_postmeta',
    #db.Column('meta_id', db.BigInteger, nullable=False),
    db.Column('post_id', db.BigInteger, nullable=False, server_default=db.FetchedValue()),
    db.Column('meta_key', db.String(255, 'utf8mb4_unicode_ci')),
    db.Column('meta_value', db.String(collation='utf8mb4_unicode_ci'))
)

And finally, this is the file with the error, views.py. It's a blueprint already registered to __init__.py. I created it only with the intention of checking if I could run queries, but I don't really intend to render anything from Flask:

from flask import render_template
from . import main
from .. import db
from app.models import t_aus_postmeta


@main.route("/", methods=["GET"])
def index():
    result = t_aus_postmeta.query_by(post_id=786).first()

This is the error I get: AttributeError: 'Table' object has no attribute 'query_by'

I think it's noteworthy that, although my linter doesn't complain due to unresolved imports, when I use t_aus_postmeta I don't get any method suggestions.

All the questions I've checked are based on using sqlalchemy instead of flask-sqlalchemy. What could be causing this error? At this point, I'm at a loss.

Improve this question
1
  • Why does this question feel like a sequence of people -- the first speaks English, the next translates it to Chinese, the next translates that to Polish, before the last person writes it down in Urdu. Commented Feb 1, 2022 at 3:09

2 Answers 2

Reset to default
1

I don't think that's the right way to create your model. Instead you should create it as a class, which will inherit from db.Model, that contains your query_by method.

models.py

class t_aus_postmeta(db.Model):
    """
        post_id: Order ID
        meta_key: Type of value (client name, billing address)
        meta_value: Value of meta_key (Name or address itself)
     """
    __tablename__ = 'aus_postmeta'
    post_id = db.Column(db.BigInteger(), nullable=False, server_default=db.FetchedValue())

    # rest of your columns...

If you do it this way a valid query would look like this:

t_aus_postmeta.query.filter_by('post_id').first()

Notice that this includes tutiplain's suggestion. I think you got your method name wrong. It's just query followed by a filter_by!

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I was puzzled as well when flask-sqlacodegen generated it like that, but it's a many-to-many database. Nevertheless, let me try and change the declaration of the model to see if it changes anything.
Well, let me know how it goes. It's been years since I last coded a Flask app and I may be wrong. But I've checked my model definitions from working apps and this was the correct from at the time.
Oh, check this heading Why does sqlacodegen sometimes generate classes and sometimes Tables? on the Official site. So after all the table-based code is probably fine. I've only ever used hand-written, class-based declarations. So it may be just down to your using the wrong method name. Do you have a primary key, though?
yes, the table-based code is fine but nevertheless it seems to be that regular methods do not work in these kinds of models.
0

I can't find the API reference for the "query_by" method you are using. It seems there is no such method. Perhaps you meant "filter_by" instead?

Improve this answer

4 Comments

Oh, that's a good point. It could also be order_by.
Indeed. query.filter_by. I also had to change temporarily to a class-based Model instead of the Table one. What a silly mistake. Thank you very much
Glad I could help!
@LeperAffinity666 Please consider upvoting all answers, which helped you. We put considerable time and effort into helping you. If I read correctly, switching to a class-based model as per my answer was also part of the solution. Not upvoting useful answers discourages people from putting their time into helping you, which erodes the ethos of this site. Thanks! How can I upvote answers and comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.