Python3-ORM-Sqlalchemy

目录：

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人的共同点）。
2. 现在的各种ORM框架都在尝试使用各种方法来减轻这块（LazyLoad，Cache），效果还是很显著的。

2. sqlalchemy安装

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

                             

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

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

pip install SQLAlchemy<br><br>pip install pymysql  #由于mysqldb依然不支持py3，所以这里我们用pymysql与sqlalchemy交互

 3.sqlalchemy基本使用

sql原生语句创建表：

CREATE TABLE user (
    id INTEGER NOT NULL AUTO_INCREMENT,
    name VARCHAR(32),
    password VARCHAR(64),
    PRIMARY KEY (id)
)

 orm,实现上面同样的功能，代码如下：

第1种创建表结构的方法：

table_structure.py

#Author:Yun
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:yun258762@localhost/test_db",
                       encoding='utf-8', echo=True)

Base = declarative_base()  # 生成orm基类


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


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

 懒惰连接
当create_engine（）第一次返回时，引擎实际上还没有尝试连接到数据库; 只有在第一次要求它对数据库执行任务时才会发生这种情况。

第2种创建表结构的方法：

#Author:Yun
from sqlalchemy import Table, MetaData, Column, Integer, String, ForeignKey
from sqlalchemy.orm import mapper
from sqlalchemy import create_engine

metadata = MetaData()#生成metadata类
#创建user表，继承metadata类 
#Engine使用Schama Type创建一个特定的结构对象
user = Table('new_user', metadata,
             Column('id', Integer, primary_key=True),
             Column('name', String(50)),
             Column('fullname', String(50)),
             Column('password', String(12))
             )
class User(object):
    def __init__(self, name, fullname, password):
        self.name = name
        self.fullname = fullname
        self.password = password

#表元数据是使用Table构造单独创建的，然后通过mapper（）函数与User类相关联
mapper(User,
       user)
# 通过ConnectionPooling 连接数据库
engine = create_engine("mysql+pymysql://root:yun258762@localhost/test_db?charset=utf8", max_overflow=5, echo=True)
# 通过Dialect执行SQL
metadata.create_all(engine) #创建表结构

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

 表已经创建好了开始数据操作：

增加数据：

#Author:Yun
import table_structure#导入表结构模块
from  sqlalchemy.orm import sessionmaker

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

user_obj = table_structure.User(name="王昭君", password="123456")  # 生成你要创建的数据对象
user_obj2 = table_structure.User(name="韩信", password="123456")  # 生成你要创建的数据对象
#print(user_obj.name, user_obj.id)  # 此时还没创建对象呢，不信你打印一下id发现还是None

#print(user_obj.name, user_obj.id)  # 此时也依然还没创建
Session.add(user_obj)  # 把要创建的数据对象添加到这个session里， 一会统一创建
Session.add(user_obj2)  # 把要创建的数据对象添加到这个session里， 一会统一创建
Session.commit()  # 现此才统一提交，创建数据

效果：

mysql> select * from  user;
+----+-------------+----------+
| id | name        | password |
+----+-------------+----------+
| 1 | 王昭君      | 123456   |
| 2 | 韩信        | 123456   |
+----+-------------+----------+
2 rows in set (0.00 sec)

 查找数据：

#Author:Yun
import table_structure#导入表结构模块
from  sqlalchemy.orm import sessionmaker

