基本框架
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()
原文:https://www.cnblogs.com/lianghaiming/p/12469848.html
如果您也喜欢它,动动您的小指点个赞吧