python【第十二篇下】操作MySQL数据库以及ORM之 sqlalchemy

内容一览：

1.Python操作MySQL数据库

2. ORM sqlachemy

2.1 ORM简介

　　对象关系映射（英语：Object Relation Mapping，简称ORM，或O/RM，或O/R mapping），是一种程序技术，用于实现面向对象编程语言里不同类型系统的数据之间的转换。从效果上说，它其实是创建了一个可在编程语言里使用的“虚拟对象数据库”。

面向对象是从软件工程基本原则（如耦合、聚合、封装）的基础上发展起来的，而关系数据库则是从数学理论发展而来的，两套理论存在显著的区别。为了解决这个不匹配的现象，对象关系映射技术应运而生。

　　对象关系映射（Object-Relational Mapping）提供了概念性的、易于理解的模型化数据的方法。ORM方法论基于三个核心原则： 简单：以最基本的形式建模数据。 传达性：数据库结构被任何人都能理解的语言文档化。 精确性：基于数据模型创建正确标准化的结构。 典型地，建模者通过收集来自那些熟悉应用程序但不熟练的数据建模者的人的信息开发信息模型。建模者必须能够用非技术企业专家可以理解的术语在概念层次上与数据结构进行通讯。建模者也必须能以简单的单元分析信息，对样本数据进行处理。ORM专门被设计为改进这种联系。

简单的说：ORM相当于中继数据。

　　通过orm将编程语言的对象模型和数据库的关系模型建立映射关系，这样我们在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作就可以了，而不用直接使用sql语言。

orm的优点：

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

缺点：

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

2.2 sqlalchemy

在Python中，最有名的ORM框架是SQLAlchemy

（1）sqlalchemy的安装

pip install sqlalchemy

前提是安装了pymysql

（2）基本使用

SQL语句创建一个MySQL表是这样的：

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

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

#! /usr/bin/env python3
# -*- coding:utf-8 -*-

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

engine = create_engine("mysql+pymysql://root:Root-123@192.168.100.64/liuyouyuan?charset=utf8",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)  # 创建表结构

看了上面的代码是不是觉得更复杂了？这时你脑中有没有浮现黑格尔的那句“存在的就是合乎理性的。”？没有的话，你文盲。如果这个orm没毛用，就没有存在的必要了。且听我慢慢道来：

上面的创建表的方式还有一种，了解一下就行：

from sqlalchemy import Table, MetaData, Column, Integer, String, ForeignKey
from sqlalchemy.orm import mapper
 
metadata = MetaData()
 
user = Table('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
 
mapper(User, user) #the table metadata is created separately with the Table construct, then associated with the User class via the mapper() function

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

#! /usr/bin/env python3
# -*- coding:utf-8 -*-
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker

engine = create_engine("mysql+pymysql://root:Root-123@192.168.100.64/liuyouyuan?charset=utf8",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))

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

user_obj = User(name="ZhaoLiyin", password="admin123")  # 生成你要创建的数据对象  也就是你要在user表中插入这样一条数据（这里只是一个对象）
print(user_obj.name, user_obj.id)  # 此时还没创建对象呢，不信你打印一下id发现还是None

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

运行结果：

