SQLAlchemy

SQLAlchemy

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

 

SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件，Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作.

SQLAlchemy使用

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DateTime, create_engine, UniqueConstraint, ForeignKey, Index
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql.elements import and_, or_

engine=create_engine('mysql+pymysql://root:123@127.0.0.1:3306/lx?charset=utf8',max_overflow=5)
#得到连接数据库的对象
Base=declarative_base()
#得到Base类在创建类时用作父类

 

创建表

class User(Base):
    __tablename__='usertype'#表名
    id=Column(Integer,autoincrement=True,primary_key=True)
#autoincrement：是否自增，primary_key：是否为主键
    title=Column(String(32),nullable=False,server_default='')
#nullable=False：是否可以为空，server_default=：默认值
Base.metadata.create_all(engine)
#在engine连接到数据库上根据类创建表
Base.metadata.drop_all(engine)
删除engine连接的数据库上的所有表

一对多

class Usertype(Base):
    __tablename__='usertype'
    id=Column(Integer,autoincrement=True,primary_key=True)
    title=Column(String(32),nullable=False,server_default='')



class User(Base):
    __tablename__='user'
    id=Column(Integer,autoincrement=True,primary_key=True)
    num=Column(Integer,nullable=False,server_default='1')
    name=Column(String(32),nullable=False,server_default='')
    extra=Column(String(16),nullable=False,server_default='')
    type_id=Column(Integer,ForeignKey(Usertype.id))

#ForeignKey（表名。列名）表示此列与Usertype表的id列建立外键

多对多

class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer, primary_key=True)
    name = Column(VARCHAR(64), unique=True, nullable=False)
    port = Column(Integer, default=22)
 
 
class Server(Base):
    __tablename__ = 'server'
 
    id = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(VARCHAR(64), unique=True, nullable=False)
 
 
class ServerToGroup(Base):
    __tablename__ = 'servertogroup'
    nid = Column(Integer, primary_key=True, autoincrement=True)
    server_id = Column(Integer, ForeignKey('server.id'))
    group_id = Column(Integer, ForeignKey('group.id'))

操作表

Session=sessionmaker(bind=engine)
session=Session()

 

　　增：

session.add（Usertype(title='白银')）增加一条
session.add_all([Usertype(title='白银'),Usertype(title='塑料'),Usertype(title='木头')])
增加多条

session.commit()执行操作，否则不会保存

　　删：

session.query(Users).filter(Users.id > 2).delete()
删除所有id>2的行
session.commit()

　　改：

session.query(Users).filter(Users.id > 2).update({"name" : "099"})
将所有id>2的行的name改成099
session.commit(

　　查：

ret = session.query(Users)

print（ret）
==》SELECT user.id AS user_id, user.num AS user_num, user.name AS
 user_name, user.extra AS user_extra, user.type_id AS user_type_id 
#如果不加.all()的话ret就是一个sql语句


ret = session.query(Users).all()

print（ret）
==》[<__main__.User object at 0x0000022FEB9F85C0>, 
<__main__.User object at 0x0000022FEB9F8630>, 
<__main__.User object at 0x0000022FEB9F86A0>, 
<__main__.User object at 0x0000022FEB9F8710>]
打印结果是符合条件的对象

ret = session.query(Users.name, Users.extra).all()

print（ret）

　　查找条件：

ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all()
查找条件为id>1且name是eric的行
ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all()
查找id在1，3中且name是eric的行
ret = session.query(Users).filter(Users.id.in_([1,3,4])).all()
查找id在集合[1,3,4]中的行
ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all()
查找id不在集合[1,3,4]中的行~表示‘非’的意思from sqlalchemy import and_, or_
ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all()
查找条件为id>3且name是eric的行
ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all()

查找条件为id<2或且name是eric的行
ret = session.query(Users).filter(
    or_(
        Users.id < 2,
        and_(Users.name == 'eric', Users.id > 3),
        Users.extra != ""
    )).all()
查找条件为name为eric id>3 extra不为空的行或者id<2的行

　　通配符

ret = session.query(Users).filter(Users.name.like('e%')).all()
查找name以e开头的行
ret = session.query(Users).filter(~Users.name.like('e%')).all()

　　限制  limit

ret = session.query(Users)[1:2]
[1：2]的规则和列表的切片规则一样展示指定范围内的行

　　排序

ret = session.query(Users).order_by(Users.name.desc()).all()
查找users所有并以name列降序输出
ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()

查找users所有并以name列降序输出id列升序输出

　　分组

from sqlalchemy.sql import func
ret = session.query(Users).group_by(Users.extra).all()
按照extra列进行分组
ret = session.query(
    func.max(Users.id),
    func.min(Users.id)).group_by(Users.name).all()
按照name列分组后搜索分组内id最大值和最小值的行
ret = session.query(
    func.max(Users.id),
    func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all()

按照name列分组后搜索分组内id最大值和最小值的行内筛选id>2的行

　　连表

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()