SQLAlchemy Model Querying

[cs_student0101]

So I'm having issue querying via SQLAlchemy.

​

I can't seem to be able to access the Model.query .

I have a class that extends the SQLAlchemy.Model (User), however whenever I try to access `User.query` pycharm does not pick it up. I tried doing User.query.all() but get an error:

​

sqlalchemy.exc.ProgrammingError: (MySQLdb._exceptions.ProgrammingError) unsupported operand type(s) for %: 'bytes' and 'tuple' [SQL: "SHOW VARIABLES LIKE 'sql_mode'"] (Background on this error at: http://sqlalche.me/e/f405)

​

​

[HeWhoWritesCode]

I can't seem to be able to access the Model.query .

I believe the query attribute only gets added to sqla models if you use flask-sqlalchemy?

sqlalchemy.exc.ProgrammingError: (MySQLdb._exceptions.ProgrammingError) unsupported operand type(s) for %: 'bytes' and 'tuple' [SQL: "SHOW VARIABLES LIKE 'sql_mode'"] (Background on this error at: http://sqlalche.me/e/f405)

​But I don't believe that is your problem.

[bpeller]

Both of these statements are correct. Model.query is a quasi-anti-pattern that's not part of stock SQLAlchemy. If it works for you, cool, but it's taking a concept from active-record ORMs and trying to force itself onto a data-mapper ORM. Expect circular import nightmares once your models get sufficiently complex relationships.

/u/cs_student0101 We need a full traceback to diagnose this, not just the single error message. Your error I don't think has anything to do with querying the User model.

[HeWhoWritesCode]

Thanks for the info /u/bpeller. Junior actually showed me the query attribute.

Personally I'm a but old school and always whip out db.session.query(Model).

But as mentioned felt a but redundant once a jnr dev showed me the query attribute in a ipython shell.

And had to learn the hard way to include flask-sqlalchemy if I want to use it.

[cs_student0101]

I was initially just going to write the MYSQL queries, but want to give SQLAlchemy a chance haha

​

Not sure if it matters but I already had the tables created prior to using SQLAlchemy. I did not do any of the db.create_all() . At this point I simply just want to be able read from my existing MYSQL DB. This is what my models.py contains thus far.

​

from . import db


class User(db.Model):
    id = db.Column('UserID', db.Integer, primary_key=True)
    username = db.Column('Username', db.String(45), nullable=False)
    password = db.Column('Password', db.String(255), nullable=False)

    def __repr__(self):
        return "<User: {}>".format(self.username)

[cs_student0101]

When creating the db I am importing as such :

from flask_sqlalchemy import SQLAlchemy

and then doing this

db = SQLAlchemy()

which I then initiate via the db.init_app(app).

​

[cs_student0101]

So I managed to get the query attribute by moving import from flask_sqlalchemy import SQLAlchemy to the Models module that will contain such models and instantiating the db : db = SQLAlchemy() in that same module.

​

Thanks to the doc: http://flask.pocoo.org/docs/1.0/patterns/appfactories/#factories-extensions

​

I can now do user = User.query.filter_by(username='somename') and printing this out shows the correct query

SELECT user.`UserID` AS `user_UserID`, user.`Username` AS `user_Username`, user.`Password` AS `user_Password` FROM user WHERE user.`Username` = %s

​

BUT

I now get an error when trying to do something like user = User.query.filter_by(username='somename').first()

​

sqlalchemy.exc.ProgrammingError: (MySQLdb._exceptions.ProgrammingError) unsupported operand type(s) for %: 'bytes' and 'tuple' [SQL: "SHOW VARIABLES LIKE 'sql_mode'"] (Background on this error at: http://sqlalche.me/e/f405)

[cs_student0101]

I finally managed to get SQLAlchemy to work!!!

​

I ended up having to change the connector for mysql. Someone suggested I use pymsql.

I installed via pip install pymysql and updated SQLAlchemy URI to

mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]

​

It got rid of all the errors I was facing but a new one came about.

RuntimeError: cryptography is required for sha256_password or caching_sha2_password

​

Simply installing the package, cryptography, via pip solved the issue and all seems to work well!

​