Google重新发明了搜索, Facebook重新发明了社交, Apple重新发明了手机, Amazon重新发明了书籍...天朝重新发明了局域网

Python操作MySQL写入读出CSV文件

Python 2017-02-08 浏览量: 4141 字数统计: 703 最后更新: 2017-03-27 23:09

文章目录[显示]

前几日看到有人讨论说一个Python的面试题,题目是这样的:

有一个数据文件,是csv格式,大约1T数据,要导入到MySQL,要求正确高效。

csv数据格式这个样子的

[root@iZ28b4rx8dxZ ~]# cat data.csv 
id,name,sex,age
1,张三,女,18
2,李四,男,19

正好最近要写再测试数据库的脚本,虽然两者不搭边,总归都是操作数据库的,正好测试的时候就用python连了

首先 Python 数据库接口支持很多数据库,什么关系型的像 mysql 啊、PG(PostgreSQL) 啊、SQL Server、Oracle,非关系型的像 MongoDB 啊,Redis 啊, Hbase 等等,

这里就以MySQL数据库为例,毕竟我要用的MySQL嘛,而且面试题也是,当然操作其他数据库道理也都一样,从一个 csv 文件中读入数据,插入到数据库中,再将数据库中的数据读出,保存到另一个csv文件。

介绍

主要定义两个对象,一个用于管理连接的 Connection(count) 了,另一个是用于执行查询的 Cursor (cur)对象。

 

Python 操作数据库的大致思路

  • 导入模块
  • 连接数据库
  • 执行查询返回结果

 

步骤:

  • 导入数据库模块 import MySQLdb
  • 连接数据库 connect ,返回一个 conn 对象
  • 通过该对象的 cursor() 成员函数返回一个 cur 对象
  • 通过 cur 对象的 execute() 方法执行SQL语句
  • 关闭 curconn对象

把csv中的数据读出来放到插入到mysql表

对于mysql 数据库,需要安装第三方模块Mysql-python 。安装完以后,在程序中导入模块即可。

库名:test
表名:student

csv和数据库表的结构都是这样的

idnamesexage
1张三18
2李四20
3王二19

首先要导入MySQLdb模块先安装MySQLdb

pip install MySQLdb


#!/usr/bin/python
# -*- coding: UTF-8 -*-
# This is a script that writes data from CSV to MySQL

import csv
import MySQLdb


def main():
    # 连接数据库
    conn = MySQLdb.connect(
        host='localhost',
        port=3306,
        user='root',
        passwd='123',
        db='test',
    )
    cur = conn.cursor()

    # 创建数据表
    cur.execute("DROP TABLE IF EXISTS `student`")
    conn.commit()
    create_db = """create table student(
                    id int,
                    name varchar(10),
                    sex char(4),
                    age int
                    )
                   ENGINE=InnoDB DEFAULT CHARSET=utf8"""
    cur.execute(create_db)    #执行创建表语句
    conn.commit()

    # 把 CSV 读到数组里
    f = open("/var/python_code/input_CSV_file.csv", 'r')
    student = []
    for row in csv.reader(f):
        student.append(row)
    f.close()

    # 还可以替换成为with
    # student = []
    # with open("/var/python_code/input_CSV_file.csv", 'r') as f:
    #     for row in csv.reader(f):
    #         student.append(row)

    # 执行 insert
    insert_db = "insert into student values(%s, %s, %s, %s)"
    cur.executemany(insert_db, student)    #批量高效插入
    conn.commit()

    # 关闭连接
    if cur:
        cur.close()
    if conn:
        conn.close()


if __name__ == '__main__':
    main()

不得不提一下

executemany() 是对数据进行批量插入如果对效率有要求的时候 最合适不过的选择了,原理大概就是将数组中的元素一个个取出来然后一条条的执行,在这之前我曾尝试过用 execute(),当时是从文件里面读出一行写入到数据可一行,后来我仔细想了一下,如果数据量很大呢,就像面试题说的,如果 1T 的数据呢,估计得等到驴年马月,估计公司也会see by . 于是果断把代码删掉重写成:把文件里面的内容全部读出来写入到数组里,然后用 executemany() 执行批量插入了


执行查询语句,并将结果输出到CSV文件里

#!/usr/bin/python
# -*- coding: UTF-8 -*-
# This is a script that writes data from MySQL to csv

import csv
import MySQLdb


def main():
    # 连接数据库
    conn = MySQLdb.connect(
        host='localhost',
        port=3306,
        user='root',
        passwd='123',
        db='test',
    )
    cur = conn.cursor()

    # 以写的方式打开 csv 文件并将内容写入到w
    f = open("/var/python_code/output_CSV_file.csv", 'w')
    write_file = csv.writer(f)

    # 从 student 表里面读出数据,写入到 csv 文件里
    cur.execute("select * from student")
    while True:
        row = cur.fetchone()    #获取下一个查询结果集为一个对象
        if not row:
            break
        write_file.writerow(row)    #csv模块方法一行一行写入
    f.close()

    # 关闭连接
    if cur:
        cur.close()
    if conn:
        conn.close()


if __name__ == '__main__':
    main()

小蜗牛 说:
Freedom is the source from which all meaning and all values spring .


文章版权归 原文作者所有丨本站默认采用CC-BY-NC-SA 4.0协议进行授权| 转载必须包含本声明,并以超链接形式注明原文作者和本文原始地址: https://www.tougetu.com/2017/02/python-mysql-input-output-csv-file.html

5 条评论

  1. Benny

    我觉得不应该这样做,当你把这个巨大的csv文件读入数组存起来的时候,内存已经炸了。况且32位的Python很容易搞出来Memory Error的错误(网上说是2G可是我1G多点的时候就挂了)。
    实际测试的时候还发现一个问题,当我读入一个200MB不到的文件到数组的时候,竟然要了我接近3G的内存,不晓得为啥会这样…… 我觉得比较好的办法还是用readline读一个插一个吧……嗯...

    1. 小蜗牛

      你说的有道理,但是如果读一行插入一行在效率上可能就有一定的缺陷,不过也不失为一个解决办法

      1. 小土豆

        这chrome移动版性能好差劲,还是特效太多了?一个一个插效率低是低了点,总比吃光内存没准还死机强,还有一个策略就是读个比如说100行存数组里,一起插入,然后利用offset再读再插。哈哈,不灵活就是了!

    2. 小蜗牛

      难道用多线程 哈哈哈

  2. 夜未央

    我和楼上的看法一样,读这么大个东西到列表里的时候内存就炸了,而且非常容易炸,同理从数据库里fetch一个非常大的结果集也容易炸。个人觉得如果库不提供对应的方法,均衡考虑效率和可用性,那么就用readlines,fetchmany这类方法配合循环,比如说处理非常大的结果集可以这么干,处理非常大的文件也差不多一个思路
    while True:

        data = cur.fetchmany(100000)
        if data:
            do some batchjob
        else:
            break
    

    另外提一句,python的多线程简直鸡肋,别想着用啦。

添加新评论

代码 Pastebin Gist 加粗 删除线 斜体 链接 签到