SQL必知必会
陈旸
清华大学计算机博士
立即订阅
10179 人已学习
课程目录
已完结 49 讲
0/4登录后,你可以任选4讲全文学习。
开篇词 (1讲)
开篇词丨SQL可能是你掌握的最有用的技能
免费
第一章:SQL语法基础篇 (19讲)
01丨了解SQL:一门半衰期很长的语言
02丨DBMS的前世今生
03丨学会用数据库的方式思考SQL是如何执行的
04丨使用DDL创建数据库&数据表时需要注意什么?
05丨检索数据:你还在SELECT * 么?
06丨数据过滤:SQL数据过滤都有哪些方法?
07丨什么是SQL函数?为什么使用SQL函数可能会带来问题?
08丨什么是SQL的聚集函数,如何利用它们汇总表的数据?
09丨子查询:子查询的种类都有哪些,如何提高子查询的性能?
10丨常用的SQL标准有哪些,在SQL92中是如何使用连接的?
11丨SQL99是如何使用连接的,与SQL92的区别是什么?
12丨视图在SQL中的作用是什么,它是怎样工作的?
13丨什么是存储过程,在实际项目中用得多么?
14丨什么是事务处理,如何使用COMMIT和ROLLBACK进行操作?
15丨初识事务隔离:隔离的级别有哪些,它们都解决了哪些异常问题?
16丨游标:当我们需要逐条处理数据时,该怎么做?
17丨如何使用Python操作MySQL?
18丨SQLAlchemy:如何使用Python ORM框架来操作MySQL?
19丨基础篇总结:如何理解查询优化、通配符以及存储过程?
第二章:SQL性能优化篇 (18讲)
20丨当我们思考数据库调优的时候,都有哪些维度可以选择?
21丨范式设计:数据表的范式有哪些,3NF指的是什么?
22丨反范式设计:3NF有什么不足,为什么有时候需要反范式设计?
23丨索引的概览:用还是不用索引,这是一个问题
24丨索引的原理:我们为什么用B+树来做索引?
25丨Hash索引的底层原理是什么?
26丨索引的使用原则:如何通过索引让SQL查询效率最大化?
27丨从数据页的角度理解B+树查询
28丨从磁盘I/O的角度理解SQL查询的成本
29丨为什么没有理想的索引?
30丨锁:悲观锁和乐观锁是什么?
31丨为什么大部分RDBMS都会支持MVCC?
32丨查询优化器是如何工作的?
33丨如何使用性能分析工具定位SQL执行慢的原因?
34丨答疑篇:关于索引以及缓冲池的一些解惑
35丨数据库主从同步的作用是什么,如何解决数据不一致问题?
36丨数据库没有备份,没有使用Binlog的情况下,如何恢复数据?
37丨SQL注入:你的SQL是如何被注入的?
第三章:认识DBMS (7讲)
38丨如何在Excel中使用SQL语言?
39丨WebSQL:如何在H5中存储一个本地数据库?
40丨SQLite:为什么微信用SQLite存储聊天记录?
41丨初识Redis:Redis为什么会这么快?
42丨如何使用Redis来实现多用户抢票问题
43丨如何使用Redis搭建玩家排行榜?
44丨DBMS篇总结和答疑:用SQLite做词云
第四章:SQL项目实战 (3讲)
45丨数据清洗:如何使用SQL对数据进行清洗?
46丨数据集成:如何对各种数据库进行集成和转换?
47丨如何利用SQL对零售数据进行分析?
结束语 (1讲)
结束语 | 互联网的下半场是数据驱动的时代
SQL必知必会
登录|注册

18丨SQLAlchemy:如何使用Python ORM框架来操作MySQL?

陈旸 2019-07-22
上节课,我介绍了 Python DB API 规范的作用,以及如何使用 MySQL 官方的 mysql-connector 驱动来完成数据库的连接和使用。在项目比较小的时候,我们可以直接使用 SQL 语句,通过 mysql-connector 完成与 MySQL 的交互,但是任何事物都有两面性,随着项目规模的增加,代码会越来越复杂,维护的成本也越来越高,这时 mysql-connector 就不够用了,我们需要更好的设计模式。
Python 还有另一种方式可以与 MySQL 进行交互,这种方式采用的是 ORM 框架。我们今天就来讲解如何使用 ORM 框架操作 MySQL,那么今天的课程你需要掌握以下几个方面的内容:
什么是 ORM 框架,以及为什么要使用 ORM 框架?
Python 中的 ORM 框架都有哪些?
如何使用 SQLAlchemy 来完成与 MySQL 的交互?

我们为什么要使用 ORM 框架?

