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]

Please provide more details, like equivalent SQL or pseudocode doing what you want. What is "SQLAlchemy database id"? — Daniel Kluev, Jul 20 '11 at 00:13

score 186 · Accepted Answer · edited Apr 20 '21 at 17:44

186

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.

edited Apr 20 '21 at 17:44

EliadL

6,230
2
26
43

answered Jul 20 '11 at 03:34

Mark Hildreth

42,023
11
120
109

12

Get function also supports multiple primary keys: `YourModel.query.get((pk1, pk2))`. **Notice the tuple.** – marc_aragones Feb 01 '18 at 09:36
3

The `get()` function queries objects by primary key. If you would like to query by `id`, you should set `id` as primary key first. – Oct 07 '19 at 15:46
5

Since Query.get() is deprecated since 1.4 - is the replacement the `session.get(User, 5)` ? – jave.web May 07 '21 at 13:36
@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. – ggorlen Apr 18 '22 at 22:02
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? https://docs.sqlalchemy.org/en/14/orm/query.html#sqlalchemy.orm.Query.get 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 – Zhao Li Nov 03 '22 at 01:36

score 67 · Answer 2 · edited Dec 27 '22 at 00:37

67

You can query a User with id = 1 like this

session.query(User).get(1)

edited Dec 27 '22 at 00:37

starball

20,030
7
43
238

answered Apr 22 '16 at 08:24

peggygao1988

779
5
2

score 11 · Answer 3 · edited Apr 18 '22 at 21:51

11

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',)

edited Apr 18 '22 at 21:51

ggorlen

44,755
7
76
106

answered Aug 24 '18 at 03:28

panda912

196
1
6

1

How is this behavior unexpected? – Solomon Ucko Nov 17 '18 at 14:52
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. – panda912 Nov 19 '18 at 01:41
Is it possible to concurrently change the database during the transaction? If not, using `get` is better due to the increase in efficiency. – Solomon Ucko Nov 19 '18 at 01:46
1

as I know the sqlalchemy cannot working with the async stuff( seems only with gevent ), and yes, the `get` is more efficient. – panda912 Nov 20 '18 at 06:08
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? – msouth Aug 22 '19 at 14:40
1

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 – George Mauer Aug 27 '19 at 15:18
Yes, what I mean is we should aware `get()` use cache and `query().filter().first()` seems not. – panda912 Aug 29 '19 at 01:46

score 3 · Answer 4 · edited Feb 01 '22 at 08:57

3

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.

edited Feb 01 '22 at 08:57

bfontaine

18,169
13
73
107

answered Oct 21 '19 at 12:28

octaedro

609
6
8

score 1 · Answer 5 · answered Oct 07 '19 at 15:54

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)

score 0 · Answer 6 · answered Apr 07 '23 at 14:57

0

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)

answered Apr 07 '23 at 14:57

Louis Huang

589
3
7

How to query database by id using SqlAlchemy?

6 Answers6

Linked