1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86
| 复制from flask import Flask from flask_sqlalchemy import SQLAlchemy import pymysql pymysql.install_as_MySQLdb()
app = Flask(__name__)
class Config(object): """配置参数""" # 设置连接数据库的URL user = 'root' password = 123456 database = 'flask_ex' app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://%s:%s@127.0.0.1:3306/%s' % (user,password,database)
# 设置sqlalchemy自动更跟踪数据库 SQLALCHEMY_TRACK_MODIFICATIONS = True
# 查询时会显示原始SQL语句 app.config['SQLALCHEMY_ECHO'] = True
# 禁止自动提交数据处理 app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = False
# 读取配置 app.config.from_object(Config)
# 创建数据库sqlalchemy工具对象 db = SQLAlchemy(app) # 创建模型类 class Role(db.Model): # 定义表名 __tablename__ = 'roles' # 定义字段 id = db.Column(db.Integer, primary_key=True,autoincrement=True) name = db.Column(db.String(64), unique=True) users = db.relationship('User',backref='role') # 反推与role关联的多个User模型对象 def __init__(self, name, users): self.name = name self.users = users
def __repr__(self): return '<User %r>' % self.name class User(db.Model): # 定义表名 __tablename__ = 'users' # 定义字段 id = db.Column(db.Integer, primary_key=True,autoincrement=True) name = db.Column(db.String(64), unique=True, index=True) email = db.Column(db.String(64),unique=True) pswd = db.Column(db.String(64)) role_id = db.Column(db.Integer, db.ForeignKey('roles.id')) # 设置外键
@app.route('/') def index(): # 删除所有表,避免创建重复表 db.drop_all() # 创建所有表 db.create_all() # 执行sql语句 items = db.session.execute('select *from movies') items = list(items) print(items) # 翻译为delete from user where id = 1 User.query.filter_by(id=1).delete() # 翻译为 update user set name = '张三' where id = 2 User.query.filter_by(id=4).update({'name': 'susu'}) # 简单的的全量查询,翻译为select *from user ulist = User.query.all() # 插入一条数据 完整操作 role1 = Role(name='admin') # session记录到对象任务中 db.session.add(role1)# 记录一个 db.session.add_all([role1, role1, role1])# 记录多个 # 提交任务 db.session.commit()#只需执行一次 if __name__ == "__main__": app.run()
|