ZhaoLiyin None
ZhaoLiyin None
2016-10-25 11:39:53,882 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2016-10-25 11:39:53,882 INFO sqlalchemy.engine.base.Engine ()
2016-10-25 11:39:53,885 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2016-10-25 11:39:53,885 INFO sqlalchemy.engine.base.Engine ()
2016-10-25 11:39:53,886 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin'
2016-10-25 11:39:53,886 INFO sqlalchemy.engine.base.Engine ()
2016-10-25 11:39:53,887 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2016-10-25 11:39:53,887 INFO sqlalchemy.engine.base.Engine ()
2016-10-25 11:39:53,888 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2016-10-25 11:39:53,888 INFO sqlalchemy.engine.base.Engine ()
2016-10-25 11:39:53,889 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1
2016-10-25 11:39:53,889 INFO sqlalchemy.engine.base.Engine ()
2016-10-25 11:39:53,890 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2016-10-25 11:39:53,892 INFO sqlalchemy.engine.base.Engine INSERT INTO user (name, password) VALUES (%s, %s)
2016-10-25 11:39:53,892 INFO sqlalchemy.engine.base.Engine ('ZhaoLiyin', 'admin123')
2016-10-25 11:39:53,893 INFO sqlalchemy.engine.base.Engine COMMIT
2016-10-25 11:39:53,896 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2016-10-25 11:39:53,896 INFO sqlalchemy.engine.base.Engine SELECT user.id AS user_id, user.name AS user_name, user.password AS user_password 
FROM user 
WHERE user.id = %s
2016-10-25 11:39:53,896 INFO sqlalchemy.engine.base.Engine (2,)
ZhaoLiyin 2

Process finished with exit code 0

注意代码中的三个print  对应的输出结果。明白什么时候数据才真正插入user表中。到这里真TM够了，是不是感觉很费劲才插入一条数据？别走，错过就没有下次了  

查询：

#! /usr/bin/env python3
# -*- coding:utf-8 -*-
from orm_1 import User
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine("mysql+pymysql://root:Root-123@192.168.100.64/liuyouyuan?charset=utf8",echo=False)
Session_class = sessionmaker(bind=engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
Session = Session_class()  # 生成session实例
my_user = Session.query(User).filter_by(name="YangZi").first()

print(my_user)
print(my_user.id,my_user.name,my_user.password)

 

输出结果：

<orm_1.User object at 0x03AA8F50>
1 YangZi admin123

可以看出：sqlalchemy帮你把返回的数据映射成一个对象啦，这样你调用每个字段就可以跟调用对象属性一样。

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

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

---

修改：

my_user = Session.query(User).filter_by(name="alex").first()
 
my_user.name = "Alex Li"
 
Session.commit()

---

回滚：

my_user = Session.query(User).filter_by(id=1).first()
my_user.name = "Jack"
 
 
fake_user = User(name='Rain', password='12345')
Session.add(fake_user)
 
print(Session.query(User).filter(User.name.in_(['Jack','rain'])).all() )  #这时看session里有你刚添加和修改的数据
 
Session.rollback() #此时你rollback一下
 
print(Session.query(User).filter(User.name.in_(['Jack','rain'])).all() ) #再查就发现刚才添加的数据没有了。
 
# Session
# Session.commit()

---

获取所有数据：

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

---

多条件查询：

objs = Session.query(User).filter(User.id>0).filter(User.id<7).all()

上面2个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

---

外键关联多对一

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

一个人有多个邮箱地址，或者说多个邮箱地址对应同一个人。这就要用到多对一。

#! /usr/bin/env python3
# -*- coding:utf-8 -*-

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


engine = create_engine("mysql+pymysql://root:admin123@localhost/test_db?charset=utf8")
Base = declarative_base()  # 生成orm基类

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


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

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

class Address(Base):
    """地址表，一个user有多个邮箱地址"""
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True,autoincrement=True)
    email_address = Column(String(32), nullable=False)
    postal_code = Column(Integer)
    user_id = Column(Integer, ForeignKey('user.id'))     # 外键
    user = relationship("User", backref="add")
    # 这句只用于查询。
        # 功能1 相当于给Address这个表添加了一个属性列user，查询时可以用.user得到对应的User对象。但是这列不能插入数据，仅用于查询。
        # 功能2 相当于给User这个表添加了一个属性列add,查询时在user表中可以通过.add得到Address对象。

    def __repr__(self):
        return "email-%s  postal_code-%s" % (self.email_address,self.postal_code)

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

def drop_db():
    Base.metadata.drop_all(engine)  # 删除


