Commits

Mike Bayer committed 4087e6f Merge

- [feature] the SQLite date and time types
have been overhauled to support a more open
ended format for input and output, using
name based format strings and regexps. A
new argument "microseconds" also provides
the option to omit the "microseconds"
portion of timestamps. Thanks to
Nathan Wright for the work and tests on
this. [ticket:2363]

  • Participants
  • Parent commits 469db2d, 4ea04df

Comments (0)

Files changed (4)

     propagating the exception onward normally.
     also in 0.7.7.
 
+- sqlite
+  - [feature] the SQLite date and time types
+    have been overhauled to support a more open
+    ended format for input and output, using 
+    name based format strings and regexps.  A
+    new argument "microseconds" also provides
+    the option to omit the "microseconds" 
+    portion of timestamps.  Thanks to 
+    Nathan Wright for the work and tests on
+    this.  [ticket:2363]
+
 - mssql
   - [bug] removed legacy behavior whereby 
     a column comparison to a scalar SELECT via 

lib/sqlalchemy/dialects/sqlite/base.py

     
     The default string storage format is::
     
-        "%04d-%02d-%02d %02d:%02d:%02d.%06d" % (value.year, 
-                                value.month, value.day,
-                                value.hour, value.minute, 
-                                value.second, value.microsecond)
+        "%(year)04d-%(month)02d-%(day)02d %(hour)02d:%(min)02d:%(second)02d.%(microsecond)06d"
     
     e.g.::
     
         from sqlalchemy.dialects.sqlite import DATETIME
         
         dt = DATETIME(
-                storage_format="%04d/%02d/%02d %02d-%02d-%02d-%06d",
-                regexp=re.compile("(\d+)/(\d+)/(\d+) (\d+)-(\d+)-(\d+)(?:-(\d+))?")
+                storage_format="%(year)04d/%(month)02d/%(day)02d %(hour)02d:%(min)02d:%(second)02d",
+                regexp=re.compile("(\d+)/(\d+)/(\d+) (\d+)-(\d+)-(\d+)")
             )
     
     :param storage_format: format string which will be applied to the 
-     tuple ``(value.year, value.month, value.day, value.hour,
-     value.minute, value.second, value.microsecond)``, given a
-     Python datetime.datetime() object.
+     dict with keys year, month, day, hour, minute, second, and microsecond.
     
     :param regexp: regular expression which will be applied to 
-     incoming result rows. The resulting match object is applied to
-     the Python datetime() constructor via ``*map(int,
-     match_obj.groups(0))``.
+     incoming result rows. If the regexp contains named groups, the
+     resulting match dict is applied to the Python datetime() constructor
+     as keyword arguments. Otherwise, if positional groups are used, the
+     the datetime() constructor is called with positional arguments via
+     ``*map(int, match_obj.groups(0))``.
     """
 
-    _storage_format = "%04d-%02d-%02d %02d:%02d:%02d.%06d"
+    _storage_format = (
+        "%(year)04d-%(month)02d-%(day)02d "
+        "%(hour)02d:%(minute)02d:%(second)02d.%(microsecond)06d"
+    )
+
+    def __init__(self, *args, **kwargs):
+        truncate_microseconds = kwargs.pop('truncate_microseconds', False)
+        super(DATETIME, self).__init__(*args, **kwargs)
+        if truncate_microseconds:
+            assert 'storage_format' not in kwargs, "You can specify only "\
+                "one of truncate_microseconds or storage_format."
+            assert 'regexp' not in kwargs, "You can specify only one of "\
+                "truncate_microseconds or regexp."
+            self._storage_format = (
+                "%(year)04d-%(month)02d-%(day)02d "
+                "%(hour)02d:%(minute)02d:%(second)02d"
+            )
 
     def bind_processor(self, dialect):
         datetime_datetime = datetime.datetime
             if value is None:
                 return None
             elif isinstance(value, datetime_datetime):
-                return format % (value.year, value.month, value.day,
-                                 value.hour, value.minute, value.second,
-                                 value.microsecond)
+                return format % {
+                    'year': value.year,
+                    'month': value.month,
+                    'day': value.day,
+                    'hour': value.hour,
+                    'minute': value.minute,
+                    'second': value.second,
+                    'microsecond': value.microsecond,
+                }
             elif isinstance(value, datetime_date):
-                return format % (value.year, value.month, value.day,
-                                 0, 0, 0, 0)
+                return format % {
+                    'year': value.year,
+                    'month': value.month,
+                    'day': value.day,
+                    'hour': 0,
+                    'minute': 0,
+                    'second': 0,
+                    'microsecond': 0,
+                }
             else:
                 raise TypeError("SQLite DateTime type only accepts Python "
                                 "datetime and date objects as input.")
 
     The default string storage format is::
     
-        "%04d-%02d-%02d" % (value.year, value.month, value.day)
+        "%(year)04d-%(month)02d-%(day)02d"
     
     e.g.::
     
         from sqlalchemy.dialects.sqlite import DATE
 
         d = DATE(
-                storage_format="%02d/%02d/%02d",
-                regexp=re.compile("(\d+)/(\d+)/(\d+)")
+                storage_format="%(month)02d/%(day)02d/%(year)04d",
+                regexp=re.compile("(?P<month>\d+)/(?P<day>\d+)/(?P<year>\d+)")
             )
     
     :param storage_format: format string which will be applied to the 
-     tuple ``(value.year, value.month, value.day)``,
-     given a Python datetime.date() object.
+     dict with keys year, month, and day.
     
     :param regexp: regular expression which will be applied to 
-     incoming result rows. The resulting match object is applied to
-     the Python date() constructor via ``*map(int,
-     match_obj.groups(0))``.
-     
+     incoming result rows. If the regexp contains named groups, the
+     resulting match dict is applied to the Python date() constructor
+     as keyword arguments. Otherwise, if positional groups are used, the
+     the date() constructor is called with positional arguments via
+     ``*map(int, match_obj.groups(0))``.
     """
 
