python实现读取excel写入mysql的小工具详解

Python是数据分析的强大利器

利用Python做数据分析,第一步就是学习如何读取日常工作中产生各种excel报表并存入数据中,方便后续数据处理。
这里向大家分享python如何读取excel,并使用Python将数据存入Mysql中,有需要的朋友们一起来看看吧。

背景

需要导入全国高校名录到数据库,从教委网站下到了最新的数据,是excel格式,需要做一个工具进行导入,想试用一下python,说干就干。

  • xlrd : excel读写库
  • pymysql : mysql数据库驱动库,纯python打造
  • re : 正则表达式库,核心库

前两个用pip轻松完成安装,本人是在mac pro是进行的,过程很顺利,以前在mac上装mysqlclient一直安装不上,所以一度放弃使用python,但我在linux下安装mysqlclient却没有任何问题。

源代码

很简单的小脚本,留存纪念。值得注意的一点,数据库连接字段串中要设定字符编码,不然默认是lanti-1,写入会出错。

import xlrd
import pymysql
import re

conn = pymysql.connect(host='database connect address', port=1234, user='root',
      passwd='****', db='database name', charset='utf8mb4')
p = re.compile(r'\s')
data = xlrd.open_workbook('./W020170616379651135432.xls')
table = data.sheets()[0]
t = table.col_values(1)
nrows = table.nrows
for i in range(nrows):
 r1 = table.row_values(i)
 if len(r1[2]) == 10:
  cur = conn.cursor()
  cur.execute('insert into `university` (`id`, `name`, `ministry`, `city`, `level`, `memo`) \
     values (%s, %s, %s, %s, %s, %s)',
     (r1[2], p.sub('', r1[1]), p.sub('', r1[3]), p.sub('', r1[4]), r1[5], r1[6]))
  conn.commit()
  cur.close()

conn.close()

心得

写惯了类C的语言,不太习惯python,想同时掌握两种风格的编程语言,好痛苦啊。python编程效率的确不错,这是我第一次用python写实用小程序,连查带写带调试,一共也就花了一个来小时。python库与资料丰富,不愁找不到合适的^_^

数据库写入优化

早上闲来无事,用批量写入优化了一下,任务秒完成,比一条条写入快了很多, 比我预想的差别还要大。看来,没有不好的工具,只是我们没有用好啊!

import xlrd
import pymysql
import re

conn = pymysql.connect(host='database connect address', port=1234, user='root',
      passwd='****', db='database name', charset='utf8mb4')
p = re.compile(r'\s')
data = xlrd.open_workbook('./W020170616379651135432.xls')
table = data.sheets()[0]
t = table.col_values(1)
nrows = table.nrows
ops = []
for i in range(nrows):
 r1 = table.row_values(i)
 if len(r1[2]) == 10:
  ops.append((r1[2], p.sub('', r1[1]), p.sub('', r1[3]), p.sub('', r1[4]), r1[5], r1[6]))

cur = conn.cursor()
cur.executemany('insert into `university_copy` (`id`, `name`, `ministry`, `city`, `level`, `memo`) \
     values (%s, %s, %s, %s, %s, %s)', ops)
conn.commit()
cur.close()

conn.close()

python读取excel文件遇到的问题

1、mac安装xlrd模块,如果cmd下执行pip install xlrd安装不成功,可以直接去官网下载,名称类似这样的文件xlrd-1.0.0-py3-none-any.whl,切换到已下载的文件路径在cmd下执行pip3 install xlrd-1.0.0-py3-none-any.whl即可
http://pypi.python.org/pypi/xlrd

2、python打开excel报xlrd.biffh.XLRDError: Unsupported format, or corrupt file: Expected BOF record; found b'username'

可以确认下要打开的excel保存时是不是本身就存在兼容性等格式提示,如果有的话,需要重新建一个不存在格式问题的文件

import xlrd
from os.path import join,abspath,dirname

fname = join(dirname(dirname(abspath(__file__))),'test1.xls')
bk = xlrd.open_workbook(fname, encoding_override="utf-8")
shxrange = range(bk.nsheets)
try:
sh = bk.sheet_by_name("工作表1")
# 获取行数
nrows = sh.nrows
# 获取列数
ncols = sh.ncols
print("nrows %d, ncols %d" % (nrows, ncols))
# 获取第一行第一列数据
cell_value = sh.cell_value(1, 1)
# print cell_value

row_list = []
# 获取各行数据
for i in range(0, nrows):
row_data = sh.row_values(i)
row_list.append(row_data)
except:
print("no sheet in %s named Sheet1" % fname)

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对我们的支持。

(0)

