從excel中導出數據插入到sqlite3數據庫python
#!/usr/bin/python
#coding=utf-8
from openpyxl.reader.excel import load_workbook
import sqlite3
conn = sqlite3.connect("XXXX.db")
cursor = conn.cursor()
wb = load_workbook(filename = r'shool.xlsx')
print "Worksheet range(s):", wb.get_named_ranges()
print "Worksheet name(s):", wb.get_sheet_names()
sheetnames = wb.get_sheet_names()
ws = wb.get_sheet_by_name(sheetnames[1])
row = ws.get_highest_row()
column = ws.get_highest_column()
print "Work Sheet Titile:",ws.title
print "Work Sheet Rows:",row
print "Work Sheet Cols:",column
values = []
for i in range(1,2868):
for j in range(0,column):
values.append(ws.cell(row = i,column = j).value)
print values[0],values[1]
sql = "insert into schools values ('%d','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')"
cursor.execute(sql % (i,values[0],values[1],values[2],values[3],values[4],values[5],values[6],values[7], values[8],values[9]))
del values[:]
conn.commit()
conn.close()
print "Done"