-    _storage_format = "%04d-%02d-%02d"
+    _storage_format = "%(year)04d-%(month)02d-%(day)02d"
 
     def bind_processor(self, dialect):
         datetime_date = datetime.date
             if value is None:
                 return None
             elif isinstance(value, datetime_date):
-                return format % (value.year, value.month, value.day)
+                return format % {
+                    'year': value.year,
+                    'month': value.month,
+                    'day': value.day,
+                }
             else:
                 raise TypeError("SQLite Date type only accepts Python "
                                 "date objects as input.")
     
     The default string storage format is::
     
-        "%02d:%02d:%02d.%06d" % (value.hour, value.minute, 
-                                value.second,
-                                 value.microsecond)
+        "%(hour)02d:%(minute)02d:%(second)02d.%(microsecond)06d"
     
     e.g.::
     
         from sqlalchemy.dialects.sqlite import TIME
 
         t = TIME(
-                storage_format="%02d-%02d-%02d-%06d",
+                storage_format="%(hour)02d-%(minute)02d-%(second)02d-%(microsecond)06d",
                 regexp=re.compile("(\d+)-(\d+)-(\d+)-(?:-(\d+))?")
             )
     
-    :param storage_format: format string which will be applied 
-     to the tuple ``(value.hour, value.minute, value.second,
-     value.microsecond)``, given a Python datetime.time() object.
+    :param storage_format: format string which will be applied to the 
+     dict with keys hour, minute, second, and microsecond.
     
     :param regexp: regular expression which will be applied to 
