4.SQLALchemy基本介绍
SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。
一.创建表：
1.创建表
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 engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/db1", max_overflow=5) # 生成一个sqlorm基类 Base = declarative_base() # 创建表，一个类代表一张表 class Users(Base): # 添加表结构 __tablename__ = 't_u_users'# 创建表名 # 创建id字段，类型是int，主键且进行自增 id = Column(Integer, primary_key=True,autoincrement=True) # 创建name字段，varchar类型，长度是32，唯一索引 name = Column(String(32),unique=True) # 创建extra字段，varchar类型，长度16，普通索引 extra = Column(String(16),index=True) __table_args__ = ( # id，name 联合唯一索引 UniqueConstraint('id', 'name', name='uix_id_name'), # 组合索引 Index('ix_id_name', 'name', 'extra'), ) # 创建表结构 Base.metadata.create_all(engine) # 删除表 # Base.metadata.drop_all(engine)
2.创建一对多表，通过外键进行关联
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 engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/db1", max_overflow=5) Base=declarative_base() class man(Base): __tablename__='men' id = Column(Integer, primary_key=True) name = Column(String(32),index=True) age = Column(Integer) class woman(Base): __tablename__='women' id=Column(Integer,primary_key=True) name=Column(String(32),index=True) age=Column(Integer) men_id=Column(Integer,ForeignKey('men.id'),nullable=False) Base.metadata.create_all(engine) mySession=sessionmaker(bind=engine) session=mySession() # father表添加数据 f1=man(name='张三',age=25) session.add(f1) session.commit() # son表添加数据 s1=woman(name='小张',age=5,men_id=1) s2=woman(name='小三',age=5,men_id=1) session.add_all([s1,s2]) session.commit()
3.创建一对多表，通过relationship进行关联关系
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 engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/db1", max_overflow=5) Base=declarative_base() class Son(Base): __tablename__='son' id=Column(Integer,primary_key=True) name=Column(String(32),index=True) age=Column(Integer) father_id=Column(Integer,ForeignKey('father.id'),nullable=False) class Father(Base): __tablename__='father' id = Column(Integer, primary_key=True) name = Column(String(32),index=True) age = Column(Integer) son = relationship('Son') Base.metadata.create_all(engine) mySession=sessionmaker(bind=engine) session=mySession() # father表添加数据 f1=Father(name='张三',age=25) # son表添加数据 s1=Son(name='小张',age=5) s2=Son(name='小三',age=5) # 添加关联关系 f1.son=[s1,s2] session.add_all([f1,s1,s2]) session.commit()
4.关联查询与relationship
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 engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/db1", max_overflow=5) Base=declarative_base() class son(Base): __tablename__='son1' id=Column(Integer,primary_key=True) name=Column(String(32),index=True) age=Column(Integer) father_id=Column(Integer,ForeignKey('father1.id'),nullable=False) class father(Base): __tablename__='father1' id = Column(Integer, primary_key=True) name = Column(String(32),index=True) age = Column(Integer) son = relationship('son') Base.metadata.create_all(engine) mySession=sessionmaker(bind=engine) session=mySession() # father表添加数据 # f1=Father(name='张三',age=25) # # son表添加数据 # s1=Son(name='小张',age=5) # s2=Son(name='小三',age=5) # # # 添加关联关系 # f1.son=[s1,s2] # session.add_all([f1,s1,s2]) # session.commit() # SELECT father1.name AS kk, son1.id AS pp FROM father1 INNER JOIN son1 ON father1.id = son1.father_id sql=session.query(father.name.label('kk'),son.id.label('pp')).join(son) # first()关联查询出列表中的第一条数据 ret1=session.query(father.name.label('kk'),son.id.label('pp')).join(son).first() # all()关联查询出列表中的所有数据 ret2=session.query(father.name.label('kk'),son.id.label('pp')).join(son).all() print("sql=========",sql,ret1,ret2) ret=session.query(father).filter_by(id=1).all() print(ret)
通过father信息获取到所有son的信息
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 engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/db1", max_overflow=5) Base=declarative_base() class son(Base): __tablename__='son1' id=Column(Integer,primary_key=True) name=Column(String(32),index=True) age=Column(Integer) father_id=Column(Integer,ForeignKey('father1.id'),nullable=False) class father(Base): __tablename__='father1' id = Column(Integer, primary_key=True) name = Column(String(32),index=True) age = Column(Integer) son = relationship('son') Base.metadata.create_all(engine) mySession=sessionmaker(bind=engine) session=mySession() # father表添加数据 # f1=Father(name='张三',age=25) # # son表添加数据 # s1=Son(name='小张',age=5) # s2=Son(name='小三',age=5) # # # 添加关联关系 # f1.son=[s1,s2] # session.add_all([f1,s1,s2]) # session.commit() # SELECT father1.name AS kk, son1.id AS pp FROM father1 INNER JOIN son1 ON father1.id = son1.father_id sql=session.query(father.name.label('kk'),son.id.label('pp')).join(son) # first()关联查询出列表中的第一条数据 ret1=session.query(father.name.label('kk'),son.id.label('pp')).join(son).first() # all()关联查询出列表中的所有数据 ret2=session.query(father.name.label('kk'),son.id.label('pp')).join(son).all() print("sql=========",sql,ret1,ret2) # 查询出father表中id等于1数据所有的儿子 ret=session.query(father).filter_by(id=1).first() # 获取到所有son的列表对象 print(ret.son) for son in ret.son: print(son.name,son.age)
通过son表的信息来找父亲
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 engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/db1", max_overflow=5) Base=declarative_base() class son(Base): __tablename__='son1' id=Column(Integer,primary_key=True) name=Column(String(32),index=True) age=Column(Integer) father_id=Column(Integer,ForeignKey('father1.id'),nullable=False) class father(Base): __tablename__='father1' id = Column(Integer, primary_key=True) name = Column(String(32),index=True) age = Column(Integer) son = relationship('son',backref='father') Base.metadata.create_all(engine) # Base.metadata.drop_all(engine) mySession=sessionmaker(bind=engine) session=mySession() # father表添加数据 # f1=father(name='张三',age=25) # # son表添加数据 # s1=son(name='小张',age=5) # s2=son(name='小三',age=5) # # # # 添加关联关系 # f1.son=[s1,s2] # session.add_all([f1,s1,s2]) # session.commit() # # SELECT father1.name AS kk, son1.id AS pp FROM father1 INNER JOIN son1 ON father1.id = son1.father_id # sql=session.query(father.name.label('kk'),son.id.label('pp')).join(son) # # # first()关联查询出列表中的第一条数据 # ret1=session.query(father.name.label('kk'),son.id.label('pp')).join(son).first() # # # all()关联查询出列表中的所有数据 # ret2=session.query(father.name.label('kk'),son.id.label('pp')).join(son).all() # print("sql=========",sql,ret1,ret2) # # # 查询出father表中id等于1数据所有的儿子 # ret=session.query(father).filter_by(id=1).first() # # 获取到所有son的列表对象 # print(ret.son) # for son in ret.son: # print(son.name,son.age) # 通过儿子的对象来找父亲 ret=session.query(son).filter_by(id=1).first() print(ret.father.name)
5.创建多对多关系
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 engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/db1", max_overflow=5) Base=declarative_base() # 创建关系表，关系表要放在第一个 class Men_to_Women(Base): __tablename__='men_to_women' nid=Column(Integer,primary_key=True,autoincrement=True) men_id=Column(Integer,ForeignKey('men.nid')) women_id=Column(Integer,ForeignKey('women.nid')) class Men(Base): __tablename__='men' nid=Column(Integer,primary_key=True) name=Column(String(32)) age=Column(Integer) class Women(Base): __tablename__='women' nid=Column(Integer,primary_key=True) name=Column(String(32)) age=Column(Integer) bf=relationship('Men',secondary=Men_to_Women.__table__,backref='gf') Base.metadata.create_all(engine) # 往men、women表中添加数据 m1=Men(name='test',age=18) m2=Men(name='java',age=18) w1=Women(name='小三',age=23) w2=Women(name='小五',age=24) mySession=sessionmaker(bind=engine) cursor=mySession() # cursor.add_all([m1,m2,w1,w2]) # cursor.commit() # 通过插入数据进行添加关联关系 # t1=Men_to_Women(men_id=1,women_id=2) # cursor.add(t1) # cursor.commit() # 通过relationship进行添加关联关系 m4=cursor.query(Women).filter_by(nid=1).first() w4=cursor.query(Men).all() m4.bf=w4 cursor.add(m4) cursor.commit()
二.操作表：
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 engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/db1", max_overflow=5) # 生成一个sqlorm基类 Base = declarative_base() # 创建表，一个类代表一张表 class Users(Base): # 添加表结构 __tablename__ = 't_u_users'# 创建表名 # 创建id字段，类型是int，主键且进行自增 id = Column(Integer, primary_key=True,autoincrement=True) # 创建name字段，varchar类型，长度是32，唯一索引 name = Column(String(32),unique=True) # 创建extra字段，varchar类型，长度16，普通索引 extra = Column(String(16),index=True) __table_args__ = ( # id，name 联合唯一索引 UniqueConstraint('id', 'name', name='uix_id_name'), # 组合索引 Index('ix_id_name', 'name', 'extra'), ) def __repr__(self): return "<User(id=%s,name'%s,extra=%s)>"%(self.id,self.name,self.extra) # 创建表结构 Base.metadata.create_all(engine) # 删除表 # Base.metadata.drop_all(engine)
1.增加表内容:
Mysession=sessionmaker(bind=engine) session=Mysession() # 添加单条数据 # ed=Users(name='ttt333',extra='423rfsf') # session.add(ed) # session.commit() # 添加多条数据 # session.add_all([Users(name='test',extra='123456'),Users(name='python',extra='123456')]) # session.commit()
2.删除表中数据
Mysession=sessionmaker(bind=engine) session=Mysession() #删除User类所对应表中id小于2的数据 session.query(Users).filter(Users.id < 2).delete() #删除User类所对应表中name等于test的数据 session.query(Users).filter_by(name='test').delete() #删除User类所对应表中的所有数据 session.query(Users).delete() session.commit()
3.更新表中数据
Mysession=sessionmaker(bind=engine) session=Mysession() #更新id大于23的数据 session.query(Users).filter(Users.id>23).update({"name":"sshhrr"}) #更新name等于test7的数据 session.query(Users).filter_by(name='test7').update({"name":'hhhwwx'}) session.query(Users).filter_by(name='test8').update({"name":'ttssseq'}, synchronize_session=False) session.query(Users).filter_by(name='test9').update({"name":'yyqysss'}, synchronize_session="evaluate") session.commit() synchronize_session值为False、fetch、evaluate synchronize_session用于query在进行delete or update操作时，对session的同步策略。 False： 不对session进行同步，直接进行delete or update操作。 fetch： 在delete or update操作之后，将session的identity_map与前一步获取的记录进行match，将符合条件的就从session中delete或update。 evaluate： 在delete or update操作之前，用query中的条件直接对session的identity_map中的objects进行eval操作，将符合条件的记录下来。
4.查询表中的数据
# 4.1查询所有数据，返回的结果是一个list类型的Users对象 result=session.query(Users).all()
# 4.2查询第一条数据，返回的结果是一个的Users对象 result=session.query(Users).first() print(result,type(result)) print(result.id,result.name,result.extra)
# 4.3根据指定列名查询第一条数据 result=session.query(Users.id,Users.name).first() print(result,type(result)) print(result.id,result.name) for value in result: print(value)
# 4.4根据条件进行查询 result=session.query(Users).filter(Users.id<20).all() print(result)
# 4.5根据条件进行查询 result=session.query(Users).filter_by(id=25).all() print(result)
# 4.6根据多个条件查询指定列名的所有数据 result=session.query(Users.name,Users.extra).filter(text("id<:value or name=:name")).params(value=25, name='fred').order_by(Users.id).all() print(result,type(result)) for value in result: print(value)
# 4.7根据原生sql语句进行查询 result=session.query(Users).from_statement(text("SELECT * FROM t_u_users where name=:name")).params(name='test5').all() print(result,type(result)) for value in result: print(value)
#4.8其他条件查询汇总 # 条件 ret = session.query(Users).filter_by(name='alex').all() ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all() ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all() ret = session.query(Users).filter(Users.id.in_([1,3,4])).all() ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all() #子查询 ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all() from sqlalchemy import and_, or_ ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all() ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all() ret = session.query(Users).filter( or_( Users.id < 2, and_(Users.name == 'eric', Users.id > 3), Users.extra != "" )).all() # 通配符 ret = session.query(Users).filter(Users.name.like('e%')).all() ret = session.query(Users).filter(~Users.name.like('e%')).all() # 限制 ret = session.query(Users)[1:2] # 排序 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() ret = session.query( func.max(Users.id), func.sum(Users.id), func.min(Users.id)).group_by(Users.name).all() 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() # 连表 ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all() ret = session.query(Person).join(Favor).all() ret = session.query(Person).join(Favor, isouter=True).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()
浙公网安备 33010602011771号