Python ORM框架之SQLALchemy

ORM框架本质: 让面向对象的类跟数据库中的表做个对应关系

ORM与原生SQL比较:

  1. 原生SQL性能较好
  2. 原生SQL的可维护性相对差一些
  3. ORM操作、开发起来比较简单、快捷

Python ORM框架:SQLALchemy、Django、Storm、SQLObject

SQLALchemy与Django比较:

  • Django操作起来更简单,双下划线跨表查询更方便
  • Django支持自动生成第三张表
  • SQLALchemy更接近原生SQL
  • 两者都是通过DB API(pymysql 、mysqlDB)模块连接数据库
  • 默认SQLALchemy不能修改表的字段,只能创建表、删除表

SQLALchemy框架:

 

  •  Engine: 框架的引擎
  • Connection Pooling : 数据库连接池
  • Dialect : 选择连接数据库的DB API类型
  • Schema / Type : 架构和类型
  • SQL Expression Language: SQL表达式语言

SQLALchemy本身无法操作数据库,需要依赖pymysql第三方模块,Dialect用于和数据API进行交流,根据配置文件的不同调用不同的数据库API,从而实现对数据库的操作

使用pymysql连接数据库格式:

    
mysql
+
pymysql:
/
/
<username>:<password>@<host>
/
<dbname>[?<options>]  

 1 import datetime 2 from sqlalchemy import create_engine 3 from sqlalchemy.ext.declarative import declarative_base 4 from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index 5 from sqlalchemy.orm import relationship, sessionmaker 6 from sqlalchemy.sql import text 7  8 Base = declarative_base() 9 10 11 class Users(Base):12 """13  单表创建14 """15 __tablename__ = users16 id = Column(Integer, primary_key=True, autoincrement=True)17 name = Column(String(32), index=True, nullable=False)18 email = Column(String(32), unique=True)19 ctime = Column(DateTime, default=datetime.datetime.now)20 extra = Column(Text, nullable=True)21 22 __table_args__ = (23 UniqueConstraint(id, name, name=uix_id_name),24 Index(ix_id_name, name, email),25  )26 27 28 # 一对多示例29 class Hobby(Base):30 __tablename__ = hobby31 id = Column(Integer, primary_key=True)32 caption = Column(String(50), nullable=True)33 34 35 class Person(Base):36 __tablename__ = person37 id = Column(Integer, primary_key=True, autoincrement=True)38 name = Column(String(32), nullable=False)39 hobby_id = Column(Integer, ForeignKey("hobby.id")) # hobby.id 中的hobby指的是tablename40 41 # 与生成表结构无关, 仅用于查询方便42 hobby = relationship(Hobby, backref=pers) # 此处Hobby指的是类名43 44 45 # 多对多示例46 class Server(Base):47 __tablename__ = server48 id = Column(Integer, primary_key=True, autoincrement=True)49 hostname = Column(String(64), unique=True, nullable=False)50 51 52 class Group(Base):53 __tablename__ = group54 id = Column(Integer, primary_key=True)55 name = Column(String(64), unique=True, nullable=False)56 57 # 与生成表无关,仅用于查询方便58 servers = relationship(Server, secondary=server2group, backref=groups)59 60 61 class Server2Grouop(Base):62 __tablename__ = server2group63 id = Column(Integer, primary_key=True, autoincrement=True)64 server_id = Column(Integer, ForeignKey(server.id))65 group_id = Column(Integer, ForeignKey(group.id))66 67 68 def init_db():69 """70  根据类创建数据库表71  :return:72 """73 engine = create_engine(74 mysql+pymysql://root:mysql@127.0.0.1:3306/t1?charset=utf8,75 max_overflow=0, # 超过连接池大小后 最多创建的连接76 pool_size=3, # 连接池大小77 pool_timeout=10, # 连接池中没有线程最多等待时间,否则报错78 pool_recycle=-1, # 多久对线程池进行一次连接回收79  )80  Base.metadata.create_all(engine)81 82 83 def drop_db():84 """85  根据类删除数据库表86  :return:87 """88 engine = create_engine(89 mysql+pymysql://root:mysql@127.0.0.1:3306/t1?charset=utf-8,90 max_overflow=0,91 pool_size=3,92 pool_timeout=5,93 pool_recycle=-1,94  )95 Base.metadata.drop_all(engine)