Session_class = sessionmaker(bind=table_structure.engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
Session = Session_class()  # 生成session实例#cursor
#查找所有数据
data1 = Session.query(table_structure.User).filter_by().all()
#查找第一个数据
data2 = Session.query(table_structure.User).filter_by().first()
#查找id=1的数据
data3= Session.query(table_structure.User).filter_by(id=1).all()
#查找id=2的数据
data4 = Session.query(table_structure.User).filter(table_structure.User.id ==2).all()
#查找id>1的所有数据
data5 = Session.query(table_structure.User).filter(table_structure.User.id>1).all()
#多条件查询，id>0&id<2的数据
data6 = Session.query(table_structure.User).filter(table_structure.User.id>0).filter(table_structure.User.id<2).all()

print('data1:',data1)
print('data2:',data2)
print('data3:',data3)

print('data4:',data4)
print('data5:',data5)
print('data6:',data6)

效果：

data1: [<1 name:王昭君>, <2 name:韩信>]
data2: <1 name:王昭君>
data3: [<1 name:王昭君>]
data4: [<2 name:韩信>]
data5: [<2 name:韩信>]
data6: [<1 name:王昭君>]

 修改数据：

#Author:Yun
#修改数据
import table_structure#导入表结构模块
from  sqlalchemy.orm import sessionmaker

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

data = Session.query(table_structure.User).filter_by(name="王昭君").first()#修改数据

data.name = "妲己"#将王昭君修改为妲己
data.password = '654321'
Session.commit()

 效果：

mysql> select * from  user;
+----+-------------+----------+
| id | name        | password |
+----+-------------+----------+
| 1 | 妲己        | 654321   |
| 2 | 韩信        | 123456   |
+----+-------------+----------+
2 rows in set (0.00 sec)

 回滚：

#Author:Yun
#回滚,类似于事物
import table_structure#导入表结构模块
from  sqlalchemy.orm import sessionmaker

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

user_obj = table_structure.User(name="老夫子", password="123456")  # 生成你要创建的数据对象
user_obj2 = table_structure.User(name="雅典娜", password="123456")  # 生成你要创建的数据对象

Session.add(user_obj)  # 把要创建的数据对象添加到这个session里， 一会统一创建
Session.add(user_obj2)  # 把要创建的数据对象添加到这个session里， 一会统一创建
print(Session.query(table_structure.User.name).filter(table_structure.User.name.in_(['老夫子','雅典娜'])).all())
print('--------------after  rollback---------------:')
Session.rollback()
print(Session.query(table_structure.User.name).filter(table_structure.User.name.in_(['老夫子','雅典娜'])).all())

Session.commit()#现在才统一提交修改数据

 效果：

[('老夫子',), ('雅典娜',)]
--------------after  rollback---------------:
[]

 

删除数据：

 

#Author:Yun
#删除数据
import table_structure#导入表结构模块
from  sqlalchemy.orm import sessionmaker

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

data = Session.query(table_structure.User).filter_by(name="妲己").first()#获取数据
Session.delete(data)#删除数据

Session.commit()

 效果：

mysql> select * from  user;
+----+-------------+----------+
| id | name        | password |
+----+-------------+----------+
| 2 | 韩信        | 123456   |
+----+-------------+----------+
1 rows in set (0.00 sec)

 

 

统计和分组：

 

#Author:Yun
import table_structure#导入表结构模块
from  sqlalchemy.orm import sessionmaker

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

print(Session.query(table_structure.User).filter(table_structure.User.name.in_(['韩信'])).count())#mysql 默认大小写是一样的，如 'hello'=='HELLO'
Session.commit()#现在才统一提交修改数据

 效果：

1

分组：

#Author:Yun
#统计
import table_structure#导入表结构模块
from  sqlalchemy.orm import sessionmaker

Session_class = sessionmaker(bind=table_structure.engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
Session = Session_class()  # 生成session实例#cursor
from  sqlalchemy  import func
print(Session.query(table_structure.User.name,func.count(table_structure.User.name)).group_by(table_structure.User.name).all())
Session.commit()#现在才统一提交修改数据

 效果：

[('韩信', 1)]

 

外键关联：

我们创建两个表，student2，study_record：

table_struct.py

#Author:Yun
#连表操作
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String,DATE,ForeignKey
from  sqlalchemy.orm import sessionmaker,relationship
engine = create_engine("mysql+pymysql://root:yun258762@localhost/test_db",
                       encoding='utf-8')

Base = declarative_base()  # 生成orm基类
class Student(Base):
    __tablename__ = 'student2'  # 表名
    id = Column(Integer, primary_key=True)
    name = Column(String(32),nullable=False)

    register_date = Column(DATE,nullable=False)

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


class  stu_record(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('student2.id'))
    # stu_obj = query(id=1)
    # student = query(Student).filter(Student.id == stu_obj.stu_id).first()
    #只是在内存中建立关联关系
    #允许在stu_record表中用字段self.student.name查询Student表中的数据
    #stu_obj2 = Session.query(Student).filter(Student.name == 'luban1').first()
    #允许在Student表中用字段stu_obj2.my_study_record查询stu_record表中的数据

    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)  # 创建表结构

为表添加数据：

#Author:Yun
import table_struct#导入刚刚创建的表结构
from  sqlalchemy.orm import sessionmaker
Session_class = sessionmaker(bind=table_struct.engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
Session = Session_class()  #生成session实例#cursor
s1 = table_struct.Student(name='学员A',register_date='2015-11-29')
s2 = table_struct.Student(name='学员B',register_date='2016-11-29')
s3 = table_struct.Student(name='学员C',register_date='2017-11-29')
s4 = table_struct.Student(name='学员D',register_date='2018-11-29')

stu_obj1 = table_struct.stu_record(day=1,status='Yes',stu_id=5)
stu_obj2 = table_struct.stu_record(day=2,status='Yes',stu_id=5)
stu_obj3 = table_struct.stu_record(day=2,status='Yes',stu_id=6)
stu_obj4 = table_struct.stu_record(day=3,status='No',stu_id=6)


Session.add_all([s1,s2,s3,s4,stu_obj1,stu_obj2,stu_obj3,stu_obj4])

Session.commit()#现在才统一提交修改数据

 跨表查询：

#Author:Yun
#连表
import table_struct#导入刚刚创建的表结构
from  sqlalchemy.orm import sessionmaker
Session_class = sessionmaker(bind=table_struct.engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
Session = Session_class()#生成session实例#cursor
#跨表查询
stu_obj = Session.query(table_struct.Student).filter(table_struct.Student.name=='学员A').first()
print(stu_obj.my_study_record)
Session.commit()#现在才统一提交修改数据

 效果：

 

[<学员A day:1 status:Yes>, <学员A day:2 status:Yes>]

 常用查询语法：

 

常见的过滤器操作符
以下是filter（）中使用的一些最常见运算符的概述

 

• equals:

       query.filter(User.name == 'ed')
  

• not equals:

       query.filter(User.name != 'ed')
  

• LIKE:

  query.filter(User.name.like('%ed%'))

• IN:

• NOT IN:
  query.filter(~User.name.in_(['ed', 'wendy', 'jack']))

• IS NULL:

• IS NOT NULL:

• AND:
  2.1. ObjectRelationalTutorial 17

 

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

# works with query objects too:

query.filter(User.name.in_( session.query(User.name).filter(User.name.like('%ed%'))

))

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

SQLAlchemy Documentation, Release 1.1.0b1

# use and_()

from sqlalchemy import and_
query.filter(and_(User.name == 'ed', User.fullname == 'Ed Jones'))

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

Note: Makesureyouuseand_()andnotthePythonandoperator! • OR:

Note: Makesureyouuseor_()andnotthePythonoroperator! • MATCH:

query.filter(User.name.match('wendy'))
Note: match() uses a database-specific MATCH or CONTAINS f 

 

4.多外键关联

 处理的最常见情况之一是两个表之间有多个外键路径。考虑一个Customer类，它包含Address类的两个外键：

 

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

#Author:Yun
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String,DATE,ForeignKey
from  sqlalchemy.orm import relationship
engine = create_engine("mysql+pymysql://root:yun258762@192.168.176.139/test_db",
                       encoding='utf-8')

Base = declarative_base()  # 生成orm基类
class Customer(Base):
    __tablename__ = 'customer'  # 表名
    id = Column(Integer, primary_key=True)
    name = Column(String(32),nullable=False)

    builling_address_id = Column(Integer,ForeignKey('address.id'))

    shipping_address_id = Column(Integer,ForeignKey('address.id'))

    builling_address = relationship("Address",foreign_keys=[builling_address_id])
    shipping_address = relationship("Address",foreign_keys=[shipping_address_id])
    def __repr__(self):
        return  "<%s name:%s>" % (self.id,self.name)


class  Address(Base):

    __tablename__ = 'address'
    id = Column(Integer,primary_key=True)
    street = Column(String(64),nullable=False)
    city = Column(String(64),nullable=False)
    state = Column(String(64),nullable=False)


    def __repr__(self):

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

 添加数据，并跨表查询

#Author:Yun
#同一张表里关联另一个表里的两个地址
import  orm_fk

from sqlalchemy.orm import  sessionmaker

Session_class = sessionmaker(bind=orm_fk.engine)#创建于数据

session = Session_class()#生成session实例  #cursor

add1  = orm_fk.Address(street='中路',city='艾欧尼亚',state='召唤师峡谷' )
add2  = orm_fk.Address(street='上路',city='艾欧尼亚',state='召唤师峡谷' )
add3  = orm_fk.Address(street='下路',city='艾欧尼亚',state='召唤师峡谷' )

session.add_all([add1,add2,add3])

c1 = orm_fk.Customer(name='鲁班',builling_address=add1,shipping_address=add1)
c2 = orm_fk.Customer(name='卡莎',builling_address=add1,shipping_address=add2)
c3 = orm_fk.Customer(name='妖姬',builling_address=add2,shipping_address=add3)
#为表添加数据
#session.add_all([c1,c2,c3])
obj = session.query(orm_fk.Customer).filter(orm_fk.Customer.name=='妖姬').first()
print(obj.name,obj.builling_address,obj.shipping_address)
session.commit()

效果：

妖姬 上路 下路

5.多对多关系

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

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

思维构图：

一本书----》多个作者；一个作者-----》多本书

 

 

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

用orm如何表示呢？

#Author:Yun
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

engine = create_engine("mysql+pymysql://root:yun258762@localhost/test_db?charset=utf8",
                       )

Base = declarative_base()
#通orm自动维护这表，所以不需要用类方法创建，映射
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 "<book_name:%s date:%s>" % (self.name,self.pub_date)

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

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

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

 

 接下来创建几本书和作者:

import orm_fk

from sqlalchemy.orm  import  sessionmaker

Session_class = sessionmaker(bind=orm_fk.engine)#创建于数据


session = Session_class()

b1 = orm_fk.Book(name='消费者行为学',pub_date='2018-11-29')
b2 = orm_fk.Book(name='活着',pub_date='2018-11-29')
b3 = orm_fk.Book(name='盲井',pub_date='2018-11-29')

a1 = orm_fk.Author(name='鲁班')
a2 = orm_fk.Author(name='妖姬')
a3 = orm_fk.Author(name='妲己')
a4 = orm_fk.Author(name='提莫')


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

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

session.commit()

 

 效果：

mysql> select * from  authors;
+----+--------+
| id | name   |
+----+--------+
|  1 | 鲁班   |
|  2 | 妖姬   |
|  3 | 提莫   |
|  4 | 妲己   |
+----+--------+
4 rows in set (0.00 sec)

mysql> select * from  books;
+----+--------------------+------------+
| id | name               | pub_date   |
+----+--------------------+------------+
|  1 | 消费者行为学       | 2018-11-29 |
|  2 | 盲井               | 2018-11-29 |
|  3 | 活着               | 2018-11-29 |
+----+--------------------+------------+
3 rows in set (0.00 sec)

mysql> select * from  book_m2m_author;
+---------+-----------+
| book_id | author_id |
+---------+-----------+
|       1 |         1 |
|       1 |         4 |
|       2 |         1 |
|       2 |         2 |
|       2 |         4 |
|       3 |         2 |
|       3 |         3 |
+---------+-----------+
7 rows in set (0.00 sec)

 

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

 

import orm_fk

from sqlalchemy.orm  import  sessionmaker

Session_class = sessionmaker(bind=orm_fk.engine)#创建于数据


session = Session_class()


print('----通过作者表查关联的书-------')
author_obj = session.query(orm_fk.Author).filter(orm_fk.Author.name=='鲁班').first()
print(author_obj.books)
print('----通过书表查看关联的作者-------')

book_obj = session.query(orm_fk.Book).filter(orm_fk.Book.id==1).first()

print(book_obj.authors)

session.commit()

 效果：

----通过作者表查关联的书-------
[<book_name:消费者行为学 date:2018-11-29>, <book_name:盲井 date:2018-11-29>]
----通过书表查看关联的作者-------
[<author:鲁班>, <author:妲己>]

多对多删除

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

通过书删除作者

#Author:Yun
import orm_fk

from sqlalchemy.orm  import  sessionmaker

Session_class = sessionmaker(bind=orm_fk.engine)#创建于数据


session = Session_class()


author_obj = session.query(orm_fk.Author).filter_by(name="鲁班").first()

book_obj = session.query(orm_fk.Book).filter_by(name="盲井").first()

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

 

 效果：

删除之前：
mysql> select * from  book_m2m_author;
+---------+-----------+
| book_id | author_id |
+---------+-----------+
|       1 |         1 |
|       1 |         4 |
|       2 |         1 |
|       2 |         2 |
|       2 |         4 |
|       3 |         2 |
|       3 |         3 |
+---------+-----------+
7 rows in set (0.00 sec)
删除之后：
mysql> select * from  book_m2m_author;
+---------+-----------+
| book_id | author_id |
+---------+-----------+
|       1 |         1 |
|       1 |         4 |
|       2 |         2 |
|       2 |         4 |
|       3 |         2 |
|       3 |         3 |
+---------+-----------+
6 rows in set (0.00 sec)

 

直接删除作者　

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

#Author:Yun
import orm_fk

from sqlalchemy.orm  import  sessionmaker

Session_class = sessionmaker(bind=orm_fk.engine)#创建于数据

session = Session_class()

author_obj =session.query(orm_fk.Author).filter_by(name="鲁班").first()
print(author_obj.name , author_obj.books)
session.delete(author_obj)

session.commit()

效果：

mysql> select * from  book_m2m_author;
+---------+-----------+
| book_id | author_id |
+---------+-----------+
|       1 |         1 |
|       1 |         4 |
|       2 |         2 |
|       2 |         4 |
|       3 |         2 |
|       3 |         3 |
+---------+-----------+
6 rows in set (0.00 sec)

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