python运维之路——sqlalchemy ORM

一、ORM介绍

orm英文全称object relational mapping,就是对象映射关系程序，简单来说我们类似python这种面向对象的程序来说一切皆对象，但是我们使用的数据库却都是关系型的，为了保证一致的使用习惯，通过orm将编程语言的对象模型和数据库的关系模型建立映射关系，这样我们在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作就可以了，而不用直接使用sql语言。

orm的优点：
- 1.隐藏了数据访问细节，“封闭”的通用数据库交互，ORM的核心。他使得我们的通用数据库交互变得简单易行，并且完全不用考虑该死的SQL语句。快速开发，由此而来。
- 2.ORM使我们构造固化数据结构变得简单易行。

orm的缺点
- 无可避免的，自动化意味着映射和关联管理，代价是牺牲性能（早期，这是所有不喜欢ORM人的共同点）。现在的各种ORM框架都在尝试使用各种方法来减轻这块（LazyLoad，Cache），效果还是很显著的。

二、sqlalchemy安装

pip3 install sqlalchemy

使用格式

MySQL-Python
    mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>

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

MySQL-Connector
    mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>

cx_Oracle
    oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]

更多详见：http://docs.sqlalchemy.org/en/latest/dialects/index.html

三、sqlalchemy基本使用

sqlalchemy中间状态的例子

#!/usr/bin/env python3

from sqlalchemy import create_engine, Table, MetaData, Column, Integer, String, ForeignKey, select
from sqlalchemy.orm import mapper

#MetaData()相当于一个基类，在下面创建的类中都会继承这个类，所以在这里现将MetaData()实例化
metadata = MetaData()
#创建了一个表
user = Table(
        'user', #表名
        metadata, #继承的父类
        Column('id', Integer, primary_key=True), #创建一个字段
        Column('name', String(20)), #又创建了一个字段
)


color = Table(
        'color',
        metadata,
        Column('id', Integer, primary_key=True),
        Column('name', String(20)),
)
#链接库
engine = create_engine("mysql+pymysql://root:123456@localhost:3306/dingyi", max_overflow=5)
#创建引擎,创建引擎后，再次执行不会报错，也不会执行
metadata.create_all(engine)

#创建一个光标
conn = engine.connect()

#在user表中插入一条数据
#sql = user.insert().values(id=123, name='dingyi') #可以不创建id，id自增
#sql_a = user.insert().values(name='ding2') #可以不创建id，id自增
#sql_b = user.insert().values(name='ding3a') #可以不创建id，id自增
#sql_c = user.insert().values(name='ding4') #可以不创建id，id自增
#conn.execute(sql_a)
#conn.execute(sql_b)
#conn.execute(sql_c)

#删除一条数据
#c在这里是写死的，一定要有，没有的话会报错
#sql_1 = user.delete().where(user.c.id > 1)
#conn.execute(sql_1)

#更新一条数据
#这句有问题忽略
#sql_2 = user.update().values(fullname=user.c.name)
#sql_3 = user.update().where(user.c.name=='dingyi').values(name='ding2')
#conn.execute(sql_3)

#查询
#查询一张表的全部数据
#sql_4 = select([user,])
#res = conn.execute(sql_4)
#print(res.fetchall())

#查询一个字段
#sql_5 = select([user.c.name, ])
#res = conn.execute(sql_5)
#print(res.fetchall())

#高级查询
sql_6 = select([user.c.name, color.c.name]).where(user.c.name == color.c.name)
res = conn.execute(sql_6)
print(res.fetchall())



conn.close()

sqlalchem的例子

#!/usr/bin/env python3
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, and_
from sqlalchemy.orm import sessionmaker

#生成一个SQL-ORM 基类
Base = declarative_base()

engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/dingyi", echo=False)

