Even if the generated SQL statements are identical, the commands you enlisted may have a different impact on your application, e.g. j.posts will cache (memoize, do not confuse with Werkzeug caching) results you have got, while others will fetch them every single time.
If you remove .all() from your queries you can simply print them:
query = Post.query.filter_by(author=j)
print(query)
Which would result in:
SELECT post.id AS post_id, post.body AS post_body, post.user_id AS post_user_id
FROM post
WHERE ? = post.user_id
Using .all() is essentially like getting [m for m in query]).
The trick with query-printing will not work for j.posts which will return something like:
> print(j.posts)
> [Post(...), Post(..)]
Still, you can see all the silently emitted queries using built-in sqlalchemy loggers. See the following code:
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.engine import Engine
from sqlalchemy import event
import logging
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/tests.db'
db = SQLAlchemy(app)
logging.basicConfig()
logger = logging.getLogger('sqlalchemy.engine')
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(64), unique=True)
posts = db.relationship('Post', backref='author', lazy=True)
class Post(db.Model):
id = db.Column(db.Integer, primary_key=True)
body = db.Column(db.String(140))
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
db.drop_all()
db.create_all()
user = User(username='test', posts=[Post(body='some body')])
db.session.add(user)
db.session.commit()
# start logging
logger.setLevel(logging.DEBUG)
j = User.query.get(1)
queries = {
"j.posts",
"Post.query.filter_by(author=j)",
"Post.query.with_parent(j)",
"Post.query.with_parent(j, property='posts')",
"Post.query.with_parent(j, property=User.posts)",
}
def test_queries():
for name in queries:
print('\n=======')
print('Executing %s:' % name)
query = eval(name)
print(query)
test_queries() # you should see j.posts query here
print('Second test')
test_queries() # but not here
Getting back to your question: yes, the emitted SQL queries are identical.
In Query object’s target mapper, Query object's target refers to Post in your example. Decoupling this, when you declare Post class, inheriting from db.Model, for SQLAlchemy it is like creating an object Post and mapping the properties of this object to columns of specially created table.
Underneath there is an instance of Mapper class, which is responsible for the mapping for every single model that you create (learn more about mapping here: Types of Mappings). You can simply get this mapper calling class_mapper on your model or object_mapper on an instance of your model:
from sqlalchemy.orm import object_mapper, class_mapper,
from sqlalchemy.orm.mapper import Mapper
assert object_mapper(j) is class_mapper(User)
assert type(class_mapper(User)) is Mapper
The Mapper has all the necessary information about the columns and relations you have in your model. When calling Post.query.with_parent(j) this information is used to find a property (i.e. relationship) relating Post and User objects, so in your case to populate 'property' with User.posts.
