数据库创建步骤:
1.安装sqlalchemy:pip3 install flask-sqlalchemy
2.配置数据库:mysql/sqlite
3.定义表结构,设计表
4.创建表
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:@127.0.0.1:3306/demo'
db = SQLAlchemy() # 数据库对象
# 此处将app与db绑定到一起,后续对app的操作就是对db的操作。
db.init(app)
class User(db.Model):
"""设计User表模型"""
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 create_all():
"""生成表"""
return db.create_all()
create_all()
1.常用数据格式:
Integer:一个整数
String(size):有长度限制的字符串
Text:一些较长的unicode文本
DateTime: 表示为python datetime对象的时间和日期
Float:存储浮点值
Boolean:存储布尔值
PickleType:存储为一个持久化的python对象
LargeBinary:存储一个任意大的二进制数据
2.参数:
db.ForeignKey('project.id')
primary_key:主键,唯一标识
autoincrement:自增
unique:唯一
index:索引
nullable:可以为空
default:默认值
comment:说明,注释
多个数据库绑定
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:password@127.0.0.1:3306/tasks'
# 绑定多个数据库
app.config['SQLALCHEMY_BINDS'] = {
'users':'mysql+pymysql://root:password@127.0.0.1:3306/users',
'students':'mysql+pymysql://root:password@127.0.0.1:3306/students',
'runs':'mysql+pymysql://root:password@127.0.0.1:3306/runs'
}
db = SQLAlchemy() # 数据库对象
# 此处将app与db绑定到一起,后续对app的操作就是对db的操作。
db.init_app(app)
class User(db.Model):
"""模型绑定,设计User表模型"""
__bind_key__ = 'users' # 指定绑定的数据库
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 create_all():
"""生成表"""
return db.create_all()
# 运行
with app.app_context() as ctx:
ctx.push()
user = User(username='chuntian', email='chuntian.163.com')
db.session.add(user)
db.session.commit()
插入与查询:
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:password@127.0.0.1:3306/tasks'
# 绑定多个数据库
app.config['SQLALCHEMY_BINDS'] = {
'users': 'mysql+pymysql://root:password@127.0.0.1:3306/users',
'students': 'mysql+pymysql://root:password@127.0.0.1:3306/students',
'runs': 'mysql+pymysql://root:password@127.0.0.1:3306/runs'
}
db = SQLAlchemy() # 数据库对象
# 此处将app与db绑定到一起,后续对app的操作就是对db的操作。
db.init_app(app)
class User(db.Model):
"""模型绑定,设计User表模型"""
__bind_key__ = 'users' # 指定绑定的数据库
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 create_all():
"""生成表"""
return db.create_all()
@app.route('/insert')
def insert():
# 插入数据
with app.app_context() as ctx:
ctx.push()
user = User(username='chuntian', email='chuntian.163.com')
user2 = User(username='chuntian2', email='chuntian.163.com')
db.session.add(user) # 保存到会话
db.session.add_all([user, user2]) # 保存多个
try:
db.session.commit() # 事务提交
except:
db.session.rollback() # 回滚
return 'insert success'
@app.route('/query')
def query():
users = User.query.all()
print(users)
return 'query success'
@app.route('/query_sql')
def query_sql():
"""sql_alchemy原生sql"""
users_obj = db.session.execute('SELECT * FROM user;')
users = users_obj.fetchall()
print(users)
return 'query success'
@app.route('/query_params')
def query_params():
""":name 原生sql语句参数化"""
sql = 'SELECT * FROM user where name = :name;'
params = {"name": "chuntian"}
users_obj = db.session.execute(sql, params)
users = users_obj.fetchall()
print(users)
return 'query success'
if __name__ == '__main__':
create_all()
app.run(debug=True)
orm执行原生sql:
db.session.execute(sql)
ORM查询与过滤:
1.所有 all()
users = User.query.all()===>select * from user;
2.第一个 first()
users = User.query.first()
3.get() 通过主键去获取
users = User.query.get(1)
多个主键:
users = User.query.get((1,5))
users = User.query.get({'id':1, 'project_id':3})
4.filter_by() 过滤
admin = User.query.filter_by(username='admin').first()
5.filter() 复杂过滤
User.query.filter(User.email.endswith('@163.com')).all()
排序:
User.query.order_by(User.username.desc()).all()
限制数量:
User.query.order_by(User.username.desc()).limit(1).all()
删除:
db.session.delete(me)
db.session.commit()
数据更新:
user = User.query.get(id)
user.name = 'xxx'
db.session.add(user) / db.session.add([user1,user2])
db.session.commit()
数据库迁移
flask db init # 生成migrations文件
flask db migrate # 生成脚本
flask db upgrade # 更新到数据库
flask db downgrade # 退回
多表查询:
多对一关系:db.relationship()
backref
class Project(db.Model):
id = db.Column(db.Integer, primary_key=True)
modules = db.relationship('Module', backref='project')
class Module(db.Model):
id = db.Column(db.Integer, primary_key=True)
project_id = db.Column(db.Integer,db.ForeignKey('project.id'))
back_populates