极客时间已完结课程限时免费阅读

17丨如何使用Python操作MySQL？

17丨如何使用Python操作MySQL？-极客时间

17丨如何使用Python操作MySQL？

2019-07-19 陈旸 来自北京
《SQL必知必会》
课程介绍

讲述：陈旸

时长07:45大小7.09M

我们之前都是直接在 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 层的代码移植会比较简单。
我们在使用 Python 对 DBMS 进行操作的时候，需要经过下面的几个步骤：
引入 API 模块；
与数据库建立连接；
执行 SQL 语句；
关闭数据库连接。

如何使用 mysql-connector

使用 Python 对数据库进行访问需要基于 DB API 规范，这里有不少库供我们选择，比如 MySQLdb、mysqlclient、PyMySQL、peewee 和 SQLAIchemy 等。今天我讲解的是 mysql-connector，它是 MySQL 官方提供的驱动器，用来给后端语言，比如 Python 提供连接。
下面我们看下如何用 Python 使用 mysql-connector，以完成数据库的连接和使用。
首先安装 mysql-connector。在使用前，你需要先使用下面这句命令进行安装：
pip install mysql-connector
在安装之后，你可以创建数据库连接，然后查看下数据库的版本号，来验证下数据库是否连接成功。代码如下：
# -*- coding: UTF-8 -*-
import mysql.connector
# 打开数据库连接
db = mysql.connector.connect(
host="localhost",
user="root",
passwd="XXX", # 写上你的数据库密码
database='wucai',
auth_plugin='mysql_native_password'
)
# 获取操作游标
cursor = db.cursor()
# 执行SQL语句
cursor.execute("SELECT VERSION()")
# 获取一条数据
data = cursor.fetchone()
print("MySQL版本: %s " % data)
# 关闭游标&数据库连接
cursor.close()
db.close()
运行结果：
MySQL版本: 8.0.13
上面这段代码中有两个重要的对象你需要了解下，分别是 Connection 和 Cursor。
Connection 就是对数据库的当前连接进行管理，我们可以通过它来进行以下操作：
通过指定 host、user、passwd 和 port 等参数来创建数据库连接，这些参数分别对应着数据库 IP 地址、用户名、密码和端口号；
使用 db.close() 关闭数据库连接；
使用 db.cursor() 创建游标，操作数据库中的数据；
使用 db.begin() 开启事务；
使用 db.commit() 和 db.rollback()，对事务进行提交以及回滚。
当我们通过cursor = db.cursor()创建游标后，就可以通过面向过程的编程方式对数据库中的数据进行操作：
使用cursor.execute(query_sql)，执行数据库查询；
使用cursor.fetchone()，读取数据集中的一条数据；
使用cursor.fetchall()，取出数据集中的所有行，返回一个元组 tuples 类型；
使用cursor.fetchmany(n)，取出数据集中的多条数据，同样返回一个元组 tuples；
使用cursor.rowcount，返回查询结果集中的行数。如果没有查询到数据或者还没有查询，则结果为 -1，否则会返回查询得到的数据行数；
使用cursor.close()，关闭游标。

对数据表进行增删改查

