natedub avatar natedub committed 4ea04df

Improve SQLite DATETIME storage format handling [ticket:2363]

This breaks backwards compatibility with old SQLite DATETIME, DATE,
and TIME storage_format strings. Formatting now occurs with named instead
of positional parameters. The regexp argument can still use positional
arguments, but named groupings are also supported. This means that you can
omit fields and change the order of date fields as desired.

SQLite's DATETIME and TIME also gained a truncate_microseconds argument.
This is shorthand for modifying the format string. Fortunately the
str_to_datetime and str_to_time processors written in C already support
omitting microseconds, so we don't have to resort to python processing
for this case.

Comments (0)

Files changed (3)

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 appled 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 appled 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 appled 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 appled 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 appled 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 appled 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 appled 
-     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 appled 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 appled 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 appled 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):
 
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.