相关推荐

  • 使用Python操作MySQL的一些基本方法

    前奏 为了能操作数据库, 首先我们要有一个数据库, 所以要首先安装Mysql, 然后创建一个测试数据库python_test用以后面的测试使用 CREATE DATABASE `python_test` CHARSET UTF8 导入数据库模块 import MySQLdb 连接数据库 con = MySQLdb.connect(host="localhost", user="root", passwd="******",db="pyt

  • Python读取Excel的方法实例分析

    本文实例讲述了Python读取Excel的方法.分享给大家供大家参考.具体如下: 今天需要从一个Excel文档(.xls)中导数据到数据库的某表,开始是手工一行行输的.后来想不能一直这样,就用Python写了下面的代码,可以很方便应对这种场景.比如利用我封装的这些方法可以很方便地生成导入数据的SQL. 当然熟悉Excel编程的同学还可以直接用VBA写个脚本生成插入数据的SQL. 还可以将.xls文件改为.csv文件,然后通过SQLyog或者Navicat等工具导入进来,但是不能细粒度控制(比如不

  • Python下的Mysql模块MySQLdb安装详解

    默认情况下,MySQLdb包是没有安装的,不信? 看到类似下面的代码你就信了. 复制代码 代码如下: -bash-3.2# /usr/local/python2.7.3/bin/python get_cnblogs_news.py Traceback (most recent call last):  File "get_cnblogs_news.py", line 9, in <module>    import MySQLdbImportError: No module

  • Python中使用第三方库xlrd来读取Excel示例

    本篇文章介绍如何使用xlrd来读取Excel表格中的内容,xlrd是第三方库,所以在使用前我们需要安装xlrd.另外我们一般会使用xlwt来写Excel,所以下一篇文章我们会来介绍如何使用xlwt来写Excel.xlrd下载:xlrd 0.8.0 安装xlrd 安装xlrd,只需运行setup即可,另外你也可以直接解压缩到你的project中,也可以直接用 xlrd的API 获取Excel,这里称之为work book 复制代码 代码如下: open_workbook(file_name) 获取

  • python文件读写并使用mysql批量插入示例分享(python操作mysql)

    复制代码 代码如下: # -*- coding: utf-8 -*-'''Created on 2013年12月9日 @author: hhdys''' import osimport mysql.connector config = {  'user': 'root',  'password': '******',  'host': '127.0.0.1',  'database': 'test',  'raise_on_warnings': True,}cnx = mysql.connect

  • Python使用xlrd读取Excel格式文件的方法

    本文实例讲述了Python使用xlrd读取Excel格式文件的方法.分享给大家供大家参考.具体如下: 使用xlrd能够很方便的读取excel文件内容,而且这是个跨平台的库,能够在windows,linux/unix,等平台上面使用,代码如下: import xlrd fname = "sample.xls" bk = xlrd.open_workbook(fname) shxrange = range(bk.nsheets) try: sh = bk.sheet_by_name(&qu

  • Python操作MySQL数据库9个实用实例

    在Windows平台上安装mysql模块用于Python开发 用python连接mysql的时候,需要用的安装版本,源码版本容易有错误提示.下边是打包了32与64版本. MySQL-python-1.2.3.win32-py2.7.exe MySQL-python-1.2.3.win-amd64-py2.7.exe 实例 1.取得 MYSQL 的版本 # -*- coding: UTF-8 -*- #安装 MYSQL DB for python import MySQLdb as mdb con

  • python3.4用循环往mysql5.7中写数据并输出的实现方法

    如下所示: #!/usr/bin/env python # -*- coding:utf-8 -*- # __author__ = "blzhu" """ python study Date:2017 """ import pymysql # import MySQLdb #python2中的产物 try: # 获取一个数据库连接,注意如果是UTF-8类型的,需要制定数据库 conn = pymysql.connect(hos

  • Python xlrd读取excel日期类型的2种方法

    有个excle表格需要做一些过滤然后写入数据库中,但是日期类型的cell取出来是个数字,于是查询了下解决的办法. 基本的代码结构 复制代码 代码如下: data = xlrd.open_workbook(EXCEL_PATH)  table = data.sheet_by_index(0)  lines = table.nrows  cols = table.ncols  print u'The total line is %s, cols is %s'%(lines, cols) 读取某个单元

  • Python导出数据到Excel可读取的CSV文件的方法

    本文实例讲述了Python导出数据到Excel可读取的CSV文件的方法.分享给大家供大家参考.具体实现方法如下: import csv with open('eggs.csv', 'wb') as csvfile: #spamwriter = csv.writer(csvfile, delimiter=' ',quotechar='|', #quoting=csv.QUOTE_MINIMAL) spamwriter = csv.writer(csvfile, dialect='excel') s

随机推荐