SQLAlchemy

是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果

安装

pip3 install sqlalchemy 

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 # 连接MySQL engine = create_engine('mysql+pymysql://root:123@127.0.0.1:3306/db3?charset=utf8', max_overflow=5) Base = declarative_base() """ create table db3(     id int auto_increment primary key,     name varchar(32) not null default '',     extra varchar(32) not null default ''     ) """ """ UserType id     title      xx00 1      普通用户 row.xxoo  : 多条记录对象 """ class UserType(Base):     __tablename__ = 'usertype'     id = Column(Integer, autoincrement=True, primary_key=True)     title = Column(String(32), nullable=False, default='') """ users id   name  extra   type_id 1    aa     nz      1 usertype = releationship('Usertype') row.usertype """ class Users(Base):     __tablename__ = 'users'     id = Column(Integer, autoincrement=True, primary_key=True)     name = Column(String(32), nullable=False, default='')     extra = Column(String(32), nullable=False, default='')     type_id = Column(Integer, ForeignKey(UserType.id))     # usertype = relationship('UserType',backref='xx00')     __table_args__ = (         UniqueConstraint('id', 'name', name='mix_id_name'),         # 联合唯一索引         Index('ix_name_extra', 'name', 'extra'),         # 组合索引     ) def drop_db():     Base.metadata.drop_all(engine) # 会将当前执行文件中所有继承自base类的类,生成表 def create_db():     Base.metadata.create_all(engine) create_db() Session = sessionmaker(bind=engine) session = Session() # 增加一条数据 obj = UserType(title='普通用户') session.add(obj) # 添加多条数据 session.add_all([     UserType(title='1'),     UserType(title='2'),     UserType(title='3'),     UserType(title='4'),     UserType(title='5') ]) # 查询 # 查询全部,返回的是一个列表,列表中套对象 res = session.query().all() for row in res:     print(row.id, row.name) # 查询一条数据 res = session.query(UserType).first() print(res) # where 条件 res = session.query(UserType).filter(UserType.name == '1', UserType.id == 2).all() for row in res:     print(row.id, row.name) print(res[0].name, res[0].id) res = session.query(UserType).filter_by(name='1').all() print(res) # 删除 session.query(UserType).filter(UserType.id > 3).delete() # 修改 session.query(UserType).filter(UserType.id == 3).updata({'name': '2'}) # MySQL 高级查询操作 # 通配符  分组 分页 排序 between and in not in # between...and... res = session.query(UserType).filter(UserType.id.between(1, 4)).all() for row in res:     print(row.id, row.title) # in 操作 res = session.query(UserType).filter(UserType.id.in_([1, 2, 5])).all() print(res) # not in # select * from Usertype res = session.query(UserType).filter(~UserType.id.in_([1, 2, 5])).all() for row in res:     print(row.id, row.title) # session.query(UserType).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all() # res = session.query(UserType.title).filter(~UserType.id.in_([1,3,4])).all() # for row in res: #     print( row.title) from sqlalchemy import and_, or_ # 通配符 # res = session.query(Users).all() from sqlalchemy.sql import func # rea = session.query( #     Users.type_id, #     func.max(Users.id), #     func.min(Users.id)).group_by(Users.type_id).all() # # print(rea) ret = session.query(     func.max(Users.id),     func.min(Users.id)).group_by(Users.type_id).having(func.min(Users.id) > 2).all() # left jion # 1. 查询某一个用户的用户类型 # 第一种方法: res = session.query(Users, UserType).join(UserType, isouter=True).all() print(res) for row in res:     print(row[0].id, row[0].name, row[1].title) # 二 res = session.query(Users).all() for row in res:     print(row.id, row.name, row.extra, row.usertype.tiytle) # 2. 某一个类型下面的用户 ### 第一种方法 res = session.query(UserType).all() for row in res:     print(row.id, row.title, session.query(Users).filter(Users.type_id == row.id)).all() ### 第二种方法 res = session.query(UserType).all() for row in res:     print(row.id, row.title, row.xx00)