Detailed example of how to export sqlite into Excel (xls) table in Python

This article mainly introduces the Python method to export the sqlite database into an Excel (xls) table, and analyzes Python's connection and reading of the sqlite database in combination with examples. To obtain and use the write operation package (xlwt) to generate Excel tables, friends who need it can refer to the following

The example in this article describes the method of Python to export a SQLite database and convert it into an Excel (xls) table. Share it with everyone for your reference, the details are as follows:

1. Assume that you have installed the Python environment with the sliqte library

Mine is Python2.5

2. Download the python xls write operation package (xlwt) and install it

3. The following is the code (db2xls.py):

import sqlite3 as sqlite
from xlwt import *
#MASTER_COLS = ['rowid', 'type','name','tbl_name', 'rootpage','sql']
def sqlite_get_col_names(cur, table):
  query = 'select * from %s' % table
  cur.execute(query)
  return [tuple[0] for tuple in cur.description]
def sqlite_query(cur, table, col = '*', where = ''):
  if where != '':
    query = 'select %s from %s where %s' % (col, table, where)
  else:
    query = 'select %s from %s ' % (col, table)
  cur.execute(query)
  return cur.fetchall()
def sqlite_to_workbook(cur, table, workbook):
  ws = workbook.add_sheet(table)
  print 'create table %s.' % table
  for colx, heading in enumerate(sqlite_get_col_names(cur, table)):
      ws.write(0,colx, heading)
  for rowy,row in enumerate(sqlite_query(cur, table)):
    for colx, text in enumerate(row):
      ws.write(rowy+ 1, colx, text)
def main(dbpath):
  xlspath = dbpath[0:dbpath.rfind('.')] + '.xls'
  print "<%s> --> <%s>"% (dbpath, xlspath)
  db = sqlite.connect(dbpath)
  cur = db.cursor()
  w = Workbook()
  for tbl_name in [row[0] for row in sqlite_query(cur, &#39;sqlite_master&#39;, &#39;tbl_name&#39;, &#39;type = \&#39;table\&#39;&#39;)]:
    sqlite_to_workbook(cur,tbl_name, w)
  cur.close()
  db.close()
  if tbl_name !=[]: w.save(xlspath)
if name == "main":
  # arg == database path
  main(sys.argv[1])

4. Usage:

> python  <path>/db2xls.py  dbpath

If it is correct, it will be in the database Generate an xls file with the same name in the directory

The above is the detailed content of Detailed example of how to export sqlite into Excel (xls) table in Python. For more information, please follow other related articles on the PHP Chinese website!