了解了 Connection 和 Cursor 的使用方式之后，我们来看下如何来对 heros 数据表进行 CRUD 的操作，即增加、读取、更新和删除。
首先是增加数据。
假设我们想在 player 表中增加一名新球员，姓名为“约翰·科林斯”，球队 ID 为 1003（即亚特兰大老鹰），身高为 2.08m。代码如下：
# 插入新球员
sql = "INSERT INTO player (team_id, player_name, height) VALUES (%s, %s, %s)"
val = (1003, "约翰-科林斯", 2.08)
cursor.execute(sql, val)
db.commit()
print(cursor.rowcount, "记录插入成功。")
我们使用 cursor.execute 来执行相应的 SQL 语句，val 为 SQL 语句中的参数，SQL 执行后使用 db.commit() 进行提交。需要说明的是，我们在使用 SQL 语句的时候，可以向 SQL 语句传递参数，这时 SQL 语句里要统一用（%s）进行占位，否则就会报错。不论插入的数值为整数类型，还是浮点类型，都需要统一用（%s）进行占位。
另外在用游标进行 SQL 操作之后，还需要使用 db.commit() 进行提交，否则数据不会被插入。
然后是读取数据。我们来看下数据是否被插入成功，这里我们查询下身高大于等于 2.08m 的球员都有哪些，代码如下：
# 查询身高大于等于2.08的球员
sql = 'SELECT 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)
运行结果：
(10003, '安德烈-德拉蒙德', 2.11)
(10004, '索恩-马克', 2.16)
(10009, '扎扎-帕楚里亚', 2.11)
(10010, '乔恩-洛伊尔', 2.08)
(10011, '布雷克-格里芬', 2.08)
(10015, '亨利-埃伦森', 2.11)
(10023, '多曼塔斯-萨博尼斯', 2.11)
(10024, '迈尔斯-特纳', 2.11)
(10032, 'TJ-利夫', 2.08)
(10033, '凯尔-奥奎因', 2.08)
(10037, '伊凯·阿尼博古', 2.08)
(10038, '约翰-科林斯', 2.08)
你能看到球员约翰·科林斯被正确插入。
那么如何修改数据呢？
假如我想修改刚才插入的球员约翰·科林斯的身高，将身高修改成 2.09，代码如下：
# 修改球员约翰-科林斯
sql = 'UPDATE player SET height = %s WHERE player_name = %s'
val = (2.09, "约翰-科林斯")
cursor.execute(sql, val)
db.commit()
print(cursor.rowcount, "记录被修改。")
最后我们看下如何删除约翰·科林斯这个球员的数据，代码如下：
sql = 'DELETE FROM player WHERE player_name = %s'
val = ("约翰-科林斯",)
cursor.execute(sql, val)
db.commit()
print(cursor.rowcount, "记录删除成功。")
最后都执行完了，我们来关闭游标和数据库的连接，使用以下代码即可：
cursor.close()
db.close()
针对上面的操作过程，你可以模拟下数据的 CRUD 操作，但有几点你需要注意。
1. 打开数据库连接以后，如果不再使用，则需要关闭数据库连接，以免造成资源浪费。
2. 在对数据进行增加、删除和修改的时候，可能会出现异常，这时就需要用try...except捕获异常信息。比如针对插入球员约翰·科林斯这个操作，你可以写成下面这样：
import traceback
try:
sql = "INSERT INTO player (team_id, player_name, height) VALUES (%s, %s, %s)"
val = (1003, "约翰-科林斯", 2.08)
cursor.execute(sql, val)
db.commit()
print(cursor.rowcount, "记录插入成功。")
except Exception as e:
# 打印异常信息
traceback.print_exc()
# 回滚
db.rollback()
finally:
# 关闭数据库连接
db.close()
运行结果告诉我们记录插入成功。
3. 如果你在使用 mysql-connector 连接的时候，系统报的错误为authentication plugin caching_sha2，这时你需要下载最新的版本更新来解决，点击这里进行更新。

总结

我今天讲解了如何使用 Python 来操作 MySQL，这里我们使用的是官方提供的 mysql-connector，当然除了它之外，还有很多库可以进行选择。
在使用基于 DB API 规范的协议时，重点需要掌握 Connection 和 Cursor 这两个对象，Connection 就是对数据库的连接进行管理，而 Cursor 是对数据库的游标进行管理，通过它们，我们可以执行具体的 SQL 语句，以及处理复杂的数据。
用 Python 操作 MySQL，还有很多种姿势，mysql-connector 只是其中一种，实际上还有另外一种方式，就是采用 ORM 框架。ORM 的英文是 Object Relational Mapping，也就是采用对象关系映射的模式，使用这种模式可以将数据库中各种数据表之间的关系映射到程序中的对象。这种模式可以屏蔽底层的数据库的细节，不需要我们与复杂的 SQL 语句打交道，直接采用操作对象的形式操作就可以。
不过如果应用数据实体少，其实没有必要使用 ORM 框架，针对少量对象的管理，自己实现起来也很简单，比如本篇文章中我讲到的采用官方提供的 mysql-connector 驱动的方式来实现 CRUD。引入一个框架的学习成本很高，代码膨胀也很厉害，所以如果是相对简单的操作，完全可以自己动手来实现。
使用 Python 对数据库进行操作，关键在于实战，所以这里我出一个练习题。请你使用 Python 对 heros 表中最大生命值大于 6000 的英雄进行查询，并且输出相应的属性值。
欢迎在评论区写下你的答案，我会与你一起交流。也欢迎把这篇文章分享给你的朋友或者同事，与它们一起交流一下。
分享给需要的人，Ta购买本课程，你将得20元
生成海报并分享

赞 15

提建议

© 版权归极客邦科技所有，未经许可不得传播售卖。 页面已增加防盗追踪，如有侵权极客邦将依法追究其法律责任。
上一篇
16丨游标：当我们需要逐条处理数据时，该怎么做？
下一篇
18丨SQLAlchemy：如何使用Python ORM框架来操作MySQL？
unpreview
 写留言

