1 #连接
 2 from sqlalchemy.ext.declarative import declarative_base
 3 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
 4 from sqlalchemy.orm import sessionmaker, relationship
 5 from sqlalchemy import create_engine
 6 import pymysql
 7 Base = declarative_base()
 8 engine=create_engine("mysql+pymysql://root:@localhost:3306/db2",max_overflow=5)
 9 Session=sessionmaker(bind=engine)
10 session=Session()
11 
12 
13 #提交与关闭
14 session.commit()#提交数据，增删改都需要
15 session.close()#关闭

 1 class UserType(Base):
 2     __tablename__=‘usertype‘
 3     id=Column(Integer,primary_key=True,autoincrement=True)
 4     title=Column(String(32),nullable=True,index=True)
 5 
 6 class Users(Base):
 7     __tablename__=‘users‘
 8     id=Column(Integer,primary_key=True)
 9     name=Column(String(32))
10     email=Column(String(16))
11 def create_db(): #创建表
12         engine=create_engine("mysql+pymysql://root:@localhost:3306/db2",max_overflow=5)
13         Base.metadata.create_all(engine)
14 def drop_db(): #删除表
15         engine = create_engine("mysql+pymysql://root:@localhost:3306/db2", max_overflow=5)
16         Base.metadata.drop_all(engine)

1 #增加单条
2 obj1=UserType(title=‘普通用户‘)
3 session.add(obj1)
4 #增加多条
5 objs=[
6     UserType(title=‘白金用户‘),
7     UserType(title=‘黑金用户‘),
8 ]
9 session.add_all(objs)

1 # 以下相当于sellect xx from UserType where...
2 usertype_list=session.query(UserType.title).filter(UserType.id>1)#filer里面加的是表达式

1 session.query(UserType.title).filter(UserType.id>1).delete()
2 
3 ###修改某一列
4 ##更新全部
5 session.query(UserType.title).filter(UserType.id==1).update({‘title‘:‘黑经‘})
6 ###更新字符串
7 session.query(UserType.title).filter(UserType.id>1).update({UserType.title: UserType.title + "X"}, synchronize_session=False) ##更新全部
8 ###更新数字
9 session.query(UserType.title).filter(UserType.id>1).update({"id": UserType.id + 1}, synchronize_session="evaluate")

 1 #　条件
 2 #filter_by表示分组？filter里面必须放表达式
 3 ret = session.query(Users).filter_by(name=‘alex‘).all() 
 4 #filter里两个条件并列默认and关系
 5 ret = session.query(Users).filter(Users.id > 1, Users.name == ‘eric‘).all()
 6 ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == ‘eric‘).all()
 7 ret = session.query(Users).filter(Users.id.in_([1,3,4])).all()
 8 #~相当于not in
 9 ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all()
10 ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name=‘eric‘))).all()
11 #引入and_和or_模块from sqlalchemy import and_, or_
12 ret = session.query(Users).filter(and_(Users.id > 3, Users.name == ‘eric‘)).all()
13 ret = session.query(Users).filter(or_(Users.id < 2, Users.name == ‘eric‘)).all()
14 ret = session.query(Users).filter(
15     or_(
16         Users.id < 2,
17         and_(Users.name == ‘eric‘, Users.id > 3),
18         Users.extra != ""
19     )).all()
20 
21 
22 # 通配符，若为_则表示一个字符
23 ret = session.query(Users).filter(Users.name.like(‘e%‘)).all()
24 ret = session.query(Users).filter(~Users.name.like(‘e%‘)).all()
25 
26 # 限制
27 ret = session.query(Users)[1:2]
28 
29 # 排序
30 ret = session.query(Users).order_by(Users.name.desc()).all()
31 ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()#第一个相同时根据第二个排
32 
33 # 分组，相当于mysql的聚合函数
34 from sqlalchemy.sql import func
35 
36 ret = session.query(Users).group_by(Users.extra).all()
37 ret = session.query(
38     func.max(Users.id),
39     func.sum(Users.id),
40     func.min(Users.id)).group_by(Users.name).all()
41 
42 ret = session.query(
43     func.max(Users.id),
44     func.sum(Users.id),
45     func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all()