在讲解 ORM 框架之前,我们需要先了解什么是持久化。如下图所示,持久化层在业务逻辑层和数据库层起到了衔接的作用,它可以将内存中的数据模型转化为存储模型,或者将存储模型转化为内存中的数据模型。
你可能会想到,我们在讲事务的 4 大特性 ACID 时,提到过持久性。你可以简单地理解为,持久性就是将对象数据永久存储在数据库中。通常我们将数据库的作用理解为永久存储,将内存理解为暂时存储。我们在程序的层面操作数据,其实都是把数据放到内存中进行处理,如果需要数据就会通过持久化层,从数据库中取数据;如果需要保存数据,就是将对象数据通过持久化层存储到数据库中。
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《SQL必知必会》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(19)

  • ttttt
    缺少一些代码,可以参考廖雪峰的这个。
    https://www.liaoxuefeng.com/wiki/1016959663602400/1017803857459008
    2019-07-22
    13
  • 墨禾
    以下从ORM的作用,是什么,优缺点以及一些比较流行的ORM的对比的个人总结:

    1.ORM的作用
    对象关系映射,能够直接将数据库对象进行持久化。

    在没有ORM前,我们要自己写数据库连接方法,自己在方法里面嵌入原生的sql语句去访问数据表……

    这时问题就来了:
    数据库名,数据表名完全暴露在代码中,有脱库的风险;
    需要我们自己处理数据表对象,比如说把数据表中取出的数据转化为标准json等,sql语句安全过滤,数据表、字段别名、兼容多种数据库等一系列的数据处理工作;

    下面介绍一下ORM到底是啥?

    2、ORM是什么?
    ORM作为数据库层与业务逻辑层之间的一个抽象,能够将业务逻辑的处理持久化为内存对象,交由数据库去处理。其封装了数据库的连接,数据表的操作细节……在文中我们可以看到ORM将sql语句做了封装,我们可以通过filter实现过滤,而不是写where子句。

    ORM真的那么好?

    3、优缺点
    优点:
    安全:因为屏蔽了数据库的具体操作细节以及对sql做了严格的过滤,因此能够保证数据库信息的隐蔽性,同时防止sql注入。
    简单:屏蔽了数据层的访问细节,我们只需要集中注意力处理业务逻辑就可以了。

    缺点:
    性能低:自动化意味着加载很多即使没有必要的关联和映射,牺牲性能。但ORM也采取了一些补救措施:对象懒加载,缓存技术等。

    学习成本高:面向对象的封装设计,是的我们必须要去了解对象的处理细节。

    难以实现复杂查询:ORM实现的是一些通用的数据处理方法,一些负责的业务处理还是需要自己组装sql。

    那么还有哪些比较流行的ORM呢?
    hibernate:强调对单条数据的处理
    mybits:基于自定义配置的sql操作
    2019-07-23
    9
  • 一叶知秋
    日常交作业~~~

    # -*- coding:utf-8 -*-
    from sqlalchemy import and_
    from sqlalchemy import Column, INT, FLOAT, VARCHAR
    from sqlalchemy import create_engine
    from sqlalchemy.orm import sessionmaker
    from sqlalchemy.ext.declarative import declarative_base

    Base = declarative_base()


    class Test_db:
        def __init__(self):
            """此处填上自己的连接配置"""
            self.engine = create_engine(
                'mysql+pymysql://UserName:Password@host:port/Db_Name?charset=utf8')
            db_session = sessionmaker(bind=self.engine)
            self.session = db_session()

        def update(self, target_class, query_filter, target_obj):
            """
            更新操作通用方法
            :param target_class: 表对象
            :param query_filter: 查询条件
            :param target_obj: 更新目标对象
            :return:
            """
            try:
                self.session.query(target_class).filter(query_filter).update(target_obj)
                self.session.commit()
                self.session.close()
                return True
            except Exception as e:
                print(e)


    class Player(Base):
        """定义表结构"""
        __tablename__ = 'player'
        player_id = Column(INT(), primary_key=True)
        team_id = Column(INT())
        player_name = Column(VARCHAR(255))
        height = Column(FLOAT())

        def __init__(self, player_id, team_id, player_name, height):
            self.player_id = player_id
            self.team_id = team_id
            self.player_name = player_name
            self.height = height


    if __name__ == '__main__':
        db_obj = Test_db()
        query_filter = and_(Player.height == 2.08)
        target_obj = {'height': 2.09}
        update_result = db_obj.update(Player, query_filter, target_obj)

    后续更新数量、更新结果等等判断就略过了...
    (小声bb:什么时候极客时间评论也能支持markdown啊。。)
    2019-07-22
    6
  • 夜路破晓
    框架对实体的映射不难理解,数据库本身就是对现实世界的映射,借由映射将事实转换为数据.
    代码部分有些基础的也不难理解;基础较弱硬钢的亲们,耐心一条条来缕也可以捋顺,都是基础的东西,无非花费时间长短问题.有几个坑这里记录下,供后来人借鉴:
    1.关于初始化连接数据库问题.creat_engine的参数这块容易卡壳,可以参考以下文字说明:
    create_engine("数据库类型+数据库驱动://数据库用户名:数据库密码@IP地址:端口/数据库",其他参数)
    2.数据库驱动这块,老师的参考代码是用mysqlconnector,沿承得是上篇中导入mysql-connector包;网上一些资料以及参考其他同学的答案有使用pymysql,要用这个需安装pip install pymysql.这两货对于本篇的学习内容在本质上是一样的,任选一个即可.
    3.在代码复写过程中,删除操作一直报错.网上查了资料说是跟返回值有关.经过测试,发现问题所在,filter返回结果为None.也就是说没有查询到"约翰-科林斯".往回倒腾,发现开始新增数据那里,增加的" 约翰-科林斯 ",前后对比后者两侧多了个空格.统一前后,删除操作顺利完成.
    2019-07-22
    4
  • ABC
    翻了一下SQLAlchemy的官方文档,看到一个简单的办法,作业如下:

    '''

    作业:

    使用SQLAlchemy工具查询身高为2.08米的球员,并且将这些球员的身高修改为2.09;


    参考:

    https://docs.sqlalchemy.org/en/13/core/dml.html


    '''

    from sqlalchemy import Column, String, Integer, Float,create_engine,update
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy.orm import sessionmaker
    Base = declarative_base()
    engine = create_engine('mysql+mysqlconnector://root:123456@localhost:3306/geektime-sql')


    class Player(Base):
        __tablename__ = 'player'
     
        player_id = Column(Integer, primary_key=True, autoincrement=True)
        team_id = Column(Integer)
        player_name = Column(String(255))
        height = Column(Float(3,2))

    def to_dict(self):
        return {c.name: getattr(self, c.name, None)
                for c in self.__table__.columns}

    if __name__ == '__main__':
    DBSession = sessionmaker(bind=engine)
    session = DBSession()
    Base.to_dict = to_dict
    print("更新前:")
    rows = session.query(Player).filter(Player.height == 2.08).all()
    print([row.to_dict() for row in rows])
    # 参考: https://docs.sqlalchemy.org/en/13/core/dml.html#sqlalchemy.sql.expression.update
    stmt = update(Player).where(Player.height == 2.08).values(height=2.09)
    engine.execute(stmt)
    session.commit()
    rows = session.query(Player).filter(Player.height == 2.09).all()
    print("更新后:")
    print([row.to_dict() for row in rows])
    session.close()
    太长,省略了部分执行结果.自己执行一下,就可以看到完整结果了..

    更新前:
    [{'player_id': 10010, 'team_id': 1001, 'player_name': '乔恩-洛伊尔', 'height': Decimal('2.0800000000')}......
    更新后:
    [{'player_id': 10010, 'team_id': 1001, 'player_name': '乔恩-洛伊尔', 'height': Decimal('2.0900000000')}......
    [Finished in 0.9s]
    2019-07-22
    3
  • ABC
    文章中的示例代码.完整可运行.

    from sqlalchemy import Column, String, Integer, Float,create_engine
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy.orm import sessionmaker
    Base = declarative_base()
    # 初始化数据库连接,修改为你的数据库用户名和密码
    engine = create_engine('mysql+mysqlconnector://root:123456@localhost:3306/geektime-sql')

    # 定义 Player 对象:
    class Player(Base):
        # 表的名字:
        __tablename__ = 'player'
     
        # 表的结构:
        player_id = Column(Integer, primary_key=True, autoincrement=True)
        team_id = Column(Integer)
        player_name = Column(String(255))
        height = Column(Float(3,2))


    # 增加 to_dict() 方法到 Base 类中
    def to_dict(self):
        return {c.name: getattr(self, c.name, None)
                for c in self.__table__.columns}



    if __name__ == '__main__':
    # 创建 DBSession 类型:
    DBSession = sessionmaker(bind=engine)
    # 创建 session 对象:
    session = DBSession()

    # 创建 Player 对象:
    new_player = Player(team_id = 1003, player_name = "约翰 - 科林斯", height = 2.08)
    # 添加到 session:
    session.add(new_player)
    # 提交即保存到数据库:
    session.commit()
    # 关闭 session:
    session.close()
    # 将对象可以转化为 dict 类型
    Base.to_dict = to_dict
    # 查询身高 >=2.08 的球员有哪些
    # rows = session.query(Player).filter(Player.height >=2.08, Player.height <=2.10).all()
    # from sqlalchemy import or_
    # rows = session.query(Player).filter(or_(Player.height >=2.08, Player.height <=2.10)).all()
    rows = session.query(Player).filter(Player.height >= 2.08).all()
    print([row.to_dict() for row in rows])
    from sqlalchemy import func
    rows = session.query(Player.team_id, func.count(Player.player_id)).group_by(Player.team_id).having(func.count(Player.player_id)>5).order_by(func.count(Player.player_id).asc()).all()
    print(rows)
    row = session.query(Player).filter(Player.player_name=='约翰 - 科林斯').first()
    session.delete(row)
    session.commit()
    session.close()
    row = session.query(Player).filter(Player.player_name=='索恩-马克').first()
    row.height = 2.17
    session.commit()
    session.close()


    2019-07-22
    3
  • ttttt
    错误解决:
    如果报如下错误:Authentication plugin 'caching_sha2_password' is not supported
    sqlalchemy.exc.NotSupportedError: (mysql.connector.errors.NotSupportedError) Authentication plugin 'caching_sha2_password' is not supported (Background on this error at: http://sqlalche.me/e/tw8g)
    可以参考下面的链接处理:
    https://stackoverflow.com/questions/51783313/how-do-i-get-sqlalchemy-create-engine-with-mysqlconnector-to-connect-using-mysql
    2019-07-22
    3
  • Destroy、
    rows = session.query(Player).filter(Player.height==2.08).all()
    for row in rows:
        row.height = 2.09
    session.commit()
    session.close()
    2019-07-22
    2
  • Geek_5d805b
    问一下,我们已经创建过了player表,能说一下对于已有的数据库表,怎么直接将存储模型转换为数据模型吗,而不是再按字段新建
    2019-07-25
    1
  • ABC
    另外,SQLAlchemy和MyBatis有点像,唯一不同的是MyBatis可以把SQL语句写在XML文件里面(当然也可以写在Java方法上),SQLAlchemy好像只能用字符串方式(在官网暂时没找到其它方式的示例)来写SQL语句.
    2019-07-22
    1
  • 许童童
    老师你好,能否多讲一些ORM框架的缺点,以及为什么互联网项目大多不用ORM的原因?
    2019-07-22
    3
    1
  • 阿锋
    上面那个分组查询,按照分组后数据行数递增的顺序进行排序,怎么结果是[(1001, 20), (1002, 17)],那不是递减?是不是写错了?

    作者回复: rows = session.query(Player.team_id, func.count(Player.player_id)).group_by(Player.team_id).having(func.count(Player.player_id)>5).order_by(func.count(Player.player_id).asc()).all()

    这里使用的是asc(),所以结果应该是:[(1002, 17), (1001, 20)],你可以再check下order_by的部分

    2019-07-22
    1
  • 大牛凯
    老师好,对于修改数据的事例有一点困惑还请您解答。对于下面这段代码中

    row = session.query(Player).filter(Player.player_name=='索恩 - 马克').first()
    row.height = 2.17
    session.commit()
    session.close()

    我理解row是存在于内存中的对象,但是我们在修改后并没有传递到数据库中,如果直接commit可以进行修改的话,这个row是在哪里存放的对象呢?

    作者回复: 我们对数据表进行的增删改查实际上都是通过 session对象来完成的。这里row是定位session对象中想要查询的位置,然后对row.height进行了修改,也就是对session对象中那个查询位置的height进行了修改。这时数据表中的内容还没有更新,需要采用session.commit()来完成持久化。所以重点是我们修改了session对象的数据,然后进行了session.commit()

    2019-07-22
    1
  • 骑行的掌柜J
    陈老师 ,你在增加数据库数据哪里是不是少导入了两个类和一行定义Base的代码?
    from sqlalchemy.ext.declarative import declarative_base
    #引用基本映射类
    from sqlalchemy.orm import sessionmaker
    #引用session类操作后面的增删改查

    Base = declarative_base()
    #建立基本映射类,方便后面真正的映射类Player来继承它
    2019-09-09
  • Venom
    老师,建议最后能把完整代码给出来。对于我们没用过的人来讲,缺少一条import语句也很苦恼啊
    2019-09-03
  • 我在你的视线里
    session对象是django自带的。还是在项目里创建的。
    2019-08-11
  • jxs1211
    老师,我的表中有个时间字段,我想在插入数据时,自动生成时间应怎么设置该字段,是这样吗:
    `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    2019-07-25
  • Geek_5d805b
    to_dict方法这块看不太懂,base类指的是player类吗,谁给讲讲
    2019-07-25
    1
  • 我不会算法
    ORM的使用,更多的不都是通过查文档的吗?
    2019-07-24
收起评论
19
返回
顶部