changeset 6:22fbe50d92e8

can now export a table
author Jeff Hammel <k0scist@gmail.com>
date Sat, 01 Apr 2017 12:35:28 -0700
parents 3a7f515571dc
children 7d6acedf1a67
files README.txt sqlex/main.py sqlex/model.py
diffstat 3 files changed, 54 insertions(+), 1 deletions(-) [+]
line wrap: on
line diff
--- a/README.txt	Sat Apr 01 11:11:28 2017 -0700
+++ b/README.txt	Sat Apr 01 12:35:28 2017 -0700
@@ -1,8 +1,17 @@
 sqlex
-===========
+=====
 
 sql(ite) explorer/exporter
 
+
+Examples
+--------
+
+Listing all columns in all tables::
+
+  # sqlex --tables ~/docs/sms.db | while read line; do echo "# ${line}"; sqlex --columns ~/docs/sms.db ${line}; echo; done
+
+
 ----
 
 Jeff Hammel
--- a/sqlex/main.py	Sat Apr 01 11:11:28 2017 -0700
+++ b/sqlex/main.py	Sat Apr 01 12:35:28 2017 -0700
@@ -40,6 +40,8 @@
         self.add_argument('--columns', '--list-columns', dest='list_columns',
                           action='store_true', default=False,
                           help="list columns in `table` and exit")
+        self.add_argument('-o', '--output',
+                          help="output to directory (if `table` not given), or filename or stdout by default")
         self.options = None
 
     def parse_args(self, *args, **kw):
@@ -56,6 +58,11 @@
         except Exception as e:
             self.error("Could not open '{}': {}".format(options.db, e))
 
+        if not any((options.output,
+                    options.list_tables,
+                    options.table)):
+            self.error("`--output` directory must be specified to output entire database")
+
         if options.list_columns and not options.table:
             self.error("`--list-columns` requires `table`")
 
@@ -90,5 +97,18 @@
         print ('\n'.join(db.columns(options.table).keys()))
         return
 
+    if options.table:
+        # output table
+
+        if options.output:
+            with open(options.output, 'w') as f:
+                db.table2csv(options.table, f)
+        else:
+            db.table2csv(options.table, sys.stdout)
+            sys.stdout.flush()
+    else:
+        # output entire db to CSV files in directory
+        raise NotImplementedError('TODO')
+
 if __name__ == '__main__':
     main()
--- a/sqlex/model.py	Sat Apr 01 11:11:28 2017 -0700
+++ b/sqlex/model.py	Sat Apr 01 12:35:28 2017 -0700
@@ -1,3 +1,4 @@
+import csv
 import sqlite3
 from collections import OrderedDict
 
@@ -65,3 +66,26 @@
         return OrderedDict([(row[NAME_INDEX], row[TYPE_INDEX])
                             for row in data])
 
+
+    def table2csv(self, table, fp):
+        """
+        export `table` to `fp` file object in CSV format
+        """
+        # TODO: option to add column headers
+
+        # sanity
+        self.ensure_table(table)
+
+        # get whole table
+        sql = 'select * from {table}'.format(table=table)
+        rows = self(sql)
+
+        # decode unicde because the CSV module won't
+        # http://stackoverflow.com/questions/22733642/how-to-write-a-unicode-csv-in-python-2-7
+        rows = [[unicode(s).encode("utf-8") for s in row]
+                for row in rows]
+
+        # write
+        writer = csv.writer(fp)
+        writer.writerows(rows)
+