# HG changeset patch
# User catherine@dellzilla
# Date 1226092461 18000
# Node ID 2ab3a24d7974f6edc71c7098bde5e8d5b156c354
# Parent  b5114b8828b1bad6d70eaa569bc860d543f0666f
noSpecialPush

diff -r b5114b8828b1 -r 2ab3a24d7974 setup.py
--- a/setup.py	Fri Nov 07 06:43:06 2008 -0500
+++ b/setup.py	Fri Nov 07 16:14:21 2008 -0500
@@ -17,7 +17,7 @@
       url="https://twiki.cern.ch/twiki/bin/view/PSSGroup/SqlPython",
       packages=find_packages(),
       include_package_data=True,    
-      install_requires=['pyparsing','cmd2>=0.4','cx_Oracle','genshi>=0.5'],
+      install_requires=['pyparsing','cmd2>=0.4.3','cx_Oracle','genshi>=0.5'],
       keywords = 'client oracle database',
       license = 'MIT',
       platforms = ['any'],
diff -r b5114b8828b1 -r 2ab3a24d7974 sqlpython/exampleSession.txt
--- a/sqlpython/exampleSession.txt	Fri Nov 07 06:43:06 2008 -0500
+++ b/sqlpython/exampleSession.txt	Fri Nov 07 16:14:21 2008 -0500
@@ -107,7 +107,7 @@
 \p   pie chart
 testschema@orcl> select * from play where author='Shakespeare'\c
 
-"TITLE","AUTHOR"
+TITLE,AUTHOR
 "Twelfth Night","Shakespeare"
 "The Tempest","Shakespeare"
 
@@ -164,12 +164,14 @@
 
 2 rows selected.
 
-testschema@orcl> select * from play where author='Shakespeare'\i
+testschema@orcl> select * from play\i
+
 
-INSERT INTO play (TITLE,AUTHOR) VALUES ('Twelfth Night','Shakespeare');
-INSERT INTO play (TITLE,AUTHOR) VALUES ('The Tempest','Shakespeare');
+INSERT INTO play (TITLE, AUTHOR) VALUES ('Twelfth Night', 'Shakespeare');
+INSERT INTO play (TITLE, AUTHOR) VALUES ('The Tempest', 'Shakespeare');
+INSERT INTO play (TITLE, AUTHOR) VALUES ('Agamemnon', 'Aeschylus');
 
-2 rows selected.
+3 rows selected.
 
 testschema@orcl> select * from play where author='Shakespeare'\t
 
diff -r b5114b8828b1 -r 2ab3a24d7974 sqlpython/output_templates.py
--- a/sqlpython/output_templates.py	Fri Nov 07 06:43:06 2008 -0500
+++ b/sqlpython/output_templates.py	Fri Nov 07 16:14:21 2008 -0500
@@ -51,4 +51,17 @@
 {% for (colname, itm) in zip(colnames, row) %}${colname.ljust(colnamelen)}: $itm
 {% end %}{% end %}"""),
 
+'\\i': genshi.template.NewTextTemplate("""{% for (rowNum, row) in enumerate(rows) %}
+INSERT INTO $tblname (${', '.join(colnames)}) VALUES (${', '.join(f % r for (r,f) in zip(row, formatters))});{% end %}"""),
+
+'\\c': genshi.template.NewTextTemplate("""
+${','.join(colnames)}{% for row in rows %}
+${','.join('"%s"' % val for val in row)}{% end %}"""),
+
+'\\C': genshi.template.NewTextTemplate("""
+{% for row in rows %}${','.join('"%s"' % val for val in row)}{% end %}""")
+
 }
+
+output_templates['\\s'] = output_templates['\\c']
+output_templates['\\S'] = output_templates['\\C']
diff -r b5114b8828b1 -r 2ab3a24d7974 sqlpython/sqlpyPlus.py
--- a/sqlpython/sqlpyPlus.py	Fri Nov 07 06:43:06 2008 -0500
+++ b/sqlpython/sqlpyPlus.py	Fri Nov 07 16:14:21 2008 -0500
@@ -359,6 +359,7 @@
                                           '/*': '_multiline_comment'})
     multilineCommands = '''select insert update delete tselect
                       create drop alter _multiline_comment'''.split()
+    sqlpython.sqlpython.noSpecialParse.append('spool')
     defaultFileName = 'afiedt.buf'
     def __init__(self):
         sqlpython.sqlpython.__init__(self)
@@ -415,35 +416,20 @@
         if needsquotes:
             return "'%s'" % str(itm)
         return str(itm)
-    def str_or_empty(self, itm):
-        if itm is None:
-            return ''
-        return str(itm)
-    def output_as_insert_statements(self):
-        usequotes = [d[1] != cx_Oracle.NUMBER for d in self.curs.description]
-        def formatRow(row):
-            return ','.join(self.sql_format_itm(itm, useq)
-                            for (itm, useq) in zip(row, usequotes))
-        result = ['INSERT INTO %s (%s) VALUES (%s);' %
-                  (self.tblname, ','.join(self.colnames), formatRow(row))
-                  for row in self.rows]
-        return '\n'.join(result)
     tableNameFinder = re.compile(r'from\s+([\w$#_"]+)', re.IGNORECASE | re.MULTILINE | re.DOTALL)          
+    inputStatementFormatters = {
+        cx_Oracle.STRING: "'%s'",
+        cx_Oracle.DATETIME: "TO_DATE('%s', 'YYYY-MM-DD HH24:MI:SS')"}
+    inputStatementFormatters[cx_Oracle.CLOB] = inputStatementFormatters[cx_Oracle.STRING]
+    inputStatementFormatters[cx_Oracle.TIMESTAMP] = inputStatementFormatters[cx_Oracle.DATETIME]    
     def output(self, outformat, rowlimit):
         self.tblname = self.tableNameFinder.search(self.curs.statement).group(1)
         self.colnames = [d[0] for d in self.curs.description]
-        if outformat == '\\i':
-            result = self.output_as_insert_statements()
-        elif outformat in output_templates:
+        if outformat in output_templates:
             self.colnamelen = max(len(colname) for colname in self.colnames)
-            result = output_templates[outformat].generate(**self.__dict__)
-        elif outformat in ('\\s', '\\S', '\\c', '\\C'): #csv
-            result = []
-            if outformat in ('\\s', '\\c'):
-                result.append(','.join('"%s"' % colname for colname in self.colnames))
-            for row in self.rows:
-                result.append(','.join('"%s"' % self.str_or_empty(itm) for itm in row))
-            result = '\n'.join(result)
+            self.coltypes = [d[1] for d in self.curs.description]
+            self.formatters = [self.inputStatementFormatters.get(typ, '%s') for typ in self.coltypes]    
+            result = output_templates[outformat].generate(**self.__dict__)        
         elif outformat == '\\t': # transposed
             rows = [self.colnames]
             rows.extend(list(self.rows))