flask-sqlalchemy数据库自动更新

发布网友发布时间：2022-04-26 22:00

共3个回答

懂视网时间：2022-04-08 05:53

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(),unique=True)
 password = db.Column(db.String())

 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: active_history=False – When True, indicates that the “set” event would like to receive the “old” value being replaced unconditionally, even if this requires firing off database loads. Note that active_history can also be set directly viacolumn_property() and relationship().

propagate=False – When True, the listener function will be established not just for the class attribute given, but for attributes of the same name on all current subclasses of that class, as well as all future subclasses of that class, using an additional listener that listens for instrumentation events.

raw=False – When True, the “target” argument to the event will be theInstanceState management object, rather than the mapped instance itself.

retval=False – when True, the user-defined event listening must return the “value” argument from the function. This gives the listening function the opportunity to change the value that is ultimately used for a “set” or “append” event.

Parameters:	active_history=False – When True, indicates that the “set” event would like to receive the “old” value being replaced unconditionally, even if this requires firing off database loads. Note that `active_history` can also be set directly via`column_property()` and `relationship()`. propagate=False – When True, the listener function will be established not just for the class attribute given, but for attributes of the same name on all current subclasses of that class, as well as all future subclasses of that class, using an additional listener that listens for instrumentation events. raw=False – When True, the “target” argument to the event will be the`InstanceState` management object, rather than the mapped instance itself. retval=False – when True, the user-defined event listening must return the “value” argument from the function. This gives the listening function the opportunity to change the value that is ultimately used for a “set” or “append” event.

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 with`retval=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:

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 with`active_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.

if the event was registered with retval=True, the given value, or a new effective value, should be returned.

(请您对文章做出评价) « 上一篇：Flask学习记录之Flask-Login
» 下一篇：Flask学习记录之使用Werkzeug散列密码

[ZHUAN]Flask学习记录之Flask-SQLAlchemy

标签：

热心网友时间：2022-04-08 03:01

python3下用flask-sqlalchemy对mysql数据库操作案例：
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://username:password@localhost/db_name'
db = SQLAlchemy(app)

class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True)
email = db.Column(db.String(120), unique=True)

def __init__(self, username, email):
self.username = username
self.email = email

def __repr__(self):
return '<User %r>' % self.username

admin = User('admin', 'admin@example.com')

db.create_all() # In case user table doesn't exists already. Else remove it.

db.session.add(admin)

db.session.commit() # This is needed to write the changes to database

User.query.all()

User.query.filter_by(username='admin').first()

热心网友时间：2022-04-08 04:19

数据库是按照数据结构来组织、存储和管理数据的仓库，数据管理不再仅仅是存储和管理数据，而转变成用户所需要的各种数据管理的方式。数据库有很多种类型，从最简单的存储有各种数据的表格到能够进行海量数据存储的大型数据库系统都在各个方面得到了广泛的应用。在信息化社会，充分有效地管理和利用各类信息资源，是进行科学研究和决策管理的前提条件。数据库技术是管理信息系统、办公自动化系统、决策支持系统等各类信息系统的核心部分，是进行科学研究和决策管理的重要技术手段。数据库是一个单位或是一个应用领域的通用数据处理系统，它存储的是属于企业和事业部门、团体和个人的有关数据的集合。数据库中的数据是从全局观点出发建立的，按一定的数据模型进行组织、描述和存储。其结构基于数据间的自然联系，从而可提供一切必要的存取路径，且数据不再针对某一应用，而是面向全组织，具有整体的结构化特征。数据库中的数据是为众多用户所共享其信息而建立的，已经摆脱了具体程序的*和制约。不同的用户可以按各自的用法使用数据库中的数据;多个用户可以同时共享数据库中的数据资源，即不同的用户可以同时存取数据库中的同一个数据。数据共享性不仅满足了各用户对信息内容的要求，同时也满足了各用户之间信息通信的要求。

全部栏目