1 import sqlite3 as sqlite
2 from xlwt import *
3 import sys
4
5 def sqlite_get_col_names(cur, select_sql):
6 cur.execute(select_sql)
7 return [tuple[0] for tuple in cur.description]
8
9 def query_by_sql(cur, select_sql):
10 cur.execute(select_sql)
11 return cur.fetchall()
12
13 def sqlite_to_workbook_with_head(cur, table, select_sql, workbook):
14 ws = workbook.add_sheet(table)
15 print('create table %s.' % table)
16 #enumerate针对一个可迭代对象，生成的是序号加上内容
17
18 for colx, heading in enumerate(sqlite_get_col_names(cur, select_sql)):
19 ws.write(0, colx, heading) #在第1行的colx列写上头部信息
20
21 for rowy, row in enumerate(query_by_sql(cur, select_sql)):
22 for colx, text in enumerate(row): #row是一行的内容
23 ws.write(rowy + 1, colx, text) #在rowy+1行，colx写入数据库内容text
24
25 def sqlite_to_workbook_without_head(cur, table, select_sql, workbook):
26 ws = workbook.add_sheet(table)
27
28 for rowy, row in enumerate(query_by_sql(cur, select_sql)):
29 for colx, text in enumerate(row): #row是一行的内容
30 ws.write(rowy, colx, text) #在rowy行，colx写入数据库内容text
31
32 def dump_db_to_excel(cur, workbook):
33 for tbl_name in [row[0] for row in query_by_sql(cur, "select tbl_name FROM sqlite_master where type = 'table'")]:
34 select_sql = "select * from '%s'" % tbl_name
35 sqlite_to_workbook_with_head(cur, tbl_name, select_sql, workbook)
36
37 def main(dbpath):
38 xlspath = dbpath[:dbpath.rfind('.')] + '.xls'
39 print("<%s> --> <%s>" % (dbpath, xlspath))
40
41 db = sqlite.connect(dbpath)
42 cur = db.cursor()
43 w = Workbook()
44
45 dump_db_to_excel(cur, w) #把所有的db中的表数据导出到excel中，每个table对应一个sheet页
46
47 #按照条件查询数据并导出到excel中
48 #sheet_name = '测试'
49 #query_data_sql = "select 100-id as used from cpu_info where cpu_name = '%Cpu0'"
50 #sqlite_to_workbook_without_head(cur, sheet_name, query_data_sql, w)
51
52 cur.close()
53 db.close()
54
55 w.save(xlspath)
56
57 if __name__ == "__main__":
58 # arg == database path
59 main(sys.argv[1])