精选留言(42)

  • JustDoDT
    2019-07-19
    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() # 建议吧数据库链接信息写到配置文件里，防止密码泄露。
    展开

    作者回复: Good Job

    共 3 条评论
    42
  • 一叶知秋
    2019-07-19
    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
    展开

    作者回复: Good Job

    8
  • mickey
    2019-07-19
    # -*- 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
    展开

    作者回复: Good Job

    6
  • 夜路破晓
    2019-07-19
    auth_plugin='mysql_native_password' 哪位亲给解释下这个参数

    作者回复: 密码验证方式，采用明文

    共 2 条评论
    5
  • 大斌
    2019-07-23
    核心代码： cursor = db.cursor() sql = "select name,hp_max from heros where hp_max > %s" val = (6000,) cursor.execute(sql,val) data = cursor.fetchall() 注意：val里面的元素后面必须要加英文逗号，不加或者中文逗号都会报错
    展开

    作者回复: Good Job

    共 3 条评论
    4
  • TKbook
    2019-07-19
    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, " 记录删除成功。")
    展开
    共 4 条评论
    4
  • victor666
    2020-03-22
    Python直接写SQL比Java方便多了

    作者回复: 哈哈 是的 Python代码更简洁

    2
  • 丁丁历险记
    2019-11-15
    当一些听着很虚的理论用于实战时，其威力是巨大的，例如信息的正交性。

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

    共 3 条评论
    2
  • 林彦
    2019-07-19
    try...except...那部分代码没有关闭游标的语句。关闭数据库连接的语句执行时一般都会先隐式关闭并释放当前的游标吗？
    2
  • 一步
    2019-07-19
    看目录，我以为到 SQL刷题了。。。
    2
  • bahao
    2020-10-25
    看来要补习python了 执行sql太方便了
    1
  • taoist
    2019-12-19
    import pymysql import pymysql.cursors cfg = { "host": "127.0.0.1", "user": "root", "passwd": "toor", "database": "test", "charset": "utf8mb4", "autocommit": True, 'cursorclass':pymysql.cursors.DictCursor } db_con = pymysql.connect(**cfg) try: with db_con.cursor() as cur: cur.execute("SELECT id,name,hp_max FROM heros WHERE hp_max > 6000 ") res = cur.fetchall() for i in res: print(i) finally: db_con.close()
    展开

    作者回复: 正确

    1
  • 大斌
    2019-07-22
    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, )
    展开
    1
  • mickey
    2019-07-19
    sql = 'DELETE FROM player WHERE player_name = " 约翰-科林斯 "'
    1
  • 极客酱
    2019-07-19
    删除约翰·科林斯这个球员的数据代码里面，excute那个函数缺少了val的参数吧？
    1
  • 年少挽滑稽世无双
    2022-09-09 来自四川
    # 请你使用 Python 对 heros 表中最大生命值大于 6000 的英雄进行查询，并且输出相应的属性值。 # -*- coding: UTF-8 -*- import mysql.connector # 打开数据库连接 db = mysql.connector.connect( port=3308, host="localhost", user="root", passwd="password", # 写上你的数据库密码 database='honor_of_kings', auth_plugin='mysql_native_password' ) # 获取操作游标 cursor = db.cursor() sql = 'SELECT name, hp_max FROM heros WHERE hp_max>6000' cursor.execute(sql) data = cursor.fetchall() for each_player in data: print(each_player) # 关闭游标&数据库连接 cursor.close() db.close()
    展开
  • Geek_dc1862
    2022-05-12
    如果MySQL版本过高，比如8.0.29 连接时会报错，可以采用以下语句解决: 查看全局参数的值: show global variables like 'authentication_policy'; show global variables like 'default_authentication_plugin'; 切换全局参数的值： set global authentication_policy='*,,'; 修改root用户的加密方式： ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'your_pwd'; 查询加密方式： SELECT user,host,plugin from mysql.user;
    展开
  • 小柒·代购
    2021-11-17
    import mysql.connector db = mysql.connector.connect( host="localhost", user="root", passwd="", database="", auth_plugin="mysql_native_password" ) cursor = db.cursor() sql = "SELECT * FROM heros WHERE hp_max > 6000" cursor.execute(sql) data = cursor.fetchall() for each_heros in data: print(each_heros) cursor.close() db.close()
    展开
  • 牛哥
    2021-07-23
    sql = 'DELETE FROM player WHERE player_name = %s' val = ("约翰-科林斯",) cursor.execute(sql, val) db.commit() print(cursor.rowcount, "记录删除成功。") val那里“约翰-科林斯”后边为何接逗号？测试了不加逗号就报语法错误。
    展开
  • 张静
    2021-06-03
    import mysql.connector # 打开数据库连接 db = mysql.connector.connect( host="xx.xx.xx.xx", port="3306", user="xx", passwd="xx", database='xx', auth_plugin='mysql_native_password') # 获取操作游标 cursor = db.cursor() SQL="SELECT 列名 FROM 表名 where 条件" # 执行SQL语句 cursor.execute(SQL) # 获取一条数据 data = cursor.fetchone() print(data) # 关闭游标&数据库连接 cursor.close() db.close() 为什么会报Unread result found ，fetchone（）需要参数吗指定返回哪一行？
    展开