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必知必会
登录|注册

17丨如何使用Python操作MySQL?

陈旸 2019-07-19
我们之前都是直接在 DBMS 里面进行 SQL 的操作,实际上我们还可以通过后端语言对 DBMS 进行访问以及进行相应的操作,这样更具有灵活性,可以实现一些较为复杂的操作。作为一个后端开发人员,掌握一些 SQL 技术是必须的;作为一个数据库管理人员,了解后端语言如何开发和管理数据库也是很有必要的。
今天我以 Python 为例,讲解下如何对 MySQL 数据库进行操作。你需要掌握以下几个方面的内容:
Python 的 DB API 规范是什么,遵守这个规范有什么用?
基于 DB API,MySQL 官方提供了驱动器 mysql-connector,如何使用它来完成对数据库管理系统的操作?
CRUD 是最常见的数据库的操作,分别对应数据的增加、读取、修改和删除。在掌握了 mysql-connector 的使用方法之后,如何完成对数据表的 CRUD 操作?

Python DB API 规范

Python 可以支持非常多的数据库管理系统,比如 MySQL、Oracle、SQL Server 和 PostgreSQL 等。为了实现对这些 DBMS 的统一访问,Python 需要遵守一个规范,这就是 DB API 规范。我在下图中列出了 DB API 规范的作用,这个规范给我们提供了数据库对象连接、对象交互和异常处理的方式,为各种 DBMS 提供了统一的访问接口。这样做的好处就是如果项目需要切换数据库,Python 层的代码移植会比较简单。
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《SQL必知必会》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(31)

  • ttttt
    import json
    import traceback
    import mysql.connector

    # 读取数据库链接配置文件
    with open('mysql.json', encoding='utf-8') as con_json:
        con_dict = json.load(con_json)

    # 打开数据库链接
    db = mysql.connector.connect(
        host=con_dict['host'],
        user=con_dict['user'],
        passwd=con_dict['passwd'],
        database=con_dict['database'],
        auth_plugin=con_dict['auth_plugin'],
    )

    # 获取操作游标
    cursor = db.cursor()
    try:
        sql = 'SELECT id, name, hp_max FROM heros WHERE hp_max>6000'
        cursor.execute(sql)
        data = cursor.fetchall()
        print(cursor.rowcount, '查询成功。')
        for each_hero in data:
            print(each_hero)
    except Exception as e:
        # 打印异常信息
        traceback.print_exc()
    finally:
        cursor.close()
        db.close()
    # 建议吧数据库链接信息写到配置文件里,防止密码泄露。
    2019-07-19
    18
  • 一叶知秋
    sqlalchemy用习惯了。。。献丑来一段Python代码吧
    ```Python
    # -*- 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 query_all(self, target_class, query_filter):
            result_list = self.session.query(target_class).filter(query_filter).all()
            self.session.close()
            return result_list


    class Heros(Base):
        """定义表结构"""
        __tablename__ = 'heros'
        id = Column(INT(), primary_key=True)
        name = Column(VARCHAR(255))
        hp_max = Column(FLOAT())
        mp_max = Column(FLOAT())

        def __init__(self, id, name, hp_max, mp_max):
            self.id = id
            self.name = name
            self.hp_max = hp_max
            self.mp_max = mp_max


    if __name__ == '__main__':
        db_obj = Test_db()
        query_filter = and_(Heros.hp_max > 6000)
        heros = db_obj.query_all(Heros, query_filter)
        for hero_info in heros:
            print("id:{}, name:{}, hp_max:{}, mp_max:{}".format(hero_info.id, hero_info.name,
                                                                hero_info.hp_max, hero_info.mp_max))
    ```
    id:10000, name:夏侯惇, hp_max:7350.0, mp_max:1746.0
    id:10046, name:钟馗, hp_max:6280.0, mp_max:1988.0
    id:10048, name:鬼谷子, hp_max:7107.0, mp_max:1808.0
    id:10051, name:赵云, hp_max:6732.0, mp_max:1760.0
    id:10052, name:橘石京, hp_max:7000.0, mp_max:0.0
    id:10055, name:杨戬, hp_max:7420.0, mp_max:1694.0
    id:10056, name:达摩, hp_max:7140.0, mp_max:1694.0
    id:10057, name:孙悟空, hp_max:6585.0, mp_max:1760.0
    id:10058, name:刘备, hp_max:6900.0, mp_max:1742.0
    .....执行结果有点多字数限制了
    Process finished with exit code 0
    2019-07-19
    5
  • Destroy、
    sql = 'DELETE FROM player WHERE name = %s'
    val = (" 约翰 - 科林斯 ")
    cursor.execute(sql)
    db.commit()
    print(cursor.rowcount, " 记录删除成功。")
    这里写错了哇,这样写才不会报错:
    sql = 'DELETE FROM player WHERE player_name = %s'
    val = (" 约翰 - 科林斯 ", )
    cursor.execute(sql, val)
    db.commit()
    print(cursor.rowcount, " 记录删除成功。")

    2019-07-19
    2
    3
  • mickey
    # -*- coding: UTF-8 -*-
    import mysql.connector
    import traceback

    # 打开数据库连接
    db = mysql.connector.connect(
        host="localhost",
        user="root",
        passwd="123456", # 写上你的数据库密码
        database='nba',
        auth_plugin='mysql_native_password'
    )

    # 获取操作游标
    cursor = db.cursor()

    try:

        # 查询heros 表中最大生命值大于 6000 的英雄进行查询,并且输出相应的属性值。
        sql = 'SELECT name, hp_max FROM heros WHERE hp_max > %s ORDER BY hp_max DESC'
        val = (6000,)
        cursor.execute(sql, val)
        data = cursor.fetchall()
        for each_player in data:
            print(each_player)
    except Exception as e:
      # 打印异常信息
      traceback.print_exc()
      # 回滚
      db.rollback()
    finally:
      # 关闭游标 & 数据库连接
      cursor.close()
      db.close()

    输出:

    ('廉颇', 9328.0)
    ('白起', 8638.0)
    ('程咬金', 8611.0)
    ('刘禅', 8581.0)
    ('牛魔', 8476.0)
    ('张飞', 8341.0)
    ('庄周', 8149.0)
    ('刘邦', 8073.0)
    ('项羽', 8057.0)
    ('亚瑟', 8050.0)
    ('东皇太一', 7669.0)
    ('典韦', 7516.0)
    ('曹操', 7473.0)
    ('杨戬', 7420.0)
    ('夏侯惇', 7350.0)
    ('吕布', 7344.0)
    ('哪吒', 7268.0)
    ('墨子', 7176.0)
    ('老夫子', 7155.0)
    ('达摩', 7140.0)
    ('鬼谷子', 7107.0)
    ('关羽', 7107.0)
    ('钟无艳', 7000.0)
    ('橘石京', 7000.0)
    ('刘备', 6900.0)
    ('太乙真人', 6835.0)
    ('孙膑', 6811.0)
    ('赵云', 6732.0)
    ('扁鹊', 6703.0)
    ('铠', 6700.0)
    ('露娜', 6612.0)
    ('孙悟空', 6585.0)
    ('钟馗', 6280.0)
    ('雅典娜', 6264.0)
    ('兰陵王', 6232.0)
    ('宫本武藏', 6210.0)
    ('娜可露露', 6205.0)
    ('高渐离', 6165.0)
    ('芈月', 6164.0)
    ('不知火舞', 6014.0)
    ('孙尚香', 6014.0)

    Process finished with exit code 0
    2019-07-19
    3
  • 林彦
    try...except...那部分代码没有关闭游标的语句。关闭数据库连接的语句执行时一般都会先隐式关闭并释放当前的游标吗?
    2019-07-19
    2
  • 大斌
    核心代码:
    cursor = db.cursor()

    sql = "select name,hp_max from heros where hp_max > %s"
    val = (6000,)
    cursor.execute(sql,val)
    data = cursor.fetchall()
    注意:val里面的元素后面必须要加英文逗号,不加或者中文逗号都会报错
    2019-07-23
    1
  • mickey
    sql = 'DELETE FROM player WHERE player_name = " 约翰-科林斯 "'
    2019-07-19
    1
  • 极客酱
    删除约翰·科林斯这个球员的数据代码里面,excute那个函数缺少了val的参数吧?
    2019-07-19
    1
  • 一步
    看目录,我以为到 SQL刷题了。。。
    2019-07-19
    1
  • 丁丁历险记
    当一些听着很虚的理论用于实战时,其威力是巨大的,例如信息的正交性。

    作者回复: 这种信息论的内容在机器学习中用的还是挺多的

    2019-11-15
    1
  • xcoder
    请问各路在线英雄,安装mysql-connector-python-8.0.17,那么对应的还是重新安装一个8.0.17的mysql了?这个有对老版本,比如5.6的mysql的支持吗?找了半天找不到,只有8.0版本的mysql才有的mysql-connector-python安装包吗?
    2019-09-04
    1
  • -- >=2.08 插入的数据显示不出来

    __author__ = 'Administrator'

    # -*- coding: UTF-8 -*-
    import traceback
    import json
    import mysql.connector
    # 打开数据库连接
    db = mysql.connector.connect(
           host="localhost",
           user="root",
           passwd="hjf@2019", # 写上你的数据库密码
           database='nba',
           auth_plugin='mysql_native_password'
    )
    # 获取操作游标
    cursor = db.cursor()
    # 执行 SQL 语句
    #cursor.execute("SELECT VERSION()")
    # 获取一条数据
    #data = cursor.fetchone()
    #print("MySQL 版本: %s " % data)
    # 关闭游标 & 数据库连接



    # 插入新球员

    #sql = "INSERT INTO player (team_id,player_id,player_name,height) VALUES (%s, %s, %s,%s)"
    #val = (1003, 10038," 约翰 - 科林斯 ", 2.08)
    #cursor.execute(sql,val)
    #db.commit()
    #print(cursor.rowcount, " 记录插入成功。")


    # 查询身高大于等于 2.08 的球员
    sql = 'SELECT team_id, player_id, player_name, height FROM player WHERE height >=2.08'
    cursor.execute(sql)
    data = cursor.fetchall()
    for each_player in data:
      print(each_player)



    cursor.close()
    db.close()
    2019-08-21
  • __author__ = 'Administrator'

    # -*- coding: UTF-8 -*-
    import traceback
    import json
    import mysql.connector
    # 打开数据库连接
    db = mysql.connector.connect(
           host="localhost",
           user="root",
           passwd="hjf@2019", # 写上你的数据库密码
           database='nba',
           auth_plugin='mysql_native_password'
    )
    # 获取操作游标
    cursor = db.cursor()
    # 执行 SQL 语句
    #cursor.execute("SELECT VERSION()")
    # 获取一条数据
    #data = cursor.fetchone()
    #print("MySQL 版本: %s " % data)
    # 关闭游标 & 数据库连接



    # 插入新球员

    sql = "INSERT INTO player (team_id,player_id,player_name,height) VALUES (%s, %s, %s,%s)"
    val = (1003, 10038," 约翰 - 科林斯 ", 2.08)
    cursor.execute(sql,val)
    db.commit()
    print(cursor.rowcount, " 记录插入成功。")

    cursor.close()
    db.close()
    2019-08-21
  • 老师在插入数据的时候,漏掉了 player_id ?
    2019-08-21
  • 发条
    使用8.0以上版本mysql的同学,在连接数据库的时候可能会受到quth_plugin不支持mysql_native_password的报错,
    可以用ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';语句把auth_plugin改掉
    2019-07-26
  • 大斌
    python连接mysql时报错【mysql.connector.errors.NotSupportedError: Authentication plugin 'caching_sha2_password' is not support】。
    原因是:mysql8.0.11使用了Use Strong Password Encryption for Authentication即强密码加密。
    通常的处理方法是:重装mysql【装更低版本的或者将Use Strong Password Encryption for Authentication改为Use Legacy Authentication Method(在Authentication Method中改)】
    还有一种更好的方案,那就是使用【pymysql】库来连接,代码如下:
        db_host = "localhost"
        db_username = "root"
        db_password = "123456"
        db_name = "database_name"
        conn = pymysql.connect(
            host=db_host,
            user=db_username,
            passwd=db_password,
            database=db_name,
        )
    2019-07-22
  • Geek_5d805b
    请问老师关于sql语句中的%s占位问题,如果我要修改多项值可否用变量替代呢?
    2019-07-22
  • cricket1981
    # -*- coding: UTF-8 -*-

    import mysql.connector
    # 打开数据库连接
    db = mysql.connector.connect(
           host="localhost",
           user="root",
           passwd="root", # 写上你的数据库密码
           database='test',
           auth_plugin='mysql_native_password'
    )
    # 获取操作游标
    cursor = db.cursor()
    sql = 'SELECT * FROM heros WHERE hp_max>=6000'
    cursor.execute(sql)
    data = cursor.fetchall()
    for each_hero in data:
      print(each_hero)
    cursor.close()
    db.close()
    2019-07-22
  • cricket1981
    python print语句出来的中文是乱码要怎么处理?python程序第一行加过 # -*- coding: UTF-8 -*-
    (10003, u'\u5b89\u5fb7\u70c8-\u5fb7\u62c9\u8499\u5fb7', 2.11)
    2019-07-22
  • 华夏
    pip install mysql-connector之后连接数据库会报错,更新到最新版还是一样报错,pip install mysql-connector-python之后可以正常运行。大家如果有遇到这个问题的可以参考@林彦-广州-数据分析师 这个办法。
    2019-07-20
收起评论
31
返回
顶部