python之SQLAlchemy

ORM介绍

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

orm的优点：

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

缺点：

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

2. sqlalchemy安装

在Python中，最有名的ORM框架是SQLAlchemy。用户包括openstack＼Dropbox等知名公司或应用，主要用户列表http://www.sqlalchemy.org/organizations.html#openstack

Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：

安装sqlalchemy

导入SQLAlchemy，并初始化DBSession：

#导入:
from sqlalchemy importColumn, String, create_engine
from sqlalchemy.orm importsessionmaker
from sqlalchemy.ext.declarative importdeclarative_base

#创建对象的基类:
Base =declarative_base()

#定义User对象:
classUser(Base):
    #表的名字:
    __tablename__ = 'user'

    #表的结构:
    id = Column(String(20), primary_key=True)
    name = Column(String(20))

#初始化数据库连接:
engine = create_engine('mysql+mysqlconnector://root:password@localhost:3306/test')
#创建DBSession类型:
DBSession = sessionmaker(bind=engine)

以上代码完成SQLAlchemy的初始化和具体每个表的class定义。如果有多个表，就继续定义其他class，例如School：

classSchool(Base):
    __tablename__ = 'school'id =...
    name = ...

create_engine()用来初始化数据库连接。SQLAlchemy用一个字符串表示连接信息：

数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'

你只需要根据需要替换掉用户名、口令等信息即可。

下面，我们看看如何向数据库表中添加一行记录。

由于有了ORM，我们向数据库表中添加一行记录，可以视为添加一个User对象：

#创建session对象:
session =DBSession()
#创建新User对象:
new_user = User(id='5', name='Bob')
#添加到session:
session.add(new_user)
#提交即保存到数据库:
session.commit()
#关闭session:
session.close()

可见，关键是获取session，然后把对象添加到session，最后提交并关闭。DBSession对象可视为当前数据库连接。

如何从数据库表中查询数据呢？有了ORM，查询出来的可以不再是tuple，而是User对象。SQLAlchemy提供的查询接口如下：

#创建Session:
session =DBSession()
#创建Query查询，filter是where条件，最后调用one()返回唯一行，如果调用all()则返回所有行:
user = session.query(User).filter(User.id=='5').one()
#打印类型和对象的name属性:
print('type:', type(user))
print('name:', user.name)
#关闭Session:
session.close()

运行结果如下：

type: <class '__main__.User'>
name: Bob

可见，ORM就是把数据库表的行与相应的对象建立关联，互相转换。

由于关系数据库的多个表还可以用外键实现一对多、多对多等关联，相应地，ORM框架也可以提供两个对象之间的一对多、多对多等功能。

除上面的创建之外，还有一种创建表的方式，虽不学用，但还是看看吧

最基本的表我们创建好了，那我们开始用orm创建一条数据试试

我擦，写这么多代码才创建一条数据，你表示太tm的费劲了，正要转身离开，我拉住你的手不放开，高潮还没到。。

查询

此时你看到的输出是这样的应该

我擦，这是什么？这就是你要的数据呀， 只不过sqlalchemy帮你把返回的数据映射成一个对象啦，这样你调用每个字段就可以跟调用对象属性一样啦，like this..

不过刚才上面的显示的内存对象对址你是没办法分清返回的是什么数据的，除非打印具体字段看一下，如果想让它变的可读，只需在定义表的类下面加上这样的代码

修改

回滚

获取所有数据

多条件查询

上面2个filter的关系相当于 user.id >1 AND user.id <7 的效果

统计和分组

分组

相当于原生sql为

输出为

[(1, 'Jack'), (2, 'Rain')]

外键关联

我们创建一个addresses表，跟user表关联

Therelationship.back_populatesparameter is a newer version of a very common SQLAlchemy feature calledrelationship.backref. Therelationship.backrefparameter hasn’t gone anywhere and will always remain available! Therelationship.back_populatesis the same thing, except a little more verbose and easier to manipulate. For an overview of the entire topic, see the sectionLinking Relationships with Backref.　　

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

创建关联对象

常用查询语法

query.filter(User.name.in_(['ed', 'wendy', 'jack']))

# works with query objects too:

query.filter(User.name == None)

# alternatively, if pep8/linters are a concern

query.filter(User.name.is_(None))

query.filter(User.name != None)

# alternatively, if pep8/linters are a concern

query.filter(User.name.isnot(None))

# or send multiple expressions to .filter()

query.filter(User.name == 'ed', User.fullname == 'Ed Jones')

# or chain multiple filter()/filter_by() calls

query.filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')

4.多外键关联

One of the most common situations to deal with is when there are more than one foreign key path between two tables.

Consider aCustomerclass that contains two foreign keys to anAddressclass:

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

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

解决办法如下

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

用orm如何表示呢？

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


from sqlalchemy importTable, Column, Integer,String,DATE, ForeignKey
from sqlalchemy.orm importrelationship
from sqlalchemy.ext.declarative importdeclarative_base
from sqlalchemy importcreate_engine
from sqlalchemy.orm importsessionmaker


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')),
                        )

classBook(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):
        returnself.name

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

    def __repr__(self):
        return self.name

orm 多对多

接下来创建几本书和作者

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

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

输出如下

牛逼了我的哥！！完善实现多对多　　

多对多删除

删除数据时不用管boo_m2m_authors　， sqlalchemy会自动帮你把对应的数据删除

通过书删除作者

直接删除作者　

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