sqlalchemy ORM

本节内容

1. ORM介绍
2. sqlalchemy安装
3. sqlalchemy基本使用
4. 多外键关联
5. 多对多关系
6. 表结构设计作业

1. 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

3.sqlalchemy基本使用

下面就开始让你见证orm的nb之处，盘古开天劈地之前，我们创建一个表是这样的

这只是最简单的sql表，如果再加上外键关联什么的，一般程序员的脑容量是记不住那些sql语句的，于是有了orm,实现上面同样的功能，代码如下

你说，娘那个腚的，并没有感觉代码量变少啊，呵呵， 孩子莫猴急，好戏在后面

Lazy Connecting
The Engine, when first returned by create_engine(), has not actually tried to connect to the database yet; that happens only the first time it is asked to perform a task against the database.　　

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

事实上，我们用第一种方式创建的表就是基于第2种方式的再封装。

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

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

查询

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

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

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

修改

回滚

获取所有数据

多条件查询

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

统计和分组

分组

相当于原生sql为

输出为

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

外键关联

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

The relationship.back_populates parameter is a newer version of a very common SQLAlchemy feature calledrelationship.backref. The relationship.backref parameter hasn’t gone anywhere and will always remain available! The relationship.back_populates is the same thing, except a little more verbose and easier to manipulate. For an overview of the entire topic, see the section Linking Relationships with Backref.　　

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

创建关联对象

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

from sqlalchemy.orm import sessionmaker

engine = create_engine("mysql+pymysql://wei:[email protected]/oldboydb",
                       encoding='utf-8')#, echo=True)#echo=True 打印数据

Base = declarative_base()  # 生成orm基类


class User(Base):
    __tablename__ = 'user'  # 表名
    id = Column(Integer, primary_key=True)#Integer 整型
    name = Column(String(32))
    password = Column(String(64))

    #------执行自动打印
    def __repr__(self):
        return "<%s name:%s>" % (self.id,self.name)

class Student(Base):
    __tablename__ = 'student'  # 表名
    id = Column(Integer, primary_key=True)#Integer 整型
    name = Column(String(32),nullable=False)
    register_date = Column(DATE,nullable=False)
    gender =Column(String(32),nullable=False)

    #------执行自动打印
    def __repr__(self):
        return "<%s name:%s>" % (self.id,self.name)


Base.metadata.create_all(engine)  # 创建表结构  engine类似sock
                #sessionmaker  游标
Session_class = sessionmaker(bind=engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
Session = Session_class()  # 生成session实例  #就是相当于cursor

# user_obj = User(name="join", password="join3714")  # 生成你要创建的数据对象
# user_obj2 = User(name="sml", password="123")
# print(user_obj.name, user_obj.id)  # 此时还没创建对象呢，不信你打印一下id发现还是None
#
# Session.add(user_obj)  # 把要创建的数据对象添加到这个session里， 一会统一创建
# Session.add(user_obj2)
# print(user_obj.name, user_obj.id)  # 此时也依然还没创建

#-------数据库查询
#data是一个对象            filter_by()里面什么都不写查所有
data = Session.query(User).filter_by().all()#查询
#data = Session.query(User).filter(User.id > 1).all()#查询 filter(User.id > 2) 等于用==
#data = Session.query(User).filter_by(id = 1).all()

#print(data[0].name,data[0].password)#data[0=]
#print(data)

#----修改要用.first()查询
# data.name = "Jack liu"
# data.password = "shit downd"

#----统计
# count = Session.query(User).filter(User.name.like("jo%")).count()
# print('---', count)


fake_user = User(name='Rain', password='12345')
#Session.add(fake_user)

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

#Session.rollback()  # 此时你rollback一下
#print('ater rollback')

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

#print(Session.query(User).filter(User.name.in_(['Jack','rain'])).count() )#统计

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

#连表
#print(Session.query(User,Student).filter(User.id == Student.id).all())
#要外键关联
#print(Session.query(User).join(Student).all())

Session.commit()  # 现此才统一提交，创建数据

-----------------------------------------------------------------------------

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Enum,DATE, Integer, String,ForeignKey

from sqlalchemy.orm import sessionmaker,relationship


engine = create_engine("mysql+pymysql://wei:[email protected]/oldboydb2",
                       encoding='utf-8')#, echo=True)#echo=True 打印数据

Base = declarative_base()  # 生成orm基类

class Student(Base):
    __tablename__ = 'student'  # 表名
    id = Column(Integer, primary_key=True)
    name = Column(String(32),nullable=False)
    register_data = Column(DATE,nullable=False)

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

class StudyRecord(Base):
    __tablename__ = 'study_record'  # 表名
    id = Column(Integer, primary_key=True)
    day = Column(Integer, nullable=False)
    status = Column(String(32),nullable=False)
    stu_id = Column(Integer,ForeignKey("student.id"))#外键关联

    #student字段查Student  Student通过my_study_record反查StudyRecord
    student = relationship("Student", backref="my_study_record")#关联

    def __repr__(self):
        return "<%s day:%s status:%s>" % (self.student.name,self.day,self.status)

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

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

# s1 = Student(name="wei",register_data="2017-06-3")
# s2 = Student(name="Jac",register_data="2015-03-25")
# s3 = Student(name="Rain",register_data="2014-08-23")
# s4 = Student(name="Eric",register_data="2017-06-13")
#
# study_obj1 = StudyRecord(day=1,status="YES",stu_id=1)
# study_obj2 = StudyRecord(day=2,status="YES",stu_id=1)
# study_obj3 = StudyRecord(day=3,status="YES",stu_id=1)
# study_obj4 = StudyRecord(day=1,status="YES",stu_id=2)
#
# session.add_all([s1,s2,s3,s4,study_obj1,study_obj2,study_obj3,study_obj4])#一次性创建

# #查询
stu_obj = session.query(Student).filter(Student.name=='wei').first()
print(stu_obj.my_study_record)
#session.commit()

常用查询语法

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 a Customer class that contains two foreign keys to an Address class:

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

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

解决办法如下

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

-----------------------------------------------------------------------------------------------------------------------------

　orm_fk.py

#多外键
from sqlalchemy import create_engine
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(64))

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


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

    def __repr__(self):
        return self.street