SQLALchemy之创建表

 


 1 from sqlalchemy import create_engine 2 from sqlalchemy.ext.declarative import declarative_base 3 from sqlalchemy.sql import text 4  5 Base = declarative_base() 6  7 # 基本增删改查示例 8 def db_action(): 9 """10  基本数据库操作11  :return:12 """13 engine = create_engine(14 mysql+pymysql://root:mysql@127.0.0.1:3306/t1?charset=utf8,15 max_overflow=0,16 pool_size=3,17 pool_timeout=5,18 pool_recycle=-1,19  )20 Session = sessionmaker(bind=engine)21 22 session = Session()23 24 # 添加操作25 obj1 = Users(name=AABB)26  session.add(obj1)27 28  session.add_all([29 Users(name=tom),30 Users(name=lily),31 Users(name=oobb),32  ])33  session.commit()34 35 # 删除操作36 session.query(Users).filter(Users.id > 2).delete()37  session.commit()38 39 # 修改操作40 session.query(Users).filter(Users.id > 0).update({"name": "110"})41 session.query(Users).filter(Users.id > 0).update({Users.name: Users.name + "110"}, synchronize_session=False)42 session.query(Users).filter(Users.id > 0).update({"age": Users.age + 1}, synchronize_session="evaluate")43  session.commit()44 45 # 查询操作46 r1 = session.query(Users).all()47 r2 = session.query(Users.name.label(别名), Users.email).all()48 r3 = session.query(Users).filter(Users.name == OOBB).all()49 r4 = session.query(Users).filter_by(name=oobb).all()50 r5 = session.query(Users).filter_by(name=oobb).first()51 r6 = session.query(Users).filter(text("id<:value and name=:name")).params(value=110, name=aa).order_by(52  Users.id).all()53 r7 = session.query(Users).from_statement(text("select * from users where name=:name")).params(name=oobb).all()54 55 # 常用操作56 # [条件查询]57 m1 = session.query(Users).filter_by(name=oouu).all()58 m2 = session.query(Users).filter(Users.id > 1, Users.name == yyuu).all()59 m3 = session.query(Users).filter(Users.id.between(1, 5, 2), Users.email == cc@qq.com).all()60 m4 = session.query(Users).filter(Users.id.in_([1, 2, 5])).all()61 m5 = session.query(Users).filter(~Users.id.in_([2, 3, 4])).all() # not in62 m6 = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name=aa))).all()63 64 65 from sqlalchemy import and_, or_66 67 m7 = session.query(Users).filter(and_(Users.id > 10, Users.name == oo)).all()68 m8 = session.query(Users).filter(or_(Users.id < 2, Users.email==cc@qq.com)).all()69 m9 = session.query(Users).filter(70  or_(71 Users.id < 3,72 and_(Users.email==cc@qq.com, Users.name==cc),73 Users.extra !=‘‘74  )75  )76 77 # 通配符78 n = session.query(Users).filter(Users.name.like(m%)).all()79 n1 = session.query(Users).filter(~Users.name.like(m%)).all()80 81 # 限制82 n2 = session.query(Users)[1:5]83 84 # 排序85 n3 = session.query(Users).order_by(Users.id.desc()).all()86 n4 = session.query(Users).order_by(Users.id.desc(), Users.name.asc()).all()87 88 # 跨表查询89 a = session.query(Hobby, Person).filter(Hobby.id == Person.hobby_id).all()90 a1 = session.query(Person).join(Hobby).all()91 a2 = session.query(Person).join(Hobby, isouter=True).all()92 93 # 组合查询94 b = session.query(Person.name).filter(Person.id>2)95 b1 = session.query(Hobby.caption).filter(Hobby.id<10)96 result = b.union(b1).all()97 result2 = b.union_all(b1).all()

SQLAlchemy之最常见的操作示例

 

相关文章