1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
|
Modifying and Querying Data
===========================
Insert, Update, Delete
----------------------
See SQLAlchemy's `ORM tutorial`_ and other SQLAlchemy documentation for more information
about modifying data with the ORM.
.. _ORM tutorial: https://docs.sqlalchemy.org/tutorial/orm_data_manipulation.html
To insert data, pass the model object to ``db.session.add()``:
.. code-block:: python
user = User()
db.session.add(user)
db.session.commit()
To update data, modify attributes on the model objects:
.. code-block:: python
user.verified = True
db.session.commit()
To delete data, pass the model object to ``db.session.delete()``:
.. code-block:: python
db.session.delete(user)
db.session.commit()
After modifying data, you must call ``db.session.commit()`` to commit the changes to
the database. Otherwise, they will be discarded at the end of the request.
Select
------
See SQLAlchemy's `Querying Guide`_ and other SQLAlchemy documentation for more
information about querying data with the ORM.
.. _Querying Guide: https://docs.sqlalchemy.org/orm/queryguide.html
Queries are executed through ``db.session.execute()``. They can be constructed
using :func:`~sqlalchemy.sql.expression.select`. Executing a select returns a
:class:`~sqlalchemy.engine.Result` object that has many methods for working with the
returned rows.
.. code-block:: python
user = db.session.execute(db.select(User).filter_by(username=username)).scalar_one()
users = db.session.execute(db.select(User).order_by(User.username)).scalars()
Queries for Views
-----------------
If you write a Flask view function it's often useful to return a ``404 Not Found`` error
for missing entries. Flask-SQLAlchemy provides some extra query methods.
- :meth:`.SQLAlchemy.get_or_404` will raise a 404 if the row with the given id doesn't
exist, otherwise it will return the instance.
- :meth:`.SQLAlchemy.first_or_404` will raise a 404 if the query does not return any
results, otherwise it will return the first result.
- :meth:`.SQLAlchemy.one_or_404` will raise a 404 if the query does not return exactly
one result, otherwise it will return the result.
.. code-block:: python
@app.route("/user-by-id/<int:id>")
def user_by_id(id):
user = db.get_or_404(User, id)
return render_template("show_user.html", user=user)
@app.route("/user-by-username/<username>")
def user_by_username(username):
user = db.one_or_404(db.select(User).filter_by(username=username))
return render_template("show_user.html", user=user)
You can add a custom message to the 404 error:
.. code-block:: python
user = db.one_or_404(
db.select(User).filter_by(username=username),
description=f"No user named '{username}'."
)
Legacy Query Interface
----------------------
You may see uses of ``Model.query`` or ``session.query`` to build queries. That query
interface is considered legacy in SQLAlchemy. Prefer using the
``session.execute(select(...))`` instead.
See :doc:`legacy-query` for documentation.
|
