Flask-SQLAlchemy库让flask更方便的使用SQLALchemy,是一个强大的关系形数据库框架,既可以使用orm方式操作数据库,也可以使用原始的SQL命令.
Flask-Migrate 是一个数据迁移框架,需要通过Flask-script库来操作.
一.配置Flask-SQLAlchemy
程序使用的数据库地址需要配置在SQLALCHEMY_DATABASE_URI中,SQLALchemy支持多种数据库,配置格式如下:
Postgres:
postgresql://scott:tiger@localhost/mydatabase
MySQL:
mysql://scott:tiger@localhost/mydatabase
Oracle:
oracle://scott:tiger@127.0.0.1:1521/sidname
SQLite:
sqlite:////absolute/path/to/foo.db
db是SQLALchemy类的实例,表示程序使用的数据库,为用户提供Flask-SQLALchemy的所有功能
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy app = Flask(__name__)
#配置数据库地址
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
#该配置为True,则每次请求结束都会自动commit数据库的变动
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True
db = SQLAlchemy(app)
#也可以db = SQLAlchemy() db.init_app(app)
二.定义模型
Flask-SQLALchemy使用继承至db.Model的类来定义模型,如:
class User(db.Model, UserMixin):#UserMixin是Flask-Login库中所需要的
__tablename__ = 'users'
#每个属性定义一个字段
id = db.Column(db.Integer,primary_key=True)
username = db.Column(db.String(64),unique=True)
password = db.Column(db.String(64))
def __repr__(self):
return '<User %r>' % self.username
定义完需要在Python Shell中导入db,调用db.create_all()来创建数据库
(1)常用字段选项:
primary_key 设置主键
unique 是否唯一
index 是否创建索引
nullable 是否允许为空
default 设置默认值,可以传入函数的引用 如传入 datetime.datetime.utcnow 则每次创建时时间都是最新时间
三.增删查改
(1) 插入数据:
from app.models import User
from app import db #创建一个新用户
u = User()
u.username = 'abc'
u.password = 'abc'
#将用户添加到数据库会话中
db.session.add(u)
#将数据库会话中的变动提交到数据库中,如果不Commit,数据库中是没有改动的
db.commit()
(2)查找数据:
#返回所有用户保存到list中
user_list = User.query.all() #查找username为abc的第一个用户,返回用户实例
u = User.query.filter_by(username='abc').first() #模糊查找用户名以c结尾的所有用户
user_list = User.query.filter(username.endswith('c')).all() #查找用户名不是abc的用户
u = User.query.filter(username != 'abc').first()
(3)删除数据:
user = User.query.first()
db.session.delete(user)
db.session.commit()
(4)修改数据:
u = User.query.first()
u.username = 'sb'
db.session.commit()
四.一对多关系
我的理解是:在多的一边定义外键,而relathonship()函数是用来建立关系的,可以只在一边定义,也可以两边都使用(只在一边使用时加上了backref选项等同于两边都使用)
class Person(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(50))
#backref将在Address表中创建个名为persons的Person引用,之后可以使用address.persons访问这个地址的所有人
addresses = db.relationship('Address', backref='persons',
lazy='dynamic') class Address(db.Model):
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(50))
#在多的一边使用db.ForeignKey声明外键
person_id = db.Column(db.Integer, db.ForeignKey('person.id'))
五.多对多关系
多对多关系可以分解为原表和关联表之间两个多对一关系,如下代码建立了学生与所选课程之间的关系:
#创建关联表,两个字段的外键是另两个表,一个学生对应多个关联表,一个关联表对应多个课程
registrations = db.Table('registrations',
db.Column('student_id',db.Integer,db.ForeignKey('students.id')),
db.Column('class_id',db.Integer,db.ForeignKey('classes.id'))
) class Student(db.Model):
__tablename__ = 'students'
id = db.Column(db.Integer,primary_key=True,)
name = db.Column(db.String)
classes = db.relationship('Class',
secondary = registrations, #关联表,只需要在一个表建立关系,sqlalchemy会负责处理好另一个表
backref = db.backref('students',lazy='dynamic'),
lazy = 'dynamic') class Class(db.Model):
__tablename__ = 'classes'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String)
多对多的使用:
#学生1增加一门选课
student1.classes.append(class1)
#学生1退选class1
student1.classes.remove(class1)
#学生1所选课程,由于指定了lazy='dynamic'所以没有直接返回列表,而需要使用.all()
student1.classes.all()
六.分页导航
Flask-SQLALchemy的Pagination对象可以方便的进行分页,
对一个查询对象调用pagenate(page, per_page=20, error_out=True)函数可以得到pagination对象,第一个参数表示当前页,第二个参数代表每页显示的数量,error_out=True的情况下如果指定页没有内容将出现404错误,否则返回空的列表
#从get方法中取得页码
page = request.args.get('page', 1, type = int)
#获取pagination对象
pagination = Post.query.order_by(Post.timestamp.desc()).paginate(page, per_page=10, error_out = False) #pagination对象的items方法返回当前页的内容列表
posts = pagination.items
pagination对象常用方法:
has_next :是否还有下一页
has_prev :是否还有上一页
items : 返回当前页的所有内容
next(error_out=False) : 返回下一页的Pagination对象
prev(error_out=False) : 返回上一页的Pagination对象
page : 当前页的页码(从1开始)
pages : 总页数
per_page : 每页显示的数量
prev_num : 上一页页码数
next_num :下一页页码数
query :返回 创建这个Pagination对象的查询对象
total :查询返回的记录总数
iter_pages(left_edge=2, left_current=2, right_current=5, right_edge=2)
在模版中使用
{% macro render_pagination(pagination, endpoint) %}
<div class=pagination>
{%- for page in pagination.iter_pages() %}
{% if page %}
{% if page != pagination.page %}
<a href="{{ url_for(endpoint, page=page) }}">{{ page }}</a>
{% else %}
<strong>{{ page }}</strong>
{% endif %}
{% else %}
<span class=ellipsis>…</span>
{% endif %}
{%- endfor %}
</div>
{% endmacro %}
七.事件监听
Flask-SQLALchemy不但提供了方便的数据库操作,还提供了事件的监听,如下
from sqlalchemy import event def my_append_listener(target, value, initiator):
print "received append event for target: %s" % target event.listen(MyClass.collection, 'append', my_append_listener)
Listeners have the option to return a possibly modified version of the value, when the retval=Trueflag is passed to listen():
def validate_phone(target, value, oldvalue, initiator):
"Strip non-numeric characters from a phone number" return re.sub(r'(?![0-9])', '', value) # setup listener on UserContact.phone attribute, instructing
# it to use the return value
listen(UserContact.phone, 'set', validate_phone, retval=True)
A validation function like the above can also raise an exception such as ValueError to halt the operation.
Several modifiers are available to the listen() function.
| Parameters: |
|
|---|
- append(target, value, initiator)
-
Receive a collection append event.
Parameters: - target – the object instance receiving the event. If the listener is registered with raw=True, this will be the InstanceState object.
- value – the value being appended. If this listener is registered withretval=True, the listener function must return this value, or a new value which replaces it.
- initiator – the attribute implementation object which initiated this event.
Returns: if the event was registered with retval=True, the given value, or a new effective value, should be returned.
- remove(target, value, initiator)
-
Receive a collection remove event.
Parameters: - target – the object instance receiving the event. If the listener is registered with raw=True, this will be the InstanceState object.
- value – the value being removed.
- initiator – the attribute implementation object which initiated this event.
Returns: No return value is defined for this event.
- set(target, value, oldvalue, initiator)
-
Receive a scalar set event.
Parameters: - target – the object instance receiving the event. If the listener is registered with raw=True, this will be the InstanceState object.
- value – the value being set. If this listener is registered with retval=True, the listener function must return this value, or a new value which replaces it.
- oldvalue – the previous value being replaced. This may also be the symbol NEVER_SET or NO_VALUE. If the listener is registered withactive_history=True, the previous value of the attribute will be loaded from the database if the existing value is currently unloaded or expired.
- initiator – the attribute implementation object which initiated this event.
Returns: if the event was registered with retval=True, the given value, or a new effective value, should be returned.