《Flask Web Development》学习笔记---chapter5 Database

1. 分析各种数据库，Relational databases 和 NoSQL databases。

2. 用Flask-SQLAlchemy

3.  Model Definition  和  Relationships

4. Database Operations

1. 创建表

from models import db
db.drop_all()
db.create_all()

如果某张表已经在数据库存在，db.create_all()是不会re-create或者update它的。所以只有先db.drop_all()
P.S:所以要学 database migration

2.插入

对象实例：

u= User(xxxxx)

加入session：

db.session.add(u)

将操作提交数据库：

db.session.commit()

新知识：

一次性加入session：

db.session.add_all([u1, u2, u3, u4, u5])

3. error occurs

db.session.rollback()

到底会回退到什么状态？不知道，懒得查，求解答

4. 删除

db.session.delete(u)
db.session.commit()

5.  查询

>>> User.query.all()
[<User u'john'>, <User u'susan'>, <User u'david'>]

过滤条件

>>>User.query.filter_by(role=user_role).all()
[<User u'susan'>, <User u'david'>]

检查SQL query语句：

>>> str(User.query.filter_by(role=user_role))
'SELECT users.id AS users_id, users.username AS users_username,
users.role_id AS users_role_id FROM users WHERE :param_1 = users.role_id'

6.  常用SQLAlchemy 查询过滤器

filter()             Returns a new query that adds an additional filter to the original query 
filter_by()          Returns a new query that adds an additional equality filter to the original query
limit()              Returns a new query that limits the number of results of the original query to the given number
offset()             Returns a new query that applies an offset into the list of results of the original query
order_by()           Returns a new query that sorts the results of the original query according to the given criteria
group_by()           Returns a new query that groups the results of the original query according to the given criteria                  

7. 常用SQLAlchemy 查询执行器

all()          Returns all the results of a query as a list
first()        Returns the first result of a query, or None if there are no results
frist_or_404()     没结果就报404
get()          Returns the row that matches the given primary key, or None if no matching row is found
get_or_404         没结果就报404
count()        Returns a Pagination object that contains the specified range of results

5. 用Flask-Migrate 进行数据库迁移

等看了flask-migrate文档再说。