python實現(xiàn)讀取excel寫入mysql的小工具詳解

更新時間：2017年11月20日 09:53:15 作者：zhoutk

EXCEL 和 MySQL 大體上來說都可以算是"數(shù)據(jù)庫"，MySQL貌似有EXCEL的接口，但是最近在自學Python，用Python實現(xiàn)了一下，下面這篇文章主要給大家介紹了關(guān)于利用python實現(xiàn)讀取excel寫入mysql的一個小工具，需要的朋友可以參考下。

Python是數(shù)據(jù)分析的強大利器

利用Python做數(shù)據(jù)分析，第一步就是學習如何讀取日常工作中產(chǎn)生各種excel報表并存入數(shù)據(jù)中，方便后續(xù)數(shù)據(jù)處理。
這里向大家分享python如何讀取excel，并使用Python將數(shù)據(jù)存入Mysql中，有需要的朋友們一起來看看吧。

背景

需要導(dǎo)入全國高校名錄到數(shù)據(jù)庫，從教委網(wǎng)站下到了最新的數(shù)據(jù)，是excel格式，需要做一個工具進行導(dǎo)入，想試用一下python，說干就干。

庫

xlrd : excel讀寫庫
pymysql : mysql數(shù)據(jù)庫驅(qū)動庫，純python打造
re : 正則表達式庫，核心庫

前兩個用pip輕松完成安裝，本人是在mac pro是進行的，過程很順利，以前在mac上裝mysqlclient一直安裝不上，所以一度放棄使用python，但我在linux下安裝mysqlclient卻沒有任何問題。

源代碼

很簡單的小腳本，留存紀念。值得注意的一點，數(shù)據(jù)庫連接字段串中要設(shè)定字符編碼，不然默認是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寫實用小程序，連查帶寫帶調(diào)試，一共也就花了一個來小時。python庫與資料豐富，不愁找不到合適的^_^

數(shù)據(jù)庫寫入優(yōu)化

早上閑來無事，用批量寫入優(yōu)化了一下，任務(wù)秒完成，比一條條寫入快了很多，比我預(yù)想的差別還要大?？磥恚瑳]有不好的工具，只是我們沒有用好啊！

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下執(zhí)行pip install xlrd安裝不成功，可以直接去官網(wǎng)下載，名稱類似這樣的文件xlrd-1.0.0-py3-none-any.whl，切換到已下載的文件路徑在cmd下執(zhí)行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")
# 獲取行數(shù)
nrows = sh.nrows
# 獲取列數(shù)
ncols = sh.ncols
print("nrows %d, ncols %d" % (nrows, ncols))
# 獲取第一行第一列數(shù)據(jù)
cell_value = sh.cell_value(1, 1)
# print cell_value

row_list = []
# 獲取各行數(shù)據(jù)
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)

總結(jié)

以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學習或者工作具有一定的參考學習價值，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持。

您可能感興趣的文章: