from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
# app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = False
# app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
# app.config['SQLALCHEMY_ECHO'] = True
# pip install mysql-connecto
class Config(object):
'''配置参数'''
SQLALCHEMY_DATABASE_URI = 'mysql+mysqlconnector://root:123456@127.0.0.1:3306/flask_db?charset=utf8'
#自动跟踪数据库
SQLALCHEMY_TRACK_MODIFICATIONS = True
app.config.from_object(Config)
db = SQLAlchemy(app)
class User(db.Model):
__tablename__ = 'bd_user'
uid = db.Column(db.Integer,primary_key=True)
name = db.Column(db.String(64),unique=True)
email = db.Column(db.String(64),unique=True)
password = db.Column(db.String(255))
image = db.Column(db.String(255))
role_id = db.Column(db.Integer,db.ForeignKey('db_roles.rid'))
class Role(db.Model):
__tablename__ = 'db_roles'
rid = db.Column(db.Integer,primary_key=True)
name = db.Column(db.String(64),unique=True)
users = db.relationship("User",backref="role")
if __name__ == '__main__':
db.drop_all()
db.create_all()
role1 = Role(name="admin")
# session记录对象任务
db.session.add(role1)
# 提交任务到数据库中
db.session.commit()
role2 = Role(name='stuff')
db.session.add(role2)
db.session.commit()
user1 = User(name='Tom',email='tom@163.com',password='123456',image='/upload/img-0000.jpg',role_id=role1.rid)
user2 = User(name='Jack',email='jack@163.com',password='654321',image='/upload/img-0000.jpg',role_id=role2.rid)
user3 = User(name='lili',email='lili@163.com',password='345678',image='/upload/img-0000.jpg',role_id=role1.rid)
user4 = User(name='Pack',email='pack@163.com',password='964152',image='/upload/img-0000.jpg',role_id=role2.rid)
db.session.add_all([user1,user2,user3,user4])
db.session.commit()
'''
Role.query.all() 查询所有
r2 = Role.query.first()
r3 = Role.query.get(2)
r3.name
db.session.query(Role).all()
db.session.query(Role).get(2)
db.session.query(Role).first()
User.query.filter_by()
user = User.query.filter_by(name="Tom",role_id=1).first()
user = User.query.filter(User.name=="Tom",User.role_id==1).first()
条件查询
from sqlalchemy import or_
User.query.filter(or_(User.name=="admin",User.email.endswith("163.com"))).all()
User.query.filter().offset().limit().order_by().all()
# offset跳过两条 limit取两条
User.query.offset(1).all()
User.query.offset(1).limit(2).all()
#不推荐
User.query.order_by("-uid").all()
#推荐 降序
User.query.order_by(User.uid.desc()).all()
#升序
User.query.order_by(User.uid).all()
User.query.order_by(User.uid.asc()).all()
# 分组
from sqlalchemy import func
db.session.query(User.role_id,func.count(User.role_id)).group_by(User.role_id).all()
'''