CODEWITHSUNDEEP
pythonsqlmodelsqlalchemy
user507220
user507220

Reputation:

How to query database by id using SqlAlchemy?

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]

Upvotes: 134

Views: 181222

Answers (6)

Louis Huang
Louis Huang

Reputation: 984

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)

Upvotes: 4

peggygao1988
peggygao1988

Reputation: 799

You can query a User with id = 1 like this

session.query(User).get(1)

Upvotes: 69

panda912
panda912

Reputation: 366

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

Upvotes: 13

octaedro
octaedro

Reputation: 649

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.

Upvotes: 3

Mark Hildreth
Mark Hildreth

Reputation: 43071

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.

Upvotes: 189

Justin Lee
Justin Lee

Reputation: 890

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)

Upvotes: 2

Related Questions