ret = session.query(Users).order_by(Users.name.desc()).all() #第一个条件重复后，再按第二个条件升序排 ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all() # 分组 from sqlalchemy.sql import func ret = session.query(Users).group_by(Users.extra).all() #分组之后取最大id，id之和，最小id ret = session.query( func.max(Users.id), func.sum(Users.id), func.min(Users.id)).group_by(Users.name).all() #haviing筛选 ret = session.query( func.max(Users.id), func.sum(Users.id), func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all() # 连表（默认用forinkey关联） ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all() #join表，默认是inner join ret = session.query(Person).join(Favor).all() #isouter=True 外连，表示Person left join Favor，没有右连接，反过来即可 ret = session.query(Person).join(Favor, isouter=True).all() #打印原生sql aa=session.query(Person).join(Favor, isouter=True) print(aa) # 自己指定on条件（连表条件）,第二个参数，支持on多个条件，用and_,同上 ret = session.query(Person).join(Favor,Person.id==Favor.id, isouter=True).all() # 组合（了解）UNION 操作符用于合并两个或多个 SELECT 语句的结果集 #union和union all的区别？ q1 = session.query(Users.name).filter(Users.id > 2) q2 = session.query(Favor.caption).filter(Favor.nid < 2) ret = q1.union(q2).all() q1 = session.query(Users.name).filter(Users.id > 2) q2 = session.query(Favor.caption).filter(Favor.nid < 2) ret = q1.union_all(q2).all()

9.执行原生sql

import time
import threading

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine
from sqlalchemy.sql import text
from sqlalchemy.engine.result import ResultProxy
from db import Users, Hosts

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)

session = Session()

# 查询
# cursor = session.execute('select * from users')
# result = cursor.fetchall()

# 添加
cursor = session.execute('insert into users(name) values(:value)',params={"value":'lqz'})
session.commit()
print(cursor.lastrowid)

session.close()

10.一对多

import time
import threading

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine
from sqlalchemy.sql import text
from sqlalchemy.engine.result import ResultProxy
from db import Users, Hosts, Hobby, Person

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = Session()
# 添加
"""
session.add_all([
    Hobby(caption='乒乓球'),
    Hobby(caption='羽毛球'),
    Person(name='张三', hobby_id=3),
    Person(name='李四', hobby_id=4),
])

person = Person(name='张九', hobby=Hobby(caption='姑娘'))
session.add(person)
#添加二
hb = Hobby(caption='人妖')
hb.pers = [Person(name='文飞'), Person(name='博雅')]
session.add(hb)

session.commit()
"""

# 使用relationship正向查询
"""
v = session.query(Person).first()
print(v.name)
print(v.hobby.caption)
"""

# 使用relationship反向查询
"""
v = session.query(Hobby).first()
print(v.caption)
print(v.pers)
"""
#方式一，自己链表
# person_list=session.query(models.Person.name,models.Hobby.caption).join(models.Hobby,isouter=True).all()
person_list=session.query(models.Person,models.Hobby).join(models.Hobby,isouter=True).all()
for row in person_list:
    # print(row.name,row.caption)
    print(row[0].name,row[1].caption)

#方式二：通过relationship

person_list=session.query(models.Person).all()
for row in person_list:
    print(row.name,row.hobby.caption)
#查询喜欢姑娘的所有人
obj=session.query(models.Hobby).filter(models.Hobby.id==1).first()
persons=obj.pers
print(persons)
session.close()

11.多对多

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sql.models import Girl,Boy,Boy2Girl

engine = create_engine("mysql+pymysql://root:@127.0.0.1:3307/flask-test?charset=utf8", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = Session()
# 添加
'''
session.add_all([
    Girl(name='g_com1'),
    Girl(name='g2.com1'),
    Boy(name='A组1'),
    Boy(name='B组2'),
])
session.commit()

s2g = Boy2Girl(girl_id=2, boy_id  =2)
session.add(s2g)
session.commit()


gp = Boy(name='C组')
gp.girl = [Girl(name='c3.com'),Girl(name='c4.com')]
session.add(gp)
session.commit()


ser = Girl(name='c6.com')
ser.boys = [Boy(name='F组pp'),Boy(name='G组ll')]
session.add(ser)
session.commit()
'''
"""


# 使用relationship正向查询
"""
'''
v = session.query(Boy).first()
print(v.name)
print(v.girl)
'''
# 使用relationship反向查询

'''
v = session.query(Girl).first()
print(v.name)
print(v.boys)
'''

session.close()

12.其它

import time
import threading

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine
from sqlalchemy.sql import text, func
from sqlalchemy.engine.result import ResultProxy
from db import Users, Hosts, Hobby, Person, Group, Server, Server2Group

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = Session()

# 关联子查询:correlate(Group)表示跟Group表做关联，as_scalar相当于对该sql加括号，用于放在后面当子查询
subqry = session.query(func.count(Server.id).label("sid")).filter(Server.id == Group.id).correlate(Group).as_scalar()
result = session.query(Group.name, subqry)
"""
SELECT `group`.name AS group_name, (SELECT count(server.id) AS sid 
FROM server 
WHERE server.id = `group`.id) AS anon_1 
FROM `group`
"""
'''

select * from tb where id in [select id from xxx];

select id,
		name,
		#必须保证此次查询只有一个值
		(select max(id) from xxx) as mid
from tb

例如，第三个字段只能有一个值
id name  mid
1  lqz   1，2  不合理
2  egon   2


'''
'''
成绩表：
id sid    cid    score
1  1      物理      99 
2  1      化学      88
3  2      物理      95

学生表：
id   name  每个学生总分数
1     xx      88
2     yy       77

select id，name,
(select avr(score) from 成绩表 where 成绩表.sid=学生表.id) as x
from 学生表
subqry = session.query(func.count(成绩表.scort).label("sc")).filter(学生表.id == 成绩表.sid).correlate(学生表).as_scalar()
result = session.query(学生表.name, subqry)

'''

# 原生SQL
"""
# 查询
cursor = session.execute('select * from users')
result = cursor.fetchall()

# 添加
cursor = session.execute('insert into users(name) values(:value)',params={"value":'wupeiqi'})
session.commit()
print(cursor.lastrowid)
"""

session.close()

13.Flask-SQLAlchemy

flask和SQLAchemy的管理者，通过他把他们做连接

db = SQLAlchemy()
	- 包含配置
	- 包含ORM基类
	- 包含create_all
	- engine
	- 创建连接

离线脚本，创建表

详见代码

flask-migrate
python3 manage.py db init 初始化：只执行一次

python3 manage.py db migrate 等同于 makemigartions
python3 manage.py db upgrade 等同于migrate