Wrote a Python script that can dump a SQLite database to CSV, it takes the column names as the header and then writes values below. It also has a –sumarise option that can create a one line CSV file of multiple lines in a database.
Does have the option to support XML output in the future but a format would need to be defined by the user.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 |
#!/usr/bin/python import sqlite3 import csv from optparse import OptionParser class SQLiteDumper(): def __init__(self, database_path, statistic_type, output_file_name, output_format, summarise): self.databaseLocation = database_path self.statisticType = statistic_type self.outputFormat = output_format self.summarise = summarise self.outputFileName = output_file_name self.table = self.__typeToTable() self.__connectToDatabase() self.haveWrittenHeader = False self.notToSummarise = ['timestamp', 'component', 'some_string'] self.csvFile = None try: self.outputFile = open(self.outputFileName, "w+") if self.outputFormat == "CSV": self.csvFile = csv.writer(self.outputFile) except IOError as e: print e def __typeToTable(self): types = { "Views" : "views", "Errors" : "errors"} return types[self.statisticType] def __connectToDatabase(self): self.dbConnection = sqlite3.connect(self.databaseLocation, detect_types=sqlite3.PARSE_DECLTYPES) def writeToCSV(self, results): if not self.haveWrittenHeader: self.csvFile.writerow(results.keys()) self.haveWrittenHeader = True self.csvFile.writerow(results.values()) def writeToXML(self, results): # This requires a bit more clarification about the output format. raise NotImplementedError("Writing to XML support currently not available.") def __write(self, result): if self.outputFormat == "CSV": self.writeToCSV(result) elif self.outputFormat == "XML": self.writeToXML(result) def processDatabase(self): cursor = self.dbConnection.cursor() result = {} for row in cursor.execute('SELECT * FROM {0} ORDER BY timestamp ASC'.format(self.table)): for i, key in enumerate(cursor.description): if self.summarise: if result.has_key(key[0]) and key[0] not in self.notToSummarise: result[key[0]] += row[i] else: result[key[0]] = row[i] else: result[key[0]] = row[i] if not self.summarise: self.__write(result) if self.summarise: self.__write(result) def getOpts(): parser = OptionParser() parser.add_option("-o", "--outputFile", dest="output_file_name", action="store", default="output.csv", type="string", help="File name to store output information") parser.add_option("-t", "--statisticType", dest="statistic_type", action="store", default="Views", choices=["Views", "Errors"], help="Statistics type [Views, Errors]") parser.add_option("-d", "--database", dest="sqlite_db", action="store", type="string", help="Location of the SQLite database to read.") parser.add_option("-f", "--outputFormat", dest="output_format", action="store", default="CSV", choices=["XML", "CSV"], help="Output format [XML, CSV]") parser.add_option("-s", "--summarise", dest="summarise", action="store_true", default=False, help="Summarise all results into a single result.") (options, args) = parser.parse_args() if not options.sqlite_db: parser.error("--database argument is required.") return options if __name__ == "__main__": opts = getOpts() dumper = SQLiteDumper(opts.sqlite_db, opts.statistic_type, opts.output_file_name, opts.output_format, opts.summarise) try: dumper.processDatabase() except Exception as e: print e |
Questions? Comments?