Ignore:
Timestamp:
Feb 3, 2016, 3:23:20 PM (4 years ago)
Author:
cito
Message:

Add typecasting of dates, times, timestamps, intervals

So far, PyGreSQL has returned these types only as strings (in various
formats depending on the DateStyle? setting) and left it to the user
to parse and interpret the strings. These types are now properly cast
into the corresponding detetime types of Python, and this works with
any setting of DatesStyle?, even if you change DateStyle? in the middle
of a database session.

To implement this, a fast method for getting the datestyle (cached and
without roundtrip to the database) has been added. Also, the typecast
mechanism has been extended so that typecast functions can optionally
also take the connection as argument.

The date and time typecast functions have been implemented in Python
using the new typecast registry and added to both pg and pgdb. Some
duplication of code in the two modules was unavoidable, since we don't
want the modules to be dependent of each other or install additional
helper modules. One day we might want to change this, put everything
in one package and factor out some of the functionality.

File:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/tests/test_classic_functions.py

    r813 r814  
    2020
    2121import pg  # the module under test
     22
     23from datetime import timedelta
    2224
    2325try:
     
    615617
    616618
     619class TestCastInterval(unittest.TestCase):
     620    """Test the interval typecast function."""
     621
     622    intervals = [
     623        ((0, 0, 0, 1, 0, 0, 0),
     624            ('1:00:00', '01:00:00', '@ 1 hour', 'PT1H')),
     625        ((0, 0, 0, -1, 0, 0, 0),
     626            ('-1:00:00', '-01:00:00', '@ -1 hour', 'PT-1H')),
     627        ((0, 0, 0, 1, 0, 0, 0),
     628            ('0-0 0 1:00:00', '0 years 0 mons 0 days 01:00:00',
     629            '@ 0 years 0 mons 0 days 1 hour', 'P0Y0M0DT1H')),
     630        ((0, 0, 0, -1, 0, 0, 0),
     631            ('-0-0 -1:00:00', '0 years 0 mons 0 days -01:00:00',
     632            '@ 0 years 0 mons 0 days -1 hour', 'P0Y0M0DT-1H')),
     633        ((0, 0, 1, 0, 0, 0, 0),
     634            ('1 0:00:00', '1 day', '@ 1 day', 'P1D')),
     635        ((0, 0, -1, 0, 0, 0, 0),
     636            ('-1 0:00:00', '-1 day', '@ -1 day', 'P-1D')),
     637        ((0, 1, 0, 0, 0, 0, 0),
     638            ('0-1', '1 mon', '@ 1 mon', 'P1M')),
     639        ((1, 0, 0, 0, 0, 0, 0),
     640            ('1-0', '1 year', '@ 1 year', 'P1Y')),
     641        ((0, 0, 0, 2, 0, 0, 0),
     642            ('2:00:00', '02:00:00', '@ 2 hours', 'PT2H')),
     643        ((0, 0, 2, 0, 0, 0, 0),
     644            ('2 0:00:00', '2 days', '@ 2 days', 'P2D')),
     645        ((0, 2, 0, 0, 0, 0, 0),
     646            ('0-2', '2 mons', '@ 2 mons', 'P2M')),
     647        ((2, 0, 0, 0, 0, 0, 0),
     648            ('2-0', '2 years', '@ 2 years', 'P2Y')),
     649        ((0, 0, 0, -3, 0, 0, 0),
     650            ('-3:00:00', '-03:00:00', '@ 3 hours ago', 'PT-3H')),
     651        ((0, 0, -3, 0, 0, 0, 0),
     652            ('-3 0:00:00', '-3 days', '@ 3 days ago', 'P-3D')),
     653        ((0, -3, 0, 0, 0, 0, 0),
     654            ('-0-3', '-3 mons', '@ 3 mons ago', 'P-3M')),
     655        ((-3, 0, 0, 0, 0, 0, 0),
     656            ('-3-0', '-3 years', '@ 3 years ago', 'P-3Y')),
     657        ((0, 0, 0, 0, 1, 0, 0),
     658            ('0:01:00', '00:01:00', '@ 1 min', 'PT1M')),
     659        ((0, 0, 0, 0, 0, 1, 0),
     660            ('0:00:01', '00:00:01', '@ 1 sec', 'PT1S')),
     661        ((0, 0, 0, 0, 0, 0, 1),
     662            ('0:00:00.000001', '00:00:00.000001',
     663             '@ 0.000001 secs', 'PT0.000001S')),
     664        ((0, 0, 0, 0, 2, 0, 0),
     665            ('0:02:00', '00:02:00', '@ 2 mins', 'PT2M')),
     666        ((0, 0, 0, 0, 0, 2, 0),
     667            ('0:00:02', '00:00:02', '@ 2 secs', 'PT2S')),
     668        ((0, 0, 0, 0, 0, 0, 2),
     669            ('0:00:00.000002', '00:00:00.000002',
     670             '@ 0.000002 secs', 'PT0.000002S')),
     671        ((0, 0, 0, 0, -3, 0, 0),
     672            ('-0:03:00', '-00:03:00', '@ 3 mins ago', 'PT-3M')),
     673        ((0, 0, 0, 0, 0, -3, 0),
     674            ('-0:00:03', '-00:00:03', '@ 3 secs ago', 'PT-3S')),
     675        ((0, 0, 0, 0, 0, 0, -3),
     676            ('-0:00:00.000003', '-00:00:00.000003',
     677             '@ 0.000003 secs ago', 'PT-0.000003S')),
     678        ((1, 2, 0, 0, 0, 0, 0),
     679            ('1-2', '1 year 2 mons', '@ 1 year 2 mons', 'P1Y2M')),
     680        ((0, 0, 3, 4, 5, 6, 0),
     681            ('3 4:05:06', '3 days 04:05:06',
     682             '@ 3 days 4 hours 5 mins 6 secs', 'P3DT4H5M6S')),
     683        ((1, 2, 3, 4, 5, 6, 0),
     684            ('+1-2 +3 +4:05:06', '1 year 2 mons 3 days 04:05:06',
     685             '@ 1 year 2 mons 3 days 4 hours 5 mins 6 secs',
     686             'P1Y2M3DT4H5M6S')),
     687        ((1, 2, 3, -4, -5, -6, 0),
     688            ('+1-2 +3 -4:05:06', '1 year 2 mons 3 days -04:05:06',
     689             '@ 1 year 2 mons 3 days -4 hours -5 mins -6 secs',
     690             'P1Y2M3DT-4H-5M-6S')),
     691        ((1, 2, 3, -4, 5, 6, 0),
     692            ('+1-2 +3 -3:54:54', '1 year 2 mons 3 days -03:54:54',
     693             '@ 1 year 2 mons 3 days -3 hours -54 mins -54 secs',
     694             'P1Y2M3DT-3H-54M-54S')),
     695        ((-1, -2, 3, -4, -5, -6, 0),
     696            ('-1-2 +3 -4:05:06', '-1 years -2 mons +3 days -04:05:06',
     697             '@ 1 year 2 mons -3 days 4 hours 5 mins 6 secs ago',
     698             'P-1Y-2M3DT-4H-5M-6S')),
     699        ((1, 2, -3, 4, 5, 6, 0),
     700            ('+1-2 -3 +4:05:06', '1 year 2 mons -3 days +04:05:06',
     701             '@ 1 year 2 mons -3 days 4 hours 5 mins 6 secs',
     702             'P1Y2M-3DT4H5M6S')),
     703        ((0, 0, 0, 1, 30, 0, 0),
     704            ('1:30:00', '01:30:00', '@ 1 hour 30 mins', 'PT1H30M')),
     705        ((0, 0, 0, 3, 15, 45, 123456),
     706            ('3:15:45.123456', '03:15:45.123456',
     707             '@ 3 hours 15 mins 45.123456 secs', 'PT3H15M45.123456S')),
     708        ((0, 0, 0, 3, 15, -5, 123),
     709            ('3:14:55.000123', '03:14:55.000123',
     710             '@ 3 hours 14 mins 55.000123 secs', 'PT3H14M55.000123S')),
     711        ((0, 0, 0, 3, -5, 15, -12345),
     712            ('2:55:14.987655', '02:55:14.987655',
     713             '@ 2 hours 55 mins 14.987655 secs', 'PT2H55M14.987655S')),
     714        ((0, 0, 0, 2, -1, 0, 0),
     715            ('1:59:00', '01:59:00', '@ 1 hour 59 mins', 'PT1H59M')),
     716        ((0, 0, 0, -1, 2, 0, 0),
     717            ('-0:58:00', '-00:58:00', '@ 58 mins ago', 'PT-58M')),
     718        ((1, 11, 0, 0, 0, 0, 0),
     719            ('1-11', '1 year 11 mons', '@ 1 year 11 mons', 'P1Y11M')),
     720        ((0, -10, 0, 0, 0, 0, 0),
     721            ('-0-10', '-10 mons', '@ 10 mons ago', 'P-10M')),
     722        ((0, 0, 2, -1, 0, 0, 0),
     723            ('+0-0 +2 -1:00:00', '2 days -01:00:00',
     724             '@ 2 days -1 hours', 'P2DT-1H')),
     725        ((0, 0, -1, 2, 0, 0, 0),
     726            ('+0-0 -1 +2:00:00', '-1 days +02:00:00',
     727             '@ 1 day -2 hours ago', 'P-1DT2H')),
     728        ((0, 0, 1, 0, 0, 0, 1),
     729            ('1 0:00:00.000001', '1 day 00:00:00.000001',
     730             '@ 1 day 0.000001 secs', 'P1DT0.000001S')),
     731        ((0, 0, 1, 0, 0, 1, 0),
     732            ('1 0:00:01', '1 day 00:00:01', '@ 1 day 1 sec', 'P1DT1S')),
     733        ((0, 0, 1, 0, 1, 0, 0),
     734            ('1 0:01:00', '1 day 00:01:00', '@ 1 day 1 min', 'P1DT1M')),
     735        ((0, 0, 0, 0, 1, 0, -1),
     736            ('0:00:59.999999', '00:00:59.999999',
     737             '@ 59.999999 secs', 'PT59.999999S')),
     738        ((0, 0, 0, 0, -1, 0, 1),
     739            ('-0:00:59.999999', '-00:00:59.999999',
     740             '@ 59.999999 secs ago', 'PT-59.999999S')),
     741        ((0, 0, 0, 0, -1, 1, 1),
     742            ('-0:00:58.999999', '-00:00:58.999999',
     743             '@ 58.999999 secs ago', 'PT-58.999999S')),
     744        ((0, 0, 42, 0, 0, 0, 0),
     745            ('42 0:00:00', '42 days', '@ 42 days', 'P42D')),
     746        ((0, 0, -7, 0, 0, 0, 0),
     747            ('-7 0:00:00', '-7 days', '@ 7 days ago', 'P-7D')),
     748        ((1, 1, 1, 1, 1, 0, 0),
     749            ('+1-1 +1 +1:01:00', '1 year 1 mon 1 day 01:01:00',
     750             '@ 1 year 1 mon 1 day 1 hour 1 min', 'P1Y1M1DT1H1M')),
     751        ((0, -11, -1, -1, 1, 0, 0),
     752            ('-0-11 -1 -0:59:00', '-11 mons -1 days -00:59:00',
     753             '@ 11 mons 1 day 59 mins ago', 'P-11M-1DT-59M')),
     754        ((-1, -1, -1, -1, -1, 0, 0),
     755            ('-1-1 -1 -1:01:00', '-1 years -1 mons -1 days -01:01:00',
     756             '@ 1 year 1 mon 1 day 1 hour 1 min ago', 'P-1Y-1M-1DT-1H-1M')),
     757        ((-1, 0, -3, 1, 0, 0, 0),
     758            ('-1-0 -3 +1:00:00', '-1 years -3 days +01:00:00',
     759             '@ 1 year 3 days -1 hours ago', 'P-1Y-3DT1H')),
     760        ((1, 0, 0, 0, 0, 0, 1),
     761            ('+1-0 +0 +0:00:00.000001', '1 year 00:00:00.000001',
     762             '@ 1 year 0.000001 secs', 'P1YT0.000001S')),
     763        ((1, 0, 0, 0, 0, 0, -1),
     764            ('+1-0 +0 -0:00:00.000001', '1 year -00:00:00.000001',
     765             '@ 1 year -0.000001 secs', 'P1YT-0.000001S')),
     766        ((1, 2, 3, 4, 5, 6, 7),
     767            ('+1-2 +3 +4:05:06.000007',
     768             '1 year 2 mons 3 days 04:05:06.000007',
     769             '@ 1 year 2 mons 3 days 4 hours 5 mins 6.000007 secs',
     770             'P1Y2M3DT4H5M6.000007S')),
     771        ((0, 10, 3, -4, 5, -6, 7),
     772            ('+0-10 +3 -3:55:05.999993', '10 mons 3 days -03:55:05.999993',
     773             '@ 10 mons 3 days -3 hours -55 mins -5.999993 secs',
     774             'P10M3DT-3H-55M-5.999993S')),
     775        ((0, -10, -3, 4, -5, 6, -7),
     776            ('-0-10 -3 +3:55:05.999993',
     777             '-10 mons -3 days +03:55:05.999993',
     778             '@ 10 mons 3 days -3 hours -55 mins -5.999993 secs ago',
     779             'P-10M-3DT3H55M5.999993S'))]
     780
     781    def testCastInterval(self):
     782        for result, values in self.intervals:
     783            f = pg.cast_interval
     784            years, mons, days, hours, mins, secs, usecs = result
     785            days += 365 * years + 30 * mons
     786            interval = timedelta(days=days, hours=hours, minutes=mins,
     787                seconds=secs, microseconds=usecs)
     788            for value in values:
     789                self.assertEqual(f(value), interval)
     790
     791
    617792class TestEscapeFunctions(unittest.TestCase):
    618793    """Test pg escape and unescape functions.
     
    677852    """
    678853
     854    def testGetDatestyle(self):
     855        self.assertIsNone(pg.get_datestyle())
     856
     857    def testGetDatestyle(self):
     858        datestyle = pg.get_datestyle()
     859        try:
     860            pg.set_datestyle('ISO, YMD')
     861            self.assertEqual(pg.get_datestyle(), 'ISO, YMD')
     862            pg.set_datestyle('Postgres, MDY')
     863            self.assertEqual(pg.get_datestyle(), 'Postgres, MDY')
     864            pg.set_datestyle('Postgres, DMY')
     865            self.assertEqual(pg.get_datestyle(), 'Postgres, DMY')
     866            pg.set_datestyle('SQL, MDY')
     867            self.assertEqual(pg.get_datestyle(), 'SQL, MDY')
     868            pg.set_datestyle('SQL, DMY')
     869            self.assertEqual(pg.get_datestyle(), 'SQL, DMY')
     870            pg.set_datestyle('German, DMY')
     871            self.assertEqual(pg.get_datestyle(), 'German, DMY')
     872            pg.set_datestyle(None)
     873            self.assertIsNone(pg.get_datestyle())
     874        finally:
     875            pg.set_datestyle(datestyle)
     876
    679877    def testGetDecimalPoint(self):
    680878        r = pg.get_decimal_point()
Note: See TracChangeset for help on using the changeset viewer.