欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

利用python将excel数据导入mySQL

程序员文章站 2022-06-27 20:25:50
主要用到的库有 xlrd 和 pymysql , 注意pymysql不支持python3 篇幅有限,只针对主要操作进行说明 连接数据库 首先pymysql需要连接数据库,我这里连接的是本地数据库(数据库叫lds714610)。 conn = connect( host='localhost', po ......

主要用到的库有 xlrd 和 pymysql , 注意pymysql不支持python3

篇幅有限,只针对主要操作进行说明

连接数据库

利用python将excel数据导入mySQL

首先pymysql需要连接数据库,我这里连接的是本地数据库(数据库叫lds714610)。

conn = connect( host='localhost', port=3306, database='lds714610',
  user='root', charset='utf8')

# 主要通过cursor来对数据库进行查询,插入等一系列操作
cursor = conn.cursor()

#在操作完所有操作后,提交修改,退出数据库
conn.commit()
conn.close()

从excel中读取数据

导入数据之前,需要先读取excel中的数据。

通过xlrd库读取到excel表的数据,返回的数据类型很单一,几乎都是字符类型。所以excel表格中的空也是用""表示的,而不是null或none。

# excel文件
# 第一步打开excel文件,类似普通的文件open操作。注意open_workbook的参数必须是unicode编码
book = open_workbook(filename[i].decode('utf-8'))

# 表格
# 一个excel文件中可能有多个表,可以通过sheets()方法返回关于所有表格的list列表
sheet = book.sheets()[0]	#通过下标可以获取某一个表格

# 行 (元组)
# 下面获取一个表格内特定行的特定列的值
sheet.cell_value(i,j)	#表格获取i行j列的值,一般会使用strip()去掉空格

# sheet有很多关于表格的属性
tolrows = sheet.nrows	#表格的总行数

向pymysql中写入数据

之后说过对于pymysql通过cursor对数据库进行读写。

对于库pymysql,程序从中读取到的数据类型可能是string、float、datetime等。同时也是使用none表示数据库中的空值,pymysql中 insert into的value如果是空字串"",不会对应mysql中的none,而是同样表示一个空字串。

# 查询
cursor.execute("select tid from train where tname = %s", tname)

# 获取查询结果,fetchall的结果是一个元组,它的每个元素也是元组,并且元素对应select的一个查询结果
# 如rows 可能是 ( (123,), (124,), (125,) ) 这种形式
rows = cursor.fetchall()
  for r in rows:
    print(r[0])		#输出tid
# fetchall(self):接收全部的返回结果行.
# fetchone(self):返回一条结果行.
# fetchmany(self, size=none):接收size条返回结果行
    
# 插入多行,rows可能包含多组[tid], 但通过executemany可以用一条语句插入全部
cursor.executemany(“ insert into train(tid) values(%s) ”, rows)

处理时间数据

使用的库是 datetime 和 xlrd

首先处理excel中的时间类型数据。在使用xlrd读取excel表格中的日期格式时,读出的内容和原来excel表格中的内容不一致。读取出来的是一个浮点数。导致不能正确使用。

而xldate_as_tuple方法可以很好地解决这个问题。获取excel中的时间数据使用xlrd的xldate_as_tuple方法。

from xlrd import xldate_as_tuple

# 参数一:要处理的单元格值
# 参数二:时间基准(0代表以1900-01-01为基准,1代表以1904-01-01为基准)
# return (year, month, day, hour, minute, nearest_second)
stime = xldate_as_tuple( sheet.cell_value(i,3), 0 )

datetime的数据格式

import datetime

#字符串转datetime
nowdatetime = datetime.datetime.strptime('2020-4-30', "%y-%m-%d")

# 直接指定年月日时分秒
nowdatetime = datetime.datetime(2020, 4, 30, 0, 0, 0)

#datetime转字符串
nowdatetime.strftime("%y-%m-%d %h:%m:%s")

# 获取当前时间
today = datetime.datetime.now()

%y-%m-%d %h:%m:%s是datetime类型数据的一般格式,可以使用 nowdatetime.date() 方法去掉时分秒,但是得到的是 datetime.date 类型的数据,格式为 %y-%m-%d 。

因此如果要将datetime中的时分秒清0,方法是先将其转为“%y-%m-%d”格式的字符串,再将字符串转为datetime类型数据。

时间类型的计算

datetime类型可以很方便的进行时间上的运行,如计算1天后、10天前、5分钟后或1小时前的年月日时分秒。

# 计算5分钟后的时间
after5min = nowdatetime + timedelta( minutes = 5 )
# 以此类推timedelta的形参还有days, hours等等

注意 nowdatetime.date() + timedelta( minutes = 5 ) 的结果里,时分秒一直是0,因为它是date类型。