sqlitepython导入数据_Python导入excel数据到sqlite;

#coding=utf-8

importxlrdimportsqlite3importosimportuuiddefinsert_data_to_db(path):

wb=xlrd.open_workbook(path)print(wb.sheet_names())

sheet=wb.sheets()[0]

nrows=sheet.nrows#获取任务行里索引

begin_index =0

end_index=begin_indexfor i inrange(nrows):

col_value= sheet.row(i)[1].valueif(begin_index == 0 and col_value == "责任人"):

begin_index= i + 1end_index=begin_indexif(begin_index != 0 and col_value == ""):

end_index=ibreak

print(begin_index)print(end_index)#检查创建数据库

db_path = "zb.db"is_db_exist=os.path.exists(db_path)

conn=sqlite3.connect(db_path)

cursor=conn.cursor()if notis_db_exist:

conn.execute('''CREATE TABLE IF NOT EXISTS Tasks(

Id varchar PRIMARY KEY,

Name varchar,

Task varchar,

PercentageTask varchar,

BeginTime datetime,

EndTime datetime,

Memo varchar,

FileName varchar)''')

conn.commit()#获取文件名

(_file_path, file_name) =os.path.split(path)

sql= '''INSERT OR REPLACE INTO Tasks

(Id,Name,Task,PercentageTask,BeginTime,EndTime,Memo,FileName) VALUES (?,?,?,?,?,?,?,?)'''

#遍历内容插入数据库

for i inrange(begin_index, end_index):

col_id=str(uuid.uuid1())

col_name= sheet.row(i)[1].value

col_task= sheet.row(i)[2].value

col_percentage_task= sheet.row(i)[3].value

col_begin_time= sheet.row(i)[4].value

col_end_time= sheet.row(i)[5].value

col_memo= sheet.row(i)[6].value

cursor.execute(sql, (col_id, col_name, col_task, col_percentage_task,

col_begin_time, col_end_time, col_memo, file_name))

conn.commit()defmain():

file_dir= "zb"

for _root, _dirs, files inos.walk(file_dir):for file infiles:

insert_data_to_db(file_dir+"/"+file)

main()