常用查询
from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine from sqlalchemy.orm import scoped_session from models import Users,Person,Hobby,Boy,Girl from sqlalchemy.sql import text engine = create_engine("mysql+pymysql://root:[email protected]:3306/sqlalchemy01", max_overflow=0, pool_size=5) Session = sessionmaker(bind=engine) session = scoped_session(Session) #### 5 高级查询--只需要看懂 #5.1 filter_by写 条件 会了 # ret = session.query(Users).filter_by(name='lqz').all() #5.2 表达式,and条件连接 between # ret = session.query(Users).filter(Users.id > 1, Users.name == 'lqz').all() # 5.3 between # select * from users where user.id between 4 and 10 and name=lqz; # ret = session.query(Users).filter(Users.id.between(4, 10), Users.name == 'lqz') # 5.4 in 条件 # ret = session.query(Users).filter(Users.id.in_([1,4,5])).all() # 5.5 ~非,除。。外 # ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all() # 5.5 二次筛选 # select * from users where id in (select id from users where name = lqz); # ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='lqz'))).all() ## 5.6 and 和 or条件 from sqlalchemy import and_, or_ # #or_包裹的都是or条件,and_包裹的都是and条件 # ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'lqz')).all() # ret = session.query(Users).filter(Users.id > 3, Users.name == 'lqz').all() # 根上面一样 # select * from users where id<=2 or name =lqz; # ret = session.query(Users).filter(or_(Users.id <= 2, Users.name == 'lqz')).all() # select * from users where id <2 or (name=lqz and id>3) or extra !=''; # ret = session.query(Users).filter( # or_( # Users.id < 2, # and_(Users.name == 'lqz', Users.id > 3), # Users.extra != "" # )) # 5.7 like # # 通配符,以e开头,不以e开头 # select * from users where name like l%; # ret = session.query(Users).filter(Users.name.like('l%')).all() # ret = session.query(Users).filter(~Users.name.like('l%')).all() #5.8 限制,用于分页,区间 # ret = session.query(Users)[1:2] # 去第一条到第二条 其实就是只取 第二条,从0开始 # 5.9 排序,根据name降序排列(从大到小) # ret = session.query(Users).order_by(Users.name.desc()).all() # ret = session.query(Users).order_by(Users.name.asc()) # ret = session.query(Users).order_by(Users.name).all() # #第一个条件重复后,再按第二个条件升序排 # ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all() #5.10 分组 from sqlalchemy.sql import func # select name from users group by name; 一旦分组,只能查询 分组字段和 聚合函数的字段 # ret = session.query(Users.name).group_by(Users.name).all() # #分组之后取最大id,id之和,最小id 和名字 ret = session.query( func.max(Users.id), func.sum(Users.id), func.min(Users.id),func.count(Users.id),Users.name).group_by(Users.name).all() # #haviing筛选 # select max(id),sum(id),min(id),count(id),name from users group by name where id >2 having min(id)>2; # ret = session.query( # func.max(Users.id), # func.sum(Users.id), # func.min(Users.id),func.count(Users.id),Users.name).filter(Users.id>2).group_by(Users.name).having(func.min(Users.id) >2) # 5.11 连表(默认用forinkey关联) # select * from person,hobby where user.hobby_id=hobby.id; # ret = session.query(Person, Hobby).filter(Person.hobby_id == Hobby.id).all() # #join表,默认是inner join # select * from person inner join hobby on person.hobby_id=hobby.id; # ret = session.query(Person).join(Hobby).all() # #isouter=True 外连,表示Person left join Hobby,没有右连接,反过来即可 # select * from person left join hobby on person.hobby_id=hobby.id; # ret = session.query(Person).join(Hobby, isouter=True).all() # ret = session.query(Hobby).join(Person, isouter=True).all() # 右链接 # 没有指定链表条件,默认以外键关联 # # 自己指定on条件(连表条件),第二个参数,支持on多个条件,用and_,同上 # select * from Person left join hobby on person.id=hobby.id; # sql 没有意义,只是讲案例 # ret = session.query(Person).join(Hobby,Person.id==Hobby.id, isouter=True) # 5.12 union和union all # # 组合(了解)UNION 操作符用于合并两个或多个 SELECT 语句的结果集 # #union和union all的区别? # q1 = session.query(Boy.id,Boy.name).filter(Boy.id > 1) # q2 = session.query(Girl.id,Girl.name).filter(Girl.id < 10) # ret = q1.union(q2).all() # # q1 = session.query(Boy.name).filter(Boy.id > 1) # q2 = session.query(Girl.name).filter(Girl.id < 10) # ret = q1.union_all(q2).all() # print(ret) # 提交事务 session.commit() # 关闭session session.close()
flask-sqlalchemy集成到flask中
## 借助于flask-sqlalchemy 1 导入 from flask_sqlalchemy import SQLAlchemy 2 实例化得到对象 db = SQLAlchemy() 3 将db注册到app中 db.init_app(app) ------2,3 可以合并为db = SQLAlchemy(app)-------- 4 视图函数中使用session 全局的db.session # 线程安全的 5 models.py 中继承Model db.Model 6 写字段 username = db.Column(db.String(80), unique=True, nullable=False) 7 配置文件中加入 SQLALCHEMY_DATABASE_URI = "mysql+pymysql://[email protected]:3306/ddd?charset=utf8" SQLALCHEMY_POOL_SIZE = 5 SQLALCHEMY_POOL_TIMEOUT = 30 SQLALCHEMY_POOL_RECYCLE = -1 # 追踪对象的修改并且发送信号 SQLALCHEMY_TRACK_MODIFICATIONS = False
flask-migrate
# 1 数据库肯定要自己创建 # 2 创建表,增加删除字段---》手动做---》django 有两个命令---》自动做 -有没有种方案,跟djagno一样,自动记录,自动迁移 # 3 django中: python manage.py makemigrations python manage.py migrate # 4 第三方模块:flask-migrate--》完成跟django一样--》命令稍微有些不同 # https://github.com/miguelgrinberg/Flask-Migrate/ pip3 install Flask-Migrate --upgrade 4.0.6 ####### 使用 from flask_migrate import Migrate app = Flask(__name__) app.config.from_pyfile('./settings.py') db = SQLAlchemy(app) # db.init_app(app) migrate = Migrate(app, db) # flask 就会多出好几个命令---》 # flask --app manage:app db init # 初始化,第一次执行,以后再也不执行了,它执行完,会出现一个migrations文件夹 # flask --app manage:app db migrate # django中的makemigrations 是一模一样 # flask --app manage:app db upgrade # 跟django的migrate一样 # flask上其他第三方插件 cors token cache restful
flask 项目
# 网上开源的---》前后端混合 layui---》rbac的权限控制 下载下来---》运行---》研究功能 # 项目--》微电影网站--》前后端混合的--》抛起来 1 pycharm打开 2 找到models 把一些注释打开,创建数据库 movie,把main打开 -执行 python models.py # 创建表,插入记录 3 把原来注释加上 4 __init__ 改真正的数据库地址 5 在cmd中执行 python manage.py runserver http://127.0.0.1:5000/admin/
django中执行原生sql
# sqlalchemy 中执行原生sql -方式一: conn = engine.raw_connection() cursor=conn.cursor(pymysql.cursors.DictCursor) cursor.execute('select * from article limit 10') res=cursor.fetchall() -方式二 res = session.query(User).from_statement(text("SELECT * FROM users where name=:name")).params(name='张三') # django 执行原生sql 方式一: from django.db import connection cur=connection.cursor() cur.execute('select distinct id, book_name from test_book') print(cur.fetchall()) cur.close() 方式二: # 原生sql,方式一,跟对象做映射: book_list=Book.objects.raw('select id,name,price,publish from app01_book where id =2') # print(book_list) # RawQuerySet # for book in book_list: # print(book.name) # 了解的,咱们不这么写 # obj_list = User.objects.raw('select id,name,price,publish from app01_book where id =2') # print(obj_list) # RawQuerySet # for obj in obj_list: # print(obj.name) # print(obj.price)
标签:name,flask,ret,09days,session,query,id,Users From: https://www.cnblogs.com/wzh366/p/18066311