engine = create_engine("mysql+pymysql://wei:[email protected]/oldboydb2",
                       encoding='utf-8')#, echo=True)#echo=True 打印数据

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

-----------------------------------------------------------------------------------------------------------------------

orm_fk_api

#插入数据
from day12 import orm_many_fk

from sqlalchemy.orm import sessionmaker

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

#创建数据
# addr1 = orm_many_fk.Address(street="Tiantongyuan",city="ChangPing",state="BJ")
# addr2 = orm_many_fk.Address(street="Wudaokou",city="Haidian",state="BJ")
# addr3 = orm_many_fk.Address(street="Yanjiao",city="LangFang",state="HB")
#
# session.add_all([addr1,addr2,addr3])
# c1 = orm_many_fk.Customer(name="weiliang",billing_address=addr1,shipping_address=addr2)
# c2 = orm_many_fk.Customer(name="Jack",billing_address=addr3,shipping_address=addr3)
#
# session.add_all([c1,c2])

#---查 ---以上是数据
obj = session.query(orm_many_fk.Customer).filter(orm_many_fk.Customer.name=="weiliang").first()
print(obj.name,obj.billing_address,obj.shipping_address)

session.commit()

5.多对多关系

现在来设计一个能描述“图书”与“作者”的关系的表结构，需求是

1. 一本书可以有好几个作者一起出版
2. 一个作者可以写好几本书

此时你会发现，用之前学的外键好像没办法实现上面的需求了，因为

当然你更不可以像下面这样干，因为这样就你就相当于有多条书的记录了，太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)#出版日期
                                     #通过 secondary字段查第三张表   backref通过Author反向查Book
    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


engine = create_engine("mysql+pymysql://wei:[email protected]/oldboydb2?charset=utf8",
                       encoding='utf-8')#, echo=True)#echo=True 打印数据

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

接下来创建几本书和作者

from day12 import orm_m2m
from sqlalchemy.orm import sessionmaker

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

#插入数据
# b1= orm_m2m.Book(name="learn python with wei",pub_date="2015-06-03")
# b2= orm_m2m.Book(name="learn zhangbility with alex",pub_date="2017-08-03")
# b3= orm_m2m.Book(name="learn hook up girls with alex",pub_date="2016-06-13")
#

b4= orm_m2m.Book(name="去放风",pub_date="2016-06-13")

# a1=orm_m2m.Author(name="wei")
# a2=orm_m2m.Author(name="jack")
# a3=orm_m2m.Author(name="Rain")
#
# #关联关系
# b1.authors = [a1,a3]
# b3.authors= [a1,a2,a3]
#
# session.add_all([b1,b2,b3,a1,a2,a3])

session.add_all([b4,])

#---- 查

# author_obj = session.query(orm_m2m.Author).filter(orm_m2m.Author.name=="wei").first()
# print(author_obj)#print(author_obj.books[1].pub_date)  #author_obj.books
# # #---反向查
# book_obj = session.query(orm_m2m.Book).filter(orm_m2m.Book.id==2).first()
# #print(book_obj.authors)
#
# #删除作者
# book_obj.authors.remove(author_obj)

session.commit()

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

多对多删除

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

通过书删除作者

直接删除作者　

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

处理中文

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

eng = create_engine('mysql://root:[email protected]:3306/test2?charset=utf8',echo=True)