ORM框架本质: 让面向对象的类跟数据库中的表做个对应关系
ORM与原生SQL比较:
Python ORM框架:SQLALchemy、Django、Storm、SQLObject
SQLALchemy与Django比较:
SQLALchemy框架:
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__ = ‘users‘16 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__ = ‘hobby‘31 id = Column(Integer, primary_key=True)32 caption = Column(String(50), nullable=True)33 34 35 class Person(Base):36 __tablename__ = ‘person‘37 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__ = ‘server‘48 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__ = ‘group‘54 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__ = ‘server2group‘63 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之最常见的操作示例