知识问答

如何使用SqlAlchemy通过ID查询数据库？

内容 隐藏
问题：如何使用SqlAlchemy通过ID查询数据库？
回答 0
回答 1
回答 2
回答 3
回答 4

问题：如何使用SqlAlchemy通过ID查询数据库？

我需要按其查询SQLAlchemy数据库 id类似于以下内容

User.query.filter_by（username =’peter’）

但为身份证。我该怎么做呢？[通过Google和SO搜索没有帮助]

点击查看英文原文

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]

回答 0

查询具有一个get函数，该函数支持通过表的主键进行查询，我认为id是这样。

例如，要查询ID为23的对象：

User.query.get(23)

注意：正如其他一些评论者和答案所述，这不仅仅是“对主键执行查询过滤”的简写。根据SQLAlchemy会话的状态，运行此代码可能会查询数据库并返回一个新实例，或者它可能会返回在代码中较早查询的对象的实例，而无需实际查询数据库。如果您尚未这样做，请考虑阅读SQLAlchemy会话上的文档以了解后果。

点击查看英文原文

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.

回答 1

您可以像这样查询ID = 1的用户

session.query(User).get(1)

点击查看英文原文

You can query an User with id = 1 like this

session.query(User).get(1)

回答 2

有时get（）并非您所期望的：

如果您的交易已完成：

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

如果您正在进行事务处理（get（）将在不查询数据库的情况下为您提供结果对象在内存中）：

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

最好使用这个：

>>> 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',)
点击查看英文原文

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

回答 3

如果您使用的tables reflection话，可能会遇到给定的解决方案问题。（这里的先前解决方案对我不起作用）。

我最终使用的是：

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

（object是通过反射从数据库中检索到的，这就是为什么需要使用.__class__）

我希望这有帮助。

点击查看英文原文

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.

回答 4

首先，应将其设置id为主键。
然后您可以使用该query.get()方法来查询对象id已经是主键的对象。

由于该query.get()方法通过主键查询对象。
从Flask-SQLAlchemy文档推断

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)
点击查看英文原文

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)