-     incoming result rows. The resulting match object is applied to
-     the Python time() constructor via ``*map(int,
-     match_obj.groups(0))``.
-
+     incoming result rows. If the regexp contains named groups, the
+     resulting match dict is applied to the Python time() constructor
+     as keyword arguments. Otherwise, if positional groups are used, the
+     the time() constructor is called with positional arguments via
+     ``*map(int, match_obj.groups(0))``.
     """
 
-    _storage_format = "%02d:%02d:%02d.%06d"
+    _storage_format = "%(hour)02d:%(minute)02d:%(second)02d.%(microsecond)06d"
+
+    def __init__(self, *args, **kwargs):
+        truncate_microseconds = kwargs.pop('truncate_microseconds', False)
+        super(TIME, self).__init__(*args, **kwargs)
+        if truncate_microseconds:
+            assert 'storage_format' not in kwargs, "You can specify only "\
+                "one of truncate_microseconds or storage_format."
+            assert 'regexp' not in kwargs, "You can specify only one of "\
+                "truncate_microseconds or regexp."
+            self._storage_format = "%(hour)02d:%(minute)02d:%(second)02d"
 
     def bind_processor(self, dialect):
         datetime_time = datetime.time
             if value is None:
                 return None
             elif isinstance(value, datetime_time):
-                return format % (value.hour, value.minute, value.second,
-                                 value.microsecond)
+                return format % {
+                    'hour': value.hour,
+                    'minute': value.minute,
+                    'second': value.second,
+                    'microsecond': value.microsecond,
+                }
             else:
                 raise TypeError("SQLite Time type only accepts Python "
                                 "time objects as input.")

lib/sqlalchemy/processors.py

     rmatch = regexp.match
     # Even on python2.6 datetime.strptime is both slower than this code
     # and it does not support microseconds.
+    has_named_groups = bool(regexp.groupindex)
     def process(value):
         if value is None:
             return None
             if m is None:
                 raise ValueError("Couldn't parse %s string: "
                                 "'%s'" % (type_.__name__ , value))
-            return type_(*map(int, m.groups(0)))
+            if has_named_groups:
+                groups = m.groupdict(0)
+                return type_(**dict(zip(groups.iterkeys(),
+                                        map(int, groups.itervalues()))))
+            else:
+                return type_(*map(int, m.groups(0)))
     return process
 
 def boolean_to_int(value):

test/dialect/test_sqlite.py

         rp = sldt.result_processor(None, None)
         eq_(rp(bp(dt)), dt)
 
+    def test_truncate_microseconds(self):
+        dt = datetime.datetime(2008, 6, 27, 12, 0, 0, 125)
+        dt_out = datetime.datetime(2008, 6, 27, 12, 0, 0)
+        eq_(str(dt), '2008-06-27 12:00:00.000125')
+        sldt = sqlite.DATETIME(truncate_microseconds=True)
+        bp = sldt.bind_processor(None)
+        eq_(bp(dt), '2008-06-27 12:00:00')
+        rp = sldt.result_processor(None, None)
+        eq_(rp(bp(dt)), dt_out)
+
+    def test_custom_format_compact(self):
+        dt = datetime.datetime(2008, 6, 27, 12, 0, 0, 125)
+        eq_(str(dt), '2008-06-27 12:00:00.000125')
+        sldt = sqlite.DATETIME(
+            storage_format=(
+                "%(year)04d%(month)02d%(day)02d"
+                "%(hour)02d%(minute)02d%(second)02d%(microsecond)06d"
+            ),
+            regexp="(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})(\d{6})",
+        )
+        bp = sldt.bind_processor(None)
+        eq_(bp(dt), '20080627120000000125')
+        rp = sldt.result_processor(None, None)
+        eq_(rp(bp(dt)), dt)
+
+
+class DateTest(fixtures.TestBase, AssertsCompiledSQL):
+
+    def test_default(self):
+        dt = datetime.date(2008, 6, 27)
+        eq_(str(dt), '2008-06-27')
+        sldt = sqlite.DATE()
+        bp = sldt.bind_processor(None)
+        eq_(bp(dt), '2008-06-27')
+        rp = sldt.result_processor(None, None)
+        eq_(rp(bp(dt)), dt)
+
+    def test_custom_format(self):
+        dt = datetime.date(2008, 6, 27)
+        eq_(str(dt), '2008-06-27')
+        sldt = sqlite.DATE(
+            storage_format="%(month)02d/%(day)02d/%(year)04d",
+            regexp="(?P<month>\d+)/(?P<day>\d+)/(?P<year>\d+)",
+        )
+        bp = sldt.bind_processor(None)
+        eq_(bp(dt), '06/27/2008')
+        rp = sldt.result_processor(None, None)
+        eq_(rp(bp(dt)), dt)
+
+class TimeTest(fixtures.TestBase, AssertsCompiledSQL):
+
+    def test_default(self):
+        dt = datetime.date(2008, 6, 27)
+        eq_(str(dt), '2008-06-27')
+        sldt = sqlite.DATE()
+        bp = sldt.bind_processor(None)
+        eq_(bp(dt), '2008-06-27')
+        rp = sldt.result_processor(None, None)
+        eq_(rp(bp(dt)), dt)
+
+    def test_truncate_microseconds(self):
+        dt = datetime.time(12, 0, 0, 125)
+        dt_out = datetime.time(12, 0, 0)
+        eq_(str(dt), '12:00:00.000125')
+        sldt = sqlite.TIME(truncate_microseconds=True)
+        bp = sldt.bind_processor(None)
+        eq_(bp(dt), '12:00:00')
+        rp = sldt.result_processor(None, None)
+        eq_(rp(bp(dt)), dt_out)
+
+    def test_custom_format(self):
+        dt = datetime.date(2008, 6, 27)
+        eq_(str(dt), '2008-06-27')
+        sldt = sqlite.DATE(
+            storage_format="%(year)04d%(month)02d%(day)02d",
+            regexp="(\d{4})(\d{2})(\d{2})",
+        )
+        bp = sldt.bind_processor(None)
+        eq_(bp(dt), '20080627')
+        rp = sldt.result_processor(None, None)
+        eq_(rp(bp(dt)), dt)
+
 
 class DefaultsTest(fixtures.TestBase, AssertsCompiledSQL):