flask_sqlalchemy 操作数据库_pandas numpy 是flask_sqlalchemy

#连接数据库
from flask import  Flask
from flask_sqlalchemy import  SQLAlchemy
from flask_login import UserMixin
app = Flask(__name__)
#配置数据库地址
app.config['SQLALCHEMY_DATABASE_URI'] ='mysql+mysqldb://root@localhost:3306/demo0507?charset=utf8'
# app.config['SQLALCHEMY_DATABASE_URI'] ='postgresql://postgres:dbuser@localhost:8032/demo0507'

#该配置为True,则每次请求结束都会自动commit数据库的变动
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] =True
db = SQLAlchemy(app)
#也可以db = SQLAlchemy()        db.init_app(app)

#定义模型
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

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

#删除和创建表
db.drop_all()
db.create_all()


#写入数据
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()


#修改数据
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)

#删除数据


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

#查询数据
#带条件查询
user_role =  Role.query.filter_by(role_name = 'User').first()
#查询所有
print  (Role.query.all())

print (User.query.filter_by(role= user_role).all())