SQLAlchemy

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

虽然小弟是个小菜鸟，虽然小弟对数据库没有见多识广，但说心里话当我看到SQLAlchemy这个架构设计时，真的有一种神奇的感觉。简洁明了，逻辑清晰。原谅小弟lowB的心态。最关键的是不需要再研究那些复杂繁琐的数据库操作细节了。

使用SQLAlchemy操作数据库的方式方法有很多种，如通过执行原生SQL语句操作，通过对象操作等。但是在这里只介绍一种最终执行结果的操作，而不是类似中间状态的一些操作。

 

一、基本操作

使用 ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 所有组件对数据进行操作。根据类创建对象，对象转换成SQL，执行SQL。

#!/usr/bin/env python
# _*_coding:utf-8 _*_

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine


Base = declarative_base()
engine = create_engine("mysql+mysqldb://root:123456@192.168.1.106:3306/test1", max_overflow=5, echo=True)    # echo控制是否显示操作过程


class Host(Base):   # 这个类就是一个表,这里只是映射成功了
    __tablename__ = 'hosts'
    id = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(String(64), unique=True, nullable=False)
    ip_addr = Column(String(128), unique=True, nullable=False)
    prot = Column(Integer, default=22)


# 寻找Base的所有子类，按照子类的结构在数据库中生成对应的数据表信息
Base.metadata.create_all(engine)  # 创建所有的表结构


if __name__ == '__main__':

    SessionCls = sessionmaker(bind=engine)   # 创建与数据库会话的session class,返回的是一个类，而不是一个实例
    session = SessionCls()    # 连接数据库的实例，就是用它来对数据库做实际的操作

# ###################### 增 #############################

    # 创建表中数据的实例
    # h1 = Host(hostname='localhost', ip_addr='127.0.0.1')
    # h2 = Host(hostname='centos', ip_addr='172.16.16.166')

    # session.add(h1)   # 单条创建
    # session.add_all([h1, h2])   # 批量创建
    # session.commit()    # 真正的提交数据库操作

# ###################### 查 ###########################

    # obj = session.query(Host).filter(Host.hostname == 'centos').all()
    # print obj
    # ret = session.query(User).filter_by(name='sb').first()

    # ret = session.query(User).filter_by(name='sb').all()

    # ret = session.query(User).filter(User.name.in_(['sb','bb'])).all()

    # ret = session.query(User.name.label('name_label')).all()
    # print ret,type(ret)

    # ret = session.query(User).order_by(User.id).all()

    # ret = session.query(User).order_by(User.id)[1:3]

    # session.commit()

# ###################### 删除 ###########################

    # obj = session.query(Host).filter(Host.hostname == 'centos').first()
    # session.delete(obj)
    #
    # session.commit()


# ###################### 更新 ###########################

    # obj = session.query(Host).filter_by(hostname='localhost').first()
    # obj.hostname = "chenchao"
    # print obj
    # session.commit()

 

更多功能参见文档，猛击这里下载PDF

 注意：SQLAlchemy不能修改已经存在的表的表结构。如果需要可以使用SQLAlchemy开发者开源的另外一个软件Alembic来完成。

 

二、一对多关联

由于SQLAlchemy不能对已经存在的表修改表结构，处于测试的原因，我们可以创建新的表。

 1、创建表

#!/usr/bin/env python
# _*_coding:utf-8 _*_

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine


Base = declarative_base()
engine = create_engine("mysql+mysqldb://root:123456@192.168.1.106:3306/test1", max_overflow=5, echo=True)


class Host1(Base):   # 这个类就是一个表,这里只是映射成功了
    __tablename__ = 'hosts1'
    id = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(String(64), unique=True, nullable=False)
    ip_addr = Column(String(128), unique=True, nullable=False)
    prot = Column(Integer, default=22)
    group_id = Column(Integer, ForeignKey('group1.id'))

class Group1(Base):
    __tablename__ = 'group1'
    id = Column(Integer, primary_key=True)
    groupname = Column(String(64), unique=True, nullable=False)


# 寻找Base的所有子类，按照子类的结构在数据库中生成对应的数据表信息
Base.metadata.create_all(engine)  # 创建所有的表结构

结果：

 

2、 创建数据：