def inser_test_data():
    init_db()
    name = ["杨幂", "赵丽颖", "刘亦菲","林志玲", "汤唯", "张馨予","赵伟彤", "陈意涵", "周冬雨","林心如", "范冰冰","梁静茹"]
    user_obj = []
    # 向user表中插入数据
    for i in range(0,11):
        obj = User(name=name[i],password="admin")
        user_obj.append(obj)
    session.add_all(user_obj)
    session.commit()

    # addresses表中插入数据，这里要指定外键关联的user表中的主键
    a1 = Address(email_address="yangmi@163.com",postal_code=12345,user_id=1)
    a2 = Address(email_address="YangMi@163.com",postal_code=12345,user_id=1)
    a3 = Address(email_address="zhaoliying@163.com",postal_code=12340,user_id=2)
    a4 = Address(email_address="ZhaoLiying@163.com",postal_code=12340,user_id=2)
    session.add_all([a1,a2,a3,a4])
    session.commit()
    print("Test data is inserted... ")

# inser_test_data()

add_obj = session.query(Address).filter_by(email_address="yangmi@163.com").first()
print(add_obj.user)  # 通过addresses表查询user
#  <1 name:杨幂 password:admin>


user_obj = session.query(User).filter_by(id=2).first()   # 这样是取第一个对象
print(user_obj.add)  # 通过user表查询对应的地址
# [email-zhaoliying@163.com  postal_code-12340, email-ZhaoLiying@163.com  postal_code-12340]
print(user_obj.add[1].email_address)
# ZhaoLiying@163.com

82 # session会话    query查询    filter过滤
83 # 这里filter的用法可以这样  filter(User.id>1)  filter(User.id==1)  filter_by(id=1)

 

多对多：

现实生活中  

一个班级或者一门课程 可以对应多个学生

一个学生可以有多门课程或者报了多个班级

这就要用到多对多

grade表：

mysql> select * from grade;
+----+--------+
| id | name   |
+----+--------+
|  1 | Python |
|  2 | Linux  |
|  3 | Go     |
+----+--------+
3 rows in set (0.00 sec)

student表：

 

grade_student表：

mysql> select * from grade_student;
+----------+------------+
| grade_id | student_id |
+----------+------------+
|        1 |         10 |
|        2 |         10 |
|        1 |         17 |
|        2 |         17 |
|        3 |         17 |
|        1 |         15 |
|        2 |         15 |
|        3 |         15 |
|        1 |          4 |
|        2 |          4 |
|        1 |          8 |
|        2 |          8 |
|        1 |          9 |
|        2 |          9 |
|        1 |         12 |
|        2 |         12 |
|        1 |         19 |
|        2 |         19 |
|        3 |         19 |
|        1 |         20 |
|        2 |         20 |
|        3 |         20 |
|        1 |          5 |
|        2 |          5 |
|        1 |         14 |
|        2 |         14 |
|        1 |         18 |
|        2 |         18 |
|        3 |         18 |
|        1 |          2 |
|        2 |          2 |
|        1 |         11 |
|        2 |         11 |
|        1 |          1 |
|        2 |          1 |
|        1 |          3 |
|        2 |          3 |
|        1 |         13 |
|        2 |         13 |
|        1 |          7 |
|        2 |          7 |
|        1 |          6 |
|        2 |          6 |
|        1 |         16 |
|        2 |         16 |
|        3 |         16 |
+----------+------------+
46 rows in set (0.00 sec)

完整代码示例：

#! /usr/bin/env python3
# -*- coding:utf-8 -*-

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


engine = create_engine("mysql+pymysql://root:admin123@localhost/test_db?charset=utf8")
Base = declarative_base()  # 生成orm基类

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


#! /usr/bin/env python3
# -*- coding:utf-8 -*-

from sqlalchemy import Table, Column, Integer,String, 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:admin123@localhost/test_db?charset=utf8")
Base = declarative_base()

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

grade_m2m_student = Table('grade_student', Base.metadata,
                                 Column('grade_id', Integer, ForeignKey('grade.id')),
                                 Column('student_id', Integer, ForeignKey('student.id')),
                                 )

