changeset 5:3a7f515571dc

can list column names
author Jeff Hammel <k0scist@gmail.com>
date Sat, 01 Apr 2017 11:11:28 -0700
parents b440206930ac
children 22fbe50d92e8
files sqlex/main.py sqlex/model.py
diffstat 2 files changed, 50 insertions(+), 0 deletions(-) [+]
line wrap: on
line diff
--- a/sqlex/main.py	Sat Apr 01 09:28:49 2017 -0700
+++ b/sqlex/main.py	Sat Apr 01 11:11:28 2017 -0700
@@ -7,6 +7,7 @@
 
 # imports
 import argparse
+import csv
 import os
 import sys
 from .model import SQLEx
@@ -36,6 +37,9 @@
         self.add_argument('--tables', '--list-tables', dest='list_tables',
                           action='store_true', default=False,
                           help="list tables and exit")
+        self.add_argument('--columns', '--list-columns', dest='list_columns',
+                          action='store_true', default=False,
+                          help="list columns in `table` and exit")
         self.options = None
 
     def parse_args(self, *args, **kw):
@@ -52,6 +56,8 @@
         except Exception as e:
             self.error("Could not open '{}': {}".format(options.db, e))
 
+        if options.list_columns and not options.table:
+            self.error("`--list-columns` requires `table`")
 
 def main(args=sys.argv[1:]):
     """CLI"""
@@ -74,5 +80,15 @@
             retval = int(options.table not in tables)
         return retval
 
+    if options.table:
+        # ensure selected table exists
+        if options.table not in db.tables():
+            parser.error("No table '{}' in {} tables:\n{}".format(options.table, options.db, ', '.join(db.tables())))
+
+    if options.list_columns:
+        # list columns and return
+        print ('\n'.join(db.columns(options.table).keys()))
+        return
+
 if __name__ == '__main__':
     main()
--- a/sqlex/model.py	Sat Apr 01 09:28:49 2017 -0700
+++ b/sqlex/model.py	Sat Apr 01 11:11:28 2017 -0700
@@ -1,4 +1,10 @@
 import sqlite3
+from collections import OrderedDict
+
+class NoTableException(Exception):
+    def __init__(self, table):
+        Exception.__init__(self, "Table '{}' does not exist".format(table))
+
 
 class SQLEx(object):
     """
@@ -31,3 +37,31 @@
         sql = "SELECT name FROM sqlite_master WHERE type='table';"
         tables = self(sql)
         return sum([list(item) for item in tables], [])
+
+    def ensure_table(self, table):
+        """
+        ensure that `table` exists;
+        if not, raise 1NoTableException
+        """
+
+        if table not in self.tables():
+            raise NoTableException(table)
+
+    def columns(self, table):
+        """
+        returns columns for `table`
+        """
+
+        self.ensure_table(table)
+
+        sql = "PRAGMA table_info({})".format(table)
+        data = self(sql)
+        # (Pdb) pp(columns)
+        # [(0, u'ROWID', u'INTEGER', 0, None, 1),
+        #  (1, u'address', u'TEXT', 0, None, 0),
+        #  (2, u'date', u'INTEGER', 0, None, 0),
+        NAME_INDEX = 1
+        TYPE_INDEX = 2
+        return OrderedDict([(row[NAME_INDEX], row[TYPE_INDEX])
+                            for row in data])
+