全栈 - 12 数据库 用Python操作MySQL

这是全栈数据工程师养成攻略系列教程的第十二期：12 数据库 用Python操作MySQL。

我们已经接触了如何使用Web工具phpMyAdmin和本地软件Navicat操作MySQL，现在再来了解下如何使用Python操作MySQL。

MySQLdb

MySQLdb是Python中操作MySQL的功能包，在命令行中使用pip安装即可。

pip install mysql-python

如果可以在Python中import成功，则表示安装没有问题，否则还得继续折腾。不过安装和配置等事情毕竟是一劳永逸的，纠结一次，受益终身，绝知此事须躬行。

import MySQLdb

建立连接

在Sublime中新建一个代码，首先需要import相关的包。

import MySQLdb
import MySQLdb.cursors

打开MAMP或者WAMP并启动Web服务，使得MySQL运行起来，可以通过访问phpMyAdmin管理页面以确认MySQL成功运行。然后，使用以下代码即可建立MySQL数据库连接，其中host为数据库的主机地址，可以使用127.0.0.1或localhost表示本机，user和passwd分别为数据库的用户名和密码，db表示接下来要操作的数据库，port和charset表示连接的端口和字符集。以上参数分别替换成实际值即可，这里我们使用之前在本机数据库中新建的douban数据库，得到的cursor变量可用于执行后续数据库操作。如果需要连接云端服务器的数据库，使用相应的配置参数即可。

db = MySQLdb.connect(host='127.0.0.1', user='root', passwd='root', db='douban', port=8889, charset='utf8', cursorclass = MySQLdb.cursors.DictCursor)
db.autocommit(True)
cursor = db.cursor()

执行操作

和数据库相关的操作无非CURD四种，即Create、Update、Read、Delete。要用到的数据可以在我的Github上找到，里面的data文件夹中除了上次的西游记小说，还包括这次要用到的douban_movie_clean.txt，其中包含一行表头，之后每一行都是一条电影数据，包括id、title、url、cover、rate等15个字段，字段之间以^分割，主要是避免中文内容里包括逗号导致冲突。

首先来看下如何向数据表中插入数据，以下代码读取douban_movie_clean.txt中的数据并逐条插入数据表中。需要注意的是，数据表的结构应当和需要插入的字段保持一致，即movie表应当中包含主键id、标题title、链接url、评分rate、时长length、简介description六个字段。

# 读取数据
fr = open('douban_movie_clean.txt', 'r')

count = 0
for line in fr:
    count += 1
    # count表示当前处理到第几行了
    print count
    # 跳过表头
    if count == 1:
        continue

    # strip()函数可以去掉字符串两端的空白符
    # split()函数按照给定的分割符将字符串分割为列表
    line = line.strip().split('^')
    # 插入数据，注意对齐字段
    # execute()函数第一个参数为要执行的SQL命令
    # 这里用字符串格式化的方法生成一个模板
    # %s表示一个占位符
    # 第二个参数为需要格式化的参数，传入到模板中
    cursor.execute("insert into movie(title, url, rate, length, description) values(%s, %s, %s, %s, %s)", [line[1], line[2], line[4], line[-3], line[-1]])

# 关闭读文件
fr.close()

运行以上代码之后，在phpMyAdmin中选择douban数据库中的movie表，在Browse标签页下即可看到成功插入的数据。点击SQL标签，输入Select count(*) from movie并点击Go执行SQL命令，可以统计数据表中一共有多少条数据记录。

接下来再了解下如何更新数据库中的数据。SQL命令可以根据给定的条件，更新满足条件的记录，例如改变记录中的某些字段。既然每条数据都有唯一的主键id，不妨将id作为条件进行更新。当然也可以做一些更有意义的更新，例如添加一个“电影时长分类”字段，然后对于每条记录，如果时长大于100，则“电影时长分类”更新为“长电影”，否则更新为“短电影”。

# 更新需要提供条件、需要更新的字段、更新的新值
# 以下对于id为1的记录，将其title和length两个字段进行更新
cursor.execute("update movie set title=%s, length=%s where id=%s", ['全栈数据工程师养成攻略', 999, 1])

运行以上代码之后，在phpMyAdmin中Browse标签页下即可看到，id为1的记录相应字段确实已经得到了更新。

然后就是最常用的读取操作，一方面是取出已有的数据进行加工和计算得到新的结果并再次存储，另一方面是在Web项目中从后端取出数据传递到前端展示。读取数据时可以仅读取一条，也可以选择多条；可以读取全部字段，也可以选择部分字段；还可以按某个字段进行排序，使得读取多条数据时的结果有序排列。

# 读取全部数据的全部字段
cursor.execute("select * from movie")
movies = cursor.fetchall()
# 返回元组，每一项都是一个字典
# 对应一条记录的全部字段和字段值
print type(movies), len(movies), movies[0]

# 读取一条数据的部分字段
# 返回一个字段，对应所选择的部分字段和字段值
cursor.execute("select id, title, url from movie")
movie = cursor.fetchone()
print type(movie), len(movie), movie

# 读取一条数据的部分字段
# 按id降序排序，默认为升序
cursor.execute("select id, title, url from movie order by id desc")
movie = cursor.fetchone()
print type(movie), len(movie), movie

最后是从数据库中删除数据，删除数据是不可恢复的，所以务必需要谨慎操作，并一定要提供删除条件，这样仅满足删除条件的记录才会被删除。

# 删除数据务必要提供删除条件
# 此处删除id为1的记录
cursor.execute("delete from movie where id=%s", [1])

关闭连接

使用Python操作完数据库之后，别忘记了关闭数据库连接。

# 关闭数据库连接
cursor.close()
db.close()

扩展内容

我们会发现在使用Python操作数据库时，主要是使用execute()函数并传入SQL命令。以上介绍的都是最基础的CURD操作，其实SQL命令可以融合非常多的功能并写得更为复杂。

以下链接提供了一份更加完整的SQL教程，http://www.runoob.com/sql/sql-tutorial.html，系统地讲解了SQL中的其他常用高级语法。强烈推荐学习一遍，这样才能在用Python操作数据库时，得心应手地写出满足需求的SQL命令来。

视频链接：用Python操作MySQL