专栏首页Python乱炖使用python将csv文件快速转存到mysql

使用python将csv文件快速转存到mysql

因为一些工作需要,我们经常会做一些数据持久化的事情,例如将临时数据存到文件里,又或者是存到数据库里。

对于一个规范的表文件(例如csv),我们如何才能快速将数据存到mysql里面呢?

这个时候,我们可以使用python来快速编写脚本。

正文

对于一个正式的csv文件,我们将它打开,看到的数据是这样的:

这个数据很简单,只有三个列,现在我们要使用python将它快速转存到mysql。

既然使用python连接mysql,我们就少不了使用pymysql这个模块。

使用pip进行快速安装:

pip install pymysql

安装结束,我们使用pymysql连接数据库:

import pymysql
con = pymysql.connect(user="root",
                      passwd="root",
                      db="test",
                      host="47.95.xxx.xxx",
                      local_infile=1)

user是连接数据库的用户名,passwd是连接数据的密码,db是你想要连接数据库的名字,host是你要连接数据库的主机,如果就是自己的电脑,就填127.0.0.1。我们这边是将csv批量写到数据库,需要设置local_infile参数,如果不添加会报错。

连接完数据库我们便可以使用游标来执行sql语句了:

cur = con.cursor()

定义好了游标我们就可以使用execute方法来执行sql语句了。

cur.execute("set names utf8")
cur.execute("SET character_set_connection=utf8;")

下面我们来打开我们的csv文件,读取里面的内容,我们需要提取第一行列名的信息,然后创建表:

with open(file_path, 'r', encoding='utf8') as f:
    reader = f.readline()
    print(reader)
    devide = reader.split(',')  # 做成列表
    devide[-1] = devide[-1].rstrip('\n')   # 去除最后的换行符
    print(devide)

默认读出来的数据就是一行字符串,现在我们通过“,”提取我们的列名,并且去除我们最后一个列名的换行符,这样我们就能得到所有的列名了。

下面我们需要创建表,在创建表之前我们需要将每个列指定一下格式:

column = ''
for dd in devide:
    column = column + dd + ' varchar(255),'

拼接好后我们需要将最后一个列的逗号去掉

col = column.rstrip(',')

这样我们就可以写创建表的sql语句了:

table_name = "TBexport"
create_table_sql = 'create table if not exists {} ({}) DEFAULT CHARSET=utf8'\
    .format(table_name, col)
cur.execute(create_table_sql)

下面我们可以向表中插数据了:

首先要介绍一下,mysql支持csv数据的导入,以下是sql的语法:

LOAD DATA INFILE '文件名'

REPLACE INTO TABLE 表名

CHARACTER SET UTF8

FIELDS TERMINATED BY ';'

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

那这边我们根据上面这个语句去拼写我们需要插入数据的语句:

file_path = "export.csv"
data = 'LOAD DATA LOCAL INFILE \'' + file_path \
       + '\'REPLACE INTO TABLE ' \
       + table_name \
       + 'CHARACTER SET UTF8 FIELDS TERMINATED BY \',' \
         '\' ENCLOSED BY \'\"\' ' \
         'LINES TERMINATED BY \'\n\' IGNORE 1 LINES;'
cur.execute(data.encode('utf8'))

最后一步,提交事务。

(事务保证他们的连贯性,只要一步错就会进行回滚)

con.commit()

记得关闭游标和数据库连接。

cur.close()
con.close()

结果:

完整代码:

import pymysql

# file_path = "exam.csv"
# table_name = 'update_time_table'
file_path = "export.csv"
table_name = "TBexport"
try:
    con = pymysql.connect(user="root",
                          passwd="root",
                          db="test",
                          host="47.95.20x.xxx",
                          local_infile=1)
    con.set_charset('utf8')
    cur = con.cursor()
    cur.execute("set names utf8")
    cur.execute("SET character_set_connection=utf8;")

    with open(file_path, 'r', encoding='utf8') as f:
        reader = f.readline()
        print(reader)
        devide = reader.split(',')  # 做成列表
        devide[-1] = devide[-1].rstrip('\n')  # 去除最后的换行符
        print(devide)

    column = ''
    for dd in devide:
        #如果标题过长,只能存成text格式
        if dd == "标题":
            column = column + dd + ' TEXT,'
        else:
            column = column + dd + ' varchar(255),'
    col = column.rstrip(',')  # 去除最后一个多余的,
    # print(column[:-1])
    create_table_sql = 'create table if not exists {} ({}) DEFAULT CHARSET=utf8'.format(table_name, col)
    print(create_table_sql)
    data = 'LOAD DATA LOCAL INFILE \'' + file_path + '\'REPLACE INTO TABLE ' + table_name + ' CHARACTER SET UTF8 FIELDS TERMINATED BY \',\' ENCLOSED BY \'\"\' LINES TERMINATED BY \'\n\' IGNORE 1 LINES;'
    cur.execute(create_table_sql)
    cur.execute(data.encode('utf8'))
    print(cur.rowcount)
    con.commit()
except:
    print("发生错误")
    con.rollback()

finally:
    cur.close()
    con.close()

代码也可以去github:

https://github.com/johnturingwu/csv_to_mysql

点击阅读原文可直达

本文分享自微信公众号 - Python乱炖(Cooking_python),作者:我被狗咬了

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-07-30

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Python基础---类的内置方法

    __init__(): __init__方法在类的一个对象被建立时,马上运行。这个方法可以用来对你的对象做一些你希望的初始化。注意,这个名称的开始和结尾都是双下...

    我被狗咬了
  • JupyterNotebook‘s Magic

    上个星期在看代码的时候,一个意外让我了解到了jupyternotebook上的黑魔法。是这样子的:

    我被狗咬了
  • Python3中的特性-----Property介绍

    在python 中 属性 这个 实例方法, 类变量 都是属性. 属性, attribute

    我被狗咬了
  • python连接sql server并执

    python操作sql server,可以使用pymssql,成功安装pymssql后,按照如下的方法,可以连接数据库并执行查询操作:

    用户2398817
  • 史上最“脑残”的“抢火车票”程序(node.js版)

    用户1749219
  • 用腾讯云通信做聊天功能遇到的问题

    用户登录腾讯后台服务器后才能正常收发消息,登录需要用户提供 UserID、UserSig。

    honey缘木鱼
  • 本体:一文读懂领域本体构建

    定义:源于哲学的概念,知识工程学者借 用了这个概念,在开发知识系统时用于领域知识的获取。本体是用于描述一个领域的术语集合,其组织结构是层次结构化的,可以作为一个...

    伏草惟存
  • Python图形编程探索系列-05-用控

    程序有了变数才有了心动的地方,blue sky是我们向往的地方,但是雾霾Hazel挡住了我们的视野,今天又是阴天,但我有了改变。

    用户2398817
  • Java虚拟机--对象的建立你的对象如何创建?

    贾博岩
  • Python3 数据库增删改查简单操作

    conn=pymysql.connect(host='127.2.2.2',user='root',passwd='123456',db='records')

    用户2398817

扫码关注云+社区

领取腾讯云代金券

玩转腾讯云 有奖征文活动