这是全栈数据工程师养成攻略系列教程的第十二期: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命令来。