How to query database by id using SqlAlchemy?

Question

I need to query a SQLAlchemy database by its id something similar to

User.query.filter_by(username='peter')

but for id. How do I do this? [Searching over Google and SO didn't help]

Tetanize

Please provide more details, like equivalent SQL or pseudocode doing what you want. What is "SQLAlchemy database id"? – Perfunctory

Does your table have an id column? – Misconceive

Answer 1

Query has a get function that supports querying by the primary key of the table, which I assume that id is.

For example, to query for an object with ID of 23:

User.query.get(23)

---

Note: As a few other commenters and answers have mentioned, this is not simply shorthand for "Perform a query filtering on the primary key". Depending on the state of the SQLAlchemy session, running this code may query the database and return a new instance, or it may return an instance of an object queried earlier in your code without actually querying the database. If you have not already done so, consider reading the documentation on the SQLAlchemy Session to understand the ramifications.

Ironhanded

Get function also supports multiple primary keys: YourModel.query.get((pk1, pk2)). Notice the tuple. – Commando

The get() function queries objects by primary key. If you would like to query by id, you should set id as primary key first. – Bonbon

Since Query.get() is deprecated since 1.4 - is the replacement the session.get(User, 5) ? – Yachtsman

@MarceloGazzola please ask a new question in a new thread, and if you need to comment on an old thread, please only comment on a single answer rather than 3 different ones. Thanks. That said, User.query.filter(User.id.in_((23,24,25,58,21))).all() should do what you want. – Na

Sorry, I am having a hard time navigating and grokking the SQLAlchemy documentation. Is the deprecated Query.get() method different than the User.query.get(23) method listed in this question? docs.sqlalchemy.org/en/14/orm/… I want to avoid using a deprecated method, but I am have not been able to figure if I should use User.query.get(23) or not. Thank you for your time 🙏 – Doreendorelia

Answer 2

You can query a User with id = 1 like this

session.query(User).get(1)

Lee

Answer 3

get() is not as your expected sometimes. If your transaction was done:

>>> session.query(User).get(1)
[SQL]: BEGIN (implicit)
[SQL]: SELECT user.id AS user_id, user.name AS user_name, user.fullname AS user_fullname
FROM user
WHERE user.id = ?
[SQL]: (1,)
<User(u'ed', u'Ed Jones')>

If you are in a transaction, get() will give you the result object in memory without query the database:

>>> session.query(User).get(1)
<User(u'ed', u'Ed Jones')>

better to use this:

>>> session.query(User.name).filter(User.id == 1).first()
[SQL]: SELECT user.name AS user_name
FROM user
WHERE user.id = ?
 LIMIT ? OFFSET ?
[SQL]: (1, 1, 0)
(u'Edwardo',)

Linsk

How is this behavior unexpected? – Court

I mean if you in a transaction (not session.commit yet) the get() seems to give you the result object in memory(without actually query the database), but the filter().first() will always query the database. – Linsk

Is it possible to concurrently change the database during the transaction? If not, using get is better due to the increase in efficiency. – Court

as I know the sqlalchemy cannot working with the async stuff( seems only with gevent ), and yes, the get is more efficient. – Linsk

Why is .first() any different from .get() as far as the transaction is concerned? Does .first() always go back to the database? Is it that .get() looks in the current env first to see if it knows that id or something? – Curly

This is a good point but I don't think this is unexpected - this is exactly how every ORM with a cache works...it would be unexpected if it didn't do this – Formic

Yes, what I mean is we should aware get() use cache and query().filter().first() seems not. – Linsk

Answer 4

If you use tables reflection you might have problems with the solutions given. (The previous solutions here didn't work for me).

What I ended up using was:

session.query(object.__class__).get(id)

(object was retrieved by reflection from the database, this is why you need to use .__class__)

I hope this helps.

Stillborn

Answer 5

As of SQLAlchemy 2.0, you can use:

with Session(engine) as session:
    user = session.get(User, 1)
    if user is not None:
        print(f'# user: {user.username}')
        print(user)

Humbertohumble

Answer 6

First, you should set id as the primary key.
Then you could use the query.get() method to query objects by id which is already the primary key.

Since the query.get() method to query objects by the primary key.
Inferred from Flask-SQLAlchemy documentation

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
db = SQLAlchemy()
db.init_app(app)

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)

def test():
    id = 1
    user = User.query.get(id)

Bingaman