平民版excel转sqlite using python

# coding:utf-8 from xlrd import open_workbook import sqlite3 import os def config_table_data(sheet): table_name = sheet.name # 判断有效sheet if sheet.nrows > 0 and sheet.ncols > 0: availible_client_col_idx = [] availible_server_col_idx = [] # 有效列筛选 for col in range(0, sheet.ncols): export_type = int(sheet.cell(3, col).value) if 1 == export_type or 3 == export_type: availible_client_col_idx.append(col) if 2 == export_type or 3 == export_type: availible_server_col_idx.append(col) # 数据组装 client_data = [] # 索引一存放数据类型 client_col_and_types = [] for i in range(0, len(availible_client_col_idx)): data = [] data.append(sheet.cell(0, availible_client_col_idx[i]).value) dataType = sheet.cell(1, availible_client_col_idx[i]).value if "int" == dataType: data.append("integer") elif "string" == dataType: data.append("text") client_col_and_types.append(data) # client_data.append(col_and_types) for row in range(4, sheet.nrows): data = [] for i in range(0, len(availible_client_col_idx)): data.append(sheet.cell(row, availible_client_col_idx[i]).value) client_data.append(data) server_data = [] server_col_and_types = [] for i in range(0, len(availible_server_col_idx)): data = [] data.append(sheet.cell(0, availible_server_col_idx[i]).value) dataType = sheet.cell(1, availible_server_col_idx[i]).value if "int" == dataType: data.append("integer") elif "string" == dataType: data.append("text") server_col_and_types.append(data) # server_data.append(col_and_types) for row in range(4, sheet.nrows): data = [] for i in range(0, len(availible_server_col_idx)): data.append(sheet.cell(row, availible_server_col_idx[i]).value) server_data.append(data) create_table("blacksun.db", table_name, client_col_and_types, client_data) create_table("blacksun_s.db", table_name, server_col_and_types, server_data) def create_table(db_name, tbl_name, col_data, row_data): # 打开数据库（不存在时会创建数据库） con = sqlite3.connect(db_name) cur = con.cursor() # 无需要转化的列时跳过该表 if len(col_data) < 1: return # 建表sql组装 print " creating table : %s" % tbl_name tbl_create_sql = "create table if not exists %s (" % tbl_name col = "" for i in range(0, len(col_data)): col = col + "%s %s" % (col_data[i][0], col_data[i][1]) if i == 0: col = col + " primary key" col = col + ", " # 删除最后一个逗号 完成括号 col = col[0:-2] col = col + ")" tbl_create_sql = tbl_create_sql + col col = "" try: cur.execute(tbl_create_sql) for i in range(0, len(row_data)): # 插入数据不要使用拼接字符串的方式，容易收到sql注入攻击 print " inserting data to %s" % tbl_name insert_sql = "insert into %s(" % tbl_name col = "" for j in range(0, len(col_data)): col = col + "%s, " % col_data[j][0] col = col + ", " # 删除最后一个逗号 完成括号 col = col[0:-4] col = col + ") values (?" for k in range(1, len(col_data)): col = col + ", ?" col = col + ")" insert_sql = insert_sql + col print insert_sql print i print row_data[i] # print len(row_data[i]) cur.execute(insert_sql, row_data[i]) con.commit() except sqlite3.Error as e: print "An error occurred: %s", e.args[0] os.system("pause") finally: cur.close() con.close() def recreate_db(db_name): # 删除当前数据库 if os.path.exists(db_name): os.remove(db_name) # 打开数据库（不存在时会创建数据库） con = sqlite3.connect(db_name) con.close() xls_file = "config_file.xlsx" book = open_workbook(xls_file) recreate_db("blacksun.db") recreate_db("blacksun_s.db") for sheet in book.sheets(): config_table_data(sheet) print "------ Done ------" os.system("pause")