if __name__ == '__main__':
    SessionCls = sessionmaker(bind=engine)   # 创建与数据库会话的session class,返回的是一个类，而不是一个实例
    session = SessionCls()    # 连接的实例

    g1 = Group1(groupname='g1')
    g2 = Group1(groupname='g2')
    g3 = Group1(groupname='g3')

    session.add_all([g1, g2, g3])

    # 同时为host1创建两条数据，但外键的参数不同。
    h1 = Host1(hostname='chenchao1', ip_addr='192.168.1.1',group_id=g1.id)
    h2 = Host1(hostname='chenchao2', ip_addr='192.168.1.2',group_id=2)
    session.add_all([h1, h2])

    session.commit()

 

用传入对象的方法和直接传id的方法分别创建两条外键的数据。

结果：

我们发现当在创建数据的时候，传入对象并不能创建外键的值。因为当时的group表里面还没有数据。所以在插入外键数据时要确保对应的数据已经存在。

    SessionCls = sessionmaker(bind=engine)   # 创建与数据库会话的session class,返回的是一个类，而不是一个实例
    session = SessionCls()    # 连接的实例

    g3 = session.query(Group1).filter(Group1.groupname == 'g3').first()
    h3 = session.query(Host1).filter(Host1.hostname == 'chenchao1').update({'group_id': g3.id})

    session.commit()

 

 

3、查找外键数据

在host表里通过定义某个字段就可以调用这个字段来获取外键表中的某个数据。

注意：这里的方法是sqlalchemy来帮我们实现的。并不是要修改数据库中的表结构。

from sqlalchemy.orm import relationship   # 导入


class Host1(Base):   # 这个类就是一个表,这里只是映射成功了
    __tablename__ = 'hosts1'
    id = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(String(64), unique=True, nullable=False)
    ip_addr = Column(String(128), unique=True, nullable=False)
    prot = Column(Integer, default=22)
    group_id = Column(Integer, ForeignKey('group1.id'))
    group = relationship('Group1')     #定义一个特殊的字段。括号里写的是一个类名，而不是表名

if __name__ == '__main__':
    SessionCls = sessionmaker(bind=engine)   
    session = SessionCls()    # 连接的实例
    h3 = session.query(Host1).filter(Host1.hostname == 'chenchao1').first()
    print h3.group.groupname    # 通过调用设置的字段就可以获取到外键表中的对应数据
    session.commit()

同理，如果要实现双向查询，那么把relationship()也添加到Group类里面，可以实现双向查询。但是，也可以只在Host表里做双向查询。

class Host1(Base):   # 这个类就是一个表,这里只是映射成功了
    __tablename__ = 'hosts1'
    id = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(String(64), unique=True, nullable=False)
    ip_addr = Column(String(128), unique=True, nullable=False)
    prot = Column(Integer, default=22)
    group_id = Column(Integer, ForeignKey('group1.id'))
    group = relationship('Group1', backref='host_list')   #添加特殊的字段


    g2 = session.query(Group1).filter(Group1.groupname == 'g2').first()    # 获取组的对象
    print g2.host_list   # 通过自定义的host_list来实现双向的查询

 

三、原生的SQL与SQLAlchemy  join查询

几个Join的区别 http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins 

join查询可以根据两张表中有关联的数据来对表中的数据做处理。

1、原生SQL查询

 

Inner join
# select * from a INNER JOIN b on a.a = b.b;
# select a.*,b.*  from a,b where a.a = b.b;

# 根据hosts1表中的group_id与group1表中的id来取出交集
# select * from hosts1 INNER JOIN group1 on hosts1.group_id = group1.id;

Left outer join
# select * from a LEFT OUTER JOIN b on a.a = b.b;
# select a.*,b.*  from a,b where a.a = b.b(+);

# 根据hosts1表中的group_id与group1表中的id来取出左边表中的数据
# select * from hosts1 LEFT OUTER JOIN group1 on hosts1.group_id = group1.id;

Right outer join
# select * from a RIGHT OUTER JOIN b on a.a = b.b;
# select a.*,b.*  from a,b where a.a(+) = b.b;

# 根据hosts1表中的group_id与group1表中的id来取出右边表中的数据
# select * from hosts1 RIGHT OUTER JOIN group1 on hosts1.group_id = group1.id;

2、SQLAlchemy查询

