1.安装sqlalchemypip install sqlalchemy2.创建表-方式1import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, Stringengine = create_engine("mysql+mysqldb://root:123456@localhost/testdb",encoding='utf-8', echo=True)# engine = create_engine("mysql+mysqldb://root:123456@127.0.0.1/testdb",encoding='utf-8', echo=True)Base = declarative_base() #生成orm基类 class User(Base): __tablename__ = 'user' #表名 id = Column(Integer, primary_key=True) name = Column(String(32)) password = Column(String(64)) #通过父类创建子类 Base.metadata.create_all(engine) #创建表结构2.创建表-方式2from sqlalchemy import Table, MetaData, Column, Integer, String, ForeignKeyfrom sqlalchemy.orm import mapper metadata = MetaData() user = Table('user', metadata, Column('id', Integer, primary_key=True), Column('name', String(50)), Column('fullname', String(50)), Column('password', String(12)) ) class User(object): def __init__(self, name, fullname, password): self.name = name self.fullname = fullname self.password = password mapper(User, user) #the table metadata is created separately with the Table construct, then associated with the User class via the mapper() function3.创建表后，添加数据import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, Stringfrom sqlalchemy.orm import sessionmakerengine = create_engine("mysql+mysqldb://root:123456@localhost/testdb",encoding='utf-8', echo=True)# engine = create_engine("mysql+mysqldb://root:123456@127.0.0.1/testdb",encoding='utf-8', echo=True)Base = declarative_base() #生成orm基类 class User(Base): __tablename__ = 'user' #表名 id = Column(Integer, primary_key=True) name = Column(String(32)) password = Column(String(64)) #通过父类创建子类 Base.metadata.create_all(engine) #创建表结构Session_class = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例Session = Session_class() #生成session实例 user_obj = User(name="zhangsan",password="123456") #生成你要创建的数据对象print(user_obj.name,user_obj.id) #此时还没创建对象呢，不信你打印一下id发现还是None Session.add(user_obj) #把要创建的数据对象添加到这个session里， 一会统一创建print(user_obj.name,user_obj.id) #此时也依然还没创建 Session.commit() #现此才统一提交，创建数据#注意：User对象创建的时候，并没有指定id,默认从1开始， 可以自己手动添加user_obj = User(id="3",name="zhangsan",password="123456") #生成你要创建的数据对象4.查询4.1数据表和数据都已经添加后，既可以在之前的基础上执行查询：data = Session.query(User).filter_by(name="zhangsan").all()print (data[0].id,data[0].name,data[0].password)输出 1 zhangsan 123456其中查找到的数据组成一个列表，如果只查到1行，data就是[]如果查到多行就是[[],[],[],...]如果直接print data，就是输出一个内存对象地址。如果想知道内存对象的其它信息，可以如下定义User：class User(Base): __tablename__ = 'user' #表名 id = Column(Integer, primary_key=True) name = Column(String(32)) password = Column(String(64))def __repr__(self): return "<id = %d ,name =%s>"%(self.id,self.name)这时候print data就是<id=1,name=zhangsan>这就能清楚知道返回了哪些数据4.2 查询命令data = Session.query(User).filter(User.name="zhangsan").all()data = Session.query(User).filter(User.id>2).filter(User.id<4).all()data = Session.query(User).filter(User.id>2).filter(User.id<4).first()4.3统计命令data = Session.query(User).filter(User.name.like("Ra%")).count()4.4分组命令from sqlalchemy import funcprint(Session.query(func.count(User.name),User.name).group_by(User.name).all() )ret = session.query(User).group_by(User.password).all()ret = session.query( func.max(User.id), func.sum(User.id), func.min(User.id)).group_by(User.name).all()ret = session.query( func.max(User.id), func.sum(User.id), func.min(User.id)).group_by(User.name).having(func.min(User.id) >2).all()5.修改data = Session.query(User).filter_by(name="zhangsan").all()print (data[0].id,data[0].name,data[0].password)输出 1 zhangsan 123456此时：data.password="123"Session.commit() #此时就将zhangsan的密码改成了1236.回滚Session.rollback() 7.增：方法1：obj = User(name="zhang", password='123')session.add(obj)方法2：session.add_all([ User(name="wang", password='123'), User(name="liu", password='123'),])方法3：obj1 = User(name="zhang", password='123')obj2 = User(name="zhang", password='123')session.add_all([obj1,obj2])session.commit()8.删：session.query(User).filter(User.id > 2).delete()session.commit()9.改：session.query(User).filter(User.id > 2).update({"name" : "099"})session.query(User).filter(User.id > 2).update({User.name: User.name + "099"}, synchronize_session=False)session.query(User).filter(User.id > 2).update({"num": User.num + 1}, synchronize_session="evaluate")session.commit()10.查：ret = session.query(User).all()ret = session.query(User.name, User.password).all()ret = session.query(User).filter_by(name='zhang').all()ret = session.query(User).filter_by(name='zhang').first()11.条件查询ret = session.query(User).filter_by(name='alex').all()ret = session.query(User).filter(User.id > 1, User.name == 'eric').all()ret = session.query(User).filter(User.id.between(1, 3), User.name == 'eric').all()ret = session.query(User).filter(User.id.in_([1,3,4])).all()ret = session.query(User).filter(~User.id.in_([1,3,4])).all()ret = session.query(User).filter(User.id.in_(session.query(User.id).filter_by(name='eric'))).all()from sqlalchemy import and_, or_ret = session.query(User).filter(and_(User.id > 3, User.name == 'eric')).all()ret = session.query(User).filter(or_(User.id < 2, User.name == 'eric')).all()ret = session.query(User).filter( or_( User.id < 2, and_(User.name == 'eric', User.id > 3), User.extra != "" )).all()12.通配符：ret = session.query(User).filter(User.name.like('z%')).all()ret = session.query(User).filter(~User.name.like('z%')).all()13限制ret = session.query(User)[1:2]14.排序ret = session.query(User).order_by(User.name.desc()).all()ret = session.query(User).order_by(User.name.desc(), User.id.asc()).all()15.多表查询class User(Base): __tablename__ = 'user' #表名 id = Column(Integer, primary_key=True) name = Column(String(32)) password = Column(String(64))def __repr__(self): return "<id = %d ,name =%s>"%(self.id,self.name) class Favor(Base): __tablename__ = 'favor' nid = Column(Integer, primary_key=True) caption = Column(String(50), default='red', unique=True) def __repr__(self): return "%s-%s" %(self.nid, self.caption)class Person(Base): __tablename__ = 'person' nid = Column(Integer, primary_key=True) name = Column(String(32), index=True, nullable=True) favor_id = Column(Integer, ForeignKey("favor.nid")) # 与生成表结构无关，仅用于查询方便 favor = relationship("Favor", backref='pers')注意：favor = relationship("Favor", backref='pers')作用：Person实例p1中可以直接使用p1.favor来访问Favor对象，例如p1.favor.capation Favor实例f1中可以直接使用f1.pers来访问Person对象，例如f1.pers.name方法1：User 和 Favor无需外键关联ret = session.query(User, Favor).filter(User.id == Favor.nid).all()方法2：Person和 Favor需外键关联favor_id = Column(Integer, ForeignKey("favor.nid"))ret = session.query(Person).join(Favor).all()方式3：ret = session.query(Person).join(Favor, isouter=True).all()16.组合q1 = session.query(User.name).filter(User.id > 2)q2 = session.query(Favor.caption).filter(Favor.nid < 2)ret = q1.union(q2).all()q1 = session.query(User.name).filter(User.id > 2)q2 = session.query(Favor.caption).filter(Favor.nid < 2)ret = q1.union_all(q2).all() 
