二:flask_sqlalchemy 对数据库进行基本操作

安装好flask及的基本开发环境后(之前一篇的博客里有说明),本篇开始将使用flask里常用的一些扩展组件编写一些测试代码，本篇主要列举写flask_sqlalchemy的演示案例
环境:win7,python2.7,flask,flask_sqlalchemy,mysql
IDE：pycharm
1、MysqlTool.py 主要是数据库连接的代码

  #!/usr/bin/python  # -*- coding: UTF-8 -*-  from flask import  Flask  from  flask_sqlalchemy import  SQLAlchemy  app = Flask(__name__)  #配置数据库地址  app.config['SQLALCHEMY_DATABASE_URI'] ='mysql://root:root123456@localhost/my_blog'  #app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'  #该配置为True,则每次请求结束都会自动commit数据库的变动  app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True  app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] =True  db = SQLAlchemy(app)  #也可以db = SQLAlchemy()        db.init_app(app)

2.MysqModel.py - ORM模型

from MysqlTool import  dbfrom flask_login import  UserMixin#角色class Role(db.Model):    __tablename__ ='roles'    id = db.Column(db.Integer,primary_key=True)    role_name = db.Column(db.String(64),unique=True)    users = db.relationship('User',backref = 'role')    def __repr__(self):        return '<Role %r>' % self.role_name#定义模型 Flask-SQLALchemy使用继承至db.Model的类来定义模型,如:class User(db.Model,UserMixin):    __tablename__ = 'users'    #每个属性定义一个字段    id = db.Column(db.Integer,primary_key=True)    user_name = db.Column(db.String(64),unique=True)    pass_word = db.Column(db.String(64))    email = db.Column(db.String(64))    phone = db.Column(db.String(64))    role_id = db.Column(db.Integer,db.ForeignKey('roles.id'))    def __repr__(self):        return '<User %r>' % self.user_name

3、创建表或者删除表

#创建数据库db.create_all()#删除库db.drop_all()

4、日入数据示列

from db.MysqlTool import dbfrom db.MysqModel import User,Role#添加数据admin_role = Role(role_name = 'Amdmin')mod_role = Role(role_name = 'Moderator')user_role = Role(role_name = 'User')user_john = User(user_name = 'john',role = admin_role)user_susan = User(user_name='susan', role=user_role)user_david = User(user_name='david', role=user_role)db.session.add_all([admin_role, mod_role, user_role,    user_john, user_susan, user_david])#提交事务db.session.commit()

5、修改数据示列

from db.MysqlTool import dbfrom db.MysqModel import User,Role#根据条件查询一行数据admin_role = Role.query.filter_by(role_name = 'Amdmin').first()#修改数据-admin_role.role_name = 'Admin'db.session.add(admin_role)db.session.commit()print  admin_role.role_name

6、删除数据示列

from db.MysqlTool import dbfrom db.MysqModel import User,Role#根据条件查询一行数据admin_role = Role.query.filter_by(role_name = 'Admin').first()#删除db.session.delete(admin_role)db.session.commit()

7、查询数据示列

from db.MysqlTool import dbfrom db.MysqModel import User,Role#带条件查询user_role =  Role.query.filter_by(role_name = 'User').first()#查询所有print  Role.query.all()print User.query.filter_by(role= user_role).all()

基本操作就到这里，更多的案例，可看官方API文档