class Grade(Base):            # 定义班级表
    """班级表"""
    __tablename__ = 'grade'    # 表名
    id = Column(Integer, primary_key=True,autoincrement=True)
    name = Column(String(32))

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

class Student(Base):
    """学生表"""
    __tablename__ = 'student'
    id = Column(Integer, primary_key=True,autoincrement=True)
    name = Column(String(32))
    qq = Column(String(32))
    grades = relationship("Grade",secondary=grade_m2m_student,backref="students")

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

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

def drop_db():
    Base.metadata.drop_all(engine)  # 删除

def create_grade(name):
    obj = Grade(name=name)
    return obj

def create_student(name,qq):
    obj = Student(name=name,qq=qq)
    return obj


# drop_db()  # 删除表结构
# init_db()   # 创建表结构
#
# # 创建三个班级
# grade_obj = []
# grades = ["Python","Linux","Go"]
# for grade in grades:
#     obj = create_grade(grade)
#     grade_obj.append(obj)
# session.add_all(grade_obj)
# session.commit()
#
# # 添加多个学生
# stu_obj = []
# students = [("杨幂","10001"),("赵丽颖","10002"),("刘亦菲","10003"),("胡歌","10004"),("勒布朗","10005"),("科比","10006"),("布兰妮","10007"),("林志玲","10008"),
#              ("汤唯", "10009"),("张馨予","10010"),("赵伟彤","10011"),("李四","10012"),("王宝强","10013"),
#              ("陈意涵", "10014"),("周冬雨","10015"),("林心如","10016"),("范冰冰","10017"),("梁静茹","10018"),("武藤兰","10019"),("小苍","10020"),]
# for i in range(0,14):
#     obj = create_student(students[i][0],students[i][1])
#     obj.grades = [grade_obj[0],grade_obj[1]]  # 为学生关联班级
#     stu_obj.append(obj)
# for j in range(14,20):
#     obj = create_student(students[j][0], students[j][1])
 99 #     obj.grades = grade_obj   # 为学生关联班级
#     stu_obj.append(obj)
# session.add_all(stu_obj)
# session.commit()
# print("ok...")

# 从grade表中通过.students查询Python班 所有的学生
grade_obj = session.query(Grade).filter_by(name="Python").first()
for stu in grade_obj.students:
    print(stu)
print("----------------------------------------------------------")

# 从stu 表中 通过.grades查询 id为4的学生所在的 所有班级
student_obj = session.query(Student).filter_by(id=4).first()
print(student_obj.grades)


# 运行结果：
# <Student-->id:10 name:张馨予 qq:10010>
# <Student-->id:17 name:范冰冰 qq:10017>
# <Student-->id:15 name:周冬雨 qq:10015>
# <Student-->id:4 name:胡歌 qq:10004>
# <Student-->id:8 name:林志玲 qq:10008>
# <Student-->id:9 name:汤唯 qq:10009>
# <Student-->id:12 name:李四 qq:10012>
# <Student-->id:19 name:武藤兰 qq:10019>
# <Student-->id:20 name:小苍 qq:10020>
# <Student-->id:5 name:勒布朗 qq:10005>
# <Student-->id:14 name:陈意涵 qq:10014>
# <Student-->id:18 name:梁静茹 qq:10018>
# <Student-->id:2 name:赵丽颖 qq:10002>
# <Student-->id:11 name:赵伟彤 qq:10011>
# <Student-->id:1 name:杨幂 qq:10001>
# <Student-->id:3 name:刘亦菲 qq:10003>
# <Student-->id:13 name:王宝强 qq:10013>
# <Student-->id:7 name:布兰妮 qq:10007>
# <Student-->id:6 name:科比 qq:10006>
# <Student-->id:16 name:林心如 qq:10016>
# ----------------------------------------------------------
# [<Grade->id:1 name:Python>, <Grade->id:2 name:Linux>]