#定义一个表
class Host(Base):
        __tablename__ = 'hosts' #表名
        d_id = Column(Integer, primary_key=True, autoincrement=True) #字段设置,autoincrement可以不写，不认就是True
        hostname = Column(String(64), unique=True, nullable=False)
        ip_addr = Column(String(128), unique=True, nullable=False)
        port = Column(Integer, default=22)

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


def func1():
        #创建数据
#       h1 = Host(hostname='ubuntu', ip_addr='127.0.0.1')
        h2 = Host(hostname='ubuntub', ip_addr='8.8.2.9', port='11')
        #上面只是实例化，这里是提交数据
        #分次提交
#       session.add(h1)
        session.add(h2)
        #全部提交
#       session.add_all([h1, h2])
        #保存数据
        session.commit()


def func2():
        #查询数据
        a = session.query(Host).filter(Host.hostname == 'localhost').first() #这里也可以是.all(),  a是一个Host对象
        a.hostname = 'lalal_ddyy' #修改查出来的数据
        session.commit()

def func3():
        a = session.query(Host).filter(Host.hostname == 'lalal_ddyy').first()
        session.delete(a)
        session.commit()

#复杂查询，详细见文档
def func4():
        a = session.query(Host).filter(and_(Host.hostname.like("ub%"), Host.port > 20)).all()
        print("the and_:", a)


if __name__ == '__main__':
        #创建数据库的会话session class,注意这里传递给SessionCls的是一个class，不是一个实例
        SessionCls = sessionmaker(bind=engine)
        session = SessionCls() #连接的实例
        func4()

详细例子

创建表
一个创建表的sql语句

mysql> create table user(
    -> id integer not null auto_increment,
    -> name varchar(32),
    -> password varchar(64),
    -> primary key (id)
    -> );

下面的代码可以实现上述表的创建

#!/usr/bin/env python3
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

