在Flask中使用Flask_sqlalchemy进行数据库操作
为编写一个小型网站程序，考虑到灵活性/操作简单的原因，选择了Flask框架，使用了Flask_sqlalchemy来进行数据库操作。使用过程中，学习了很多新的方法，现记录如下。（注：以下内容部分原创，有部分资料性的知识均为借鉴）
一、Flask_sqlalchemy简单介绍
Flask本身没有内置orm框架，需要依赖第三方模块。Flask-SQLAlchemy 是 Flask 的扩展，本质上是对sqlalchemy的进一步封装。
（sqlalchemy框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果，
二、安装/导入
pip3 install sqlalchemy
from flask_sqlalchemy import SQLAlchemy
操作数据库需要先创建一个db对象。
db=SQLAlchemy()
三、初始化连接/建模
1、在使用sqlalchemy之前，要先配置SQLALCHEMY_DATABASE_URI等几个重要选项
base_dir = os.path.abspath(os.path.dirname(__file__))
app=Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + os.path.join(base_dir, r'db\***.sqlite3')
还有几个不常用的配置选项
SQLALCHEMY_BINDS 一个将会绑定多种数据库的字典。 SQLALCHEMY_ECHO 调试设置为true SQLALCHEMY_POOL_SIZE 数据库池的大小，默认值为5。 SQLALCHEMY_POOL_TIMEOUT 连接超时时间 SQLALCHEMY_POOL_RECYCLE 自动回收连接的秒数。 SQLALCHEMY_MAX_OVERFLOW 控制在连接池达到最大值后可以创建的连接数。当这些额外的 连接回收到连接池后将会被断开和抛弃。 SQLALCHEMY_TRACK_MODIFICATIONS 如果设置成 True (默认情况)，Flask-SQLAlchemy 将会追踪对象的修改并且发送信号。这需要额外的内存， 如果不必要的可以禁用它。
2、关联app
两种方法：
db=SQLalchemy()
app=Falsk(__name__) db.init_app(app)
app=Flask(__name__) db=SQLalchemy(app)
3、建立模型
常用字段类型有：
| 数据类型 | 说明 |
| Integer | 整型 |
| String | 字符串 |
| Text | 文本 |
| Datetime | 时间 |
| Date | 日期 |
| Float | 浮点型 |
| Boolean | 布尔 |
| PickleType | 序列化对象 |
| LargeBinary | 大长度二进制 |
创建模型，其中primary_key指定主键，uniqure指定是否允许重复，default指定默认值，String(80)指定了字符串的长度，db.relationship()用于在两个表之间建立一对多关系。例如下边例子中Job表中一个Job项目，可以对应Progress表中多个数据。实现这种关系时，要在“多”这一侧加入一个外键，指向“一”这一侧联接的记录。
class Job(db.Model): __tablename__ = 'job' id = db.Column(db.Integer, primary_key=True) jobname = db.Column(db.String(80), unique=True) jobcontent = db.Column(db.Text) department = db.Column(db.String, default='办公室') jobdate = db.Column(db.Date, default=datetime.date.today) job_status = db.Column(db.Boolean, default=False)
#一对多中的“一”端，后期利用Job.ps,可以得到一个对象列表。
ps = db.relationship('Progress', backref='job')
class Progress(db.Model): __tablename__ = 'progress' id = db.Column(db.Integer, primary_key=True) p_data = db.Column(db.Date, default=datetime.date.today) p_content = db.Column(db.Text) p_cname=db.Column(db.Text)
#一对多中的“多”端，后期利用Progress.job,可以获取工作项目的对象。 j_id = db.Column(db.Integer, db.ForeignKey('job.id'), nullable=False)
3、初始化
with app.app_context():
db.create_all()
with app.app_context():
db.drop_all()
db.create_all()
四、增删改查
1、增加操作：
u_name = request.form.get('username') pwd = request.form.get('pwd') c_name = request.form.get('c_name') role = request.form.get('role') result = UserInfo.query.filter_by(username=u_name).first() if result is None: with app.app_context(): user1 = UserInfo(username=u_name, pwd=generate_password_hash( pwd), c_name=c_name, role=role) db.session.add(user1) db.session.commit() else: flash('用户名重复！')
2、删除操作（查询要删除的对象，后进行delete)：
#先找到要删除的对象
user1 = UserInfo.query.get(result.get('userid', '0'))
#如果对象存在
if user1: try: db.session.delete(user1) db.session.commit() return jsonify({'emsg': '删除成功！'}) except: return jsonify({'emsg': '删除失败!'}) return jsonify({'emsg': '找不到用户！'})
3、修改操作(查询要修改的对象，后进行修改）：
user2 = UserInfo.query.get(result.get('userid', '0')) if user2: try: user2.pwd = generate_password_hash(user2.username) db.session.commit() return jsonify({'emsg': '重置成功！'}) except: return jsonify({'emsg': '重置失败!'}) else: return jsonify({'emsg': '找不到用户！'}) else: return jsonify({'emsg': '非法操作！'})
3、查找操作
常用的SQLAlchemy查询过滤器
| 过滤器 | 说明 |
|---|---|
| filter() | 把过滤器添加到原查询上，返回一个新查询 |
| filter_by() | 把等值过滤器添加到原查询上，返回一个新查询 |
| limit | 使用指定的值限定原查询返回的结果 |
| offset() | 偏移原查询返回的结果，返回一个新查询 |
| order_by() | 根据指定条件对原查询结果进行排序，返回一个新查询 |
| group_by() |
根据指定条件对原查询结果进行分组，返回一个新查询
|
常用的SQLAlchemy查询执行器
| 方法 | 说明 |
|---|---|
| all() | 以列表形式返回查询的所有结果 |
| first() | 返回查询的第一个结果，如果未查到，返回None |
| first_or_404() | 返回查询的第一个结果，如果未查到，返回404 |
| get() | 返回指定主键对应的行，如不存在，返回None |
| get_or_404() | 返回指定主键对应的行，如不存在，返回404 |
| count() | 返回查询结果的数量 |
| paginate() | 返回一个Paginate对象，它包含指定范围内的结果 |
常用的查询例子
模型.query: 得到了所有模型的数据的结果集对象
模型.query.执行器：得到了指定数据的结果集中的内容。 模型.query.过滤器: 过滤出了想要的数据,还是一个查询结果集对象 模型.query.过滤器.执行器: 取出了结果集中的内容 查询所有用户数据 User.query.all() ==> [user1,user2] 查询有多少个用户 User.query.count() 查询第1个用户 User.query.first() 查询id为4的用户[3种方式] User.query.get(4) (这里面只能是数字 或 ‘4’) 查询不到不会报错 User.query.filter_by(id = 4).first() （一个等号）查询不到不会报错 User.query.filter(User.id == 4).first() 查询不到不会报错 查询名字结尾字符为g的所有数据[开始/包含] User.query.filter(User.name.endswith('g')).all() User.query.filter(User.name.startswith('g')).all() User.query.filter(User.name.contains('g')).all() 查询名字不等于wang的所有数据[2种方式] User.query.fliter(User.name != 'wang').all() 或者 from sqlalchemy import not_ User.qudry.filter(not_(User.name == 'wang')) 查询名字和邮箱都以 li 开头的所有数据[2种方式] from sqlalchemy import and_ User.query.filter(User.name.startswith('li'),User.email.startswith('li')).all() User.query.filter(and_(User.name.startswith('li'),User.email.startswith('li'))).all() 查询password是 `123456` 或者 `email` 以 `itheima.com` 结尾的所有数据 from sqlalchemy import or_ User.query.filter(or_(User.password == '123456',User.email.endswith('itheima.com'))).all() 查询id为 [1, 3, 5, 7, 9] 的用户列表 User.query.filter(User.id.in_([1,3,5,7,9])).all() 查询name为liu的角色数据 user = User.query.filter(User.name == 'liu').first() role = Role.query.filter(Role.id == user.role_id).first() 查询所有用户数据，并以邮箱排序 User.query.order_by(User.email).all() User.query.order_by(User.email.desc()).all() 每页3个，查询第2页的数据 paginate = User.query.paginate(page, per_page,Error_out) paginate = User.query.paginate(2,3,False) page: 哪一个页 per_page: 每页多少条数据 Error_out: False 查不到不报错 paginate .pages: 共有多少页 paginate .items: 当前页数的所有对象 paginate .page: 当前页
例子:
if page == None:
page = 1
#按时间由近到远顺序，每页10条信息
jobs = Job.query.order_by(Job.jobdate.desc()).paginate(page=page, per_page=10)
return render_template('index.html', jobs=jobs, username=current_user.c_name)
sqlalchemy查询常见的过滤方式 filter过滤条件： 过滤是数据提取的一个很重要的功能，以下对一些常用的过滤条件进行解释，并且这些过滤条件都是只能通过filter方法实现的： 1. equals ： == user1=User.query.filter(User.username=="admin").first（）
2. not equals : !=
user1=User.query.filter(User.username！="admin").first（）
3. like[不区分大小写] & ilike[区分大小写]：
key = request.json.get('s_key')
if key:
jobs = Job.query.order_by(Job.jobdate.desc()).filter(Job.jobname.like('%'+key+'%')).all()
4. in：
User.query.filter(User.name.in_(['ed','wendy','jack']))
5. not in：
User.query.filter(~User.name.in_(['ed','wendy','jack']))
6. is null：
User.query.filter(User.name==None)
# 或者是 User.query.filter(User.name.is_(None))
7. is not null:
User.query.filter(User.name != None)
# 或者是 User.query.filter(User.name.isnot(None))
8. and：
User.query.filter(and_(User.name=='ed',User.c_name=='Ed Jones'))
# 或者是传递多个参数
User.query.filter(User.name=='ed',User.fullname=='Ed Jones')
# 或者是通过多次filter操作
User.query.filter(User.name=='ed').filter(User.fullname=='Ed Jones')
9. or：
User.query.filter(or_(User.name=='ed',User.name=='wendy'))
filter filter_by的区别
-
filter用类名.属性名，比较用==，filter_by直接用属性名，比较用=
-
filter不支持组合查询，只能连续调用filter来变相实现。而filter_by的参数是**kwargs，直接支持组合查询。
- 例子：
User.query.filter_by(id = 4).first() User.query.filter(User.id==4).first() 以上两条查询语句是等值的，其实在实际中，一般用filter_by比较多
五、数据迁移
数据迁移
一般情况下如果生成表后，不再修改表结构的话，就不需要考虑迁移的问题了；另外如果数据表比较简单，内部数据较少，在修改时，可以直接删除表，再重新生成即可。
但是在多数情况下，每次修改数据库表字段的时候，必须删除表，然后重新运行'db.create_all' 才会重新映射。这样就太麻烦了。
因此我们要考虑数据迁移的问题。
修改完表结构后需要映射到数据库中，这里需要用到flask-migrate库。下面是启动文件manage.py。 from flask_script import Manager, Server from app import app from flask_migrate import Migrate, MigrateCommand from ext import db from first import models # 模型文件必须导入进来，否则运行报错 manager = Manager(app) Migrate(app=app, db=db) manager.add_command('db', MigrateCommand) # 创建数据库映射命令 manager.add_command('start', Server(port=8000, use_debugger=True)) # 创建启动命令 if __name__ == '__main__': manager.run() 配置好启动文件后，进入项目根目录，在命令行输入以下代码： >python manage.py db init >python manage.py db migrate >python manage.py db upgrade 注意：flask_migrate ＝＝2.7.0 flask_script 中修改from flask_script._compat import text_type
六、其它小问题
1、flask-sqlalchemy中模型： DateTime Date Time 是三种不同的类型，要与视图对应。
2、实现网站查询功能时，要注意%的应用。如：
jobs = Job.query.order_by(Job.jobdate.desc()).filter( Job.jobname.like('%'+key+'%')).paginate(page=1, per_page=10)
3、在非视图函数中，使用数据查询等语句，需要将其置于 with app.app_context():里。
