How to query database by id using SqlAlchemy?

python sql model sqlalchemy
user507220 picture user507220 · Jul 19, 2011 · Viewed 79.4k times · Source

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]

Answer

Mark Hildreth picture Mark Hildreth · Jul 20, 2011

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.

Related questions

SQLAlchemy - order_by on relationship for join table

I'm using declarative SQLAlchemy and I have three models: Role, Permission, and RolePermission. In my Role model, I have the following: class Role(Base): name = Column(u'NAME', VARCHAR(50), nullable=False, unique=True) permissionLinks = relationship(RolePermission, backref="role", order_by=name) …

Read More
Connecting to Microsoft SQL server using Python

I am trying to connect to SQL through python to run some queries on some SQL databases on Microsoft SQL server. From my research online and on this forum the most promising library seems to be pyodbc. So I have …

Read More
How to execute raw SQL in Flask-SQLAlchemy app

How do you execute raw SQL in SQLAlchemy? I have a python web app that runs on flask and interfaces to the database through SQLAlchemy. I need a way to run the raw SQL. The query involves multiple table joins …

Read More