engine = create_engine("mysql+pymysql://root:123456@local
host/test", encoding='utf-8', echo=True)

Base = declarative_base() #生成基类

class User(Base):
    __tablename__ = "user" #表名
    id = Column(Integer, primary_key=True) #字段名
    name = Column(String(32))
    password = Column(String(64))

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

创建数据
在上边的代码的基础上，创建数据

#创建与数据库会的session class，注意这里返回给session的是
个class不是实例
Session_class = sessionmaker(bind=engine)
#生成要创建的数据对象
user_obj = User(name='dingyi', password='123456')
#此时还没有创建成功，现在打印出来是None
print(user_obj.name, user_obj.id)

#要把创建的数据对象添加到这个session里，一会统一创建
Session.add(user_obj)
print(user_obj.name, user_obj.id)

#同意提交才创建
Session.commit()

查询数据

my_user = Session.query(User).filter_by(name="dingyi").first()
print(my_user)
#返回的是一个对象
<__main__.User object at 0x105b4ba90>
print(my_user.id,my_user.name,my_user.password)
dingyi 123456

如果想要让返回的对象可读，要需要下面的代码，添加在创建表的类里面

def __repr__(self):
    return "<User(name='%s',  password='%s')>" % (
        self.name, self.password)

修改数据

my_user = Session.query(User).filter_by(name="dingyi").first()
my_user.name = "dinger"
Session.commit()

数据回滚

my_user = Session.query(User).filter_by(id=1).first()
my_user.name = "dingsan"
fake_user = User(name='Rain', password='12345')
Session.add(fake_user)

#这时看session里有刚添加和修改的数据
print(Session.query(User).filter(User.name.in_(['Jack','rain'])).all() )

#数据回滚
Session.rollback()

#数据没有了
print(Session.query(User).filter(User.name.in_(['Jack','rain'])).all() )

#可以commit一下，也可以不commit一下
Session.commit()

获取所有数据

print(Session.query(User.name,User.id).all() )

多条件查询

objs = Session.query(User).filter(User.id>0).filter(User.id<7).all()
两个filter相当于 user.id >1 and user.id <7

统计

Session.query(User).filter(User.name.like("Ra%")).count()

分组

from sqlalchemy import func
print(Session.query(func.count(User.name),User.name).group_by(User.name).all() )

相当于原生SQL
select count(user.name) as count_1, user.name as user_name from user group by user.name

外键关联

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    email_address = Column(String(32), nullable=False)
    user_id = Column(Integer, ForeignKey('user.id'))

    user = relationship("User", backref="addresses") #允许你在user表里通过backref字段反向查出所有它在addresses表里的关联项

    def __repr__(self):
        return "<Address(email_address='%s')>" % self.email_address

表创建好后，我们可以这样反查试试

obj = Session.query(User).first()
for i in obj.addresses: #通过user对象反查关联的addresses记录
    print(i)

addr_obj = Session.query(Address).first()
print(addr_obj.user.name)  #在addr_obj里直接查关联的user表

创建关联对象

obj = Session.query(User).filter(User.name=='rain').all()[0]
print(obj.addresses)

obj.addresses = [Address(email_address="r1@126.com"), #添加关联对象
                 Address(email_address="r2@126.com")]


Session.commit()

常用查询语法
equals:
- query.filter(User.name == ‘ed’)

not equals:
- query.filter(User.name != ‘ed’)

like:
- query.filter(User.name.like(‘%ed%’))

更详细内容查看PDF文档

四、多外键关联

带关联的表的例子

#!/usr/bin/env python3
from  sqlalchemy import create_engine, Column, Integer, String, ForeignKey, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

#生成一个orm的基类
Base = declarative_base()

engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/dingyi")

#sqlalchemy中的表，一旦创建，就不能修改表结构，需要用原生SQL语句修改或者是安装一个开源的sqlalchemy第三方工具

class Host(Base):
        __tablename__ = 'hoss_2'
        host_id = Column(Integer, primary_key = True)
        host_name = Column(String(64), unique = True, nullable = False)
        ip_addr = Column(String(15), unique = True, nullable = False)
        host_post = Column(Integer, default=44)
        group_id = Column(Integer, ForeignKey('group_2.group_id')) #设置外键,这里对应的是表名.字段名
        #这里对应的是创建的类的名字, 这个语句只是为了查询方便，与表结构无关, backref表示在查询group表中可以通过host_list（自己随便定义）字段查询到hoss_2的信息
        group_relation = relationship("Group", backref="host_list") 
        #back_populates 与 backref 的区别是，backref支持单向的定义，而back_populates需要在Group中定义一个相对应的back_populates，并且 “group_relation” 和 “host_relation” 的名字要相对应
        #group_relation = relationship("Group", back_populates="host_relation")
        #这个内部方法可以直观的显示表中的列
        def __repr__(self):
                return "Hoss_2(host_id=%s, host_name=%s, ip_addr=%s, host_post= %s, group_id=%s)" % (self.host_id, self.host_name, self.ip_addr, self.host_post, self.group_id)



class Group(Base):
        __tablename__ = 'group_2'
        group_id = Column(Integer, primary_key = True, autoincrement=True)
        group_name = Column(String(100), unique = True, nullable = False)
        group_admin = Column(String(100), unique = True, nullable = False)
#       host_id = Column(Integer, ForeignKey('hoss_2.host_id'))
        #host_relation = relationship("Host")
#       host_relation = relationship("Host", back_populates="group_relation")

        def __repr__(self):
                return "group_2(group_id:%s, group_name:%s, group_admin:%s)" % (self.group_id, self.group_name, self.group_admin)

Base.metadata.create_all(engine)

sess = sessionmaker(bind=engine)
session = sess()

def func1():
        g1 = Group(group_name='g1', group_admin='dingyi',)
#       g2 = Group(group_name='g2', group_admin='dingsan', )
#       g3 = Group(group_name='g3', group_admin="dinger", )
#
#       session.add_all([g1, g2, g3, ])
#       session.commit()
#       g1 = session.query(Group).filter(Group.group_id==3).first()
        h1 = Host(host_name='ding1', ip_addr='1.1.1.1', host_post=33, group_id=g1.group_id) 
        #一开始执行的时候，group_id=g1.group_id这句话不会生效，因为还没有commit，所以g1还没有数据，在hoss_2表查询后,会是下面的样子
        #+---------+-----------+---------+-----------+----------+
        #| host_id | host_name | ip_addr | host_post | group_id |
        #+---------+-----------+---------+-----------+----------+
        #|       1 | ding1     | 1.1.1.1 |        33 |     NULL |
        #+---------+-----------+---------+-----------+----------+

        session.add_all([h1,])
        session.commit()

def func2():
        #在func1发生外键没有创建成功的时候
        #这个时候就需要手动添加外键，将group_id字段填上g1的group_id
        group_1 = session.query(Group).filter(Group.group_name=='g1').first()
        #查询更新一气呵成
        host_1 = session.query(Host).filter(Host.ip_addr=='1.1.1.1').update({'group_id':group_1.group_id})
        session.commit()

def func3():
        #一对一的连接查询
        #现在hoss_2中查到一条数据
        host_select_1 = session.query(Host).filter(Host.ip_addr=='1.1.1.2').first()
        #这里如果在func1()上面没有设置relationship的话会报错
        #这里直接通过hoss_2里面的数据查到了group_2 中的数据
        print("测试hoss_2中第一条数据的外键:", host_select_1.group_relation.group_name)
#       print("测试hoss_2中第一条数据的外键:", dir(host_select_1.group_relation))


def func4():
        #查询一条group_2表中的信息
        g1 = session.query(Group).filter(Group.group_name=='g1').first()
        print('g1的查询：',g1.host_list)
        #print('g1的查询：',dir(g1.host_relation))

def func5():
        #多表联合查询，right join
        a = session.query(Host).join(Host.group_relation).filter(Group.group_name=='g1').group_by("host_name").all()
        print(a)

#def func6():
#       #分组统计+联合查询 ,func 是包含原生SQL中count，sum等方法
#       a = session.query(Host, func.count(Group.group_name)).join(Host.group_relation).group_by(Group.group_name).all()
#       print(a)

if __name__ == "__main__":
        func5()

下表中，Customer表有2个字段都关联了Address表

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

Base = declarative_base()

class Customer(Base):
    __tablename__ = 'customer'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    billing_address_id = Column(Integer, ForeignKey("address.id"))
    shipping_address_id = Column(Integer, ForeignKey("address.id"))

    billing_address = relationship("Address") 
    shipping_address = relationship("Address")

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    street = Column(String)
    city = Column(String)
    state = Column(String)

创建表结构是没有问题的，但你Address表中插入数据时会报下面的错

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join
condition between parent/child tables on relationship
Customer.billing_address - there are multiple foreign key
paths linking the tables.  Specify the 'foreign_keys' argument,
providing a list of those columns which should be
counted as containing a foreign key reference to the parent table.

解决办法如下

class Customer(Base):
    __tablename__ = 'customer'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    billing_address_id = Column(Integer, ForeignKey("address.id"))
    shipping_address_id = Column(Integer, ForeignKey("address.id"))

    billing_address = relationship("Address", foreign_keys=[billing_address_id])
    shipping_address = relationship("Address", foreign_keys=[shipping_address_id])

这样sqlachemy就能分清哪个外键是对应哪个字段了

五、多对多关系

现在来设计一个能描述“图书”与“作者”的关系的表结构，需求是
- 一本书可以有好几个作者一起出版
- 一个作者可以写好几本书
此时你会发现，用之前学的外键好像没办法实现上面的需求了，因为

当然你更不可以像下面这样干，因为这样就你就相当于有多条书的记录了，太low b了，改书名还得都改。。。

那怎么办呢？ 此时，我们可以再搞出一张中间表，就可以了

这样就相当于通过book_m2m_author表完成了book表和author表之前的多对多关联

用orm如何表示呢？

#一本书可以有多个作者，一个作者又可以出版多本书


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


Base = declarative_base()

book_m2m_author = Table('book_m2m_author', Base.metadata,
                        Column('book_id',Integer,ForeignKey('books.id')),
                        Column('author_id',Integer,ForeignKey('authors.id')),
                        )

class Book(Base):
    __tablename__ = 'books'
    id = Column(Integer,primary_key=True)
    name = Column(String(64))
    pub_date = Column(DATE)
    authors = relationship('Author',secondary=book_m2m_author,backref='books')

    def __repr__(self):
        return self.name

class Author(Base):
    __tablename__ = 'authors'
    id = Column(Integer, primary_key=True)
    name = Column(String(32))

    def __repr__(self):
        return self.name

接下来创建几本书和作者

Session_class = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
s = Session_class() #生成session实例

b1 = Book(name="dingyi_book_1")
b2 = Book(name="dingyi_book_2")
b3 = Book(name="dingyi_book_3")
b4 = Book(name="dingyi_book_4")

a1 = Author(name="dingyi")
a2 = Author(name="Jack")
a3 = Author(name="Rain")

b1.authors = [a1,a2]
b2.authors = [a1,a2,a3]

s.add_all([b1,b2,b3,b4,a1,a2,a3])

s.commit()

此时，手动连上mysql，分别查看这3张表，你会发现，book_m2m_author中自动创建了多条纪录用来连接book和author表

mysql> select * from books;
+----+-------------------+----------+
| id | name              | pub_date |
+----+-------------------+----------+
|  1 | dingyi_book_1     | NULL     |
|  2 | dingyi_book_2     | NULL     |
|  3 | dingyi_book_3     | NULL     |
|  4 | dingyi_book_4     | NULL     |
+----+-------------------+----------+
4 rows in set (0.00 sec)

mysql> select * from authors;
+----+--------+
| id | name   |
+----+--------+
| 10 | dingyi |
| 11 | Jack   |
| 12 | Rain   |
+----+--------+
3 rows in set (0.00 sec)

mysql> select * from book_m2m_author;
+---------+-----------+
| book_id | author_id |
+---------+-----------+
|       2 |        10 |
|       2 |        11 |
|       2 |        12 |
|       1 |        10 |
|       1 |        11 |
+---------+-----------+
5 rows in set (0.00 sec)

此时，我们去用orm查一下数据

print('--------通过书表查关联的作者---------')

book_obj = s.query(Book).filter_by(name="dingyi_book_1").first()
print(book_obj.name, book_obj.authors)

print('--------通过作者表查关联的书---------')
author_obj =s.query(Author).filter_by(name="dingyi").first()
print(author_obj.name , author_obj.books)
s.commit()

输出如下

--------通过书表查关联的作者---------
dingyi_book_1 [dingyi, Jack]
--------通过作者表查关联的书---------
dingyi [dingyi_book_2, dingyi_book_1]

多对多删除
删除数据时不用管boo_m2m_authors　， sqlalchemy会自动帮你把对应的数据删除
通过书删除作者

author_obj =s.query(Author).filter_by(name="Jack").first()

book_obj = s.query(Book).filter_by(name="跟Alex学把妹").first()

book_obj.authors.remove(author_obj) #从一本书里删除一个作者
s.commit()

直接删除作者
删除作者时，会把这个作者跟所有书的关联关系数据也自动删除

author_obj =s.query(Author).filter_by(name="Alex").first()
# print(author_obj.name , author_obj.books)
s.delete(author_obj)
s.commit()

处理中文
sqlalchemy设置编码字符集一定要在数据库访问的URL上增加charset=utf8，否则数据库的连接就不是utf8的编码格式

eng = create_engine('mysql://root:root@localhost:3306/test2?charset=utf8',echo=True)