python - In flask-sqlalchemy, how to convert query statements into raw SQL and print them out?

Question

Use sqlalchemy in python flask to create a table as follows:

class Role(db.Model):
    __tablename__ = 'roles'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    default = db.Column(db.Boolean, default=False, index=True)
    permissions = db.Column(db.Integer)
    users = db.relationship('User', backref='role', lazy='dynamic')

The current query results are as follows:

In [8]: Role.query.filter_by(name='User').first()
Out[8]: <app.models.Role at 0x159211982b0>

What we get is a queryset object. Is there any way to print out all the contents of this object? Instead of just getting one property of this object each time like the following? Also, how to print out the SQL statement actually executed when executing this statement?

In [7]: Role.query.filter_by(name='User').first().permissions
Out[7]: 7

Answer 1

This is more about the operation of sqlalchemy itself.

What I know is that when using sqlalchemy. Suppose there is a query as follows:

q = DBSession.query(model.Name).order_by(model.Name.value)
   

Do it directly:

print str(q)

You can print out the actual sql statement. And you can do dialect output for different data, as follows:

from sqlalchemy.dialects import postgresql
print str(q.statement.compile(dialect=postgresql.dialect()))

So in falsk. Because flask-sqlalchemy itself is just a package of sqlalchemy. The same approach should apply

Answer 2

If I remember correctly, just use str (your model) directly

Answer 3

Print the actual executed sql statement:
SQLALCHEMY_ECHO=True