Home›Flask in 5 Days›Day 2
Day 2 of 5
⏱ ~60 minutes
Flask in 5 Days — Day 2

SQLAlchemy and Databases

Connect Flask to a database via SQLAlchemy. Define models, run migrations with Flask-Migrate, and perform CRUD operations.

Setting Up SQLAlchemy

Terminal
pip install flask-sqlalchemy flask-migrate
app.py
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///app.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)
migrate = Migrate(app, db)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    posts = db.relationship('Post', backref='author', lazy=True)

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
    body = db.Column(db.Text, nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
Terminal — Migrations
flask db init
flask db migrate -m 'initial'
flask db upgrade
CRUD in Views
# CREATE
new_post = Post(title='Hello', body='World', user_id=1)
db.session.add(new_post)
db.session.commit()

# READ
posts = Post.query.all()
post = Post.query.get_or_404(post_id)
posts = Post.query.filter_by(user_id=1).order_by(Post.id.desc()).all()

# UPDATE
post.title = 'New Title'
db.session.commit()

# DELETE
db.session.delete(post)
db.session.commit()
📝 Day 2 Exercise
Add Database to Your App
  1. S
  2. e
  3. t
  5. u
  6. p
  8. S
  9. Q
  10. L
  11. A
  12. l
  13. c
  14. h
  15. e
  16. m
  17. y
  19. w
  20. i
  21. t
  22. h
  24. a
  26. U
  27. s
  28. e
  29. r
  31. a
  32. n
  33. d
  35. P
  36. o
  37. s
  38. t
  40. m
  41. o
  42. d
  43. e
  44. l
  45. .
  47. R
  48. u
  49. n
  51. m
  52. i
  53. g
  54. r
  55. a
  56. t
  57. i
  58. o
  59. n
  60. s
  61. .
  63. A
  64. d
  65. d
  67. r
  68. o
  69. u
  70. t
  71. e
  72. s
  74. t
  75. o
  77. c
  78. r
  79. e
  80. a
  81. t
  82. e
  83. ,
  85. l
  86. i
  87. s
  88. t
  89. ,
  91. a
  92. n
  93. d
  95. d
  96. e
  97. l
  98. e
  99. t
  100. e
  102. p
  103. o
  104. s
  105. t
  106. s
  107. .
  109. U
  110. s
  111. e
  113. <
  114. c
  115. o
  116. d
  117. e
  118. >
  119. g
  120. e
  121. t
  122. _
  123. o
  124. r
  125. _
  126. 4
  127. 0
  128. 4
  129. <
  130. /
  131. c
  132. o
  133. d
  134. e
  135. >
  137. f
  138. o
  139. r
  141. t
  142. h
  143. e
  145. d
  146. e
  147. t
  148. a
  149. i
  150. l
  152. v
  153. i
  154. e
  155. w
  156. .

Day 2 Summary

  • db = SQLAlchemy(app) and model classes that extend db.Model. Fields are class attributes.
  • flask db migrate + flask db upgrade — run every time you change a model.
  • db.session.add(obj) + db.session.commit() to save. db.session.delete(obj) to remove.
  • Relationships: db.ForeignKey on the child + db.relationship on the parent for easy traversal.
→
← Previous
Day 1: Routes, Views, and Jinja2 Templates
Next →
Day 3: Forms and Validation
→
Finished this lesson?