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

作者: 宏伦工作室 | 来源:发表于2017-03-14 11:14 被阅读337次

    这是全栈数据工程师养成攻略系列教程的第十二期: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.1localhost表示本机,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

    相关文章

      网友评论

        本文标题:全栈 - 12 数据库 用Python操作MySQL

        本文链接:https://www.haomeiwen.com/subject/wwicnttx.html