python读取xlsx文件内容，插入到sqlite3数据库里面
import xlrdimport re
import sqlite3
def read_xlsx():
workbook = xlrd.open_workbook('E:\20160322.xlsx')
booksheet = workbook.sheet_by_name('Sheet1')
p = list()
for row in range(booksheet.nrows):
row_data = []
for col in range(booksheet.ncols):
cel = booksheet.cell(row, col)
val = cel.value
try:
val = cel.value
val = re.sub(r'\s+', '', val)
except:
pass
if type(val) == float:
val = int(val)
else:
val = str( val )
row_data.append(val)
p.append(row_data)
returnp
def operat_sqlite(*data):
# print(type(data))
# print(data)
print(data)
try:
conn = sqlite3.connect('E:\list.db')
except:
print('open sqlite3 failed.')
return
else:#操作数据库
c = conn.cursor()
for item in data:
for i in range(len(item)):
DLDMv = item
LDDMv = item
LDMCv = item
FHSSLXv = item
XZQHv = item
try:
#creat sql
c.execute("insert into roadkey (DLDM, LDDM, LDMC, FHSSLX, XZQH) values (?, ?, ?, ?, ?)", (DLDMv, LDDMv, LDMCv, FHSSLXv, XZQHv))
conn.commit()
except:
print('insert roadky failed ')
pass
print(i)
print(item)
conn.close()
return
if __name__ == '__main__':
data_list =list()
data_list = read_xlsx()
operat_sqlite(data_list)1. 代码功能：读取xlsx文件内容，插入到sqlite3数据库里面。2. 安装xlrd： pip install xlrd。 sqlite3已经有库了。。不用安装，直接使用。
页:
[1]