[sqlite] add support for tz-aware datetime (closes #2777713)

This patch relies on python-dateutil to parse timestamp strings.

authorSylvain Thénault <sylvain.thenault@logilab.fr>
changesetb639b12aaf38
branchdefault
phasepublic
hiddenno
parent revision#0187bf306d97 stop dropping tzinfo attribute on datetime objects (closes #1485893)
child revision#c3dac08a7bb8 [pkg] 1.15.0
files modified by this revision
__pkginfo__.py
debian/control
logilab/database/sqlite.py
python-logilab-database.spec
test/unittest_sqlite.py
# HG changeset patch
# User Sylvain Thénault <sylvain.thenault@logilab.fr>
# Date 1448362049 -3600
# Tue Nov 24 11:47:29 2015 +0100
# Node ID b639b12aaf38dabb738b2f860da6e630c8219281
# Parent 0187bf306d978097e6b2f05a6b4d600f893f5b34
[sqlite] add support for tz-aware datetime (closes #2777713)

This patch relies on python-dateutil to parse timestamp strings.

diff --git a/__pkginfo__.py b/__pkginfo__.py
@@ -38,10 +38,11 @@
1  install_requires = [
2      'setuptools',
3      'logilab-common >= 0.63.2',
4      'six >= 1.4.0',
5      'Yapps2',
6 +    'python-dateutil',
7      ]
8 
9  tests_require = [
10      'psycopg2',
11      ]
diff --git a/debian/control b/debian/control
@@ -22,10 +22,11 @@
12  Depends:
13   ${python:Depends},
14   ${misc:Depends},
15   python-logilab-common (>= 0.63.2),
16   python-six (>= 1.4.0),
17 + python-dateutil,
18  Breaks:
19   cubicweb-server (<< 3.22.0),
20  Description: true unified databases access
21   logilab-database provides some classes to make unified access
22   to different RDBMS possible:
@@ -40,10 +41,11 @@
23  Depends:
24   ${python3:Depends},
25   ${misc:Depends},
26   python3-logilab-common (>= 0.63.2),
27   python3-six (>= 1.4.0),
28 + python3-dateutil,
29  Description: true unified databases access
30   logilab-database provides some classes to make unified access
31   to different RDBMS possible:
32   .
33    * actually compatible db-api from different drivers
diff --git a/logilab/database/sqlite.py b/logilab/database/sqlite.py
@@ -21,19 +21,20 @@
34  """
35  __docformat__ = "restructuredtext en"
36 
37  from warnings import warn
38  from os.path import abspath
39 -import os
40  import re
41  import inspect
42 
43  from six import PY2, text_type
44 +from dateutil import tz, parser
45 
46  from logilab.common.date import strptime
47  from logilab import database as db
48 
49 +
50  class _Sqlite3Adapter(db.DBAPIAdapter):
51      # no type code in sqlite3
52      BINARY = 'XXX'
53      STRING = 'XXX'
54      DATETIME = 'XXX'
@@ -148,10 +149,17 @@
55                                   hours*3600 + minutes*60 + seconds,
56                                   microseconds)
57 
58              sqlite.register_converter('interval', convert_timedelta)
59 
60 +            def convert_tzdatetime(data):
61 +                dt = parser.parse(data)
62 +                if dt.tzinfo is None:
63 +                    dt = dt.replace(tzinfo=tz.tzutc())
64 +                return dt
65 +            sqlite.register_converter('tzdatetime', convert_tzdatetime)
66 +
67 
68      def connect(self, host='', database='', user='', password='', port=None,
69                  schema=None, extra_args=None):
70          """Handles sqlite connection format"""
71          sqlite = self._native_module
@@ -232,10 +240,16 @@
72      intersect_all_support = False
73      alter_column_support = False
74 
75      TYPE_CONVERTERS = db._GenericAdvFuncHelper.TYPE_CONVERTERS.copy()
76 
77 +    TYPE_MAPPING = db._GenericAdvFuncHelper.TYPE_MAPPING.copy()
78 +    TYPE_MAPPING.update({
79 +        'TZTime': 'tztime',
80 +        'TZDatetime': 'tzdatetime',
81 +    })
82 +
83      def backup_commands(self, backupfile, keepownership=True,
84                          dbname=None, dbhost=None, dbport=None, dbuser=None, dbschema=None):
85          dbname = dbname or self.dbname
86          return ['gzip -c %s > %s' % (dbname, backupfile)]
87 
diff --git a/python-logilab-database.spec b/python-logilab-database.spec
@@ -25,10 +25,11 @@
88  Requires:       %{python}
89  Requires:       %{python}-setuptools
90  Requires:       %{python}-logilab-common >= 0.63.2
91  Requires:       %{python}-six >= 1.4.0
92  Requires:       %{python}-yapps2 >= 2.1.1
93 +Requires:       %{python}-dateutil
94  Conflicts:      cubicweb < 3.22.0
95 
96 
97  %description
98  logilab-database provides some classes to make unified access
diff --git a/test/unittest_sqlite.py b/test/unittest_sqlite.py
@@ -1,6 +1,6 @@
99 -# copyright 2003-2011 LOGILAB S.A. (Paris, FRANCE), all rights reserved.
100 +# copyright 2003-2015 LOGILAB S.A. (Paris, FRANCE), all rights reserved.
101  # contact http://www.logilab.fr/ -- mailto:contact@logilab.fr
102  #
103  # This file is part of logilab-database.
104  #
105  # logilab-database is free software: you can redistribute it and/or modify it
@@ -14,34 +14,54 @@
106  # for more details.
107  #
108  # You should have received a copy of the GNU Lesser General Public License along
109  # with logilab-database. If not, see <http://www.gnu.org/licenses/>.
110  import unittest
111 +import sqlite3
112 +from datetime import datetime
113 +
114 +from dateutil.tz import tzutc
115 
116  from logilab.common.testlib import MockConnection
117 
118 -from logilab.database import get_db_helper
119 +from logilab.database import sqlite as lgdbsqlite
120 +from logilab.database import get_connection, get_db_helper
121 
122 
123  class SQLiteHelperTC(unittest.TestCase):
124 
125      def setUp(self):
126          self.cnx = MockConnection( () )
127          self.helper = get_db_helper('sqlite')
128 
129      def test_type_map(self):
130 +        self.assertEqual(self.helper.TYPE_MAPPING['TZDatetime'], 'tzdatetime')
131          self.assertEqual(self.helper.TYPE_MAPPING['Datetime'], 'timestamp')
132          self.assertEqual(self.helper.TYPE_MAPPING['String'], 'text')
133          self.assertEqual(self.helper.TYPE_MAPPING['Password'], 'bytea')
134          self.assertEqual(self.helper.TYPE_MAPPING['Bytes'], 'bytea')
135 
136 +
137  class SQLiteAdapterTC(unittest.TestCase):
138 
139      def test_only_one_lazy_module_initialization(self):
140 -        import sqlite3
141 -        from logilab.database import sqlite as lgdbsqlite
142          self.assertFalse(lgdbsqlite._Sqlite3Adapter._module_is_initialized)
143          adapter = lgdbsqlite._Sqlite3Adapter(sqlite3)
144          self.assertTrue(adapter._module_is_initialized)
145 
146 +    def test_tzsupport(self):
147 +        cnx = get_connection(database=':memory:', driver='sqlite')
148 +        cu = cnx.cursor()
149 +        cu.execute('CREATE TABLE tztest(tzt tzdatetime)')
150 +        now = datetime.now(tzutc())
151 +        cu.execute('INSERT INTO tztest VALUES (%(tzt)s)', {'tzt': now})
152 +        cu.execute('SELECT * FROM tztest')
153 +        dbnow = cu.fetchone()[0]
154 +        self.assertEqual(dbnow, now)
155 +
156 +        cu.execute('UPDATE tztest SET tzt=(%(tzt)s)', {'tzt': datetime.utcnow()})
157 +        cu.execute('SELECT * FROM tztest')
158 +        dbnow = cu.fetchone()[0]
159 +        self.assertEqual(dbnow.tzinfo, tzutc())
160 +
161  if __name__ == '__main__':
162      unittest.main()