从excel中导出数据插入到sqlite3数据库

从excel中导出数据插入到sqlite3数据库

#!/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"

转载于:https://my.oschina.net/u/240562/blog/71959