obj = session.query(Host1).join(Host1.group).all()

3、group by 查询 （这里很懵逼，小弟对原生SQL也不懂）

分类聚合。

    obj = session.query(Host1, func.count(Group1.groupname)).join(Host1.group).group_by(Group1.groupname).all()

上面一行代码解释：

1、基于相同主机组，通过join来查找两张表中交集的数据

2、统计出交集组里包含的主机个数

3、并按主机组名和主机个数显示。

 

 

四、多对多关联

在SQLAlchemy里，多对多的关联同样是通过建立第三张关系表来实现的。在这里需要把创建第三张表放在开始位置，且是通过SQLAlchemy的中间状态来实现的。

#!/usr/bin/env python
# _*_coding:utf-8 _*_

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, func, Table
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine


Base = declarative_base()
engine = create_engine("mysql+mysqldb://root:123456@192.168.1.106:3306/test1", max_overflow=5, echo=False)


# 通过中间状态的方法创建第三张关系表，关联其他两张表
HostToGroup = Table('host_to_group', Base.metadata,
                    Column('host_id', ForeignKey('hostsmsg.id'), primary_key=True),
                    Column('group_id', ForeignKey('GroupMsg.id'), primary_key=True),
                    )


class HostMsg(Base):
    __tablename__ = 'hostsmsg'
    id = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(String(64), unique=True, nullable=False)
    ip_addr = Column(String(128), unique=True, nullable=False)
    prot = Column(Integer, default=22)
    group = relationship('GroupMsg', backref='host_list', secondary=HostToGroup)   # 添加第三张表的实例

    # 设置回显对象的详细信息
    def __repr__(self):
        return "<id=%s, hostname=%s, ip_addr=%s>" % (self.id, self.hostname, self.ip_addr)


class GroupMsg(Base):
    __tablename__ = 'GroupMsg'
    id = Column(Integer, primary_key=True)
    groupname = Column(String(64), unique=True, nullable=False)

    def __repr__(self):
        return "<id=%s, groupname=%s>" % (self.id, self.groupname)

基本的多对多的操作：

Base.metadata.create_all(engine)  # 创建所有的表结构

if __name__ == '__main__':
    SessionCls = sessionmaker(bind=engine)
    session = SessionCls()    # 连接的实例

# =================== 创建组 =======================

    # g1 = GroupMsg(groupname='g1')
    # g2 = GroupMsg(groupname='g2')
    # g3 = GroupMsg(groupname='g3')
    # g4 = GroupMsg(groupname='g4')
    # g5 = GroupMsg(groupname='g5')
    # session.add_all([g1, g2, g3, g4, g5])


# =================== 创建主机 =======================

    # h1 = HostMsg(hostname='host1', ip_addr='172.16.172.1', prot=55)
    # h2 = HostMsg(hostname='host2', ip_addr='172.16.172.2', prot=56)
    # h3 = HostMsg(hostname='host3', ip_addr='172.16.172.3', prot=57)
    # h4 = HostMsg(hostname='host4', ip_addr='172.16.172.4', prot=58)
    # session.add_all([h1, h2, h3, h4])

    # 根据对象，在创建主机时就确定主机组
    # g3 = session.query(GroupMsg).filter(GroupMsg.groupname == 'g3').first()
    # g5 = session.query(GroupMsg).filter(GroupMsg.groupname == 'g5').first()
    # h5 = HostMsg(hostname='host5', ip_addr='172.16.172.5', prot=58, group=[g3,g5])

# =================== 创建多对多关联 =======================

    # all_groups = session.query(GroupMsg).all()     # 获取所有的主机
    # h2 = session.query(HostMsg).filter(HostMsg.hostname == 'host2').first()   # 获取其中一台主机
    # h2.group = all_groups

    # g2 = session.query(GroupMsg).filter(GroupMsg.groupname == 'g2').first()   # 获取一个组名
    g3 = session.query(GroupMsg).filter(GroupMsg.groupname == 'g3').first()
    h3 = session.query(HostMsg).filter(HostMsg.hostname == 'host3').first()   # 获取其中一台主机
    # h3.group = [g2, g3]

    # 查看组包含的主机   主机所属的组
    print "------------------>g3:", g3.host_list
    print "------------------>h3:", h3.group

    session.commit()